[postgis-users] ST_Contains() performance problem
Obe, Regina
robe.dnd at cityofboston.gov
Fri Apr 25 04:25:23 PDT 2008
Stephen and Kevin,
PostgreSQL and actually all databases do short-circuiting but they do some form of
cost-based short-circuiting rather than order of logic short-circuiting.
It could be something about the new built in function cost feature in PostgreSQL 8.3 that is the culprit or perhaps some logic they took out about guessing which cost plan is best because they figured it was too time consuming.
Did you revacuum analyze and everything after reloading your data?
Also as Kevin said -
As far as Pg goes - the general rule of thumb I take is
1) Phrase your conditions in order of cheapest calculation (sometimes I forget). If pg decides it is not worthwhile to calculate a cost, then it will use the lazy left to right ordering for short-circuiting.
2) But Keep in mind left-right short-circuiting is not always done if the planner decides a different order of processing is better.
As a side note I'm wondering if for 8.3 and forward it makes sense to put cost estimates on the PostGIS functions since I presume some are more costly than others and definitely more costly than some other mainstream functions.
Hope that helps,
Regina
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Stephan Grüter
Sent: Friday, April 25, 2008 6:45 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] ST_Contains() performance problem
Hi Kevin,
Am 25.04.2008 um 03:59 schrieb Kevin Neufeld:
> I find it curious that your old system has
> "retyp=8 AND (geom && ...) AND contains(...)"
>
> but you new system has
> "(geom && ...) AND contains(...) AND regtyp=8"
> I think PostgreSQL does short-circuit evaluation. Testing for
> integer equality and dropping out of a filter clause is
> significantly faster than testing a bounding box intersection, a
> contains operation, and then finishing with a test for integer
> equality.
Yes, I think the execution planner of PG 8.3 makes a very bad
decision in this case. But why? The query, the data,
the indexes and the clustering are identical on both systems.
> Are you sure you use the same query on both systems?
Yes, I'm sure. I noticed a performance degradation of an existing
web application and after some profiling I found the this query
as the source of the problem.
Regards,
Stephan
>
> Stephan Grüter wrote:
>> Hi,
>>
>> after migration from PostgreSQL-8.2.4/PostGIS-1.2.1 to
>> PostgreSQL-8.3.1/PostGIS-1.3.3
>> I have massive performance problems doing point in polygon
>> analyses with ST_Contains().
>>
>> Query:
>>
>> EXPLAIN ANALYZE
>> SELECT geo.regcode, bez.name FROM reg.geometrien geo,
>> reg.bezeichnungen bez WHERE geo.regtyp = 8
>> AND geo.the_geom && GeomFromText('Point(682970.983613
>> 246747.010965)', 21781) AND Contains(geo.the_geom, GeomFromText
>> ('Point(682970.983613 246747.010965)', 21781))
>> AND geo.regtyp = bez.regtyp AND geo.regcode = bez.regcode;
>>
>> Old system:
>>
>> version: "PostgreSQL 8.2.4 on powerpc-apple-darwin8.10.1, compiled
>> by GCC powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple
>> Computer, Inc. build 5363)"
>> postgis_full_version: "POSTGIS="1.2.1" GEOS="2.2.3-CAPI-1.1.1"
>> PROJ="Rel. 4.5.0, 22 Oct 2006" USE_STATS"
>>
>> "Nested Loop (cost=0.00..16.56 rows=1 width=18) (actual
>> time=7.278..7.422 rows=1 loops=1)"
>> " -> Index Scan using geometrien_geom_idx on geometrien geo
>> (cost=0.00..8.27 rows=1 width=8) (actual time=7.089..7.231 rows=1
>> loops=1)"
>> " Index Cond: (the_geom &&
>> '0020000001000055154124D7B5F79C1F86410E1ED81674D163'::geometry)"
>> " Filter: ((regtyp = 8) AND (the_geom &&
>> '0020000001000055154124D7B5F79C1F86410E1ED81674D163'::geometry)
>> AND contains(the_geom,
>> '0020000001000055154124D7B5F79C1F86410E1ED81674D163'::geometry))"
>> " -> Index Scan using bezeichnungen_pkey on bezeichnungen bez
>> (cost=0.00..8.27 rows=1 width=20) (actual time=0.155..0.157 rows=1
>> loops=1)"
>> " Index Cond: ((bez.regtyp = 8) AND (geo.regcode =
>> bez.regcode))"
>> "Total runtime: 7.893 ms"
>>
>> New system:
>>
>> version: "PostgreSQL 8.3.1 on i386-apple-darwin9.2.0, compiled by
>> GCC i686-apple-darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5465)"
>> postgis_full_version: "POSTGIS="1.3.3" GEOS="3.0.0-CAPI-1.4.1"
>> PROJ="Rel. 4.6.0, 21 Dec 2007" USE_STATS"
>>
>> "Nested Loop (cost=0.00..16.56 rows=1 width=15) (actual
>> time=7306.497..7362.836 rows=1 loops=1)"
>> " -> Index Scan using geometrien_geom_idx on geometrien geo
>> (cost=0.00..8.27 rows=1 width=8) (actual time=7306.475..7362.812
>> rows=1 loops=1)"
>> " Index Cond: (the_geom &&
>> '010100002015550000861F9CF7B5D7244163D17416D81E0E41'::geometry)"
>> " Filter: ((the_geom &&
>> '010100002015550000861F9CF7B5D7244163D17416D81E0E41'::geometry)
>> AND contains(the_geom,
>> '010100002015550000861F9CF7B5D7244163D17416D81E0E41'::geometry)
>> AND (regtyp = 8))"
>> " -> Index Scan using bezeichnungen_pkey on bezeichnungen bez
>> (cost=0.00..8.27 rows=1 width=17) (actual time=0.015..0.016 rows=1
>> loops=1)"
>> " Index Cond: ((bez.regtyp = 8) AND (bez.regcode =
>> geo.regcode))"
>> "Total runtime: 7362.916 ms"
>>
>> Thanks for any help
>>
>>
>> Stephan Grüter
>>
>> Wüest & Partner
>> Gotthardstr. 6
>> 8002 Zürich
>> 044 289 90 32
>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
More information about the postgis-users
mailing list