Re: [HACKERS] WIP: Covering + unique indexes. - Mailing list pgsql-hackers

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          ?                ?

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.

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