Pull to refresh

Comments 30

Назовите по человечески «Покрывающие индексы».
Мы говорим так. Но это спорное название, т.к. понятие «покрывающий индекс» определено по отношению к конкретному запросу. По сути, это индекс, которого достаточно для index-only scan в этом запросе. Не обязательно с INCLUDE. См use-the-index-luke.com/sql/clustering/index-only-scan-covering-index
Осталось немного:
— 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 по короткому кортежу (как в этом примере и др. тому подобные вещи).

заменяет не 2 индекса, а 3, порядок полей в Include неважен, в этом вся и суть
(c1, c2); (c1, c2, c3, c4); (c1, c2, c4, c3);
Что-то я не пойму никак зачем тут три индекса, если бы хватило одного, который накрывает все 4 поля?
=> 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-м полям), вы "теряете" обе эти возможности.

UNIQUE просмотрел, точно.
Так нет, непонятно всё же. По include индексированный поиск возможен разве? сортировка?
1. Да
2. Да, но смысл сомнителен при выборке по юник-ключам (c1, c2)
порядок полей в Include неважен

Ну уточните тогда, неважен для какого действия?
Где например ниже будет важен порядок, т.е. по вашему выходит следующие два запроса разные?


select c3, c4 from sometab where c1 = ?v1 and c2 = ?v2
select c4, c3 from sometab where c1 = ?v1 and c2 = ?v2
Порядок полей неважен, когда по include-полям требуется только получение данных, а отбор не производится.
Не совсем заменяет.
Подумайте как запрос вида
 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.
Грубо причина описана выше.

А теперь сделайте то же сравнение, но с c3 и c4 — строковыми типами максимального размера.
MS SQL, например, даже не даст создать такой индекс, т.к. размер индексных колонок суммарно не должен превышать 900 байт, ЕМНИП. А вот в INCLUDE их вполне можно поместить, чтобы не делать KEY LOOKUP в основную таблицу на каждую найденную строку.
INCLUDE полезен именно для того, чтобы не растягивать без необходимости уровень дерева в индексе, оставив большие поля, которые используются в запросе, только на листовом уровне.
ЗЫ: с Postgre не работал, работаю с MS SQL, пишу по опыту использования таких индексов там.
То есть если включить в индекс все поля исходной таблицы, то получится Index Organized Table, за тем исключением, что данные будут дублированы и в индексе, и в таблице?
Индекс не очень-то приспособлен для таких вещей. Вот например, размер записи в btree ограничен тем, что на странице индекса их должно минимум три умещаться. Большие поля не пройдут, а toast в индексе нет.
Ну то есть в очень ограниченном виде, но иногда можно и так использовать.
Правильно ли я понимаю, что такой подход сводит на нет оптимизацию HOT-update в случае, если изменяется «включенное» поле? Так же не очень понятно на счёт работы MVCC. PostgreSQL в худшем случае все равно придётся сходить в таблицу, чтобы узнать xmin/xmax?
Да, это так.
Как и для любого случая использования покрывающих индексов и стратегии index only scan.

Главная полезная особенность индексов с INCLUDE в том, что можно объединить уникальные индексы с покрывающими там, где они уже используются.
И ещё одна особенность заключается в том, что в INCLUDE можно добавить типы данных, для которых нет opclass, поскольку они не учитываются в сортировке.
Вот это действительно звучит круто! Особенно в случаях, когда индексы создаются только для поддержки ограничений целостности. Теперь они смогут выполнять ещё и «полезную» работу. Жаль, что это не отмечено в основном тексте статьи

Случайно наткнулся на данную статью, и стало интересно.


В чем отличие multicolumn index от include?

В том что, во многоколоночном индексе индексируются все поля, а в данном случае включённые поля не индексируются. Просто хранятся в индексе
Ждём INCLUDE-джоинов в скором будущем и поддержку блокчейна.
Sign up to leave a comment.