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

[Nicholas Bower]

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

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
(from 65s).

Saving 15s and losing caching seems like a bit of a false win no?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100525/475aa10b/attachment.html>