[postgis-users] No index usage on geography query plan?
mark.cave-ayland at siriusit.co.uk
Tue May 25 05:35:10 PDT 2010
Nicholas Bower wrote:
> Neither of the ST_Intersects clauses below invoke index usage according
> to explain output, despite docs saying they should automatically be
> doing bbox on the index;
> What's going on - the difference in total cost above proves to me the
> indexes are not being used.
I think you're missing the point here; the aim of the planner is to work
out which join order will produce the query that executes in the
shortest time. Therefore just because an index is present does not
necessarily mean it is correct to use it.
From your first query (where the spatial index is not being used):
Total runtime: 44744.241 ms
From your second query (where the spatial index is being used):
Total runtime: 65952.140 ms
So I'd say that this is working exactly as it should be, since the join
order chosen by the planner has resulted in the shortest query time.
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
t: +44 870 608 0063
Sirius Labs: http://www.siriusit.co.uk/labs
More information about the postgis-users