У світі Postgres індекси дуже важливі для ефективної навігації по сховищу бази даних (його називають купа, heap). Postgres не підтримує кластеризацію, і архітектура MVCC призводить до того, що у вас накопичується багато версій одного і того ж кортежу. Тому дуже важливо вміти створювати та супроводжувати ефективні індекси для підтримки програм.
Пропоную вашій увазі кілька порад щодо оптимізації та покращення використання індексів.
Примітка: наведені нижче запити працюють на не модифікованому .
Використання індексів, що покривають (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_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 надає безліч гнучких можливостей для створення індексів і шляхів вирішення будь-яких окремих випадків, а також надає способи управління базою даних на випадок вибухового зростання вашої програми. Сподіваємось, що ці поради допоможуть вам зробити запити швидкими, а базу готовою масштабуватись.
Джерело: habr.com