[postgis-users] Postgis 2.1.0 poor performance vs Postgis 2.0.3

[Moen, Paul T.]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20131210/b6a1070e/attachment.html>