[postgis-users] diffrent execution plan on Postgres 9.5 and Postgres 11 for ST_union and performance problem Postgres 11
Lars Aksel Opsahl
Lars.Opsahl at nibio.no
Thu Apr 11 09:57:02 PDT 2019
Hi
Yes then it switched GroupAggregate, but it stil used around 1 hour.
sl=# SET enable_hashagg=false;
SET
sl=# EXPLAIN ANALYZE
sl-# select atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext , (ST_dump(st_union(geo))).geom as geo
sl-# from sde_markslag.markslag_myrikilden_temp
sl-# group by atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.42..173072.99 rows=192000 width=46) (actual time=4574.289..3348175.809 rows=482885 loops=1)
-> ProjectSet (cost=0.42..123152.99 rows=192000 width=46) (actual time=4574.285..3348126.192 rows=482885 loops=1)
-> GroupAggregate (cost=0.42..122144.03 rows=192 width=46) (actual time=4572.051..3344371.117 rows=166 loops=1)
Group Key: atil, myr, myrtype, myromdanning, myrtypetext, myromdanningtext
-> Index Scan using test_idx on markslag_myrikilden_temp (cost=0.42..112214.91 rows=567241 width=1614) (actual time=0.087..509.296 rows=567241 loops=1)
Planning Time: 0.255 ms
Execution Time: 3348188.751 ms
(7 rows)
Thanks.
Lars
________________________________
From: postgis-users <postgis-users-bounces at lists.osgeo.org> on behalf of Regina Obe <lr at pcorp.us>
Sent: Thursday, April 11, 2019 4:42 PM
To: 'PostGIS Users Discussion'
Subject: Re: [postgis-users] diffrent execution plan on Postgres 9.5 and Postgres 11 for ST_union and performance problem Postgres 11
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/4a6bf4a2/attachment.html>
More information about the postgis-users
mailing list