[postgis-users] Postgis 2.1.0 poor performance vs Postgis 2.0.3

[Brent Wood]

Hmm...

From Postgres v9.0+, with autovacuum on (which it is by default), analyse is automatically run :

"In the default PostgreSQL configuration, the autovacuum daemon (see Section 23.1.5) takes care of automatic analyzing of tables when they are first loaded with data, and as they change throughout regular operation."

Is it still useful to run ANALYSE in such cases? I had assumed it was not necessary. Is an upgrade loading data differently and not triggering ANALYSE?


Thanks,


Brent Wood


________________________________
 From: "Moen, Paul T." <pmoen at nd.gov>
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org> 
Sent: Wednesday, December 11, 2013 6:00 AM
Subject: Re: [postgis-users] Postgis 2.1.0 poor performance vs Postgis 2.0.3
 

Steve,

That did the trick in a big way.  I will make ANALYZE a step in my upgrade
process from now on.

My new query after the ANALYZE.

--select 
"flow_type","feat_type",encode(ST_AsBinary(ST_Force2D("the_geom"),'NDR'),'h
ex') as geom,"gid" from basemap.water24k_line where the_geom &&
ST_GeomFromText('POLYGON((1564746.75408708
903581.471987315,1564746.75408708 938518.971987315,1599744.49115605
938518.971987315,1599744.49115605 903581.471987315,1564746.75408708
903581.471987315))',2266);
Total query runtime: 569 ms.
344 rows retrieved.


Thank you very much,

Paul

On 12/10/13, 10:02 AM, "Stephen Woodbridge" <woodbri at swoodbridge.com>
wrote:

>Paul,
>
>Did you ANALYZE the new database after you upgraded it to 2.1.0?
>Try that and rerun your query and see if that helps.
>
>-Steve
>
>On 12/10/2013 10:46 AM, Moen, Paul T. wrote:
>> I have a table of multilines that the query below slows down
>> considerably after upgrading to postgis 2.1.0.
>>
>> I am running the following Postgis.
>> "POSTGIS="2.0.3 r11128" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0,
>> 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.7.3"
>> LIBJSON="UNKNOWN" TOPOLOGY RASTER"
>> Below is a query that is created by Mapserver 6.0.3 that I am running in
>> pgAdmin.
>> -- Executing query:
>> select
>> 
>>"flow_type","feat_type",encode(ST_AsBinary(ST_Force_2D("the_geom"),'NDR')
>>,'hex')
>> as geom,"gid" from basemap.water24k_line where the_geom &&
>> ST_GeomFromText('POLYGON((1564746.75408708
>> 903581.471987315,1564746.75408708 938518.971987315,1599744.49115605
>> 938518.971987315,1599744.49115605 903581.471987315,1564746.75408708
>> 903581.471987315))',2266)
>> Total query runtime: 569 ms.
>> 344 rows retrieved.
>>
>> After upgrading to Postgis 2.1.0,
>> "POSTGIS="2.1.0 r11822" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0,
>> 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.7.3"
>> LIBJSON="UNKNOWN" TOPOLOGY RASTER"
>> the same query takes over 10 times the time.
>>
>> -- Executing query:
>> select
>> 
>>"flow_type","feat_type",encode(ST_AsBinary(ST_Force_2D("the_geom"),'NDR')
>>,'hex')
>> as geom,"gid" from basemap.water24k_line where the_geom &&
>> ST_GeomFromText('POLYGON((1564746.75408708
>> 903581.471987315,1564746.75408708 938518.971987315,1599744.49115605
>> 938518.971987315,1599744.49115605 903581.471987315,1564746.75408708
>> 903581.471987315))',2266);
>> --select postgis_full_version();
>> --alter extension postgis_topology update to '2.1.0';
>> WARNING:  ST_Force_2d signature was deprecated in 2.1.0. Please use
>> ST_Force2D
>> CONTEXT:  SQL function "st_force_2d" during startup
>> Total query runtime: 6202 ms.
>> 344 rows retrieved.
>>
>> Changing ST_Force_2D to ST_Force2D helped quite a bit, but it is still
>> twice as slow as Postgis 2.0.3.
>>
>> -- Executing query:
>> select
>> 
>>"flow_type","feat_type",encode(ST_AsBinary(ST_Force2D("the_geom"),'NDR'),
>>'hex')
>> as geom,"gid" from basemap.water24k_line where the_geom &&
>> ST_GeomFromText('POLYGON((1564746.75408708
>> 903581.471987315,1564746.75408708 938518.971987315,1599744.49115605
>> 938518.971987315,1599744.49115605 903581.471987315,1564746.75408708
>> 903581.471987315))',2266);
>> --select postgis_full_version();
>> --alter extension postgis_topology update to '2.1.0';
>> Total query runtime: 1029 ms.
>> 344 rows retrieved.
>>
>> Is performance hit expected?
>>
>> Thanks for any insight,
>>
>>
>> Paul
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

>>
>
>_______________________________________________
>postgis-users mailing list
>postgis-users at lists.osgeo.org
>http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20131210/2a80390c/attachment.html>