Postgres 12

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?.

CREATE TABLE t (geom geometry(point,4326));
INSERT INTO t 
SELECT ('SRID=4326;POINT('||floor(random() * 50)||' ' ||floor(random() * 50) ||')')
FROM generate_series(1,50000);

CREATE TABLE

INSERT 0 50000

-- Without gist index
EXPLAIN ANALYSE
SELECT * FROM t
WHERE ST_DWithin('SRID=4326;POINT(1 1)',geom,1);

QUERY PLAN

Seq Scan on t  (cost=0.00..1252068.48 rows=5 width=32) (actual time=0.328..68.287 rows=76 loops=1)

  Filter: st_dwithin('0101000020E6100000000000000000F03F000000000000F03F'::geometry, geom, '1'::double precision)

  Rows Removed by Filter: 49924

Planning Time: 0.471 ms

Execution Time: 68.341 ms

EXPLAIN

CREATE INDEX idx_t_geom ON t USING gist (geom);

CREATE INDEX

-- With gist index 
EXPLAIN ANALYSE
SELECT * FROM t
WHERE ST_DWithin('SRID=4326;POINT(1 1)',geom,1);

QUERY PLAN

Bitmap Heap Scan on t  (cost=4.44..147.74 rows=5 width=32) (actual time=0.118..0.449 rows=76 loops=1)

  Filter: st_dwithin('0101000020E6100000000000000000F03F000000000000F03F'::geometry, geom, '1'::double precision)

  Rows Removed by Filter: 83

  Heap Blocks: exact=128

  ->  Bitmap Index Scan on idx_t_geom  (cost=0.00..4.44 rows=5 width=0) (actual time=0.078..0.078 rows=159 loops=1)

        Index Cond: (geom && st_expand('0101000020E6100000000000000000F03F000000000000F03F'::geometry, '1'::double precision))

Planning Time: 1.115 ms

Execution Time: 1.319 ms

EXPLAIN