Proposal Clarification. I see that discussion become too complicated. So, I'd like to clarify what we are talking about.
We are discussing 2 different improvements of index. The one is "partially unique index" and the other "index with included columns". Let's look at example.
- We have a table tbl(f1, f2, f3, f4). - We want to have an unique index on (f1,f2). - We want to have an index on (f1, f2, f3) which allow us to use index for complex "where" clauses.
Can someone write a query where F3 being ordered is a contribution?
If F1 and F2 are unique, adding F3 to a where or order by clause doesn't seem to contribute anything.
-- Already fully ordered by F1,F2
SELECT ... ORDER BY F1, F2, F3;
-- F3 isn't in a known order without specifying F2
SELECT ... WHERE F1 = ? ORDER BY F1, F3;
-- Index resolves to a single record; nothing to order
SELECT ... WHERE F1 = ? AND F2 = ? ORDER BY F3;
-- Without a where clause, the index isn't helpful unless F3 is the first column
SELECT ... ORDER BY F3;
What is it that I'm missing?
Joining relations may have more than one matching tuple for any given unique tuple, therefore the tuples may no longer be unique on the columns which are in the unique index.
https://commitfest.postgresql.org/6/129/ takes steps to add infrastructure to the planner to allow it to know when this happens. Although I'm currently "selling" it as a performance improvement patch.