#997: ST_Union performance degradation from GEOS 3.6.x -> 3.7.x
-------------------------+--------------------------
Reporter: greenlaw | Owner: geos-devel@…
Type: defect | Status: new
Priority: major | Milestone:
Component: Default | Version: 3.7.0
Severity: Significant | Keywords: union
-------------------------+--------------------------
I [https://lists.osgeo.org/pipermail/postgis-
users/2019-October/043688.html posted] about this earlier this week on the
postgis-users mailing list.
I've encountered a severe performance issue with GEOS >=3.7.0 when calling
`ST_Union(ST_Buffer(...))` on some large polygons (EPSG:3857) with lots of
vertices. I've been testing primarily on PostgreSQL 9.5 but have
reproduced the issue with multiple versions of PostGIS (2.2 to 2.5).
This appears to be a separate issue from #867. That ticket references a
recent JTS performance enhancement which was ported to GEOS and merged to
master, but I tested with the latest version (which contains that fix),
3.8.0rc3, and it didn't make a difference (actually performance was
worse).
With PostgreSQL 9.5, PostGIS 2.3.7, and GEOS 3.6.4, my query takes ~100
sec to run, but after upgrading GEOS to 3.7.0, the query duration balloons
to ~70 minutes. With 3.8.0rc3, duration again increased (to ~147 minutes).
Exact timings for different version combinations is pasted below.
I will attach a pgdump of the table (21 rows, 23mb uncompressed).
Steps to reproduce:
1. Have PostgreSQL 9.5 instance with PostGIS 2.2 - 2.5 and GEOS >= 3.7.0
2. Import the pgdump
{{{
psql -h hostname -p port -U postgres -d dbname -f
pgdump_nws_haz_hang_201910.sql
}}}
3. Execute the query
{{{
EXPLAIN ANALYZE SELECT ST_Union(ST_Buffer(wkb_geometry,
100.0))::geometry(Geometry,
3857),category,vteccode,prod_type,validtime,starttime,endtime FROM
public.nws_haz_hang_201910 GROUP BY
category,vteccode,prod_type,validtime,starttime,endtime;
}}}
The query will likely take between 1 - 3 hours to complete on most
systems.
Timings for specific version combinations are pasted below.
3.6.1 = ~85 sec:
{{{
POSTGIS="2.3.7 r16523" PGSQL="95" GEOS="3.6.1-CAPI-1.10.1 r0" PROJ="Rel.
4.9.3, 15 August 2016" GDAL="GDAL 1.11.4, released 2016/01/25"
LIBXML="2.9.1" LIBJSON="0.11" RASTER
EXPLAIN ANALYZE SELECT ST_Union(ST_Buffer(wkb_geometry,
100.0))::geometry(Geometry,
3857),category,vteccode,prod_type,validtime,starttime,endtime FROM
public.nws_haz_hang_201910 GROUP BY
category,vteccode,prod_type,validtime,starttime,endtime;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=10.30..10.45 rows=10 width=706) (actual
time=35922.055..85310.959 rows=5 loops=1)
Group Key: category, vteccode, prod_type, validtime, starttime, endtime
-> Seq Scan on nws_haz_hang_201910 (cost=0.00..10.10 rows=10
width=706) (actual time=0.014..0.053 rows=21 loops=1)
Planning time: 0.460 ms
Execution time: 85314.516 ms
}}}
3.6.2 = ~90 sec:
{{{
POSTGIS="2.3.7 r16523" PGSQL="95" GEOS="3.6.2-CAPI-1.10.2 4d2925d6"
PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 1.11.4, released 2016/01/25"
LIBXML="2.9.1" LIBJSON="0.11" RASTER
EXPLAIN ANALYZE SELECT ST_Union(ST_Buffer(wkb_geometry,
100.0))::geometry(Geometry,
3857),category,vteccode,prod_type,validtime,starttime,endtime FROM
public.nws_haz_hang_201910 GROUP BY
category,vteccode,prod_type,validtime,starttime,endtime;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=10.30..10.45 rows=10 width=706) (actual
time=34092.861..90393.216 rows=5 loops=1)
Group Key: category, vteccode, prod_type, validtime, starttime, endtime
-> Seq Scan on nws_haz_hang_201910 (cost=0.00..10.10 rows=10
width=706) (actual time=0.012..0.047 rows=21 loops=1)
Planning time: 0.315 ms
Execution time: 90393.369 ms
}}}
3.6.3 = ~89 sec:
{{{
POSTGIS="2.3.7 r16523" PGSQL="95" GEOS="3.6.3-CAPI-1.10.3 80c13047"
PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 1.11.4, released 2016/01/25"
LIBXML="2.9.1" LIBJSON="0.11" RASTER
EXPLAIN ANALYZE SELECT ST_Union(ST_Buffer(wkb_geometry,
100.0))::geometry(Geometry,
3857),category,vteccode,prod_type,validtime,starttime,endtime FROM
public.nws_haz_hang_201910 GROUP BY
category,vteccode,prod_type,validtime,starttime,endtime;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=10.30..10.45 rows=10 width=706) (actual
time=33833.006..88916.065 rows=5 loops=1)
Group Key: category, vteccode, prod_type, validtime, starttime, endtime
-> Seq Scan on nws_haz_hang_201910 (cost=0.00..10.10 rows=10
width=706) (actual time=0.011..0.047 rows=21 loops=1)
Planning time: 0.306 ms
Execution time: 88916.182 ms
}}}
3.6.4 = ~100 sec:
{{{
POSTGIS="2.3.7 r16523" PGSQL="95" GEOS="3.6.4-CAPI-1.10.4 ba90ca5"
PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 1.11.4, released 2016/01/25"
LIBXML="2.9.1" LIBJSON="0.11" RASTER
EXPLAIN ANALYZE SELECT ST_Union(ST_Buffer(wkb_geometry,
100.0))::geometry(Geometry,
3857),category,vteccode,prod_type,validtime,starttime,endtime FROM
public.nws_haz_hang_201910 GROUP BY
category,vteccode,prod_type,validtime,starttime,endtime;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=10.30..10.45 rows=10 width=706) (actual
time=35376.572..99728.566 rows=5 loops=1)
Group Key: category, vteccode, prod_type, validtime, starttime, endtime
-> Seq Scan on nws_haz_hang_201910 (cost=0.00..10.10 rows=10
width=706) (actual time=0.024..0.052 rows=21 loops=1)
Planning time: 0.408 ms
Execution time: 99728.803 ms
}}}
3.7.0 = ~70 min:
{{{
POSTGIS="2.3.7 r16523" PGSQL="95" GEOS="3.7.0-CAPI-1.11.0 673b9939"
PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 1.11.4, released 2016/01/25"
LIBXML="2.9.1" LIBJSON="0.11" RASTER
EXPLAIN ANALYZE SELECT ST_Union(ST_Buffer(wkb_geometry,
100.0))::geometry(Geometry,
3857),category,vteccode,prod_type,validtime,starttime,endtime FROM
public.nws_haz_hang_201910 GROUP BY
category,vteccode,prod_type,validtime,starttime,endtime;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=10.30..10.45 rows=10 width=706) (actual
time=30353.481..4204487.862 rows=5 loops=1)
Group Key: category, vteccode, prod_type, validtime, starttime, endtime
-> Seq Scan on nws_haz_hang_201910 (cost=0.00..10.10 rows=10
width=706) (actual time=0.017..0.053 rows=21 loops=1)
Planning time: 0.362 ms
Execution time: 4204488.069 ms
}}}
3.7.1 = ~72 min:
{{{
POSTGIS="2.3.7 r16523" PGSQL="95" GEOS="3.7.1-CAPI-1.11.1 27a5e771"
PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 1.11.4, released 2016/01/25"
LIBXML="2.9.1" LIBJSON="0.11" RASTER
EXPLAIN ANALYZE SELECT ST_Union(ST_Buffer(wkb_geometry,
100.0))::geometry(Geometry,
3857),category,vteccode,prod_type,validtime,starttime,endtime FROM
public.nws_haz_hang_201910 GROUP BY
category,vteccode,prod_type,validtime,starttime,endtime;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=10.30..10.45 rows=10 width=706) (actual
time=31342.188..4323753.205 rows=5 loops=1)
Group Key: category, vteccode, prod_type, validtime, starttime, endtime
-> Seq Scan on nws_haz_hang_201910 (cost=0.00..10.10 rows=10
width=706) (actual time=0.021..0.055 rows=21 loops=1)
Planning time: 0.446 ms
Execution time: 4323755.076 ms
}}}
3.7.2 = ~70 min:
{{{
POSTGIS="2.3.7 r16523" PGSQL="95" GEOS="3.7.2-CAPI-1.11.2 b55d2125"
PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 1.11.4, released 2016/01/25"
LIBXML="2.9.1" LIBJSON="0.11" RASTER
EXPLAIN ANALYZE SELECT ST_Union(ST_Buffer(wkb_geometry,
100.0))::geometry(Geometry,
3857),category,vteccode,prod_type,validtime,starttime,endtime FROM
public.nws_haz_hang_201910 GROUP BY
category,vteccode,prod_type,validtime,starttime,endtime;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=10.30..10.45 rows=10 width=706) (actual
time=33643.593..4195304.854 rows=5 loops=1)
Group Key: category, vteccode, prod_type, validtime, starttime, endtime
-> Seq Scan on nws_haz_hang_201910 (cost=0.00..10.10 rows=10
width=706) (actual time=0.019..0.052 rows=21 loops=1)
Planning time: 0.374 ms
Execution time: 4195305.021 ms
}}}
3.8.0rc3 = ~147 min:
{{{
POSTGIS="2.5.3 r17699" [EXTENSION] PGSQL="95" GEOS="3.8.0rc3-CAPI-1.13.1 "
PROJ="Rel. 6.2.0, September 1st, 2019" GDAL="GDAL 3.0.1, released
2019/06/28" LIBXML="2.9.1" LIBJSON="0.11" LIBPROTOBUF="1.0.2" RASTER
EXPLAIN ANALYZE SELECT ST_Union(ST_Buffer(wkb_geometry,
100.0))::geometry(Geometry,
3857),category,vteccode,prod_type,validtime,starttime,endtime FROM
public.nws_haz_hang_201910 GROUP BY
category,vteccode,prod_type,validtime,starttime,endtime;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=10.30..10.45 rows=10 width=706) (actual
time=42941.634..8834235.340 rows=5 loops=1)
Group Key: category, vteccode, prod_type, validtime, starttime, endtime
-> Seq Scan on nws_haz_hang_201910 (cost=0.00..10.10 rows=10
width=706) (actual time=0.016..0.048 rows=21 loops=1)
Planning time: 0.198 ms
Execution time: 8834235.446 ms
}}}
--
Ticket URL: <https://trac.osgeo.org/geos/ticket/997>
GEOS <http://trac.osgeo.org/geos>
GEOS (Geometry Engine - Open Source) is a C++ port of the Java Topology Suite
(JTS).
_______________________________________________
geos-devel mailing list
geos-devel@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/geos-devel
