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

[Stephen Woodbridge]

There is a lag between loading the data and when AUTOVACUUM is run and 
even if it is running it might take a while to get to the tables the you 
just loaded.

ANALYZE is relatively fast to run if you want to use it immediately. 
Then you can wait on autovacuum to run later clean things up.

-Steve

On 12/10/2013 2:55 PM, Brent Wood wrote:
> 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
> <http://www.postgresql.org/docs/9.0/static/routine-vacuuming.html#AUTOVACUUM>)
> 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
> <mailto: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 <mailto: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 <mailto: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 <mailto:postgis-users at lists.osgeo.org>
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>