[postgis-users] Performance tuning PostGIS and understanding the explain statement
Benjamin Wragg
bwragg at tpg.com.au
Thu Jun 12 19:02:59 PDT 2003
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
More information about the postgis-users
mailing list