В света на 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_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