[postgis-users] Query Performance Problem

[Dirk Jesko]

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.

Regards,
Dirk


>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:
>>
>>CREATE OR REPLACE VIEW infogis_admin AS
>>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.
>
>--strk;
>  
>

-------------- 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>