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

[Benjamin Wragg]

I checked the list for the histogram2d and I found the treads I think
you were talking about. So the statement SELECT UPDATE_GEOMETRY_STATS();
runs a function which fills the content of the histogram2d field in the
geometry_columns table. This is then used by the query planner when a
query is executed to better estimate which path to take? E.g which index
to use first

-----Original Message-----
From: David Blasby [mailto:dblasby at refractions.net] 
Sent: Friday, 13 June 2003 9:14 AM
To: bwragg at tpg.com.au; PostGIS Users Discussion
Subject: Re: [postgis-users] Performance tuning PostGIS and
understanding the explain statement

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