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." <pm...@nd.gov>
*To:* PostGIS Users Discussion <postgis-users@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" <wood...@swoodbridge.com
<mailto:wood...@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@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>
>_______________________________________________
>postgis-users mailing list
>postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
>http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
