[postgis-users] Another Question on PostGIS Performance

[david blasby]

Thomas,

You're asking postgis to do a table join - thats a fairly time consuming
operation, but I'm surprised its taking 10+ seconds.  What kind of hardware
are you using.

How much data is in your tables and how large a spatial area are you
searching in?  Whats the distribution of values for the "pg" column?

The other problem could be postgresql is doing a full table join, then using
the spatial index.  There is a way of forcing it to do it first, but its a
bit complex.

To find out about the SQL statement mapserver is executing:
1. Make sure you're using a modern mappostgis.c
2. Cause an error: just change the "select" word to "ErrorMe" in your DATA
statement.
3. Mapserver will report an error, and it will give you the offending SQL
statement (if not, see #1)
4. The SQL statement will look something like:
     DECLARE mycursor BINARY CURSOR FOR SELECT <bunch of columns> FROM (...)
as foo WHERE the_geom && BOX3D(...)
5. remove the beginning "DECLARE mycursor BINARY CURSOR FOR" from the sql,
and replace it with "EXPLAIN"
6. fix the "ErrorMe" typo
7. execute the EXPLAIN command in psql

This will tell you how postgresql is executing your query.

dave