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

From: Hannu Krosing <hannu(at)tm(dot)ee>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: possible TODO: read-only tables, select from indexes only.
Date: 2005-04-22 15:34:09
Message-ID: 1114184049.5871.44.camel@fuji.krosing.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)skype(dot)net>

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2005-04-22 15:34:26 Re: Woo hoo ... a whole new set of compiler headaches!!
Previous Message Hannu Krosing 2005-04-22 15:17:38 possible TODO: read-only tables, select from indexes only.