Re: Add primary key/unique constraint using prefix columns of an index

On Tue, May 22, 2012 at 1:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jeff Janes <jeff.janes@gmail.com> writes:
>> Now that there are index only scans, there is a use case for having a
>> composite index which has the primary key or a unique key as the
>> prefix column(s) but with extra columns after that.  Currently you
>> would also need another index with exactly the primary/unique key,
>> which seems like a waste of storage and maintenance.
>
>> Should there be a way to declare a "unique" index with the unique
>> property applying to a prefix of the indexed columns/expression?  And
>> having that, a way to turn that prefix into a primary key constraint?
>
>> Of course this is easier said then done, but is there some reason for
>> it not to be a to-do item?
>
> Um ... other than it being ugly as sin?  I can't say that I can get
> excited about this concept.  It'd be better to work on index-organized
> tables, which is really more or less what you're wishing for here.
> Duplicating most of a table into an index is always going to be a loser
> in the end because of the redundant storage.

An index on pgbench_accounts (aid, abalance) is the same size as an
index on pgbench_accounts (aid), but even if it were larger, there's
no theoretical reason it couldn't have enough utility to justify its
existence.   A bigger problem is that creating such an index turns all
of pgbench's write traffic from HOT updates into non-HOT updates,
which means this is probably only going to be a win if the write
volume is miniscule.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company