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

On Fri, 2005-04-22 at 18:34 +0300, 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,

I'm with you on this, for all sorts of reasons. I've done a lot of
detailed thought on this.

As Ron says, until we have partitioning, it isn't as useful for DW.

Best Regards, Simon Riggs