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

[Paul Ramsey]

Might also be a nice thing for us to add to the end of the load process :/

On Tue, Dec 10, 2013 at 12:09 PM, Stephen Woodbridge
<woodbri at swoodbridge.com> wrote:
> 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" <woodbri at swoodbridge.com>
>> 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
>>>> postgis-users at lists.osgeo.org
>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users