On Tue, May 22, 2012 at 1:41 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Jeff Janes <jeff.ja...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
