<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
</head>
<body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; color: rgb(0, 0, 0); font-size: 14px; font-family: Calibri, sans-serif;">
<div>I have a table of multilines that the query below slows down considerably after upgrading to postgis 2.1.0. </div>
<div><br>
</div>
<div>I am running the following Postgis.</div>
<div><span class="Apple-tab-span" style="white-space:pre"></span>"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"</div>
<div>Below is a query that is created by Mapserver 6.0.3 that I am running in pgAdmin.</div>
<div>-- Executing query:</div>
<div>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)</div>
<div>Total query runtime: 569 ms.</div>
<div>344 rows retrieved.</div>
<div><br>
</div>
<div>After upgrading to Postgis 2.1.0, </div>
<div><span class="Apple-tab-span" style="white-space:pre"></span>"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"</div>
<div>the same query takes over 10 times the time.</div>
<div><br>
</div>
<div>-- Executing query:</div>
<div>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);</div>
<div>--select postgis_full_version();</div>
<div>--alter extension postgis_topology update to '2.1.0';</div>
<div>WARNING: ST_Force_2d signature was deprecated in 2.1.0. Please use ST_Force2D</div>
<div>CONTEXT: SQL function "st_force_2d" during startup</div>
<div>Total query runtime: 6202 ms.</div>
<div>344 rows retrieved.</div>
<div><br>
</div>
<div>Changing ST_Force_2D to ST_Force2D helped quite a bit, but it is still twice as slow as Postgis 2.0.3.</div>
<div><br>
</div>
<div>-- Executing query:</div>
<div>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);</div>
<div>--select postgis_full_version();</div>
<div>--alter extension postgis_topology update to '2.1.0';</div>
<div>Total query runtime: 1029 ms.</div>
<div>344 rows retrieved.</div>
<div><br>
</div>
<div>Is performance hit expected?</div>
<div><br>
</div>
<div>Thanks for any insight,</div>
<div><br>
</div>
<div><br>
</div>
<div>Paul</div>
</body>
</html>