[postgis-users] postgis and mapserver - queries

[Dave Blasby]

Carl,

>I have not seen a case where a statement runs quickly and a explain 
>verbose dose not.  Can you gave a more specific example?

No - explain verbose will always run quickly because its just parsing the SQL 
statement and generating the plan.  If you run the statement, it will parse, 
make a plan, then execute it.

The OLD way of pre-determining the columns a query would return was to run 
the "EXPLAIN VERBOSE <stmt>".  This would return a weird NOTICE event with the 
explain information in it.  The postgis-mapserver connector setup a NOTICE 
handler and would parse the information.  It was a bit hacky, but it worked 
well. 

When postgresql 7.3 came out, the "EXPLAIN VERBOSE <statement>" returned a 
query result (not a NOTICE) with the results of the query plan.  Of course, 
this broke the postgis-mapserver connector!  Who knows what will happen in 
future postgresql versions?

Paul asked me to change it so that it wasnt so hacky.  This is when I made it 
do "SELECT * FROM (<stmt>) as foo LIMIT 0" and look at what the headers for the 
result set said.  This works great in postgresql 7.1, 7.2, and 7.3. It will 
almost certainly work in 7.4 and future versions.

Unfortunately, I noticed that this query can actually take a while to execute - 
as your statements show.

The new way is a bit slow - its taking something that should take a few 
milliseconds and extending it to several seconds.  I'm not happy with this.


I guess an alternative would be to go back to the old "explain verbose" method, 
but its going to be more difficult to maintain.

dave