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

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. This bit would have to
be cleared when a heap tuple is expired.

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

---------------------------------------------------------------------------

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
>

--
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