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

[Stephen Woodbridge]

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 at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>