Re: WIP: Covering + unique indexes.

Finally, completed patch "covering_unique_3.0.patch" is here.
It includes the functionality discussed above in the thread, regression tests and docs update.
I think it's quite ready for review.

Future work:
Besides that, I'd like to get feedback about attached patch "optional_opclass_3.0.patch".
It should be applied on the "covering_unique_3.0.patch".

Actually, this patch is the first step to do opclasses for "included" columns optional
and implement real covering indexing.

Example:
CREATE TABLE tbl (c1 int, c4 box);
CREATE UNIQUE INDEX idx ON tbl USING btree (c1) INCLUDING (c4);

If we don't need c4 as an index scankey, we don't need any btree opclass on it.
But we still want to have it in covering index for queries like

SELECT c4 FROM tbl WHERE c1=1000; // uses the IndexOnlyScan
SELECT * FROM tbl WHERE c1=1000; // uses the IndexOnlyScan

The patch "optional_opclass" completely ignores opclasses of included attributes.
To see the difference, look at the explain analyze output:

explain analyze select * from tbl where c1=2 and c4 && box '(0,0,1,1)';
                                                  QUERY PLAN                                                  
---------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx on tbl  (cost=0.13..4.15 rows=1 width=36) (actual time=0.010..0.013 rows=1 loops=1)
   Index Cond: (c1 = 2)
   Filter: (c4 && '(1,1),(0,0)'::box)

"Index Cond" shows the index ScanKey conditions and "Filter" is for conditions which are used after index scan. Anyway it is faster than SeqScan that we had before, because IndexOnlyScan avoids extra heap fetches.

As I already said, this patch is just WIP, so included opclass is not "optional" but actually "ignored".
And following example works worse than without the patch. Please, don't care about it.

CREATE TABLE tbl2 (c1 int, c2 int);
CREATE UNIQUE INDEX idx2 ON tbl2 USING btree (c1) INCLUDING (c2);
explain analyze select * from tbl2 where c1<20 and c2<5;
                                                      QUERY PLAN                                                      
-----------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx2 on tbl2  (cost=0.28..4.68 rows=10 width=8) (actual time=0.055..0.066 rows=9 loops=1)
   Index Cond: (c1 < 20)
   Filter: (c2 < 5)

The question is more about suitable syntax.
We have two different optimizations here:
1. INCLUDED columns
2. Optional opclasses
It's logical to provide optional opclasses only for included columns.
Is it ok, to handle it using the same syntax and resolve all opclass conflicts while create index?

CREATE TABLE tbl2 (c1 int, c2 int, c4 box);
CREATE UNIQUE INDEX idx2 ON tbl2 USING btree (c1) INCLUDING (c2, c4);
CREATE UNIQUE INDEX idx3 ON tbl2 USING btree (c1) INCLUDING (c4, c2);

Of course, order of attributes is important.
Attrs which have oplass and want to use it in ScanKey must be situated before the others.
idx2 will use c2 in IndexCond, while idx3 will not. But I think that it's the job for DBA.

If you see any related changes in planner, please mention them. I haven't explored that part of code yet and could have missed something.

-- 
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company