[postgis-users] diffrent execution plan on Postgres 9.5 and Postgres 11 for ST_union and performance problem Postgres 11

Sat Apr 13 00:38:38 PDT 2019

Hi

Thanks , this was fast code update .

Tested the code now but with geos 3.7 and the attached file and I still have the same problem .

I assume there is something related to that I tested with geos 3.7.

I also had to test om another server with this cpu (Intel(R) Xeon(R) CPU E3-1270 V2 @ 3.50GHz (fam: 06, model: 3a, stepping: 09)) where I was running centos 7.
But I don't think this should make so  much difference

Here is the test after the update on this server.

How I installed and compiled is further down the mail.

SELECT PostGIS_full_version();

                                                                                                    postgis_full_version

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 POSTGIS="2.5.3dev r17378" [EXTENSION] PGSQL="110" GEOS="3.7.2dev-CAPI-1.11.2 995c7c4" SFCGAL="1.2.2" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.5, released 2016/07/01" LIBXML="2.9.1" LIBJSON="0.11" TOPOLOGY RASTER

EXPLAIN ANALYZE

select (ST_dump(st_union(geo))).geom as geo

from sde_markslag.markslag_myrikilden_temp

where gid  < 10000

                                                                    QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------

 Result  (cost=62634.10..62899.37 rows=1000 width=32) (actual time=23445.338..23544.121 rows=9205 loops=1)

   ->  ProjectSet  (cost=62634.10..62639.37 rows=1000 width=32) (actual time=23445.333..23542.636 rows=9205 loops=1)

         ->  Aggregate  (cost=62634.10..62634.11 rows=1 width=32) (actual time=23442.220..23442.220 rows=1 loops=1)

               ->  Seq Scan on markslag_myrikilden_temp  (cost=0.00..62609.51 rows=9835 width=1600) (actual time=0.015..178.305 rows=9999 loops=1)

                     Filter: (gid < 10000)

                     Rows Removed by Filter: 557242

 Planning Time: 0.189 ms

 Execution Time: 23544.779 ms

(8 rows)

Can I use 3.7 or do I have to compile the master branch together postgis 2.5 ?
I was not sure about what to merge from master into 3.7. So I just picked the attached file from master and nothing else..

Then I removed postgis, gets and gdal installed by yum by using yum remove.

Then I Downloaded and compiled : The size  was 521488  for ./src/operation/union/CascadedPolygonUnion.o

gdal branch 1.1 (Why do we use this old gdal ?)
geos branch 3.7 with a copy of CascadedPolygonUnion.cpp from master
postgis brach 2.5

./configure --with-geosconfig=/home/lop/postgres_code/geos/tools/geos-config --with-pgconfig=/usr/pgsql-11/bin/pg_config

Then I got this error

./configure: line 13475: /home/lop/postgres_code/geos/tools/geos-config: Permission denied

I added execute on that file then it compiled with ok and installed

PostGIS is now configured for x86_64-unknown-linux-gnu

 -------------- Compiler Info -------------

  C compiler:           gcc -g -O2

  SQL preprocessor:     /usr/bin/cpp -traditional-cpp -w -P

 -------------- Additional Info -------------

  Interrupt Tests:   DISABLED use: --with-interrupt-tests to enable

 -------------- Dependencies --------------

  GEOS config:          /home/lop/postgres_code/geos/tools/geos-config

  GEOS version:         3.7.2dev

  GDAL config:          /usr/local/bin/gdal-config

  GDAL version:         1.11.5

  SFCGAL config:        /usr/bin/sfcgal-config

  SFCGAL version:       1.2.2

  PostgreSQL config:    /usr/pgsql-11/bin/pg_config

  PostgreSQL version:   PostgreSQL 11.2

  PROJ4 version:        48

  Libxml2 config:       /usr/bin/xml2-config

  Libxml2 version:      2.9.1

  JSON-C support:       yes

  protobuf-c support:   no

  PCRE support:         no

  Perl:                 /usr/bin/perl

 --------------- Extensions ---------------

  PostGIS Raster:       enabled

  PostGIS Topology:     enabled

  SFCGAL support:       enabled

  Address Standardizer support:       disabled

 -------- Documentation Generation --------

  xsltproc:             /usr/bin/xsltproc

  xsl style sheets:

  dblatex:

  convert:

  mathml2.dtd:          http://www.w3.org/Math/DTD/mathml2/mathml2.dtd

To get work postgres I had to add this file

cat /etc/ld.so.conf.d/geos37-pgdg-libs.conf

/usr/local/lib/

And in /usr/local/lib I have these files

[root at vroomtest geos]# ls /usr/local/lib

gdalplugins  libgdal.la  libgdal.so.1       libgeos-3.7.2.so  libgeos_c.a   libgeos_c.so    libgeos_c.so.1.11.2  libgeos.so          liblwgeom-2.5.so.0.0.0  liblwgeom.la  pkgconfig

libgdal.a    libgdal.so  libgdal.so.1.18.5  libgeos.a         libgeos_c.la  libgeos_c.so.1  libgeos.la           liblwgeom-2.5.so.0  liblwgeom.a             liblwgeom.so

Then I started postgres and created a new database and adde the postgis extension and added the testdata.

Thanks .

Lars

________________________________
From: postgis-users <postgis-users-bounces at lists.osgeo.org> on behalf of Martin Davis <mtnclimb at gmail.com>
Sent: Friday, April 12, 2019 11:36 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

I've figured out a fix for the performance reqression and it's now in GEOS master.  My testing using the sample data provided above indicates it's at least 10x faster, so essentially eliminating the performance regression in this case (and it should provide that for most situations)

If anyone can test further to confirm it is indeed faster and most importantly correct that would be great.

On Thu, Apr 11, 2019 at 1:03 PM Martin Davis <mtnclimb at gmail.com<mailto:mtnclimb at gmail.com>> wrote:
Well, we have figured out what the problem is - it's a known regression in the GEOS UnaryUnion code, reported in this ticket [1]

Unfortunately there's no fix for this yet, but we'll try and escalate this.

[1] https://trac.osgeo.org/geos/ticket/867

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190413/0df6468a/attachment.html>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: CascadedPolygonUnion.cpp
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190413/0df6468a/attachment.ksh>