Еще один перевод. Искал информацию про составные индексы, нашел эту статью от CYBERTEC. Начал читать — и решил перевести (потренироваться в переводе). Как-то так нахлынуло настроение сегодня 🙂
Но, честно говоря, не всё из статьи понятно.
Перевод не художественный, а как получилось.
Ссылка на оригинал.
—
«Составной индекс» («composite index»), также известный как «объединенный индекс» [???] («concatenated index») — это индекс по нескольким столбцам в таблице. Многие задаются вопросом, что выгоднее: использование отдельных индексов или использование составных индексов? Каждый раз, когда мы проводим обучение, консультируем или осуществляем поддержку проектов, этот вопрос стоит на повестке дня, и многие люди продолжают задавать этот вопрос. Поэтому я решил пролить свет на этот вопрос.
Какие индексы нужно создать?
Чтобы обсудить тему на более практическом уровне я создал таблицу, состоящую из трех столбцов:
test=# CREATE TABLE t_data (a int, b int, c int);
CREATE TABLE
test=# INSERT INTO t_data
SELECT random()*100000,
random()*100000,
random()*100000
FROM generate_series(1, 1000000);
INSERT 0 1000000
test=# CREATE INDEX idx_data ON t_data(a, b, c);
CREATE INDEX
Таким образом, таблица выглядит следующим образом:
test=# \d t_data
Table "public.t_data"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
b | integer | | |
c | integer | | |
Indexes:
"idx_data" btree (a, b, c)
Давайте сейчас запустим ANALYZE, чтобы убедиться, что статистика оптимизатора появилась. Обычно срабатывает автоочистка и создает статистику для вашей таблицы, но чтобы убедиться в этом проведем тестовый запуск ANALYZE:
test=# ANALYZE t_data; ANALYZE
PostgreSQL изменит порядок фильтров за вас
Первое, что нужно заметить, это то, что PostgreSQL попытается упорядочить фильтры в вашем запросе за вас. Следующий запрос будет фильтровать все проиндексированные столбцы:
test=# explain SELECT *
FROM t_data
WHERE c = 10
AND b = 20
AND a = 10;
QUERY PLAN
---------------------------------------------------
Index Only Scan using idx_data on t_data
(cost=0.42..4.45 rows=1 width=12)
Index Cond: ((a = 10) AND (b = 20) AND (c = 10))
(2 rows)
Как видите, мы отфильтровали «c, b, a», но оптимизатор изменил порядок этих условий и превратил его в «a, b, c», чтобы убедиться, что созданный нами индекс соответствует запросу. Здесь есть несколько важных вещей, которые следует запомнить:
-
Порядок условий в вашем предложении WHERE не имеет значения;
-
PostgreSQL автоматически найдет нужные индексы.
Также имейте в виду, что PostgreSQL знает, что равенство транзитивно, и может выводить условия из этого:
test=# explain SELECT *
FROM t_data
WHERE c = 10
AND b = a
AND a = c;
QUERY PLAN
---------------------------------------------------
Index Only Scan using idx_data on t_data
(cost=0.42..4.45 rows=1 width=12)
Index Cond: ((a = 10) AND (b = 10) AND (c = 10))
(2 rows)
Здесь вы можете увидеть, что PostgreSQL автоматически определил, что a, b и c на самом деле одинаковы.
Использование частей индекса
Однако, если у вас есть составной индекс, нет необходимости фильтровать все столбцы. Также можно использовать первое или первое и второе поля для фильтрации. Например:
test=# explain SELECT *
FROM t_data
WHERE a = 10;
QUERY PLAN
------------------------------------------
Index Only Scan using idx_data on t_data
(cost=0.42..4.62 rows=11 width=12)
Index Cond: (a = 10)
(2 rows)
Как видите, PostgreSQL по-прежнему может использовать тот же индекс. Индекс — это просто отсортированный список, который (в нашем случае) упорядочен по трем полям. В многостолбцовых индексах этот порядок является так называемым «лексикографическим порядком»: строки сначала сортируются по первому столбцу индекса. Строки с одинаковым первым столбцом сортируются по второму столбцу и т. д. Совершенно нормально использовать только первые столбцы. Поговорим о списках сортировки (order by):
test=# explain SELECT *
FROM t_data
WHERE a = 10
ORDER BY b, c;
QUERY PLAN
-------------------------------------------
Index Only Scan using idx_data on t_data
(cost=0.42..4.62 rows=11 width=12)
Index Cond: (a = 10)
(2 rows)
Индекс также может предоставить вам отсортированные данные. В этом случае мы фильтруем по «a» и упорядочиваем по оставшимся двум столбцам «b» и «c».
Когда составные индексы не работают
Попробуем разобраться, когда составной индекс не помогает ускорить запрос. Например:
test=# explain SELECT *
FROM t_data
WHERE b = 10;
QUERY PLAN
---------------------------------------------------
Gather (cost=1000.00..11615.43 rows=11 width=12)
Workers Planned: 2
→ Parallel Seq Scan on t_data
(cost=0.00..10614.33 rows=5 width=12)
Filter: (b = 10)
(4 rows)
В этом случае мы фильтруем по второму столбцу. Помните: индекс btree — это не что иное, как отсортированный список. В нашем случае он сортируется по «a, b, c». Поэтому, естественно, мы не можем эффективно фильтровать это поле [а почему?]. Однако в некоторых редких случаях может случиться так, что вы все равно увидите сканирование индекса. Некоторые люди видят в этом доказательство того, что «это действительно работает». Но вы увидите сканирование индекса по неправильной причине: PostgreSQL может полностью читать индекс — не для фильтрации, а для уменьшения количества операций ввода-вывода, необходимых для чтения таблицы. Если в таблице много столбцов, возможно, будет быстрее прочитать индекс, чем обработать таблицу.
Давайте смоделируем это:
test=# SET seq_page_cost TO 10000; SET
Сообщив PostgreSQL, что последовательный ввод-вывод дороже, чем он есть на самом деле, вы увидите, что оптимизатор переключается на сканирование индекса:
test=# explain analyze
SELECT *
FROM t_data
WHERE b = 10;
QUERY PLAN
--------------------------------------------------
Index Only Scan using idx_data on t_data
(cost=0.42..22892.53 rows=11 width=12)
(actual time=7.626..63.087 rows=8 loops=1)
Index Cond: (b = 10)
Heap Fetches: 0
Planning Time: 0.121 ms
Execution Time: 63.122 ms
(5 rows)
Однако время выполнения составляет 63 миллисекунды, что НАМНОГО больше, чем если бы мы сделали это для первого столбца в индексе.
Обратите внимание, что «Index Cond: (b = 10)» означает здесь нечто иное, чем в предыдущих примерах: в то время как раньше у нас были условия сканирования индекса, здесь у нас есть условие фильтрации индекса. Не хорошо, что эти два случая в выводе EXPLAIN выглядят одинаково.
Понимание сканирования по битовой карте (bitmap index scan) в PostgreSQL
PostgreSQL может использовать более одного индекса одновременно. Это особенно важно, если вы используете OR, как показано в следующем примере:
test=# SET seq_page_cost TO default;
SET
test=# explain SELECT *
FROM t_data
WHERE a = 4
OR a = 23232;
QUERY PLAN
----------------------------------------------------
Bitmap Heap Scan on t_data
(cost=9.03..93.15 rows=22 width=12)
Recheck Cond: ((a = 4) OR (a = 23232))
→ BitmapOr (cost=9.03..9.03 rows=22 width=0)
→ Bitmap Index Scan on idx_data
(cost=0.00..4.51 rows=11 width=0)
Index Cond: (a = 4)
→ Bitmap Index Scan on idx_data
(cost=0.00..4.51 rows=11 width=0)
Index Cond: (a = 23232)
(7 rows)
PostgreSQL будет делать выбор в пользу сканирования по битовой карте. Идея состоит в том, чтобы сначала просмотреть все индексы, потом составить список строк / блоков, которые затем должны быть извлечены из таблицы (кучи). Обычно это намного лучше, чем последовательное сканирование. В моем примере один и тот же индекс используется дважды. Однако, в реальной жизни вы можете чаще видеть сканирование по битовой карте с использованием различных индексов.
Использование подмножества индексов в одном операторе SQL
Во многих случаях использование слишком большого количества индексов может быть даже контрпродуктивным. Планировщик позаботится о том, чтобы было выбрано достаточное количество индексов, но их не потребуется слишком много. Давайте посмотрим на следующий пример:
test=# DROP INDEX idx_data;
DROP INDEX
test=# CREATE INDEX idx_a ON t_data (a);
CREATE INDEX
test=# CREATE INDEX idx_b ON t_data (b);
CREATE INDEX
test=# CREATE INDEX idx_c ON t_data (c);
CREATE INDEX
test=# \d t_data
Table "public.t_data"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
b | integer | | |
c | integer | | |
Indexes:
"idx_a" btree (a)
"idx_b" btree (b)
"idx_c" btree (c)
Следующий запрос снова фильтрует все столбцы. На этот раз я не использовал единый составной индекс, а решил использовать отдельные индексы, что обычно менее эффективно. Вот что происходит:
test=# explain SELECT *
FROM t_data
WHERE a = 10
AND b = 20
AND c = 30;
QUERY PLAN
----------------------------------------------------
Bitmap Heap Scan on t_data
(cost=9.27..13.28 rows=1 width=12)
Recheck Cond: ((c = 30) AND (b = 20))
Filter: (a = 10)
→ BitmapAnd (cost=9.27..9.27 rows=1 width=0)
→ Bitmap Index Scan on idx_c
(cost=0.00..4.51 rows=11 width=0)
Index Cond: (c = 30)
→ Bitmap Index Scan on idx_b
(cost=0.00..4.51 rows=11 width=0)
Index Cond: (b = 20)
(8 rows)
Присмотримся к плану подробнее. Фильтр запросов по трем столбцам, НО PostgreSQL выбрал только два индекса (idx_c, idx_b). Почему? Мы вставили в таблицу 1 миллион строк. Каждый столбец содержит 100 000 различных значений, что означает, что каждое значение встречается 10 раз. Какой смысл извлекать пару строк из каждого индекса, если два индекса уже достаточно сужают результат? Именно это и происходит [как-то запутанно…].
Оптимизация min / max в SQL-запросах
Индексы — это не только фильтрация. Это также поможет вам помнить самое маленькое и самое большое значение в столбце, как показано в следующем операторе SQL:
test=# explain SELECT min(a), max(b) FROM t_data;
QUERY PLAN
-----------------------------------------------------------------------
Result (cost=0.91..0.92 rows=1 width=8)
InitPlan 1 (returns $0)
→ Limit (cost=0.42..0.45 rows=1 width=4)
→ Index Only Scan using idx_a on t_data
(cost=0.42..28496.42 rows=1000000 width=4)
Index Cond: (a IS NOT NULL)
InitPlan 2 (returns $1)
→ Limit (cost=0.42..0.45 rows=1 width=4)
→ Index Only Scan Backward using idx_b on t_data t_data_1
(cost=0.42..28496.42 rows=1000000 width=4)
Index Cond: (b IS NOT NULL)
(9 rows)
Как видите, PostgreSQL уже довольно сложен. Если вы ищете хорошую производительность, безусловно, имеет смысл увидеть как PostgreSQL обрабатывает индексы и проверяет ваш код, чтобы ускорить процесс. Если вы хотите узнать больше о производительности, посмотрите статью Лоренца Альбе (Laurenz Albe) об ускорении count(*). Кроме того, если вы не уверены, почему ваша база данных медленная, посмотрите мой пост о производительности базы данных PostgreSQL, в котором объясняется, как находить медленные запросы.
Ханс-Юрген Шёниг (Hans-Jürgen Schönig)
Ханс-Юрген Шениг имеет опыт работы с PostgreSQL с 90-х годов. Он является генеральным директором и техническим руководителем компании CYBERTEC, которая является одним из лидеров рынка в этой области и с 2000 года обслуживает бесчисленное количество клиентов по всему миру.
Еще раз ссылка на оригинал.
Leave a Reply