[postgis-users] No index usage on geography query plan?

[Mark Cave-Ayland]

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;

(cut)

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


HTH,

Mark.

-- 
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs