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
