Re: Indexed views?

On Sat, 11 Sep 2004, Tiago Wright wrote:

> IMHO, it is worth duplicating the mvcc data to all index entries. To
> summarize what I understand from this discussion, with the current
> method:
>
> a1 - Index seeks must return invisible tuples because mvcc data is not
> found in the index. These tuples are eliminated once the data is read
> from the actual data pages.
>
> a2 - Covered queries are not possible since the data page must be
> visited to determine visibility

a4 - Indexes must be fully vacuumed before vacuuming the corresponding
heap entries

> If mvcc data is replicated to the index entries:
>
> b1 - Index seeks will never return invisible tuples, possibly
> eliminating some page reads
>
> b2 - Covered queries are possible
>
> b3 - Inserts are not affected performancewise. Deletes must now visit
> every index entry, which is a larger cost. Updates must visit every
> index entry too. It may be possible to reduce the cost of update if
> the indexed data is not affected, since the new index entry will
> likely end up in the same page as the index entry that must be
> deleted, so no extra page reads would be necessary in this scenario.

b4 - Heap and index pages can be vacuumed independently.

> Since the great majority of performance issues are related to select
> queries, the benefit of eliminating invisible tuple page fetches and
> supporting covered queries probably outweight the extra cost of
> updating index entries. And once covered queries are supported, it
> would be possible to build indexed views or multi-table indexes that
> can address some of the most performance demanding queries out there.
>
> I am wondering whether it would be possible to measure the costs of a1
> and a2 above and compare with the probable costs for b3. It seems to
> me that applications for which b3 are most expensive are also those
> for which a1 would be most expensive, and since selects are much more
> common than updates, could one offset the other in the long run? Can
> anyone shed some light on these?

If it seems that there are some cases where it's better to have the
visibility information in the index and some cases where not, I think we
could support both kinds of indexes and let the DBA choose.

- Heikki