Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group