În lumea Postgres, indexurile sunt extrem de importante pentru navigarea eficientă prin stocarea bazei de date (numită „heap”). Postgres nu suportă pentru aceasta clusterizarea, iar arhitectura MVCC duce la acumularea multor versiuni ale aceleași tupluri. De aceea, este foarte important să puteți crea și întreține indexuri eficiente pentru a susține aplicațiile.
Vă propun câteva sfaturi pentru optimizarea și îmbunătățirea utilizării indexurilor.
Notă: interogările prezentate mai jos funcționează pe o bază de date pagila .
Utilizarea indexurilor acoperitoare (Covering Indexes)
Să luăm în considerare o interogare pentru extragerea adreselor de email pentru utilizatorii inactivi. În tabelul customer este o coloană activ, iar interogarea devine simplă:
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) Interogarea implică o scanare secvențială completă a tabelului customer. Să creăm un index pentru coloana activ:
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) A funcționat, scanarea ulterioară s-a transformat în „index scan“. Aceasta înseamnă că Postgres va scana indexul „idx_cust1“, iar apoi va continua să caute în heap-ul tabelului pentru a citi valorile altor coloane (în acest caz, coloana email), care sunt necesare interogării.
În PostgreSQL 11 au apărut indexuri acoperitoare. Acestea permit includerea în index a uneia sau mai multor coloane suplimentare — valorile lor sunt stocate în depozitul de date al indexului.
Dacă am folosi această capacitate și am adăuga valoarea emailului în interiorul indexului, atunci Postgres nu ar mai avea nevoie să caute în heap-ul tabelului valoarea email. Să vedem dacă asta va funcționa:
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“ ne spune că interogării îi este suficient acum doar un index, ceea ce ajută la evitarea tuturor operațiunilor de intrare/ieșire pe disc pentru citirea heap-ului tabelului.
În prezent, indecșii de acoperire sunt disponibili doar pentru arbori B. Totuși, în acest caz, eforturile de întreținere vor fi mai mari.
Utilizarea indecșilor parțiali
Indecșii parțiali indexează doar un subset de rânduri din tabel. Aceasta permite economisirea dimensiunii indecșilor și executarea mai rapidă a scanărilor.
Să presupunem că trebuie să obținem o listă de adrese de e-mail ale clienților noștri din California. Interogarea va fi astfel:
SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
care are un plan de interogare ce implică scanarea ambelor tabele care sunt unite:
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
PLAN DE INTEROGARE
----------------------------------------------------------------------
Join Hash (cost=15.65..32.22 rows=9 width=32)
Condiție Hash: (c.address_id = a.address_id)
-> Scanare Secvențială pe customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=15.54..15.54 rows=9 width=4)
-> Scanare Secvențială pe address a (cost=0.00..15.54 rows=9 width=4)
Filtru: (district = 'California'::text)
(6 rânduri)Ce ne oferă indecșii obișnuiți:
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';
PLAN DE INTEROGARE
---------------------------------------------------------------------------------------
Join Hash (cost=12.98..29.55 rows=9 width=32)
Condiție Hash: (c.address_id = a.address_id)
-> Scanare Secvențială pe customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=12.87..12.87 rows=9 width=4)
-> Scanare Heap Bitmap pe address a (cost=4.34..12.87 rows=9 width=4)
Condiție Recheck: (district = 'California'::text)
-> Scanare Index Bitmap pe idx_address1 (cost=0.00..4.34 rows=9 width=0)
Condiție Index: (district = 'California'::text)
(8 rânduri) Scanare address a fost înlocuită de scanarea indexului idx_address1, iar apoi a fost scanată o grămadă address.
Dat fiind că aceasta este o interogare frecventă și trebuie optimizată, putem folosi un index parțial, care indexează doar acele rânduri cu adrese, în care districtul ‘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';
PLAN DE INTEROGARE
------------------------------------------------------------------------------------------------
Join Hash (cost=12.38..28.96 rows=9 width=32)
Condiție Hash: (c.address_id = a.address_id)
-> Scanare Secvențială pe customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=12.27..12.27 rows=9 width=4)
-> Scanare Doar Index folosind idx_address2 pe address a (cost=0.14..12.27 rows=9 width=4)
(5 rânduri) Acum interogarea citește doar idx_address2 și nu atinge tabelul address.
Utilizarea indecșilor cu valori multiple (Multi-Value Indexes)
Unele coloane care trebuie indexate pot să nu conțină un tip scalar de date. Tipuri de coloane precum jsonb, arrays și tsvector pot conține valori compuse sau multiple. Dacă trebuie să indexați astfel de coloane, de obicei va trebui să căutați în toate valorile separate din aceste coloane.
Să încercăm să găsim titlurile tuturor filmelor care conțin secvențe din duble nereușite. În tabelul film există o coloană de text numită special_features. Dacă filmul are această „caracteristică specială”, atunci în coloană se află un element sub formă de masiv de text Behind The Scenes. Pentru a găsi toate aceste filme, trebuie să selectăm toate rândurile care conțin „Behind The Scenes” la orice valori ale masei special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}'; Operatorul de conținere @> verifică dacă partea dreaptă este un subset al părții stângi.
Planul interogării:
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)Care presupune o scanare completă a heap-ului cu un cost de 67.
Să vedem dacă un index B-tree ne va ajuta:
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)Indexul nu a fost chiar considerat. Indexul B-tree nu știe despre existența elementelor individuale în valorile indexate.
Avem nevoie de un index 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)Indexul GIN suportă potrivirea valorilor individuale cu valorile compuse indexate, rezultând în reducerea costului planului de interogare cu mai mult de jumătate.
Scăpăm de duplicarea indecșilor
Indecșii se acumulează în timp, iar uneori un nou index poate conține aceeași definiție ca unul dintre cele anterioare. Pentru a obține definiții SQL ușor de citit de către oameni, puteți folosi vizualizarea catalogului pg_indexes. De asemenea, veți putea găsi cu ușurință definiții identice:
SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
FROM pg_indexes
GROUP BY defn
HAVING count(*) > 1;
Și iată rezultatul când este rulat pe baza de date pagila standard:
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)
Indecșii suprapunerii (Superset Indexes)
Este posibil să acumulați mulți indecși, dintre care unul indexează un superset de coloane care sunt indexate de alte indecși. Acest lucru poate fi atât dorit, cât și nu — supersetul poate duce la scanări doar pe indecși, ceea ce este bine, dar în același timp poate ocupa prea mult spațiu, sau interogarea pentru optimizarea căreia a fost destinat acest superset nu mai este folosită.
Dacă trebuie să automatizați identificarea unor astfel de indecși, atunci puteți începe cu din tabelul pg_catalog.
Indecși neutilizați
Pe măsură ce aplicațiile care utilizează baze de date evoluează, se dezvoltă și interogările utilizate de acestea. Indecșii adăugați anterior pot să nu mai fie utilizați de nicio interogare. La fiecare scanare a indexului, acesta este marcat de managerul de statistică, iar în vizualizarea catalogului sistemului pg_stat_user_indexes puteți verifica valoarea idx_scan, care este un contor cumulativ. Monitorizarea acestei valori pe o anumită perioadă de timp (să spunem o lună) va oferi o bună înțelegere a indecșilor care nu sunt utilizați și care pot fi eliminați.
Iată o cerere pentru obținerea contorilor curenți de scanare pentru toate indexurile din schemă 'public':
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
cu un rezultat ca acesta:
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 rânduri)Recrearea indexurilor cu mai puține blocări
Adesea, indexurile trebuie să fie recreează, de exemplu, atunci când se măresc în dimensiune, iar recrearea poate accelera scanarea. De asemenea, indexurile pot deveni corupte. Modificarea parametrilor indexului poate necesita, de asemenea, recrearea acestuia.
Activăm crearea paralelă a indexurilor
În PostgreSQL 11, crearea unui index B-Tree este concurentă. Pentru a accelera procesul de creare, pot fi utilizate mai multe lucrători care funcționează în paralel. Cu toate acestea, asigurați-vă că aceste parametrii de configurare sunt setați corect:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Valorile implicite sunt prea mici. Ideal, aceste numere ar trebui să fie crescute odată cu numărul de nuclee CPU. Citiți mai multe în .
Crearea în fundal a indexurilor
Puteți crea un index în fundal, folosind parametrul CONCURRENTLY instrucțiuni CREAȚI INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXAceastă procedură de creare a indexului este diferită de cea obișnuită, deoarece nu necesită blocarea tabelei, ceea ce înseamnă că nu blochează operațiunile de scriere. Pe de altă parte, durează mai mult timp și consumă mai multe resurse.
Postgres oferă numeroase opțiuni flexibile pentru crearea indexurilor și căi de soluționare a oricăror cazuri particulare, precum și modalități de gestionare a bazei de date în cazul unei creșteri explodează a aplicației dumneavoastră. Sperăm că aceste sfaturi vă vor ajuta să faceți cererile rapide și baza de date pregătită pentru scalare.
Sursa: habr.com
