[postgis-users] Query Performance Problem

[strk at refractions.net]

On Thu, Jan 27, 2005 at 05:29:48PM +0100, Dirk Jesko wrote:
> I am experimenting with PostGIS (currently 1.0RC2) and the UMN Mapserver 
> on a Windows System. Today I encountered the following problem:
> 
> 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;

> 
> 
> Kind regards,
> 
> Dirk
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users