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