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> 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 >> 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 _______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users