Re: possible TODO: read-only tables, select from indexes only.

Russell Smith wrote:
> On Sat, 23 Apr 2005 03:14 am, Bruce Momjian wrote:
> > Hannu Krosing wrote:
> > > On R, 2005-04-22 at 11:40 -0400, Bruce Momjian wrote:
> > > > See this TODO:
> > > >
> > > > * Allow data to be pulled directly from indexes
> > > >
> > > > Currently indexes do not have enough tuple visibility information
> > > > to allow data to be pulled from the index without also accessing
> > > > the heap. One way to allow this is to set a bit to index tuples
> > > > to indicate if a tuple is currently visible to all transactions
> > > > when the first valid heap lookup happens.
> > >
> Storing visibility information in the index has always been put down
> as a cause of performance problems. Would it be plausible to have an
> index type that included the information and one that didn't. You
> could choose which way you wanted to go.
>
> I know especially for some tables, I would choose this index with
> visibility as it would increase performance by not looking at the table
> at all for that information (count being a good example). However for
> general purpose indexes I would use the normal index with no visibility
> information.
>
> The possibly of the bit method or full tuples is probably a decision
> for others, but having the flexibility to choose in this would be a
> great thing.

I thought a little bit about the complexity. I think we should use a
heap bit to say if the index "all visible" bit is set. The idea is that
you only set the index "all visible" bit after the tuple has been
visible for a while, perhaps 1000 transactions, and a GUC could countrol
that number. I assume that will reduce the overhead of clearing the
index "all visible" bit on an UPDATE or DELETE to an acceptable load.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073