Comments 30
— table in memory
— columnstore index
:)
Нужно наверное добавить, что де факто — эта штука "слизана" с INCLUDE-INDEX for SQL Server от мелко-мягких… (которая по моему появилась в MSSQL аж в 2005-й версии).
Реально полезная штука…
И тут по моему не совсем понятно описаны преимущества сего действа — т.е. грубо говоря для чего оно вообще имеет место.
В общем, вот это вот:
CREATE UNIQUE INDEX newidx ON sometab (c1, c2) INCLUDE (c3, c4);заменяет следующие два индекса (в старом виде):
CREATE UNIQUE INDEX oldunqidx ON sometab (c1, c2);
CREATE INDEX oldcvridx ON sometab (c1, c2, c3, c4);без хранения редундантной "дубликации" для одинаковых значений.
Т.е. прирост скорости тут скорее вторичен (например через меньшее вымывание кэша на соответственно меньшем индексе и за счет меньших потерь времени на перестроение его на CUD-операциях).
Например, для этого примера (если все c1-c4 int):
Name | RowCnt | Reserved | Data | IndexSize | Unused
------- | ------- | -------- | -------- | --------- | ------
- testold | 638976 | 49112 KB | 15832 KB | 33192 KB | 88 KB
+ testnew | 638976 | 35472 KB | 15832 KB | 19560 KB | 80 KBПро скорость оно всё сложно на самом деле, но если тупо в лоб при прочих равных (под паразитной нагрузкой) — как-то так:
# 1M rows
- testold: 365.057 µs/# 2740 # 2739.3 #/sec
+ testnew: 357.219 µs/# 2800 # 2799.4 #/sec
# 10M rows
- testold: 413.326 µs/# 2420 # 2419.4 #/sec
+ testnew: 367.916 µs/# 2719 # 2718.0 #/sec
# 100M rows
- testold: 696.943 µs/# 1435 # 1434.8 #/sec
+ testnew: 455.329 µs/# 2197 # 2196.2 #/secНу и при таком "сахаре" бонусом сверху возможность использовать короткий UNIQUE индекс по значениям c1, c2 при scan по короткому кортежу (как в этом примере и др. тому подобные вещи).
(c1, c2); (c1, c2, c3, c4); (c1, c2, c4, c3);
=> create index on test(a,b,c,d);
CREATE INDEX
=> explain select c,d from test where a=100 and b=1;
QUERY PLAN
-----------------------------------------------------------------------
Index Only Scan using test_a_b_c_d_idx on test (cost=0.42..8.45 rows=1 width=8)
Index Cond: ((a = 100) AND (b = 1))
(2 rows)
В вашем примере всё работает хорошо, т.к. отбор идёт по первым двум полям. Если же потребуется отбор по полям a и c, индекс уже не поможет.
Заменяет два, потому что oldunqidx — UNIQUE и покрывает ключевые поля (и соответственно быстрее), кроме того играет дополнительно роль CONSTRAINT по кортежу (c1,c2);
Собрав же только один индекс oldcvridx (даже если и UNIQUE, но уже по 4-м полям), вы "теряете" обе эти возможности.
порядок полей в Include неважен
Ну уточните тогда, неважен для какого действия?
Где например ниже будет важен порядок, т.е. по вашему выходит следующие два запроса разные?
select c3, c4 from sometab where c1 = ?v1 and c2 = ?v2
select c4, c3 from sometab where c1 = ?v1 and c2 = ?v2
Подумайте как запрос вида
select .. from where c1 = var1 and c2 = var2 and c3 = var3 and c4 = var4Будет вести себя с
CREATE INDEX oldcvridx ON sometab (c1, c2, c3, c4);и с
CREATE UNIQUE INDEX newidx ON sometab (c1, c2) INCLUDE (c3, c4);Иии… что там должно чем отличатся?
Вы правда про запрос по одной row спрашиваете (я напомню кортеж (с1, с2) уникален).
Весь смысл в том что весь кортеж (с1, с2, c3, c4) находится в индексе, т.е. ему не нужно лезть в таблицу для проверки данных.
А вообще-то, даже следующее будет с INCLUDE-индексом если не быстрее, то за то-же самое время:
select ... where c1 = ?v1 and c2 >= ?v2fr and c2 <= ?v2to and c3 = ?v3 and c4 = ?v4Собственно по той же причине.
По крайней мере для MSSQL проверялось мною не раз — чистый index-seek в обоих случаях.
Иии… что там должно чем отличатся?
Вы правда про запрос по одной row спрашиваете (я напомню кортеж (с1, с2) уникален).
Мм, в случае уникальности (с1, с2) пожалуй, скорей всего, вы правы. Тогда это не совсем понял ваш пример. Почему индекс по (с1, с2, c3, c4) в вашем примере не уникальный, если кортеж c1, c2 уникален?
Потому что только ключ (или ключи) включаются в индекс напрямую, а покрытие (nokeys) соответственно в INCLUDE.
Грубо причина описана выше.
MS SQL, например, даже не даст создать такой индекс, т.к. размер индексных колонок суммарно не должен превышать 900 байт, ЕМНИП. А вот в INCLUDE их вполне можно поместить, чтобы не делать KEY LOOKUP в основную таблицу на каждую найденную строку.
INCLUDE полезен именно для того, чтобы не растягивать без необходимости уровень дерева в индексе, оставив большие поля, которые используются в запросе, только на листовом уровне.
ЗЫ: с Postgre не работал, работаю с MS SQL, пишу по опыту использования таких индексов там.
Как и для любого случая использования покрывающих индексов и стратегии index only scan.
Главная полезная особенность индексов с INCLUDE в том, что можно объединить уникальные индексы с покрывающими там, где они уже используются.
И ещё одна особенность заключается в том, что в INCLUDE можно добавить типы данных, для которых нет opclass, поскольку они не учитываются в сортировке.
Случайно наткнулся на данную статью, и стало интересно.
В чем отличие multicolumn index от include?
Что нового в PostgreSQL 11: INCLUDE-индексы