pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedindex as large as table
3+ messages / 3 participants
[nested] [flat]
* index as large as table
@ 2005-08-20 12:18 ohp@pyrenet.fr
2005-08-20 13:08 ` Re: index as large as table Gavin Sherry <swm@alcove.com.au>
0 siblings, 1 reply; 3+ messages in thread
From: ohp@pyrenet.fr @ 2005-08-20 12:18 UTC (permalink / raw)
To: pgsql-performance
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.
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.
Is there any chance we have a "btree table" in the future for that case?
Regards,
--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges +33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: ohp@pyrenet.fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: index as large as table
2005-08-20 12:18 index as large as table ohp@pyrenet.fr
@ 2005-08-20 13:08 ` Gavin Sherry <swm@alcove.com.au>
2005-08-20 15:10 ` Re: index as large as table Steinar H. Gunderson <sgunderson@bigfoot.com>
0 siblings, 1 reply; 3+ messages in thread
From: Gavin Sherry @ 2005-08-20 13:08 UTC (permalink / raw)
To: ohp@pyrenet.fr; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: index as large as table
2005-08-20 12:18 index as large as table ohp@pyrenet.fr
2005-08-20 13:08 ` Re: index as large as table Gavin Sherry <swm@alcove.com.au>
@ 2005-08-20 15:10 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
0 siblings, 0 replies; 3+ messages in thread
From: Steinar H. Gunderson @ 2005-08-20 15:10 UTC (permalink / raw)
To: pgsql-performance
On Sat, Aug 20, 2005 at 11:08:13PM +1000, Gavin Sherry wrote:
> 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.
I think his question was more why you needed the data in itself, when you had
everything you needed in the index anyway. (Actually, you don't -- indexes
don't carry MVCC information, but I guess that's a bit beside the point.)
There has been discussion on "heap tables" or whatever you'd want to call
them (ie. tables that are organized as a B+-tree on some index) here before;
I guess the archives would be a reasonable place to start looking.
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-08-20 15:10 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-20 12:18 index as large as table ohp@pyrenet.fr
2005-08-20 13:08 ` Gavin Sherry <swm@alcove.com.au>
2005-08-20 15:10 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
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