Re: WIP: Covering + unique indexes. (the good and the bad)
Teodor Sigaev <teodor@sigaev.ru>
From: Teodor Sigaev <teodor@sigaev.ru>
To: Erik Rijkers <er@xs4all.nl>,
Alexander Korotkov <a.korotkov@postgrespro.ru>
Cc: Peter Geoghegan <pg@bowt.ie>,
Anastasia Lubennikova <a.lubennikova@postgrespro.ru>,
PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
Alexander Kuzmenkov <a.kuzmenkov@postgrespro.ru>
Date: 2018-04-07T12:27:15Z
Lists: pgsql-hackers
Thank you!
> create unique index ${t}uniqueinclude_idx on $t using btree (c1, c2)
> include (c3, c4);
> or for HEAD, just:
> create unique index ${t}unique_idx on $t using btree (c1, c2);
> -- explain analyze select c1, c2 from nt0___100000000 where c1 < 10000
> -- explain analyze select c1, c2 from nt0___100000000 where c1 < 10000
> and c3 < 20
Not so fair comparison, include index twice bigger because of include
columns. Try to compare with covering-emulated index:
create unique index ${t}unique_idx on $t using btree (c1, c2, c3, c4)
--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/
Commits
-
Adjust INCLUDE index truncation comments and code.
- 075aade4361b 11.0 landed
-
Add commentary explaining why MaxIndexTuplesPerPage calculation is safe.
- 2a67d6440db4 11.0 cited
-
Indexes with INCLUDE columns and their support in B-tree
- 8224de4f42cc 11.0 landed
-
Add amcheck verification of heap relations belonging to btree indexes.
- 7f563c09f890 11.0 cited
-
Doc: move info for btree opclass implementors into main documentation.
- 3785f7eee3d9 11.0 cited
-
Doc: mention that you can't PREPARE TRANSACTION after NOTIFY.
- e4fbf22831c2 11.0 cited
-
Remove dedicated B-tree root-split record types.
- 0c504a80cf2e 11.0 cited
-
Restructure index access method API to hide most of it at the C level.
- 65c5fcd353a8 9.6.0 cited
-
Split _bt_insertonpg to two functions.
- bc292937ae6a 8.3.0 cited
-
Major overhaul of btree index code. Eliminate special BTP_CHAIN logic for
- 9e85183bfc31 7.1.1 cited