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

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

From: Hannu Krosing <hannu(at)tm(dot)ee>
To: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: Russell Smith <mr-russ(at)pws(dot)com(dot)au>,PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: possible TODO: read-only tables, select from indexes
Date: 2005-04-23 09:17:26
Message-ID: 1114247846.6004.37.camel@fuji.krosing.net (view raw or flat)
Thread:
Lists: pgsql-hackers
On L, 2005-04-23 at 00:02 -0400, Bruce Momjian wrote:
> Russell Smith wrote:
...
> > I know especially for some tables, I would choose this index with
> > visibility as it would increase performance by not looking at the table
> > at all for that information (count being a good example).  However for
> > general purpose indexes I would use the normal index with no visibility
> > information.
> > 
> > The possibly of the bit method or full tuples is probably a decision
> > for others, but having the flexibility to choose in this would be a
> > great thing.
> 
> I thought a little bit about the complexity.   I think we should use a
> heap bit to say if the index "all visible" bit is set.  The idea is that
> you only set the index "all visible" bit after the tuple has been
> visible for a while, perhaps 1000 transactions, and a GUC could countrol
> that number.  I assume that will reduce the overhead of clearing the
> index "all visible" bit on an UPDATE or DELETE to an acceptable load.

Having the full index either CHECK-FROM-HEAP or INDEX-ONLY makes
planning decisions much simpler. If the index has some leafs that
require heap access and others that don't,  we need to also start
keeping some statistics about what proportion of index lookups result in
heap accesses, possibly correlated with certain index regions.

I think that starting from something simple (like read-only tables :)
gives most gain for effort.

-- 
Hannu Krosing <hannu(at)tm(dot)ee>

In response to

pgsql-hackers by date

Next:From: Stephen FrostDate: 2005-04-23 13:02:37
Subject: Re: Postgres: pg_hba.conf, md5, pg_shadow, encrypted
Previous:From: Greg StarkDate: 2005-04-23 08:02:05
Subject: Re: Bad n_distinct estimation; hacks suggested?

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