В мире Postgres индексы крайне важны для эффективной навигации по хранилищу базы данных (его называют «куча», heap). Postgres не поддерживает для него кластеризацию, и архитектура MVCC приводит к тому, что у вас накапливается много версий одного и того же кортежа. Поэтому очень важно уметь создавать и сопровождать эффективные индексы для поддержки приложений.

Предлагаю вашему вниманию несколько советов по оптимизации и улучшению использования индексов.

Примечание: показанные ниже запросы работают на не модифицированном образце базы данных pagila.

Использование покрывающих индексов (Covering Indexes)

Давайте рассмотрим запрос на извлечение адресов электронной почты для неактивных пользователей. В таблице customer есть колонка active, и запрос получается несложным:

pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on customer  (cost=0.00..16.49 rows=15 width=32)
   Filter: (active = 0)
(2 rows)

В запросе вызывается полная последовательность сканирования таблицы customer. Давайте создадим индекс для столбца active:

pagila=# CREATE INDEX idx_cust1 ON customer(active);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Index Scan using idx_cust1 on customer  (cost=0.28..12.29 rows=15 width=32)
   Index Cond: (active = 0)
(2 rows)

Помогло, последующее сканирование превратилось в "index scan". Это означает, что Postgres просканирует индекс "idx_cust1", а затем дальше продолжит поиск по куче таблицы, чтобы прочесть значения других колонок (в данном случае, колонку email), которые нужны запросу.

В PostgreSQL 11 появились покрывающие индексы. Они позволяют включать в сам индекс одну или несколько дополнительных колонок — их значения хранятся в хранилище данных индекса.

Если бы мы использовали эту возможность и добавили значение электронной почты внутрь индекса, то Postgres’у не понадобится искать в куче таблицы значение email. Посмотрим, будет ли это работать:

pagila=# CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Index Only Scan using idx_cust2 on customer  (cost=0.28..12.29 rows=15 width=32)
   Index Cond: (active = 0)
(2 rows)

"Index Only Scan" говорит нам, что запросу теперь достаточно одного лишь индекса, что помогает избегать всех дисковых операций ввода/вывода для чтения кучи таблицы.

Сегодня покрывающие индексы доступны только для B-деревьев. Однако в этом случае усилия по сопровождению будут выше.

Использование частичных индексов

Частичные индексы индексируют лишь подмножество строк таблицы. Это позволяет экономить размер индексов и быстрее выполнять сканирование.

Допустим, нам нужно получить список адресов электронной почты наших клиентов из Калифорнии. Запрос будет таким:

SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
which has a query plan that involves scanning both the tables that are joined:
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
                              QUERY PLAN
----------------------------------------------------------------------
 Hash Join  (cost=15.65..32.22 rows=9 width=32)
   Hash Cond: (c.address_id = a.address_id)
   ->  Seq Scan on customer c  (cost=0.00..14.99 rows=599 width=34)
   ->  Hash  (cost=15.54..15.54 rows=9 width=4)
         ->  Seq Scan on address a  (cost=0.00..15.54 rows=9 width=4)
               Filter: (district = 'California'::text)
(6 rows)

Что нам дадут обычные индексы:

pagila=# CREATE INDEX idx_address1 ON address(district);
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Hash Join  (cost=12.98..29.55 rows=9 width=32)
   Hash Cond: (c.address_id = a.address_id)
   ->  Seq Scan on customer c  (cost=0.00..14.99 rows=599 width=34)
   ->  Hash  (cost=12.87..12.87 rows=9 width=4)
         ->  Bitmap Heap Scan on address a  (cost=4.34..12.87 rows=9 width=4)
               Recheck Cond: (district = 'California'::text)
               ->  Bitmap Index Scan on idx_address1  (cost=0.00..4.34 rows=9 width=0)
                     Index Cond: (district = 'California'::text)
(8 rows)

Сканирование address было заменено сканированием индекса idx_address1, а затем просканирована куча address.

Поскольку это частый запрос и его нужно оптимизировать, мы можем использовать частичный индекс, который индексирует лишь те строки с адресами, в которых район ‘California’:

pagila=# CREATE INDEX idx_address2 ON address(address_id) WHERE district='California';
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Hash Join  (cost=12.38..28.96 rows=9 width=32)
   Hash Cond: (c.address_id = a.address_id)
   ->  Seq Scan on customer c  (cost=0.00..14.99 rows=599 width=34)
   ->  Hash  (cost=12.27..12.27 rows=9 width=4)
         ->  Index Only Scan using idx_address2 on address a  (cost=0.14..12.27 rows=9 width=4)
(5 rows)

Теперь запрос считывает только idx_address2 и не трогает таблицу address.

Использование многозначных индексов (Multi-Value Indexes)

Некоторые колонки, которые нужно проиндексировать, могут не содержать скалярного типа данных. Типы колонок наподобие jsonb, arrays и tsvector содержать составные или множественные значения. Если вам нужно индексировать такие колонки, то обычно приходится искать по всем отдельным значениям в этих колонках.

Попробуем найти названия всех фильмов, содержащие нарезки из неудачных дублей. В таблице film есть текстовая колонка, называющаяся special_features. Если у фильма есть это «особое свойство», то в колонке содержится элемент в виде текстового массива Behind The Scenes. Для поиска всех таких фильмов нам нужно выбрать все ряды с «Behind The Scenes» при любых значениях массива special_features:

SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';

Оператор вложенности (containment operator) @> проверяет, является ли правая часть подмножеством левой части.

План запроса:

pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on film  (cost=0.00..67.50 rows=5 width=15)
   Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)

Который запрашивает полное сканирование кучи со стоимостью 67.

Посмотрим, поможет ли нам обычный индекс B-дерева:

pagila=# CREATE INDEX idx_film1 ON film(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on film  (cost=0.00..67.50 rows=5 width=15)
   Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)

Индекс даже не рассматривался. Индекс B-дерева не догадывается о существовании отдельных элементов в индексируемых значениях.

Нам нужен GIN-индекс.

pagila=# CREATE INDEX idx_film2 ON film USING GIN(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
                                QUERY PLAN
---------------------------------------------------------------------------
 Bitmap Heap Scan on film  (cost=8.04..23.58 rows=5 width=15)
   Recheck Cond: (special_features @> '{"Behind The Scenes"}'::text[])
   ->  Bitmap Index Scan on idx_film2  (cost=0.00..8.04 rows=5 width=0)
         Index Cond: (special_features @> '{"Behind The Scenes"}'::text[])
(4 rows)

GIN-индекс поддерживает сопоставление отдельных значений с проиндексированными составными значениями, в результате стоимость плана запроса уменьшится более чем вдвое.

Избавляемся от дублирования индексов

Индексы накапливаются со временем, и иногда новый индекс может содержать то же определение, что и один из предыдущих. Для получения удобных для чтения человеком SQL-определений индексов можно использовать каталожное представление pg_indexes. Вы также сможете легко находить одинаковые определения:

 SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
    FROM pg_indexes
GROUP BY defn
  HAVING count(*) > 1;
And here’s the result when run on the stock pagila database:
pagila=#   SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
pagila-#     FROM pg_indexes
pagila-# GROUP BY defn
pagila-#   HAVING count(*) > 1;
                                indexes                                 |                                defn
------------------------------------------------------------------------+------------------------------------------------------------------
 {payment_p2017_01_customer_id_idx,idx_fk_payment_p2017_01_customer_id} | CREATE INDEX  ON public.payment_p2017_01 USING btree (customer_id
 {payment_p2017_02_customer_id_idx,idx_fk_payment_p2017_02_customer_id} | CREATE INDEX  ON public.payment_p2017_02 USING btree (customer_id
 {payment_p2017_03_customer_id_idx,idx_fk_payment_p2017_03_customer_id} | CREATE INDEX  ON public.payment_p2017_03 USING btree (customer_id
 {idx_fk_payment_p2017_04_customer_id,payment_p2017_04_customer_id_idx} | CREATE INDEX  ON public.payment_p2017_04 USING btree (customer_id
 {payment_p2017_05_customer_id_idx,idx_fk_payment_p2017_05_customer_id} | CREATE INDEX  ON public.payment_p2017_05 USING btree (customer_id
 {idx_fk_payment_p2017_06_customer_id,payment_p2017_06_customer_id_idx} | CREATE INDEX  ON public.payment_p2017_06 USING btree (customer_id
(6 rows)

Индексы надмножеств (Superset Indexes)

Может случиться так, что у вас накопится много индексов, один из которых индексирует надмножество колонок, которые индексируют другие индексы. Это может быть как желательно, так и нет — надмножество может привести к сканированию только по индексам, что хорошо, но при этом оно может занимать слишком много места, или запрос, для оптимизации которого предназначалось это надмножество, уже не используется.

Если вам нужно автоматизировать определение таких индексов, то можно начать с pg_index из таблицы pg_catalog.

Неиспользуемые индексы

По мере развития приложений, которые используют базы данных, развиваются и используемые ими запросы. Добавленные ранее индексы могут уже не применяться ни одним запросом. При каждом сканировании индекса он отмечается диспетчером статистики, и в представлении системного каталога pg_stat_user_indexes можно посмотреть значение idx_scan, которое является накопительным счётчиком. Отслеживание этого значение за какой-то промежуток времени (скажем, месяц) даст хорошее представление о том, какие индексы не используются и могут быть удалены.

Вот запрос на получение текущих счётчиков сканирования всех индексов в схеме ‘public’:

SELECT relname, indexrelname, idx_scan
FROM   pg_catalog.pg_stat_user_indexes
WHERE  schemaname = 'public';
with output like this:
pagila=# SELECT relname, indexrelname, idx_scan
pagila-# FROM   pg_catalog.pg_stat_user_indexes
pagila-# WHERE  schemaname = 'public'
pagila-# LIMIT  10;
    relname    |    indexrelname    | idx_scan
---------------+--------------------+----------
 customer      | customer_pkey      |    32093
 actor         | actor_pkey         |     5462
 address       | address_pkey       |      660
 category      | category_pkey      |     1000
 city          | city_pkey          |      609
 country       | country_pkey       |      604
 film_actor    | film_actor_pkey    |        0
 film_category | film_category_pkey |        0
 film          | film_pkey          |    11043
 inventory     | inventory_pkey     |    16048
(10 rows)

Пересоздание индексов с меньшим количеством блокировок

Частенько индексы приходится пересоздавать, например, когда они раздуваются в размерах, и пересоздание может ускорить сканирование. Также индексы могут повреждаться. Изменение параметров индекса тоже может потребовать его пересоздания.

Включаем параллельное создание индексов

В PostgreSQL 11 создание индекса B-Tree является конкурентным. Для ускорения процесса создания может использоваться несколько параллельно работающих воркеров. Однако убедитесь, что эти параметры конфигурации заданы правильно:

SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;

Значения по умолчанию слишком малы. В идеале, эти числа нужно увеличивать вместе с количеством ядер процессора. Подробнее читайте в документации.

Фоновое создание индексов

Вы можете создать индекс в фоновом режиме, воспользовавшись параметром CONCURRENTLY команды CREATE INDEX:

pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEX

Эта процедура создания индекса отличается от обычной тем, что она не требует блокирования таблицы, а значит и не блокирует операции записи. С другой стороны, она занимает больше времени и потребляет больше ресурсов.

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