On 25 May 2010 22:35, Mark Cave-Ayland <mark.cave-ayl...@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?
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users