Re: Including Snapshot Info with Indexes

On Mon, 2007-10-08 at 09:40 +0100, Heikki Linnakangas wrote:
> This idea has been discussed to death many times before. Please search
> the archives.

Somewhat related to the "visibility in index" thing: would it be
possible to have a kind of index-table ? We do have here some tables
which have 2-4 fields, and the combination of them forms the primary key
of the table. There are usually no other indexes on the table, and the
net result is that the PK index duplicates the heap. So it would be cool
if the index would be THE heap somehow...

The most prominent example of this in our DBs is this table:

db> \d table_a
Table "public.table_a"
Column | Type | Modifiers
-----------+--------+-----------
id_1 | bigint | not null
id_2 | bigint | not null
Indexes:
"pk_table_a" PRIMARY KEY, btree (id_1, id_2)

db> select reltuples::bigint, relpages from pg_class where
relname='table_a';
reltuples | relpages
-----------+----------
445286464 | 710090
(1 row)

db> select reltuples::bigint, relpages from pg_class where
relname='pk_table_a';
reltuples | relpages
-----------+----------
445291072 | 599848
(1 row)

This postgres install is compiled with 32K page size (for the ones who
wonder about the page counts). In any case, it is clear that the index
basically duplicates the heap...

Cheers,
Csaba.