Използваме всички възможности на индексите в PostgreSQL

В света на Postgres индексите играят изключително важна роля за ефективната навигация в хранилището на базата данни (известно като „heap“). Postgres не поддържа клъстериране за него, а архитектурата MVCC води до натрупване на много версии на един и същи кортеж. Затова е много важно да можете да създавате и поддържате ефективни индекси за поддръжка на приложенията.

Представям ви няколко съвета за оптимизация и подобряване на употребата на индексите.

Забележка: показаните по-долу заявки работят на немодифицирания образец на базата данни pagila..

Използване на покриващи индекси (Covering Indexes).

Нека разгледаме заявка за извличане на имейли на неактивни потребители. В таблицата customer има колона активно, а заявката е несложна:

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Нека създадем индекс за колоната активно:

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“, а след това ще продължи да търси в heap-а на таблицата, за да прочете стойностите на другите колони (в този случай, колоната email), които са нужни за заявката.

В PostgreSQL 11 се появиха покриващи индекси. Те позволяват включването на една или повече допълнителни колони — техните стойности се съхраняват в хранилището на данните на индекса.

Ако използвахме тази възможност и добавихме стойността на имейла в индекса, Postgres няма да се налага да търси в heap-а на таблицата стойността 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“ ни казва, че на заявката вече й е достатъчен само индексът, което помага да се избегнат всички операции за въвеждане/извеждане на данни от диска за четене на heap-а на таблицата.

В днешно време покриващите индекси са налични само за B-деревата. Въпреки това, в този случай усилията за поддръжка ще бъдат по-високи.

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

Частичните индекси индексират само подмножество от редовете на таблицата. Това позволява да се спести размерът на индексите и по-бързо да се извършва сканиране.

Да предположим, че ни трябва списък с имейл адресите на нашите клиенти от Калифорния. Запитването ще бъде такова:

SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
което има план за запитване, който включва сканиране на двете свързани таблици:
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.

Тъй като това е често срещано запитване и трябва да бъде оптимизирано, можем да използваме частичен индекс, който индексира само тези редове с адреси, в които районът ‘Калифорния’:

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, масиви и tsvector може да съдържат сложни или множество стойности. Ако искате да индексите такива колони, обикновено трябва да търсите за всички отделни стойности в тези колони.

Нека да опитаме да намерим заглавията на всички филми, съдържащи изрезки от неуспешни дубльори. В таблицата film има текстова колона, наречена special_features. Ако филмът има това „особено свойство“, в колоната е записан елемент под формата на текстов масив Зад кулисите. За да намерим всички такива филми, трябва да изберем всички редове с „Зад кулисите“ при всякакви стойности на масива 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;
А ето и резултата, когато бъде изпълнено на базата данни pagila:
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 реда)

Индекси на надмножества (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';
с резултат от този вид:
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 реда)

Прeсоздаване на индексите с по-малко блокировки

Често индексите трябва да бъдат преремонтирани, например, когато се увеличават размерите им, и преремонтирането може да ускори сканирането. Също така индексите могат да бъдат повредени. Промяната на параметрите на индекса също може да изисква неговото преремонтирване.

Активиране на паралелно създаване на индекси

В 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