Използване на всички функции на индексите в PostgreSQL

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

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

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

Използване на покриващи индекси

Нека разгледаме заявка за извличане на имейл адреси за неактивни потребители. Таблица 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' ни казва, че заявката сега се нуждае само от индекс, който помага да се избегнат всички дискови I/O за четене на купчината на таблицата.

Покриващите индекси в момента са налични само за 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.

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

Някои колони за индексиране може да не съдържат скаларен тип данни. Типове колони като jsonb, arrays и tsvector съдържат съставни или множество стойности. Ако трябва да индексирате такива колони, обикновено трябва да търсите във всички отделни стойности в тези колони.

Нека се опитаме да намерим заглавията на всички филми, съдържащи изрезки от неуспешни дубли. Таблица film има текстова колона, наречена special_features. Ако филмът има това „специално свойство“, тогава колоната съдържа елемента като текстов масив Behind The Scenes. За да търсим всички такива филми, трябва да изберем всички редове с „Зад кулисите“, когато който и да е стойности на масива special_features:

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

Оператор за влагане @> проверява дали дясната страна е подмножество на лявата страна.

План за заявка:

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)

Супермножество индекси

Може да се случи да получите много индекси, един от които индексира надмножество от колони, които индексират други индекси. Това може или не може да е желателно – надмножеството може да доведе до сканиране само на индекс, което е добре, но може да заема твърде много място или заявката, която надмножеството е предназначено да оптимизира, вече не се използва.

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

Източник: www.habr.com