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(
  geom geometry(Polygon),
  match_col1 text,
  match_col2 text,
  match_col3 text,
  col_adm text)with(fillfactor=50);

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,200)x,
     generate_series(1,100)y;

create index on __test.table_grid 
  using gist(geom)with(fillfactor=100);
create index on __test.table_grid 
  (match_col1,match_col2,match_col3)
  with(fillfactor=100);

drop table if exists __test.table_administrative;
create table __test.table_administrative(
  geom geometry(MultiPolygon),
  match_col1 text,
  match_col2 text,
  match_col3 text,
  col_adm text);

with random_sub_areas as (
  select st_voronoipolygons(
            st_collect(
               st_makepoint(random()*200,
                            random()*100)
            ),
            .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
select st_multi(st_union) as geom, 
  random() as match_col1,
  random() as match_col2,
  random() as match_col3,
  row_number()over() as col_adm
from sub_area_polygons_grouped;

create index on __test.table_administrative 
  using gist(geom)
  include(col_adm)
  with(fillfactor=100);
create index on __test.table_administrative 
  (match_col1,match_col2,match_col3)
  include(col_adm)
  with(fillfactor=100);

setseed

SELECT 1

CREATE EXTENSION

CREATE SCHEMA

DROP TABLE

CREATE TABLE

INSERT 0 20000

CREATE INDEX

CREATE INDEX

DROP TABLE

CREATE TABLE

INSERT 0 380

CREATE INDEX

CREATE INDEX

vacuum analyze __test.table_grid;

VACUUM

vacuum analyze __test.table_administrative;

VACUUM

--preparing equal match for both the text columns and the intersection
explain analyze verbose
update __test.table_grid 
set match_col1 = table_administrative.match_col1,
    match_col2 = table_administrative.match_col2,
    match_col3 = table_administrative.match_col3,
    col_adm    = table_administrative.col_adm
from __test.table_administrative 
where ST_Intersects(table_grid.geom, table_administrative.geom);

QUERY PLAN

Update on __test.table_grid  (cost=0.15..12045.70 rows=0 width=0) (actual time=563.326..563.327 rows=0 loops=1)

  ->  Nested Loop  (cost=0.15..12045.70 rows=8722 width=72) (actual time=0.115..268.295 rows=33382 loops=1)

        Output: table_administrative.match_col1, table_administrative.match_col2, table_administrative.match_col3, table_administrative.col_adm, table_grid.ctid, table_administrative.ctid

        ->  Seq Scan on __test.table_administrative  (cost=0.00..67.80 rows=380 width=1265) (actual time=0.006..0.336 rows=380 loops=1)

              Output: table_administrative.match_col1, table_administrative.match_col2, table_administrative.match_col3, table_administrative.col_adm, table_administrative.ctid, table_administrative.geom

        ->  Index Scan using table_grid_geom_idx on __test.table_grid  (cost=0.15..31.50 rows=2 width=126) (actual time=0.059..0.686 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.744 ms

Execution Time: 563.841 ms

EXPLAIN

create index on __test.table_grid 
  (match_col1,match_col2,match_col3)with(fillfactor=100);

CREATE INDEX

vacuum analyze __test.table_grid;

VACUUM

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

QUERY PLAN

Update on __test.table_grid  (cost=0.28..12677.20 rows=0 width=0) (actual time=310.195..310.196 rows=0 loops=1)

  ->  Nested Loop  (cost=0.28..12677.20 rows=8722 width=15) (actual time=0.075..224.552 rows=33382 loops=1)

        Output: table_administrative.col_adm, table_grid.ctid, table_administrative.ctid

        ->  Seq Scan on __test.table_administrative  (cost=0.00..67.80 rows=380 width=1208) (actual time=0.006..0.249 rows=380 loops=1)

              Output: table_administrative.col_adm, table_administrative.ctid, table_administrative.geom

        ->  Index Scan using table_grid_geom_idx on __test.table_grid  (cost=0.28..33.16 rows=2 width=126) (actual time=0.044..0.574 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.524 ms

Execution Time: 310.241 ms

EXPLAIN

vacuum analyze __test.table_grid;

VACUUM

explain analyze verbose
update __test.table_grid as this
set col_adm = that.col_adm
from __test.table_administrative as that
where this.match_col1 = that.match_col1
  and this.match_col2 = that.match_col2
  and this.match_col3 = that.match_col3;

QUERY PLAN

Update on __test.table_grid this  (cost=0.29..1565.40 rows=0 width=0) (actual time=49.625..49.626 rows=0 loops=1)

  ->  Nested Loop  (cost=0.29..1565.40 rows=1 width=15) (actual time=0.017..13.174 rows=20000 loops=1)

        Output: that.col_adm, this.ctid, that.ctid

        ->  Seq Scan on __test.table_administrative that  (cost=0.00..67.80 rows=380 width=66) (actual time=0.006..0.193 rows=380 loops=1)

              Output: that.col_adm, that.ctid, that.match_col1, that.match_col2, that.match_col3

        ->  Index Scan using table_grid_match_col1_match_col2_match_col3_idx1 on __test.table_grid this  (cost=0.29..3.93 rows=1 width=63) (actual time=0.003..0.028 rows=53 loops=380)

              Output: this.ctid, this.match_col1, this.match_col2, this.match_col3

              Index Cond: ((this.match_col1 = that.match_col1) AND (this.match_col2 = that.match_col2) AND (this.match_col3 = that.match_col3))

Planning Time: 0.579 ms

Execution Time: 49.667 ms

EXPLAIN