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

On 22 May 2012 19:01, Robert Haas <robertmhaas@gmail.com> wrote:
> 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.

Agreed

>  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.

Not sure whether you see that as an argument against the proposal.
This argument applies to any index. In particular covered indexes are
specifically encouraged by index only scans, so is not a reason to
avoid implementing the feature as Jeff describes.

The main reason for the feature as described by Jeff is that it avoids
having 2 indexes when only one is required. In 9.2, with index only
scans, Jeff showed elsewhere that we can get an amazing speed up by
having a covered index. However, what Jeff is noticing is that he
needs 2 indexes on the table: 1 PK on (aid) and another index on (aid,
abalance). The first index can be avoided altogether, allowing a good
improvement in cache efficiency and general performance.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services