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