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

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.

I don't think this is implementable in any reasonably cheap way (i.e.
this will be a general performance loss).

This has all the downsides of storing full visibility in index tuples,
except the size.

> This bit would have to be cleared when a heap tuple is expired.

Does "expired" here mean marked for deletion ?

This can be prohibitively pricey for big tables with lots of indexes, as
marking the tuple means alsn finding and possibly writing to all index
enytries pointing to this tuple.

> I think this is the direction we should be heading because it has more
> general usefulness.

OTOH this will probably never be implemented for the same reason that
full visibility in index tuples will not, whereas my proposition can be
implemented quite easily (it's just a SMOP).

> ---------------------------------------------------------------------------
>
> Hannu Krosing wrote:
> > Fetching data from just indexes has been discussed on this list several
> > times before, and it has been told that this can't be done with postgres
> > thanks to MVCC.
> >
> > But this is true only when data is changing. In a data-warehousing
> > scenario what it is often needed is a possibility for fast querying of
> > static historical data.
> >
> > If table has been VACUUM'ed or REINDEX'ed after last change
> > (update/delete/insert), then there is 1-1 correspondence between table
> > and indexes, and thus no need to fetch the tuple from heap for extra
> > visibility checks.
> >
> > What I am proposing is
> >
> > 1) possibility to explicitly change table status to READ-ONLY .
> >
> > 2) setting a flag CAN_OMIT_HEAP_CHECK after REINDEX TABLE for tables
> > that are READ-ONLY
> >
> > 3) changing postgres planner/executor to make use of this flag, by not
> > going to heap for tuples on tables where CAN_OMIT_HEAP_CHECK is true.
> >
> > --
> > Hannu Krosing <hannu(at)skype(dot)net>
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faq
> >

--
Hannu Krosing <hannu(at)skype(dot)net>