Date: Sat, 20 Aug 2005 23:08:13 +1000 (EST)
From: Gavin Sherry <swm@alcove.com.au>
To: ohp@pyrenet.fr
Cc: pgsql-performance@postgresql.org
Subject: Re: index as large as table
In-Reply-To: <Pine.UW2.4.53.0508201413430.7116@sun.pyrenet>
Message-ID: <Pine.LNX.4.58.0508202302270.4243@linuxworld.com.au>
References: <Pine.UW2.4.53.0508201413430.7116@sun.pyrenet>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Sat, 20 Aug 2005 ohp@pyrenet.fr wrote:

> Hi,
>
> While testing 8.1dev I came to this:
>
> CREATE TABLE t (
> a int,
> b int
> PRIMARY KEY (a,b));
>
> In  that case, the index is as big as the table.

Right. Think about it: the index must store a, b, a reference to the data
in the table itself and index meta data. If an index is defined across all
columns of the table, it must be bigger than the table itself. (In
PostgreSQL, when the table is small, the index will be smaller still. This
is because of each entry in the table itself has meta data. But the amount
of data per row of a table remains constant, whereas, the amount of
metadata in an index grows.)

> My question is is it worthwhile to have such index peformance wise.
> I understand I'd loose uniqness buthas such an index any chance to be used
> against seq scan.

Of course. The idea is that, generally speaking, you're only interested in
a small portion of the data stored in the table. Indexes store extra data
so that they can locate the portion you're interested in faster.

Gavin