Might also be a nice thing for us to add to the end of the load process :/
On Tue, Dec 10, 2013 at 12:09 PM, Stephen Woodbridge <wood...@swoodbridge.com> wrote: > Paul, > > Cool! And yes, anytime you load a database or table like from shp2pgsql you > should analyze it to update the database statistics other wise the planner > has no information about what indexes to use. This might be a good practice > after adding a new index or making lots of changes to an existing index > doing updates/deletes/inserts on a table. > > -Steve > > > On 12/10/2013 12:00 PM, Moen, Paul T. wrote: >> >> 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 >> > > _______________________________________________ > 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