possible TODO: read-only tables, select from indexes only. - Mailing list pgsql-hackers

From Hannu Krosing
Subject possible TODO: read-only tables, select from indexes only.
Date
Msg-id 1114183058.5871.41.camel@fuji.krosing.net
Whole thread Raw
List pgsql-hackers
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@skype.net>



pgsql-hackers by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: possible TODO: read-only tables, select from indexes only.
Next
From: Antoine Martin
Date:
Subject: Re: Postgres: pg_hba.conf, md5, pg_shadow, encrypted