[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