|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.|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
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
> 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
> @@ -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)".
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.
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
|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|