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

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>