[postgis-users] Query Performance Problem

Dirk Jesko jesko at geocontent.de
Fri Jan 28 02:20:05 PST 2005

Ok, here it comes. The two queries in question and the EXPLAIN ANALYZE 
VERBOSE results are attached (test1 is the long running, test3 the 
"normal" one). The joins are quite different. I think, that especially 
the different placement of the contains(...)-condition from 
infogis_admin might be an issue.


>On Thu, Jan 27, 2005 at 05:29:48PM +0100, Dirk Jesko wrote:
>>I have a table "admingebiete" with about 85000 tuples. Each tuple has a 
>>primary key ("gid"), geometry ("the_geom" with multipolygons), a feature 
>>type ("feat_type") and a few other attributes. "gid", "the_geom" and 
>>"feat_type" are indexed. Since I need only a certain area, which is 
>>defined by some features in "admingebiete", I create a view that 
>>calculates the boundary of that area, e.g.:
>>CREATE VIEW infogis_area AS
>>SELECT geomunion(admingebiete.the_geom) AS the_geom
>>  FROM admingebiete
>> WHERE admingebiete.gid in (410, 422, 423, 424, 426, 427, 430);
>>This one takes about 1.3s. Next, I create a view that selects all areas 
>>from "admingebiete" contained within that area:
>>SELECT a.oid, a.polygon_nm, a.feat_type, a.the_geom
>>  FROM admingebiete a, infogis_area b
>> WHERE a.the_geom && b.the_geom AND contains(b.the_geom, a.the_geom);
>>Mapserver uses this query as the data source for multiple layers. A 
>>filter based on the "feat_type" attribute is added, e.g. 
>>"feat_type"='GEMEINDE' (this one takes ~10s for 303 rows).
>>Now the problem: Mapserver adds another term to the where-clause which 
>>specifies the area to display,e.g.:
>>SELECT asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text
>>from infogis_admin
>>WHERE (feat_type='GEMEINDE') and (the_geom && setSRID( 
>>'BOX3D(10.0976912562163 51.6005810254548,13.4185945311196 
>>52.6039103320119)'::BOX3D,4326) )
>>As the result the query runs for ever ( >600s for 303 rows). However if 
>>I put the BOX3D into a table ("test" with just one tuple/no index) and 
>>change the query to:
>>SELECT asbinary(force_collection(force_2d(a.the_geom)),'NDR'),a.OID::text
>>from infogis_admin a, test t
>>WHERE (feat_type='GEMEINDE') and (t.the_geom && a.the_geom)
>>it delivers the 303 rows in ~10s again.
>>So, the questions are: Why does the query take for ever if the bounding 
>>box is specified as text rather than joining it via a table? Is there 
>>any way to get around this, other than creating separate tables 
>>containing just the areas needed? Latter approach works fine, but is 
>>difficult to handle.
>>I am currently doing some EXPLAIN ANALYZE for the queries to see whether 
>>there are substantial differences.
>EXPLAIN ANALYZE output is really needed here.
>Also, which postgresql version are you running ?
>I suspect this has to do with the the JOIN selectivity
>estimator giving more optimistic results then the RESTRICT
>selectivity estimator.

-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: test1_result.txt
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20050128/1ad90c4f/attachment.txt>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: test3_result.txt
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20050128/1ad90c4f/attachment-0001.txt>

More information about the postgis-users mailing list