Re: WIP: Covering + unique indexes.

From: Anastasia Lubennikova <a(dot)lubennikova(at)postgrespro(dot)ru>
To: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
Cc: Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Andrey Borodin <amborodin(at)acm(dot)org>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Anastasia Lubennikova <lubennikovaav(at)gmail(dot)com>, Brad(dot)Dejong(at)infor(dot)com
Subject: Re: WIP: Covering + unique indexes.
Date: 2017-01-09 15:02:54
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: 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 ? ?

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

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;
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.

> 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:
The Russian Postgres Company

Attachment Content-Type Size
include_columns_10.0_v1.patch text/x-patch 141.6 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Magnus Hagander 2017-01-09 15:05:10 Re: Make pg_basebackup -x stream the default
Previous Message Pavel Stehule 2017-01-09 14:14:14 Re: merging some features from plpgsql2 project