[PostGIS] #5953: Slow st_intersection with big input polygons
PostGIS
trac at osgeo.org
Sun Jul 27 13:14:46 PDT 2025
#5953: Slow st_intersection with big input polygons
--------------------------------+---------------------------
Reporter: Lars Aksel Opsahl | Owner: pramsey
Type: enhancement | Status: new
Priority: medium | Milestone: PostGIS 3.6.0
Component: postgis | Version: master
Resolution: | Keywords:
--------------------------------+---------------------------
Comment (by Lars Aksel Opsahl):
The tables in tests below can be found here
[https://matrix.to/#/!ZmSluDJMNfyTwfQEJh:osgeo.org/$TZUmxHaH5sxH0O4fBLmrmfKst9y3EekDmkmgUSDgQQQ?via=dend.ro&via=osgeo.org&via=matrix.org]
. I have simplified the data lot so I was able to get below 100 MB.
Table klima.gk_02 only contains data covered by an single row in
klima.plantegning_3035_test .
Doing a intersection here with out using subdivide takes to long time. And
if remove all holes in the polygon it goes extremly slow.
I am now testing on
{{{
PostgreSQL 16.4 (Ubuntu 16.4-1.pgdg22.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
POSTGIS="3.6.0beta1 3.6.0beta1-10-ge0e19372b" [EXTENSION] PGSQL="160"
GEOS="3.13.1-CAPI-1.19.2" PROJ="9.3.0 NETWORK_ENABLED=OFF
URL_ENDPOINT=https://cdn.proj.org USER_WRITABLE_DIRECTORY=/tmp/proj
DATABASE_PATH=/usr/local/share/proj/proj.db" (compiled against PROJ 9.3.0)
LIBXML="2.9.13" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0
(Internal)" TOPOLOGY
}}}
Here are some tests I have run with analyze explain for this tables to
show the problems. 400 slower with out subdivide and many thousand times
slower if I remove the holes.
If this expected behavior we then always have to some preprossing job
before starting involving uploaded from an client.
{{{
explain analyze select sum(ST_area(st_intersection(gk.geom, pt.geo)))
FROM
(select st_subdivide(geo,300) as geo from klima.plantegning_3035_test) pt,
klima.gk_02 AS gk
WHERE ST_Intersects(gk.geom, pt.geo);
Aggregate (cost=4590314.56..4590314.57 rows=1 width=8) (actual
time=15769.839..15769.841 rows=1 loops=1)
-> Nested Loop (cost=0.28..434377.01 rows=329118 width=488)
(actual time=1434.086..2749.967 rows=340682 loops=1)
-> ProjectSet (cost=0.00..18.02
rows=1000 width=32) (actual time=1433.803..1437.009 rows=1208 loops=1)
-> Seq Scan on
plantegning_3035_test (cost=0.00..0.51 rows=1 width=32) (actual
time=148.764..148.767 rows=1 loops=1)
-> Index Scan using gk_02_geom_idx on
gk_02 gk (cost=0.28..434.03 rows=33 width=456) (actual time=0.058..1.045
rows=282 loops=1208)
Index Cond: (geom
&& (st_subdivide(plantegning_3035_test.geo, 300, '-1'::double precision)))
Filter:
st_intersects(geom, (st_subdivide(plantegning_3035_test.geo, 300,
'-1'::double precision)))
Rows Removed by
Filter: 12
Planning Time: 3.362 ms
JIT:
Functions: 14
Options: Inlining true, Optimization true, Expressions true,
Deforming true
Timing: Generation 1.106 ms, Inlining 33.650 ms, Optimization
71.171 ms, Emission 44.014 ms, Total 149.942 ms
Execution Time: 15771.090 ms
(14 rows)
explain analyze select sum(ST_area(st_intersection(gk.geom, pt.geo)))
FROM
klima.plantegning_3035_test AS pt,
klima.gk_02 AS gk
WHERE ST_Intersects(gk.geom, pt.geo);
Aggregate (cost=4602.65..4602.66 rows=1 width=8) (actual
time=6332557.780..6332557.783 rows=1 loops=1)
-> Nested Loop (cost=0.28..448.20 rows=329 width=488) (actual
time=6.110..11408.753 rows=329118 loops=1)
-> Seq Scan on plantegning_3035_test pt
(cost=0.00..0.51 rows=1 width=32) (actual time=0.020..0.024 rows=1
loops=1)
-> Index Scan using gk_02_geom_idx on
gk_02 gk (cost=0.28..447.36 rows=33 width=456) (actual
time=4.399..10786.337 rows=329118 loops=1)
Index Cond: (geom
&& pt.geo)
Filter:
st_intersects(geom, pt.geo)
Planning Time: 1.400 ms
Execution Time: 6332558.277 ms
(8 rows)
explain analyze select sum(ST_area(st_intersection(gk.geom, pt.geo)))
FROM
( select ST_BuildArea(ST_Boundary(geo)) as geo from
klima.plantegning_3035_test) pt,
klima.gk_02 AS gk
WHERE ST_Intersects(gk.geom, pt.geo)
Aggregate (cost=9367.02..9367.03 rows=1 width=8) (actual
time=78854253.350..78854253.352 rows=1 loops=1)
-> Nested Loop (cost=13.41..894.45 rows=329 width=488) (actual
time=284.957..36249118.814 rows=329118 loops=1)
-> Seq Scan on plantegning_3035_test
(cost=0.00..0.51 rows=1 width=32) (actual time=0.016..0.019 rows=1
loops=1)
-> Index Scan using gk_02_geom_idx on
gk_02 gk (cost=13.41..893.61 rows=33 width=456) (actual
time=156.118..36247941.787 rows=329118 loops=1)
Index Cond: (geom
&& st_buildarea(st_boundary(plantegning_3035_test.geo)))
Filter:
st_intersects(geom, st_buildarea(st_boundary(plantegning_3035_test.geo)))
Planning Time: 0.204 ms
Execution Time: 78854253.721 ms
(8 rows)
explain analyze select count(gk.geom)
FROM
klima.plantegning_3035_test AS pt,
klima.gk_02 AS gk
WHERE ST_Intersects(gk.geom, pt.geo);
Aggregate (cost=449.02..449.03 rows=1 width=8) (actual
time=773.033..773.035 rows=1 loops=1)
-> Nested Loop (cost=0.28..448.20 rows=329 width=456) (actual
time=5.188..751.117 rows=329118 loops=1)
-> Seq Scan on plantegning_3035_test pt
(cost=0.00..0.51 rows=1 width=32) (actual time=0.010..0.012 rows=1
loops=1)
-> Index Scan using gk_02_geom_idx on
gk_02 gk (cost=0.28..447.36 rows=33 width=456) (actual
time=3.516..727.646 rows=329118 loops=1)
Index Cond: (geom
&& pt.geo)
Filter:
st_intersects(geom, pt.geo)
Planning Time: 0.991 ms
Execution Time: 773.335 ms
(8 rows)
explain analyze select sum(ST_area(gk.geom)), count(*)
FROM
klima.gk_02 AS gk
Aggregate (cost=58524.52..58524.53 rows=1 width=16) (actual
time=158.000..158.002 rows=1 loops=1)
-> Seq Scan on gk_02 gk (cost=0.00..15739.18 rows=329118
width=456) (actual time=3.872..31.532 rows=329118 loops=1)
Planning Time: 0.061 ms
Execution Time: 158.032 ms
(4 rows)
explain analyze select sum(ST_area(p.geo)), count(*)
FROM
klima.plantegning_3035_test AS p
Aggregate (cost=0.64..0.65 rows=1 width=16) (actual time=2.079..2.080
rows=1 loops=1)
-> Seq Scan on plantegning_3035_test p (cost=0.00..0.51 rows=1
width=32) (actual time=0.012..0.016 rows=1 loops=1)
Planning Time: 0.061 ms
Execution Time: 2.203 ms
(4 rows)
}}}
--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5953#comment:1>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.
More information about the postgis-tickets
mailing list