Re: Index oddity (still)

ken <southerland(at)samsixedd(dot)com> writes:
> Is there something wrong with rtree indexes on box data types?

Not per se, but the selectivity estimator for && is just a stub :-(.
Feel free to take a stab at improving this, or take a look at PostGIS
--- I think those guys are working the same problem even now.

> However, even if I *drop* the rtree index on the boundingbox
> column, so that it can't use that index, the optimizer does not choose
> the other index. Instead it reverts to doing a sequential scan of the
> entire table and its really slow.

This is also a lack-of-statistical-knowledge problem. The optimizer has
no stats about the distribution of length(lseg(boundingbox)) and so it
does not realize that it'd be reasonable to use that index for a query
like "length(lseg(boundingbox)) > largevalue". As of 7.5 this issue
will be addressed, but in existing releases I think you could only get
that index to be used by forcing it with enable_seqscan = off :-(

As a short-term workaround it might be reasonable to store that length
as an independent table column, which you could put an index on. You
could use triggers to ensure that it always matches the boundingbox.

regards, tom lane