[PROPOSAL] Covering + unique indexes. - Mailing list pgsql-hackers

Hi, hackers!<br /><div class="moz-forward-container"><br /> Use case:<br /> Index-only scans is a wonderful feature
thatallows to speed up select queries of indexed columns.<br /> Therefore some users want to create multicolumn indexes
oncolumns which are queried often.<br /> But if there's unique constraint on some column, they have to maintain another
uniqueindex.<br /> Even if the column is already in covering index.<br /> This adds overhead to data manipulation
operationsand database size.<br /><br /> I've started work on a patch that allows to combine covering and unique
functionality.<br/> The main idea is to allow user create multicolumn indexes with a definite number of unique
columns.<br/> For example (don't mind SQL syntax here, please):<br /> CREATE INDEX index ON table (c1, c2, c3) UNIQUE
ON(c1, c2);<br /> Created index has three columns, but only two of them have unique constraint.<br /><br /> This idea
hasobvious restriction. We can set unique only for first index columns.<br /> There is no clear way to maintain
followingindex.<br /> CREATE INDEX index ON table (c1, c2, c3) UNIQUE ON (c1, c3);<br /><br /> So I suggest following
syntax:<br/> CREATE [UNIQUE {ON FIRST {COLUMN | n_unique_column COLUMNS}} INDEX ON table_name (column_name1,
column_name2...);<br /><br /> Examples:<br /> CREATE UNIQUE INDEX ON table_name (c1, c2, c3); // (c1,c2, c3) must be
UNIQUE.That's how it works now.<br /><br /> CREATE UNIQUE ON FIRST COLUMN INDEX ON table_name (c1, c2, c3); // (c1)
mustbe UNIQUE<br /> CREATE UNIQUE ON FIRST 2 COLUMNS INDEX ON table_name (c1, c2, c3); // (c1,c2) must be UNIQUE<br />
CREATEUNIQUE ON FIRST 3 COLUMNS INDEX ON table_name (c1, c2, c3); // (c1,c2, c3) must be UNIQUE<br /><br /><div
class="line">Nextissue is pg_index changes.<br /> Now there's only a boolean flag<br /><div class="line"><ul><li><span
class="keywordtype">bool</span>indisunique; <span class="comment">/* is this a unique index? */</span></ul><span
class="comment">Butnew algorithm requires to store a single number<br /></span><ul><li>unit16<span class="comment">
n_unique_columns;/* number of first columns of index which has unique constrains. */<br /></span></ul> I think, that
numbersof all attributes themselves are not needed. Is it right?<br /></div></div><br /> I'd like to see your
suggestionsabout syntax changes. <br /> And of course any other comments are welcome.<br /><pre class="moz-signature"
cols="72">--
 
Anastasia Lubennikova
Postgres Professional: <a class="moz-txt-link-freetext" href="http://www.postgrespro.com"
moz-do-not-send="true">http://www.postgrespro.com</a>
The Russian Postgres Company</pre></div>