Postgres 16

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?.
select setseed(.42);

create extension if not exists postgis;
create schema if not exists __test;
drop table if exists __test.table_grid;
create table __test.table_grid(data1 int, geom geometry(Polygon));

insert into __test.table_grid(geom)
select st_makepolygon(st_makeline(array[st_makepoint(x  ,y  ),
                                        st_makepoint(x+1,y  ),
                                        st_makepoint(x+1,y+1),
                                        st_makepoint(x  ,y+1),
                                        st_makepoint(x  ,y  ) ]))
from generate_series(1,2e2)x,
     generate_series(1,1e2)y;

create index on __test.table_grid 
  using gist(geom)include(data1)with(fillfactor=100);

drop table if exists __test.table_administrative;
create table __test.table_administrative(data1 int, geom geometry(MultiPolygon));

with random_sub_areas as (
  select st_voronoipolygons(
            st_collect(
               st_makepoint(random()*2e2,
                            random()*1e2)
            ),
            .001)
   from generate_series(1,1e4))
,sub_area_polygons as (
  select (st_dump(st_voronoipolygons)).geom as geom
  from random_sub_areas )
,sub_area_polygons_grouped as (
  select st_union(geom), 
         ((st_x(st_centroid(geom)) + 6*random()-3)/10)::int x_group, 
         ((st_y(st_centroid(geom)) + 6*random()-3)/10)::int y_group
  from sub_area_polygons
  group by x_group,y_group )
insert into __test.table_administrative (data1,geom)
select row_number()over(),st_multi(st_union)
from sub_area_polygons_grouped;

create index on __test.table_administrative 
  using gist(geom)include(data1)with(fillfactor=100);

setseed

SELECT 1

CREATE EXTENSION

CREATE SCHEMA

DROP TABLE

CREATE TABLE

INSERT 0 20000

CREATE INDEX

DROP TABLE

CREATE TABLE

INSERT 0 380

CREATE INDEX

explain analyze verbose
update __test.table_grid 
set data1 = table_administrative.data1 
from __test.table_administrative 
where ST_Intersects(table_grid.geom, table_administrative.geom);

QUERY PLAN

Update on __test.table_grid  (cost=0.28..11651.20 rows=0 width=0) (actual time=516.002..516.003 rows=0 loops=1)

  ->  Nested Loop  (cost=0.28..11651.20 rows=7600 width=16) (actual time=0.127..264.743 rows=33382 loops=1)

        Output: table_administrative.data1, table_grid.ctid, table_administrative.ctid

        ->  Seq Scan on __test.table_administrative  (cost=0.00..65.80 rows=380 width=42) (actual time=0.007..0.298 rows=380 loops=1)

              Output: table_administrative.data1, table_administrative.ctid, table_administrative.geom

        ->  Index Scan using table_grid_geom_data1_idx on __test.table_grid  (cost=0.28..30.47 rows=2 width=38) (actual time=0.058..0.677 rows=88 loops=380)

              Output: table_grid.ctid, table_grid.geom

              Index Cond: (table_grid.geom && table_administrative.geom)

              Filter: st_intersects(table_grid.geom, table_administrative.geom)

              Rows Removed by Filter: 55

Planning Time: 0.401 ms

Execution Time: 516.604 ms

EXPLAIN

--predictable assignment
explain analyze verbose
with cte as (select distinct on (tg.ctid) tg.ctid, ta.data1
             from __test.table_grid tg 
             join __test.table_administrative ta
               on ST_Intersects(tg.geom,ta.geom)
             order by tg.ctid, tg.geom <<->> ta.geom)
update __test.table_grid as this
set data1 = cte.data1
from cte
where this.ctid=cte.ctid;

QUERY PLAN

Update on __test.table_grid this  (cost=24619.70..25888.28 rows=0 width=0) (actual time=716.238..716.242 rows=0 loops=1)

  ->  Hash Join  (cost=24619.70..25888.28 rows=15639 width=44) (actual time=481.928..496.712 rows=20000 loops=1)

        Output: cte.data1, this.ctid, cte.*

        Inner Unique: true

        Hash Cond: (this.ctid = cte.ctid)

        ->  Seq Scan on __test.table_grid this  (cost=0.00..1160.54 rows=41154 width=6) (actual time=1.453..6.627 rows=20000 loops=1)

              Output: this.ctid

        ->  Hash  (cost=24424.21..24424.21 rows=15639 width=44) (actual time=480.446..480.448 rows=20000 loops=1)

              Output: cte.data1, cte.*, cte.ctid

              Buckets: 32768 (originally 16384)  Batches: 1 (originally 1)  Memory Usage: 1741kB

              ->  Subquery Scan on cte  (cost=24189.63..24424.21 rows=15639 width=44) (actual time=466.838..477.155 rows=20000 loops=1)

                    Output: cte.data1, cte.*, cte.ctid

                    ->  Unique  (cost=24189.63..24267.82 rows=15639 width=18) (actual time=466.826..472.409 rows=20000 loops=1)

                          Output: tg.ctid, ta.data1, ((tg.geom <<->> ta.geom))

                          ->  Sort  (cost=24189.63..24228.73 rows=15639 width=18) (actual time=466.825..468.818 rows=33382 loops=1)

                                Output: tg.ctid, ta.data1, ((tg.geom <<->> ta.geom))

                                Sort Key: tg.ctid, ((tg.geom <<->> ta.geom))

                                Sort Method: quicksort  Memory: 2931kB

                                ->  Nested Loop  (cost=0.28..23100.15 rows=15639 width=18) (actual time=0.105..449.088 rows=33382 loops=1)

                                      Output: tg.ctid, ta.data1, (tg.geom <<->> ta.geom)

                                      ->  Seq Scan on __test.table_administrative ta  (cost=0.00..65.80 rows=380 width=36) (actual time=0.004..0.205 rows=380 loops=1)

                                            Output: ta.data1, ta.geom

                                      ->  Index Scan using table_grid_geom_data1_idx on __test.table_grid tg  (cost=0.28..60.47 rows=4 width=38) (actual time=0.054..0.602 rows=88 loops=380)

                                            Output: tg.ctid, tg.geom

                                            Index Cond: (tg.geom && ta.geom)

                                            Filter: st_intersects(tg.geom, ta.geom)

                                            Rows Removed by Filter: 55

Planning Time: 0.296 ms

Execution Time: 716.343 ms

EXPLAIN

--downgrading to plain && doesn't improve things
explain analyze verbose
with cte as (select distinct on (tg.ctid) tg.ctid, ta.data1
             from __test.table_grid tg 
             join __test.table_administrative ta
               on tg.geom && ta.geom
             order by tg.ctid, tg.geom <<->> ta.geom)
update __test.table_grid as this
set data1 = cte.data1
from cte
where this.ctid=cte.ctid;

QUERY PLAN

Update on __test.table_grid this  (cost=7925.47..9844.42 rows=0 width=0) (actual time=875.906..875.911 rows=0 loops=1)

  ->  Hash Join  (cost=7925.47..9844.42 rows=23656 width=44) (actual time=660.029..674.988 rows=20000 loops=1)

        Output: cte.data1, this.ctid, cte.*

        Inner Unique: true

        Hash Cond: (this.ctid = cte.ctid)

        ->  Seq Scan on __test.table_grid this  (cost=0.00..1755.53 rows=62253 width=6) (actual time=0.184..5.581 rows=20000 loops=1)

              Output: this.ctid

        ->  Hash  (cost=7629.77..7629.77 rows=23656 width=44) (actual time=659.815..659.818 rows=20000 loops=1)

              Output: cte.data1, cte.*, cte.ctid

              Buckets: 32768  Batches: 1  Memory Usage: 1741kB

              ->  Subquery Scan on cte  (cost=7274.93..7629.77 rows=23656 width=44) (actual time=643.960..656.612 rows=20000 loops=1)

                    Output: cte.data1, cte.*, cte.ctid

                    ->  Unique  (cost=7274.93..7393.21 rows=23656 width=18) (actual time=643.950..651.932 rows=20000 loops=1)

                          Output: tg.ctid, ta.data1, ((tg.geom <<->> ta.geom))

                          ->  Sort  (cost=7274.93..7334.07 rows=23656 width=18) (actual time=643.948..646.946 rows=54192 loops=1)

                                Output: tg.ctid, ta.data1, ((tg.geom <<->> ta.geom))

                                Sort Key: tg.ctid, ((tg.geom <<->> ta.geom))

                                Sort Method: quicksort  Memory: 3906kB

                                ->  Nested Loop  (cost=2.62..5556.33 rows=23656 width=18) (actual time=0.085..622.026 rows=54192 loops=1)

                                      Output: tg.ctid, ta.data1, (tg.geom <<->> ta.geom)

                                      ->  Seq Scan on __test.table_administrative ta  (cost=0.00..65.80 rows=380 width=36) (actual time=0.004..0.197 rows=380 loops=1)

                                            Output: ta.data1, ta.geom

                                      ->  Bitmap Heap Scan on __test.table_grid tg  (cost=2.62..14.23 rows=6 width=38) (actual time=0.076..0.114 rows=143 loops=380)

                                            Output: tg.ctid, tg.geom

                                            Recheck Cond: (tg.geom && ta.geom)

                                            Heap Blocks: exact=4415

                                            ->  Bitmap Index Scan on table_grid_geom_data1_idx  (cost=0.00..2.62 rows=6 width=0) (actual time=0.060..0.060 rows=285 loops=380)

                                                  Index Cond: (tg.geom && ta.geom)

Planning Time: 0.379 ms

Execution Time: 876.001 ms

EXPLAIN

--for random assignment, && will be even faster
explain analyze verbose
update __test.table_grid 
set data1 = table_administrative.data1 
from __test.table_administrative
where table_grid.geom && table_administrative.geom;

QUERY PLAN

Update on __test.table_grid  (cost=3.01..7122.01 rows=0 width=0) (actual time=347.388..347.389 rows=0 loops=1)

  ->  Nested Loop  (cost=3.01..7122.01 rows=31674 width=16) (actual time=0.081..65.619 rows=54192 loops=1)

        Output: table_administrative.data1, table_grid.ctid, table_administrative.ctid

        ->  Seq Scan on __test.table_administrative  (cost=0.00..65.80 rows=380 width=42) (actual time=0.008..0.225 rows=380 loops=1)

              Output: table_administrative.data1, table_administrative.ctid, table_administrative.geom

        ->  Bitmap Heap Scan on __test.table_grid  (cost=3.01..18.49 rows=8 width=38) (actual time=0.101..0.153 rows=143 loops=380)

              Output: table_grid.ctid, table_grid.geom

              Recheck Cond: (table_grid.geom && table_administrative.geom)

              Heap Blocks: exact=4388

              ->  Bitmap Index Scan on table_grid_geom_data1_idx  (cost=0.00..3.00 rows=8 width=0) (actual time=0.076..0.076 rows=517 loops=380)

                    Index Cond: (table_grid.geom && table_administrative.geom)

Planning Time: 0.103 ms

Execution Time: 347.430 ms

EXPLAIN