[postgis-users] postgis and mapserver - queries

[David Blasby]

I recently changed mappostgis.c so its a bit wiser about how it 
determines the columns in a query.  Previously, it did this:

EXPLAIN VERBOSE <query>;

The advantage of this is that it is extreamly quick.  Unfortunately, the 
EXPLAIN VERBOSE command changes its output with different versions of 
postgresql.  For example, in 7.1 and 7.2, the EXPLAIN VERBOSE is set as 
a NOTICE message.  In 7.3 its sent as a query result (and is formated 
differently).

The new mappostgis.c determines columns like this (<query> is basically 
the query in your DATA statement):

SELECT * FROM (<query>) as foo LIMIT 0;

Basically, it executes the query asking for zero rows.  In most cases 
this will return a result (where you can determine the column names and 
types easily) quickly.  Unfortunately, i noticed that it can actually 
take a long time to execute.  For example, imagine you have a big table 
with road data in it:

SELECT * FROM (  SELECT gid, sum(len) FROM roads GROUP BY gid) as foo 
LIMIT 0;

This can take many seconds to compute because postgresql will do the 
entire GROUP BY (which involves a SORT) before it can start returning 
results.

I was thinking that you can use the fact you know the unique column 
attribute ("using unique <column>") and the geometry column's name to 
speed things up by reducing the query set:

SELECT * FROM (<query>) as foo WHERE <unique column> = -666 AND 
<geom_column> && 'BOX3D(0 0, 0 0)' LIMIT 0;

[Here -666 and the BOX3D are just made up to reduce the query results]

But this seems pretty hacky (and will not always work cut down on the 
work) - anyone else have ideas?

dave