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 buffer_table (gid int,geom geometry(multipolygon,4326));
INSERT INTO buffer_table VALUES
(1,'SRID=4326;MULTIPOLYGON(((-4.46003675 54.16624832,-4.46027708 54.16624832,-4.46027708 54.16617966,-4.46027708 54.16597366,-4.46101761 54.16597366,-4.46194601 54.16597366,-4.46194601 54.16576385,-4.46194601 54.16569519,-4.46228552 54.16569519,-4.462502 54.16569519,-4.462502 54.1656456,-4.462502 54.16541672,-4.46337223 54.16541672,-4.46361208 54.16541672,-4.46361208 54.16569519,-4.46416807 54.16569519,-4.46416807 54.16541672,-4.46472216 54.16541672,-4.46472216 54.16513824,-4.46583414 54.16513824,-4.46583414 54.16485977,-4.46694613 54.16485977,-4.46694613 54.16430664,-4.46750021 54.16430664,-4.46750021 54.16402817,-4.4680562 54.16402817,-4.4680562 54.16374969,-4.46861219 54.16374969,-4.46861219 54.16319275,-4.4691658 54.16319275,-4.4691658 54.16263962,-4.46972179 54.16263962,-4.46972179 54.16236115,-4.47083378 54.16236115,-4.47083378 54.1618042,-4.47138786 54.1618042,-4.47138786 54.16125107,-4.47194386 54.16125107,-4.47194386 54.1609726,-4.47249985 54.1609726,-4.47249985 54.16041565,-4.47305584 54.16041565,-4.47305584 54.15986252,-4.47360992 54.15986252,-4.47360992 54.15930557,-4.47416592 54.15930557,-4.47416592 54.1590271,-4.47472191 54.1590271,-4.47472191 54.15874863,-4.47527885 54.15874863,-4.47527885 54.1581955,-4.47583199 54.1581955,-4.47583199 54.1570816,-4.47638798 54.1570816,-4.47638798 54.15680695,-4.47694492 54.15680695,-4.47694492 54.15625,-4.47750092 54.15625,-4.47750092 54.15597153,-4.478055 54.15597153,-4.478055 54.15569305,-4.47749805 54.15569305,-4.47750092 54.1534729,-4.47694492 54.1534729,-4.47694492 54.15041733,-4.47638798 54.15041733,-4.47638798 54.15013885,-4.47583199 54.15013885,-4.47583199 54.14986038,-4.47527885 54.14986038,-4.47527885 54.14958191,-4.47472191 54.14958191,-4.47472191 54.14930725,-4.47416592 54.14930725,-4.47416592 54.14902878,-4.47255325 54.14902878,-4.47249985 54.14902878,-4.47249985 54.14875031,-4.4708786 54.14875031,-4.47083378 54.14875031,-4.47083378 54.14847183,-4.46936655 54.14847183,-4.4691658 54.14847183,-4.4691658 54.14842606,-4.4691658 54.14819336,-4.46861219 54.14819336,-4.46861219 54.14791489,-4.46750021 54.14791489,-4.46750021 54.14764023,-4.46694613 54.14764023,-4.46694613 54.14736176,-4.466

CREATE TABLE point_table (gid int, geom geometry (point,4326));
INSERT INTO point_table
SELECT j.path[1], j.geom
FROM buffer_table,LATERAL ST_Dump(ST_GeneratePoints(ST_Buffer(geom,1),10000)) j;

CREATE INDEX buffer_table_geom_idx ON buffer_table USING GIST (geom);
CREATE INDEX point_table_geom_idx ON point_table USING GIST (geom);

CREATE TABLE

INSERT 0 1

CREATE TABLE

INSERT 0 10000

CREATE INDEX

CREATE INDEX

-- your query --
EXPLAIN (ANALYSE,COSTS OFF)
SELECT
    point_table.*,
    buffer_table.gid
FROM
    point_table
    LEFT JOIN buffer_table ON ST_Contains (buffer_table.geom, point_table.geom);

QUERY PLAN

Nested Loop Left Join (actual time=0.237..49.227 rows=10000 loops=1)

  Join Filter: st_contains(buffer_table.geom, point_table.geom)

  Rows Removed by Join Filter: 9831

  ->  Seq Scan on point_table (actual time=0.015..2.140 rows=10000 loops=1)

  ->  Materialize (actual time=0.000..0.001 rows=1 loops=10000)

        ->  Seq Scan on buffer_table (actual time=0.005..0.006 rows=1 loops=1)

Planning Time: 8.731 ms

Execution Time: 49.706 ms

EXPLAIN

EXPLAIN (ANALYSE,COSTS OFF)
SELECT buffer_table.gid, point_table.*
FROM point_table
JOIN buffer_table ON ST_Contains (buffer_table.geom, point_table.geom); 

QUERY PLAN

Nested Loop (actual time=3.808..6.969 rows=169 loops=1)

  ->  Seq Scan on buffer_table (actual time=0.007..0.008 rows=1 loops=1)

  ->  Bitmap Heap Scan on point_table (actual time=3.795..5.555 rows=169 loops=1)

        Filter: st_contains(buffer_table.geom, geom)

        Rows Removed by Filter: 274

        Heap Blocks: exact=83

        ->  Bitmap Index Scan on point_table_geom_idx (actual time=0.405..0.405 rows=443 loops=1)

              Index Cond: (geom @ buffer_table.geom)

Planning Time: 0.368 ms

Execution Time: 11.252 ms

EXPLAIN

CREATE TABLE buffer_table_divided AS
SELECT gid,ST_SubDivide(geom) AS geom FROM buffer_table

SELECT 34

EXPLAIN ANALYSE
SELECT point_table.*
FROM point_table
JOIN buffer_table_divided d ON ST_Contains (d.geom, point_table.geom);

QUERY PLAN

Nested Loop  (cost=0.15..318564.15 rows=12700 width=36) (actual time=0.176..5.942 rows=169 loops=1)

  ->  Seq Scan on buffer_table_divided d  (cost=0.00..22.70 rows=1270 width=32) (actual time=0.010..0.035 rows=34 loops=1)

  ->  Index Scan using point_table_geom_idx on point_table  (cost=0.15..250.81 rows=1 width=36) (actual time=0.099..0.169 rows=5 loops=34)

        Index Cond: (geom @ d.geom)

        Filter: st_contains(d.geom, geom)

        Rows Removed by Filter: 1

Planning Time: 0.213 ms

Execution Time: 5.984 ms

EXPLAIN