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

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

From: Jochem van Dieten <jochemd(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: possible TODO: read-only tables, select from indexes only.
Date: 2005-04-22 15:53:30
Message-ID: f96a9b830504220853670bf7cd@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On 4/22/05, 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

Why stop at indexes? If you switch to readonly status with a CLUSTER
or ALTER TABLE command you can even remove the visibility information
from the heap tuples. Visibility would be exclusively controlled by
the visibility of the table in the catalog, i.e. all or nothing.

Jochem

In response to

Responses

pgsql-hackers by date

Next:From: Stephen FrostDate: 2005-04-22 15:53:49
Subject: Re: Postgres: pg_hba.conf, md5, pg_shadow, encrypted passwords
Previous:From: Dave HeldDate: 2005-04-22 15:44:55
Subject: Re: Woo hoo ... a whole new set of compiler headaches!! :)

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