Что нового в PostgreSQL 11: INCLUDE-индексы

Релиз PostgreSQL 11 состоится еще не скоро, только в октябре. Но фичфриз уже наступил, а значит мы знаем, какие фичи попали в этот релиз, и можем их потестировать, собрав PostgreSQL ^[1] из ветки master. Особого внимания заслуживает фича под названием INCLUDE-индексы. Патч ^[2] изначально написан Анастасией Лубенниковой ^[3], а потом допилен Александром Коротковым ^[4] и Федором Сигаевым ^[5]. Протолкнуть его в 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)

За счет того, что теперь мы не ходим в таблицу, запрос должен работать быстрее. Стоит однако отметить, что на практике все зависит от ваших данных. Каждый случай уникален, поэтому я сознательно не привожу здесь каких-то синтетических бенчмарков ^[6]. Может оказаться, что на ваших объемах данных index only scan с include-индексами работает так же быстро, как и в случае с обычными индексами. А то и вовсе накопленная статистика говорит PostgreSQL, что запрос быстрее сделать heap scan'ом. Такое может произойти, например, если селективность вашего запроса низка.

Так или иначе, знать про эту возможность полезно, и я искренне рад, что она появится в PostgreSQL 11.

Автор: Aleksander Alekseev

Источник ^[7]