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

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

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

Ако бяхме използвали тази възможност и добавили стойността на имейла в индекса, то на Postgres не би било необходимо да търси в купчината на таблицата стойността имейл. Нека видим дали това ще работи:

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';
което има план за запитване, включващ сканиране и на двете свързани таблици:
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, масиви и tsvector могат да съдържат сложни или множество стойности. Ако трябва да индексirате такива колони, обикновено е необходимо да търсите във всички отделни стойности в тези колони.

Да опитаме да намерим имената на всички филми, съдържащи нарязки от неуспешни дублажи. В таблицата 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 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