From: Gavin Sherry <swm@alcove.com.au>
To: ohp@pyrenet.fr
Cc: pgsql-performance@postgresql.org
Subject: Re: index as large as table
Date: Sat, 20 Aug 2005 23:08:13 +1000 (EST)
Message-ID: <Pine.LNX.4.58.0508202302270.4243@linuxworld.com.au> (raw)
In-Reply-To: <Pine.UW2.4.53.0508201413430.7116@sun.pyrenet>
References: <Pine.UW2.4.53.0508201413430.7116@sun.pyrenet>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: swm@alcove.com.au, ohp@pyrenet.fr
Subject: Re: index as large as table
In-Reply-To: <Pine.LNX.4.58.0508202302270.4243@linuxworld.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox