> On Mon, 19 Nov 2001, Bruce Momjian wrote:
>
> > > > There is not much point in arguing a specific query case,
> > > It is no specific query case. It is the speed of an index scan which
> > > goes like N if you do it with PostgreSQL and it goes like log N if
> > > you do not have to look back into the table like MS SQL server does.
> >
> > Have you tried using CLUSTER to match the heap order with the index
> > order. That should help with index scans looking up heap rows.
> Yes, I?ve tried even that and it increase PostgreSQLs performance a little
> bit for this special query but it did not get nearly the speed of the
> same query on the MS SQL server. Moreover there are tables with more than
> one index and I guess it makes only sense to cluster one index per table.
Yes, CLUSTER only matches one index.
Something I just realized, that other probably figured out, is that
while we have plans to backfill expired tuple status into the index
tuples, it is not easy to backfill enough information to know a tuple is
valid.
Setting aside the problem of different tuple visibilities for different
backends, one problem is that when we go to expire a tuple, we would
have to update all the index tuples that point to the heap tuple. That
is an expensive operation because you have to use the keys in the heap
to find the index.
So, while we do have plans to mark some index tuples so we _know_ they
are expired, we don't know how to efficiently mark index tuples so we
_know_ they are valid.
This is what I believe you want, where we can scan the index without
checking the heap at all.
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026