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

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

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Hannu Krosing <hannu(at)skype(dot)net>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: possible TODO: read-only tables, select from indexes only.
Date: 2005-04-22 15:40:41
Message-ID: 200504221540.j3MFefZ02176@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
See this TODO:
	
	* Allow data to be pulled directly from indexes
	
	  Currently indexes do not have enough tuple visibility information
	  to allow data to be pulled from the index without also accessing
	  the heap.  One way to allow this is to set a bit to index tuples
	  to indicate if a tuple is currently visible to all transactions
	  when the first valid heap lookup happens.  This bit would have to
	  be cleared when a heap tuple is expired.

I think this is the direction we should be heading because it has more
general usefulness.

---------------------------------------------------------------------------

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 Krosing <hannu(at)skype(dot)net>
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

Responses

pgsql-hackers by date

Next:From: Dave HeldDate: 2005-04-22 15:44:55
Subject: Re: Woo hoo ... a whole new set of compiler headaches!! :)
Previous:From: Alvaro HerreraDate: 2005-04-22 15:34:26
Subject: Re: Woo hoo ... a whole new set of compiler headaches!!

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