[postgis-users] diffrent execution plan on Postgres 9.5 and Postgres 11 for ST_union and performance problem Postgres 11
Regina Obe
lr at pcorp.us
Thu Apr 11 07:42:11 PDT 2019
Can you try to force them to use the same plan.
I vaguely recall running into this issue with HashAggregate or
GroupAggregate misbehaving but can't recall the exact fix.
At anyrate to check if that's the issue on your PostgreSQL 11, try doing
SET enable_hashagg=false;
And then rerun your query - that should prevent it from using hashagg and
presumably would force it to use groupagg
From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf
Of Lars Aksel Opsahl
Sent: Thursday, April 11, 2019 6:27 AM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: [postgis-users] diffrent execution plan on Postgres 9.5 and
Postgres 11 for ST_union and performance problem Postgres 11
Hi
We are upgrading from Postgres 9.5 to Postgres 11 and have a problem with
ST_Union query.
It used around 5 minutts on Postgres 9.5 but on the Postgres 11 it takes
around a hour.
This is two different physical servers.
* Postgres 11 server (Intel(R) Xeon(R) Gold 6126 CPU @ 2.60GHz), is
running Redhat 7.0
* Postgres 9.5 server ( Intel(R) Xeon(R) CPU E5-2667 v2 @ 3.30GHz) is
running Cent 6.9 .
The only index on table in the geo column
* Sql and query plan for from Postgres 9.5
POSTGIS="2.2.2 r14797" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.8.0, 6
March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.6"
LIBJSON="0.11" TOPOLOGY RASTER
sl=# EXPLAIN ANALYZE
sl-# select atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext
sl-# , st_union(geo) as geo
sl-# from sde_markslag.markslag_myrikilden_temp
sl-# group by atil, myr,
myrtype,myromdanning,myrtypetext,myromdanningtext ;
QUERY
PLAN
----------------------------------------------------------------------------
-----------------------------------------------------------------------
QUERY
PLAN
----------------------------------------------------------------------------
------------------------------------------------------------------------
GroupAggregate (cost=796867.85..1683469.04 rows=170173000 width=1668)
(actual time=20548.994..386236.175 rows=482885 loops=1)
Group Key: atil, myr, myrtype, myromdanning, myrtypetext,
myromdanningtext
-> Sort (cost=796867.85..801122.16 rows=1701723 width=1668) (actual
time=19215.245..28191.175 rows=567241 loops=1)
Sort Key: atil, myr, myrtype, myromdanning, myrtypetext,
myromdanningtext
Sort Method: external merge Disk: 834880kB
-> Seq Scan on markslag_myrikilden_temp (cost=0.00..180364.23
rows=1701723 width=1668) (actual time=0.115..2625.401 rows=567241 loops=1)
Planning time: 1.942 ms
Execution time: 386479.469 ms
(8 rows)
* Sql and query plan from Postgres 11
POSTGIS="2.5.2 r17328" [EXTENSION] PGSQL="110" GEOS="3.7.1-CAPI-1.11.1
27a5e771" SFCGAL="1.2.2" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL
1.11.4, released 2016/01/25" LIBXML="2.9.1" LIBJSON="0.11" TOPOLOGY RASTER
EXPLAIN ANALYZE
select atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext
, st_union(geo) as geo
from sde_markslag.markslag_myrikilden_temp
group by atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext ;
QUERY
PLAN
----------------------------------------------------------------------------
---------------------------------------------------------------------------
Result (cost=69528.63..120459.99 rows=192000 width=46) (actual
time=962.266..3272057.295 rows=482885 loops=1)
-> ProjectSet (cost=69528.63..70539.99 rows=192000 width=46) (actual
time=962.262..3272011.001 rows=482885 loops=1)
-> HashAggregate (cost=69528.63..69531.03 rows=192 width=46)
(actual time=962.214..3268248.140 rows=166 loops=1)
Group Key: atil, myr, myrtype, myromdanning, myrtypetext,
myromdanningtext
-> Seq Scan on markslag_myrikilden_temp
(cost=0.00..59601.91 rows=567241 width=1614) (actual time=0.012..336.203
rows=567241 loops=1)
Planning Time: 0.163 ms
Execution Time: 3272080.299 ms
(7 rows)
So on Postgres 9.5 we see groupAggregate but not on postgres 11.
If I on postgres 11 tests with ST_collect it's very fast, bu we can not use
that result.
EXPLAIN ANALYZE
select atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext ,
(ST_dump(st_Collect(geo))).geom as geo
from sde_markslag.markslag_myrikilden_temp
group by atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext ;
QUERY
PLAN
----------------------------------------------------------------------------
---------------------------------------------------------------------------
Result (cost=69528.63..120459.99 rows=192000 width=46) (actual
time=934.723..5611.094 rows=567241 loops=1)
-> ProjectSet (cost=69528.63..70539.99 rows=192000 width=46) (actual
time=934.720..5556.601 rows=567241 loops=1)
-> HashAggregate (cost=69528.63..69531.03 rows=192 width=46)
(actual time=934.684..1554.725 rows=166 loops=1)
Group Key: atil, myr, myrtype, myromdanning, myrtypetext,
myromdanningtext
-> Seq Scan on markslag_myrikilden_temp
(cost=0.00..59601.91 rows=567241 width=1614) (actual time=0.012..335.069
rows=567241 loops=1)
Planning Time: 0.179 ms
Execution Time: 5629.207 ms
(7 rows)
Can it be problem related ST_Union and aggregate ?
I have tested with and with out indexes on atil, myr,
myrtype,myromdanning,myrtypetext,myromdanningtext and it does not make any
difference .
Lars
Thanks
Lars
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190411/06c582cf/attachment.html>
More information about the postgis-users
mailing list