[postgis-users] No index usage on geography query plan?
nick at petangent.net
Tue May 25 05:45:42 PDT 2010
On 25 May 2010 22:35, Mark Cave-Ayland <mark.cave-ayland at siriusit.co.uk>wrote:
> 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.
A repeated explain analyze on the first query showed about 45s second time
around (same). However for the && version, second time around was just 2s
Saving 15s and losing caching seems like a bit of a false win no?
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the postgis-users