Релиз PostgreSQL 11 состоится еще не скоро, только в октябре. Но фичфриз уже наступил, а значит мы знаем, какие фичи попали в этот релиз, и можем их потестировать, собрав PostgreSQL из ветки master. Особого внимания заслуживает фича под названием INCLUDE-индексы. Патч изначально написан Анастасией Лубенниковой, а потом допилен Александром Коротковым и Федором Сигаевым. Протолкнуть его в PostgreSQL заняло «всего лишь» что-то около трех лет.
Давайте попробуем разобраться, что же это за индексы за такие. Для начала создадим табличку для опытов:
create table test (k serial primary key, v text, ts timestamp);
insert into test (v, ts) select 'key_' || s , now() from generate_series(1, 10000) as s;
… и построим по ней обычный btree-индекс:
create index on test (v);
Взглянем на план выполнения следующего запроса:
=# explain select v, ts from test where v > 'key_1337' and v < 'key_2337';
QUERY PLAN
-----------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=31.57..112.09 rows=1101 width=16)
Recheck Cond: ((v > 'key_1337'::text) AND (v < 'key_2337'::text))
-> Bitmap Index Scan on test_v_idx (cost=0.00..31.29 rows=1101 width=0)
Index Cond: ((v > 'key_1337'::text) AND (v < 'key_2337'::text))
(4 rows)
Смотрите, что происходит. Поскольку индекс построен по колонке v, а в запросе мы выбираем v и ts, PostgreSQL вынужден выполнять запрос в два шага. Сначала он идет по индексу и находит строки, удовлетворяющие условию. Затем ему приходится сходить в таблицу для получения ts.
Идея INCLUDE-индексов заключается в том, чтобы включить все необходимые для выполнения запроса данные прямо в индекс (но не индексировать их). Таким образом, запрос становится возможно выполнить за один index scan.
Давайте проверим:
drop index test_v_idx;
create index on test (v) include (ts);
explain select v, ts from test where v > 'key_1337' and v < 'key_2337';
Результат:
Index Only Scan using test_v_ts_idx on test (cost=0.29..46.30 rows=1101 width=16)
Index Cond: ((v > 'key_1337'::text) AND (v < 'key_2337'::text))
(2 rows)
За счет того, что теперь мы не ходим в таблицу, запрос должен работать быстрее. Стоит однако отметить, что на практике все зависит от ваших данных. Каждый случай уникален, поэтому я сознательно не привожу здесь каких-то синтетических бенчмарков. Может оказаться, что на ваших объемах данных index only scan с include-индексами работает так же быстро, как и в случае с обычными индексами. А то и вовсе накопленная статистика говорит PostgreSQL, что запрос быстрее сделать heap scan'ом. Такое может произойти, например, если селективность вашего запроса низка.
Так или иначе, знать про эту возможность полезно, и я искренне рад, что она появится в PostgreSQL 11.
Комментарии (25)
AxisPod
09.04.2018 19:01Назовите по человечески «Покрывающие индексы».
x-wao
09.04.2018 22:46Мы говорим так. Но это спорное название, т.к. понятие «покрывающий индекс» определено по отношению к конкретному запросу. По сути, это индекс, которого достаточно для index-only scan в этом запросе. Не обязательно с INCLUDE. См use-the-index-luke.com/sql/clustering/index-only-scan-covering-index
sebres
09.04.2018 21:46+1Нужно наверное добавить, что де факто — эта штука "слизана" с 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 по короткому кортежу (как в этом примере и др. тому подобные вещи).
DenisTrunin
10.04.2018 07:10заменяет не 2 индекса, а 3, порядок полей в Include неважен, в этом вся и суть
(c1, c2); (c1, c2, c3, c4); (c1, c2, c4, c3);
bolk
10.04.2018 09:40Что-то я не пойму никак зачем тут три индекса, если бы хватило одного, который накрывает все 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)Alozar
10.04.2018 10:25-1Не надо забывать, что индекс по нескольким полям не во всех случаях поможет, а СУБД нагружает всегда.
В вашем примере всё работает хорошо, т.к. отбор идёт по первым двум полям. Если же потребуется отбор по полям a и c, индекс уже не поможет.
sebres
10.04.2018 10:41Заменяет два, потому что
oldunqidx— UNIQUE и покрывает ключевые поля (и соответственно быстрее), кроме того играет дополнительно роль CONSTRAINT по кортежу (c1,c2);
Собрав же только один индекс
oldcvridx(даже если и UNIQUE, но уже по 4-м полям), вы "теряете" обе эти возможности.
sebres
10.04.2018 10:32порядок полей в Include неважен
Ну уточните тогда, неважен для какого действия?
Где например ниже будет важен порядок, т.е. по вашему выходит следующие два запроса разные?
select c3, c4 from sometab where c1 = ?v1 and c2 = ?v2 select c4, c3 from sometab where c1 = ?v1 and c2 = ?v2Alozar
10.04.2018 10:35-1Порядок полей неважен, когда по include-полям требуется только получение данных, а отбор не производится.
gsl23
10.04.2018 15:04Не совсем заменяет.
Подумайте как запрос вида
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);sebres
10.04.2018 15:15Иии… что там должно чем отличатся?
Вы правда про запрос по одной 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 в обоих случаях.gsl23
10.04.2018 16:07Иии… что там должно чем отличатся?
Вы правда про запрос по одной row спрашиваете (я напомню кортеж (с1, с2) уникален).
Мм, в случае уникальности (с1, с2) пожалуй, скорей всего, вы правы. Тогда это не совсем понял ваш пример. Почему индекс по (с1, с2, c3, c4) в вашем примере не уникальный, если кортеж c1, c2 уникален?
coctic
10.04.2018 11:57+1То есть если включить в индекс все поля исходной таблицы, то получится Index Organized Table, за тем исключением, что данные будут дублированы и в индексе, и в таблице?
x-wao
10.04.2018 14:43+1Индекс не очень-то приспособлен для таких вещей. Вот например, размер записи в btree ограничен тем, что на странице индекса их должно минимум три умещаться. Большие поля не пройдут, а toast в индексе нет.
vovik0134
10.04.2018 23:38Правильно ли я понимаю, что такой подход сводит на нет оптимизацию HOT-update в случае, если изменяется «включенное» поле? Так же не очень понятно на счёт работы MVCC. PostgreSQL в худшем случае все равно придётся сходить в таблицу, чтобы узнать xmin/xmax?
miolini
Огонь! :-)