[postgis-users] Query Performance Problem

Dirk Jesko jesko at geocontent.de
Thu Jan 27 08:29:48 PST 2005


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.


Kind regards,

Dirk





More information about the postgis-users mailing list