[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