By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
version |
---|
PostgreSQL 13.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
SELECT 1
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE FUNCTION
CREATE TYPE
CREATE INDEX
QUERY PLAN |
---|
Bitmap Heap Scan on ip_range_domains (cost=6.20..17.22 rows=68 width=105) (actual time=0.414..0.414 rows=0 loops=1) |
Recheck Cond: (('200.8.8.8'::inet >= low) AND ('200.8.8.8'::inet <= high)) |
Buffers: shared hit=1 |
-> Bitmap Index Scan on idx_ip_range_inversed (cost=0.00..6.18 rows=68 width=0) (actual time=0.123..0.123 rows=0 loops=1) |
Index Cond: ((low <= '200.8.8.8'::inet) AND (high >= '200.8.8.8'::inet)) |
Buffers: shared hit=1 |
Planning: |
Buffers: shared hit=54 read=7 |
Planning Time: 2.446 ms |
Execution Time: 0.607 ms |
EXPLAIN
QUERY PLAN |
---|
Bitmap Heap Scan on ip_range_domains (cost=4.17..11.28 rows=3 width=105) (actual time=0.129..0.129 rows=0 loops=1) |
Recheck Cond: ('200.8.8.8'::inet <@ inetrange(low, high, '[]'::text)) |
Buffers: shared hit=1 |
-> Bitmap Index Scan on idx_ip_range_gist (cost=0.00..4.17 rows=3 width=0) (actual time=0.126..0.127 rows=0 loops=1) |
Index Cond: (inetrange(low, high, '[]'::text) @> '200.8.8.8'::inet) |
Buffers: shared hit=1 |
Planning: |
Buffers: shared hit=15 read=2 |
Planning Time: 1.310 ms |
Execution Time: 0.350 ms |
EXPLAIN