Thread: index as large as table
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)
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
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/