Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> On Tue, Nov 18, 2003 at 02:31:03PM -0800, Sailesh Krishnamurthy wrote:
>
> > Another thing I toyed with was having an implementation of a
> > Tid-List-Fetch .. sorting a TID-list from an index and fetching the
> > records of the relation off the sorted list for better IO
> > performance. AFAICT something like this isn't present yet .. can pgsql
> > do this already ?
I think you're talking about situations like"where x = ? or y = ?"
or"where x = ? and y = ?"
When both `x' and `y' are indexed. It's possible to do the index lookup,
gather a list of tid pointers in some efficient format like a bit vector, and
apply the and/or and any other clauses.
Oracle can do this, and it's useful in some cases when you have DSS-style
where all the indexes have poor selectivity but using enough of them together
gets you a reasonable number of records.
In my experience it was never really very useful. I suspect there are specific
situations where it makes a big difference though.
> Nope. In fact it's on the TODO list. I think people call it "bitmap
> indexes", the idea being that the TID list is represented as a "sparse
> bitmap" (go ask some JPEG hacker what that means).
>
> Extra points if multiple bitmaps can be ANDed and ORed, to allow using
> multiple indexes simultaneously ...
I think this is different from what he meant, but yes, bitmap indexes might be
an interesting project. Like hash indexes they have limited uses, but when
they're useful they're VERY useful. In Oracle they're mainly useful for
low-cardinality attributes like flags on rarely-updated tables. (Actually I
wonder whether locking in postgres would be simpler than locking Oracle
because of the no-update style of MVCC, hmm.)
--
greg