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

[Benjamin Wragg]

Hi Dave,

I see the difference between Explain and Explain Analyse. Thanks. So
you're saying every region is being joined with every feature, then it
selects the region with an id of 28?

I don't get how you know that the spatial index is being used over the
attribute index from just looking at these explain statements?


Thanks,

Benjamin
-----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