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: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: possible TODO: read-only tables, select from indexes
Date: 2005-04-22 20:13:21
Message-ID: 1114200801.6004.20.camel@fuji.krosing.net (view raw or flat)
Thread:
Lists: pgsql-hackers
On R, 2005-04-22 at 13:14 -0400, Bruce Momjian wrote:
> > This can be prohibitively pricey for big tables with lots of indexes, as
> > marking the tuple means alsn finding and possibly writing to all index
> > enytries pointing to this tuple.
> 
> Yep, it could be very ugly, but it would help with our COUNT(*) problem
> too.  Isn't there a solution?  If there isn't, I can remove the TODO
> item.
> 
> > > I think this is the direction we should be heading because it has more
> > > general usefulness.
> > 
> > OTOH this will probably never be implemented for the same reason that
> > full visibility in index tuples will not, whereas my proposition can be
> > implemented quite easily (it's just a SMOP).
> 
> I would like to find something that has more general usefulness.

So would I :)

But I assure you that this would be generally usefull in DataWarehousing
applications, where you have to play partitioning tricks anyway and
making some sub-table RO and running REINDEX on it would add little
complexity..

This would ease my current problem of running queries over tables with
more than  >100 M rows and getting the results in reasonable time.

My setup is a big logical table, which consists of many inherited
tables, filled one after another from OLAP database. After each 5 to 10
M rows old table is frozen and new table started. 

So when I run a query that uses an index, which does not correlate well
with primary_key and timestamp, postgres finds the needed rows from
index quickly and spends most of the time waiting for seeks from heap-
tuple accesses for visibility checks, which is pure wasting of resources
as they all succeed.

I guess avoiding heap tuple lookups could make some of the queries run
10's maybe 100's of times faster, as index tuples are naturally
clustered.


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

In response to

pgsql-hackers by date

Next:From: Dave HeldDate: 2005-04-22 20:30:38
Subject: Re: Woo hoo ... a whole new set of compiler headaches!! :)
Previous:From: Antoine MartinDate: 2005-04-22 20:02:21
Subject: Re: Postgres: pg_hba.conf, md5, pg_shadow, encrypted

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