[postgis-users] Performance tuning PostGIS and understanding the explain statement

[David Blasby]

The difference between 'EXPLAIN' and 'EXPLAIN ANALYSE' is simple - 
'EXPLAIN ANALYSE' actually does the query and reports the actual time it 
takes to do each step.

So "actual" time is in millisecond, and the other times are estimated 
times in some arbitrary units.  Postgresql uses these estimated times to 
determine which is the best query plan to excecute.

A brief look at your query seems to indicate that postgresql is using 
the spatial index over the attribute index (region.id=28).  If you use 
the PostGIS stats package (search the mailing list for histogram2d) you 
will probably find it estimates a better plan.

You can also try dropping the spatial index and see what you get.

The problem is that you are doing a full spatial join of the region and 
feature tables.

dave