[postgis-users] postgis and mapserver - queries

[Carl Anderson]

Dave,
I have been running some rather complex queries under
PG 7.3 and mpaserver for some time now.  I have not seen anything that 
gives me concern with regard to speed.

to illustracte the effects of parser setting on executions
i ran some timings


roads ( table with 22 items and 100,000 records )
roads_geo ( table with 4 items and 100,000 records )
roads_vw ( view with 25 items connecting roads and road_geo with an 
inner join )

timings are taken by setting timings on in psql (\timing)
several samples are taken for each test


The shape field referenced is a GEOMETRY type for added complexity.


SELECT * FROM (  SELECT full_name, sum(length(shape)) FROM roads_vw 
GROUP BY full_name) as foo LIMIT 0;

set explain_pretty_print=off;
explain verbose		3.85ms		3.96ms		3.94ms

set explain_pretty_print=on;
explain verbose		7.37ms		7.45ms		7.41ms
explain analyze		7627.60ms	8216.97ms	7692.21ms
run stmt 		7446.63ms	7701.39ms	7825.01ms



Compare to the view only
SELECT * from roads_vw LIMIT 0;

set explain_pretty_print=off;
explain verbose		7.00ms		7.05ms		7.00ms

set explain_pretty_print=on;
explain verbose		18.64ms		18.68ms		18.68ms
explain analyze		6088.65ms	5914.64ms	7180.17ms
run stmt		6821.49ms	8423.46ms	6410.52ms



and the simplest case
SELECT * from roads LIMIT 0;

set explain_pretty_print=off;
explain verbose		2.59ms		2.01ms		3.05ms

set explain_pretty_print=on;
explain verbose		1.67ms		2.01ms		1.25ms
explain analyze		1.87ms		1.81ms		1.82ms
run stmt		1.68ms		1.67ms		1.71ms



I see that EXPLAIN VERBOSE varies by 18 while the statement
itself (and the explain analyze) varies by 4000.

I suspect the the select * from view takes the longest because it hase 
the most items to describe (from multiple tables).

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



C.