Re: WIP: Covering + unique indexes.

Updated version of the patch is attached. Besides code itself, it 
contains new regression test,
documentation updates and a paragraph in nbtree/README.

Syntax was changed - keyword is INCLUDE now as in other databases.

Below you can see the answers to the latest review by Brad DeJong.

> Given "create table foo (a int, b int, c int, d int)" and "create 
> unique index foo_a_b on foo (a, b) including (c)".
>
>                        index only?   heap tuple needed?
> select a, b, c from foo where a = 1                    yes              no
> select a, b, d from foo where a = 1                    no             
>   yes
> select a, b    from foo where a = 1 and c = 1          ?                ?

select a, b    from foo where a = 1 and c = 1             yes           
    no

As you can see in EXPLAIN this query doesn't need heap tuple. We can 
fetch tuple using index-only scan strategy,
because btree never use lossy data representation (i.e stores the same 
data as in heap). Afterward we apply
Filter (c=1) to the fetched tuple.

explain analyze select a, b    from foo where a = 1 and c = 1;
                                                     QUERY PLAN
------------------------------------------------------------------------------------------------------------------
  Index Only Scan using foo_a_b on foo  (cost=0.28..4.30 rows=1 width=8) 
(actual time=0.021..0.022 rows=1 loops=1)
    Index Cond: (a = 1)
    Filter: (c = 1)
    Heap Fetches: 0
  Planning time: 0.344 ms
  Execution time: 0.073 ms


> Are included columns counted against the 32 column and 2712 byte index 
> limits? I did not see either explicitly mentioned in the discussion or 
> the documentation. I only ask because in SQL Server the limits are 
> different for include columns.

This limit remains unchanged since included attributes are stored in the 
very same way as regular index attributes.

> 1. syntax - on 2016-08-14, Andrey Borodin wrote "I think MS SQL syntax 
> INCLUDE instead of INCLUDING would be better". I would go further than 
> that. This feature is already supported by 2 of the top 5 SQL 
> databases and they both use INCLUDE. Using different syntax because of 
> an internal implementation detail seems short sighted.

Done.
> 4. documentation - minor items (these are not actual diffs)
Thank you. All issues are fixed.

> 5. coding
>     parse_utilcmd.c
>         @@ -1334,6 +1334,38 @@ ...
>         The loop is handling included columns separately.
>         The loop adds the collation name for each included column if 
> it is not the default.
>
>         Q: Given that the create index/create constraint syntax does 
> not allow a collation to be specified for included columns, how can 
> you ever have a non-default collation?
>
>         @@ -1776,6 +1816,7 @@
>         The comment here says "NOTE that exclusion constraints don't 
> support included nonkey attributes". However, the paragraph on 
> INCLUDING in create_index.sgml says "It's the same for the other 
> constraints (PRIMARY KEY and EXCLUDE)".

Good point.
In this version I added syntax for EXCLUDE and INCLUDE compatibility.
Though names look weird, it works as well as other constraints. So 
documentation is correct now.

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