<html><body><div style="color:#000; background-color:#fff; font-family:times new roman, new york, times, serif;font-size:12pt"><div><span>Hmm...</span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><br><span></span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><span>From Postgres v9.0+, with autovacuum on (which it is by default), analyse is automatically run :</span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><br><span></span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;">"<span style="font-style: italic;">In
the default <span class="PRODUCTNAME">PostgreSQL</span>
configuration, the autovacuum daemon (see <a href="http://www.postgresql.org/docs/9.0/static/routine-vacuuming.html#AUTOVACUUM">Section 23.1.5</a>) takes
care of automatic analyzing of tables when they are first loaded
with data, and as they change throughout regular operation."</span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><span><br></span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><span>Is it still useful to run ANALYSE in such cases?</span> I had assumed it was not necessary. Is an upgrade loading data differently and not triggering ANALYSE?<br><span style="font-style: italic;"></span></div><div><br></div><div>Thanks,<br></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><br></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style:
normal;">Brent Wood<br></div> <div style="font-family: times new roman, new york, times, serif; font-size: 12pt;"> <div style="font-family: times new roman, new york, times, serif; font-size: 12pt;"> <div dir="ltr"> <hr size="1"> <font face="Arial" size="2"> <b><span style="font-weight:bold;">From:</span></b> "Moen, Paul T." <pmoen@nd.gov><br> <b><span style="font-weight: bold;">To:</span></b> PostGIS Users Discussion <postgis-users@lists.osgeo.org> <br> <b><span style="font-weight: bold;">Sent:</span></b> Wednesday, December 11, 2013 6:00 AM<br> <b><span style="font-weight: bold;">Subject:</span></b> Re: [postgis-users] Postgis 2.1.0 poor performance vs Postgis 2.0.3<br> </font> </div> <div class="y_msg_container"><br>Steve,<br clear="none"><br clear="none">That did the trick in a big way. I will make ANALYZE a step in my upgrade<br clear="none">process from now on.<br clear="none"><br clear="none">My new query after the ANALYZE.<br
clear="none"><br clear="none">--select <br clear="none">"flow_type","feat_type",encode(ST_AsBinary(ST_Force2D("the_geom"),'NDR'),'h<br clear="none">ex') as geom,"gid" from basemap.water24k_line where the_geom &&<br clear="none">ST_GeomFromText('POLYGON((1564746.75408708<br clear="none">903581.471987315,1564746.75408708 938518.971987315,1599744.49115605<br clear="none">938518.971987315,1599744.49115605 903581.471987315,1564746.75408708<br clear="none">903581.471987315))',2266);<br clear="none">Total query runtime: 569 ms.<br clear="none">344 rows retrieved.<br clear="none"><br clear="none"><br clear="none">Thank you very much,<br clear="none"><br clear="none">Paul<br clear="none"><br clear="none">On 12/10/13, 10:02 AM, "Stephen Woodbridge" <<a shape="rect" ymailto="mailto:woodbri@swoodbridge.com" href="mailto:woodbri@swoodbridge.com">woodbri@swoodbridge.com</a>><br clear="none">wrote:<br clear="none"><br clear="none">>Paul,<br
clear="none">><br clear="none">>Did you ANALYZE the new database after you upgraded it to 2.1.0?<br clear="none">>Try that and rerun your query and see if that helps.<br clear="none">><br clear="none">>-Steve<br clear="none">><br clear="none">>On 12/10/2013 10:46 AM, Moen, Paul T. wrote:<br clear="none">>> I have a table of multilines that the query below slows down<br clear="none">>> considerably after upgrading to postgis 2.1.0.<br clear="none">>><br clear="none">>> I am running the following Postgis.<br clear="none">>> "POSTGIS="2.0.3 r11128" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0,<br clear="none">>> 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.7.3"<br clear="none">>> LIBJSON="UNKNOWN" TOPOLOGY RASTER"<br clear="none">>> Below is a query that is created by Mapserver 6.0.3 that I am running in<br clear="none">>> pgAdmin.<br clear="none">>> --
Executing query:<br clear="none">>> select<br clear="none">>> <br clear="none">>>"flow_type","feat_type",encode(ST_AsBinary(ST_Force_2D("the_geom"),'NDR')<br clear="none">>>,'hex')<br clear="none">>> as geom,"gid" from basemap.water24k_line where the_geom &&<br clear="none">>> ST_GeomFromText('POLYGON((1564746.75408708<br clear="none">>> 903581.471987315,1564746.75408708 938518.971987315,1599744.49115605<br clear="none">>> 938518.971987315,1599744.49115605 903581.471987315,1564746.75408708<br clear="none">>> 903581.471987315))',2266)<br clear="none">>> Total query runtime: 569 ms.<br clear="none">>> 344 rows retrieved.<br clear="none">>><br clear="none">>> After upgrading to Postgis 2.1.0,<br clear="none">>> "POSTGIS="2.1.0 r11822" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0,<br clear="none">>> 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26"
LIBXML="2.7.3"<br clear="none">>> LIBJSON="UNKNOWN" TOPOLOGY RASTER"<br clear="none">>> the same query takes over 10 times the time.<br clear="none">>><br clear="none">>> -- Executing query:<br clear="none">>> select<br clear="none">>> <br clear="none">>>"flow_type","feat_type",encode(ST_AsBinary(ST_Force_2D("the_geom"),'NDR')<br clear="none">>>,'hex')<br clear="none">>> as geom,"gid" from basemap.water24k_line where the_geom &&<br clear="none">>> ST_GeomFromText('POLYGON((1564746.75408708<br clear="none">>> 903581.471987315,1564746.75408708 938518.971987315,1599744.49115605<br clear="none">>> 938518.971987315,1599744.49115605 903581.471987315,1564746.75408708<br clear="none">>> 903581.471987315))',2266);<br clear="none">>> --select postgis_full_version();<br clear="none">>> --alter extension postgis_topology update to '2.1.0';<br clear="none">>>
WARNING: ST_Force_2d signature was deprecated in 2.1.0. Please use<br clear="none">>> ST_Force2D<br clear="none">>> CONTEXT: SQL function "st_force_2d" during startup<br clear="none">>> Total query runtime: 6202 ms.<br clear="none">>> 344 rows retrieved.<br clear="none">>><br clear="none">>> Changing ST_Force_2D to ST_Force2D helped quite a bit, but it is still<br clear="none">>> twice as slow as Postgis 2.0.3.<br clear="none">>><br clear="none">>> -- Executing query:<br clear="none">>> select<br clear="none">>> <br clear="none">>>"flow_type","feat_type",encode(ST_AsBinary(ST_Force2D("the_geom"),'NDR'),<br clear="none">>>'hex')<br clear="none">>> as geom,"gid" from basemap.water24k_line where the_geom &&<br clear="none">>> ST_GeomFromText('POLYGON((1564746.75408708<br clear="none">>> 903581.471987315,1564746.75408708
938518.971987315,1599744.49115605<br clear="none">>> 938518.971987315,1599744.49115605 903581.471987315,1564746.75408708<br clear="none">>> 903581.471987315))',2266);<br clear="none">>> --select postgis_full_version();<br clear="none">>> --alter extension postgis_topology update to '2.1.0';<br clear="none">>> Total query runtime: 1029 ms.<br clear="none">>> 344 rows retrieved.<br clear="none">>><br clear="none">>> Is performance hit expected?<br clear="none">>><br clear="none">>> Thanks for any insight,<br clear="none">>><br clear="none">>><br clear="none">>> Paul<br clear="none">>><br clear="none">>><br clear="none">>> _______________________________________________<br clear="none">>> postgis-users mailing list<br clear="none">>> <a shape="rect" ymailto="mailto:postgis-users@lists.osgeo.org"
href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br clear="none">>> <a shape="rect" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><div class="yqt1290593807" id="yqtfd02493"><br clear="none">>><br clear="none">><br clear="none">>_______________________________________________<br clear="none">>postgis-users mailing list<br clear="none">><a shape="rect" ymailto="mailto:postgis-users@lists.osgeo.org" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br clear="none">><a shape="rect" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br clear="none"><br clear="none">_______________________________________________<br clear="none">postgis-users mailing list<br clear="none"><a
shape="rect" ymailto="mailto:postgis-users@lists.osgeo.org" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br clear="none"><a shape="rect" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br clear="none"></div><br><br></div> </div> </div> </div></body></html>