Re: Indexed views?

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Doug McNaught <doug(at)mcnaught(dot)org> writes:
> > Short answer: MVCC tuple visibility status isn't (and can't be) stored
> > in the index.
>
> Well, in principle it *could* be, but there are strong arguments why it
> shouldn't be: the costs of updating N index entries instead of just one
> tuple entry, the potential reliability hit (what happens when the index
> entries disagree with the master?), and the increase in index size
> (adding an extra dozen bytes to an index entry is a very nontrivial
> I/O hit).

Hm. Just thinking aloud here. But what if there was an option to store the
visibility information separately from the heap entirely. There would still
only be one copy of the visibility information and it wouldn't increase
storage or i/o requirements.

I'm assuming this would only make sense if the visibility information could be
stored on a separate spindle. Or at least if the application never uses
sequential scans, especially if the indexes cover the needed columns.

But if the table has particularly wide records, then it might be useful to
avoid having to read in the many blocks of records. Even if the index doesn't
cover the columns needed if there are many dead tuples (or not-yet-alive
tuples) reading the very densely packed visibility information might be faster
than reading the wide records.

Even for narrow tables, if the index covers the columns it would be faster to
read the even narrower visibility information alone. If the user opted to
*only ever* access the data via the index he could drop the actual heap
information and end up with a 90% solution for "index organized tables". The
visibility information would still be in a heap but not all the column data.

I'm not sure the benefits would really outweigh the costs, but it would
probably be simpler than storing duplicate visibility information in an index.

--
greg