У свеце 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