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,
| 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