Hi, hackers!
Use case:
Index-only scans is a wonderful feature that allows to speed up select
queries of indexed columns.
Therefore some users want to create multicolumn indexes on columns which
are queried often.
But if there's unique constraint on some column, they have to maintain
another unique index.
Even if the column is already in covering index.
This adds overhead to data manipulation operations and database size.
I've started work on a patch that allows to combine covering and unique
functionality.
The main idea is to allow user create multicolumn indexes with a
definite number of unique columns.
For example (don't mind SQL syntax here, please):
CREATE INDEX index ON table (c1, c2, c3) UNIQUE ON (c1, c2);
Created index has three columns, but only two of them have unique
constraint.
This idea has obvious restriction. We can set unique only for first
index columns.
There is no clear way to maintain following index.
CREATE INDEX index ON table (c1, c2, c3) UNIQUE ON (c1, c3);
So I suggest following syntax:
CREATE [UNIQUE {ON FIRST {COLUMN | n_unique_column COLUMNS}} INDEX ON
table_name (column_name1, column_name2 ...);
Examples:
CREATE UNIQUE INDEX ON table_name (c1, c2, c3); // (c1,c2, c3) must be
UNIQUE. That's how it works now.
CREATE UNIQUE ON FIRST COLUMN INDEX ON table_name (c1, c2, c3); // (c1)
must be UNIQUE
CREATE UNIQUE ON FIRST 2 COLUMNS INDEX ON table_name (c1, c2, c3); //
(c1,c2) must be UNIQUE
CREATE UNIQUE ON FIRST 3 COLUMNS INDEX ON table_name (c1, c2, c3); //
(c1,c2, c3) must be UNIQUE
Next issue is pg_index changes.
Now there's only a boolean flag
* bool indisunique; /* is this a unique index? */
But new algorithm requires to store a single number
* unit16n_unique_columns; /* number of first columns of index which
has unique constrains. */
I think, that numbers of all attributes themselves are not needed. Is it
right?
I'd like to see your suggestions about syntax changes.
And of course any other comments are welcome.
--
Anastasia Lubennikova
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company
