proposal for smaller indexes on index-ordered tables

From: "Jeffrey Baker" <jwbaker(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: proposal for smaller indexes on index-ordered tables
Date: 2008-06-24 20:34:27
Message-ID: fd145f7d0806241334v34cc4a17p3987367ebcc5286f@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

The way I read it, the current btree index stores the index value and the
TID of every tuple having that value. When you have a table with three
columns, you index one of them and you get an index which is practically as
large as the table itself.

Supposing the table is generally or strictly ordered by the column to be
indexed, it would be more compact if the index stored ranges of tuples.
Instead of storing the TID of every tuple with that value, the index would
store a first and last TID, between which all tuples have the value.

Example: table with one million rows indexed on a column having one thousand
distinct values. Table is in-order by the indexed column. The traditional
index would contain a million TIDs, whereas a range index would contain only
two thousand. The range index would be 500 times smaller, more likely to be
cached, etc.

Thoughts?

-jwb

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jonah H. Harris 2008-06-24 20:50:14 Re: proposal for smaller indexes on index-ordered tables
Previous Message Magnus Hagander 2008-06-24 20:27:28 Re: Git Repository for WITH RECURSIVE and others