[postgis-users] Query Performance Problem

[Mark Cave-Ayland]

Hi Dirk,

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of Dirk Jesko
> Sent: 27 January 2005 16:30
> To: PostGIS Users Discussion
> Subject: [postgis-users] Query Performance Problem
> 
> 
> 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. 

I'm not sure that creating a view for use in a query is the most efficient
way to do this, i.e. returning a single row, as the result of the
geomunion() can be cached assuming your data is static. If you're using a
SRID of -1, you can do something similar to this to cache the union of your
area into a new table called areas:

	CREATE TABLE areas AS (
		name text
	);

	SELECT AddGeometryColumn('', 'areas', 'union', -1, 'POINT', 2);

	INSERT INTO areas (name, union) VALUES SELECT 'infogis_area' AS
name, 
	geomunion(admingebiete.the_geom) AS union FROM admingebiete WHERE
	admingebiete.gid IN (410, 422, 423, 424, 426, 427, 430);

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

With the above in place, this could be replaced by:

	CREATE OR REPLACE VIEW infogis_admin AS
		SELECT a.oid, a.polygon_nm, a.feat_type, a.the_geom FROM
admingebiete a, 			(SELECT union FROM areas WHERE name
= 'infogis_area' LIMIT 1) AS b WHERE
		a.the_geom && b.the_geom AND contains(b.the_geom,
a.the_geom);

By using a join, the costly recalculation of geomunion() is avoided in your
main view that you are passing onto Mapserver.

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

As strk suggested, there may be an issue with the join selectivity for && -
I have a feeling that it may be the use of a view that is confusing it.
Please post your EXPLAIN ANALYZE outputs back to the list and we should have
a better idea of exactly what is happening.


Kind regards,

Mark.

------------------------
WebBased Ltd
South West Technology Centre
Tamar Science Park
Plymouth
PL6 8BT 

T: +44 (0)1752 791021
F: +44 (0)1752 791023
W: http://www.webbased.co.uk