În lumea Postgres, indexurile sunt esențiale pentru navigarea eficientă a stocării bazei de date (numită „heap”). Postgres nu acceptă clustering pentru aceasta, iar arhitectura MVCC vă face să ajungeți cu multe versiuni ale aceluiași tuplu. Prin urmare, este foarte important să puteți crea și menține indecși eficienți pentru a susține aplicații.
Iată câteva sfaturi pentru optimizarea și îmbunătățirea utilizării indicilor.
Notă: interogările prezentate mai jos funcționează nemodificat .
Utilizarea indicilor de acoperire
Să ne uităm la o solicitare de extragere a adreselor de e-mail pentru utilizatorii inactivi. Masa customer există o coloană active, iar interogarea este 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 invocă secvența completă de scanare a tabelului customer. Să creăm un index pe o coloană 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)
A ajutat, scanarea ulterioară s-a transformat în „index scan". Aceasta înseamnă că Postgres va scana indexul "idx_cust1", apoi continuați căutarea în grămada de tabel pentru a citi valorile altor coloane (în acest caz, coloana email) de care interogarea are nevoie.
Indicii de acoperire sunt introduși în PostgreSQL 11. Ele vă permit să includeți una sau mai multe coloane suplimentare în indexul în sine - valorile lor sunt stocate în depozitul de date index.
Dacă am profita de această caracteristică și am adăugat valoarea de e-mail în interiorul indexului, atunci Postgres nu ar trebui să caute valoarea în heap-ul tabelului. email. Să vedem dacă 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ă interogarea are nevoie acum doar de un index, ceea ce ajută la evitarea tuturor I/O pe disc pentru a citi heap-ul tabelului.
În prezent, indicii de acoperire sunt disponibili numai pentru arbori B. Cu toate acestea, în acest caz, efortul de întreținere va fi mai mare.
Utilizarea indicilor parțiali
Indicii parțiali indexează doar un subset de rânduri dintr-un tabel. Acest lucru salvează dimensiunea indexurilor și face scanările mai rapide.
Să presupunem că vrem să obținem o listă cu adresele de e-mail ale clienților noștri din California. Cererea va fi astfel:
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)Ce indici obișnuiți ne vor oferi:
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)
scanda address a fost înlocuit cu scanarea indexului idx_address1și apoi a scanat grămada address.
Deoarece 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';
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)
Acum interogarea doar citește idx_address2 și nu atinge masa address.
Utilizarea indicilor cu mai multe valori
Este posibil ca unele coloane de indexat să nu conțină un tip de date scalare. Tipuri de coloane cum ar fi jsonb, arrays и tsvector conțin valori compuse sau multiple. Dacă trebuie să indexați astfel de coloane, de obicei trebuie să căutați prin toate valorile individuale din acele coloane.
Să încercăm să găsim titlurile tuturor filmelor care conțin tăieturi din filmări nereușite. Masa film există o coloană de text numită special_features. Dacă filmul are această „proprietate specială”, atunci coloana conține elementul ca matrice de text Behind The Scenes. Pentru a căuta toate astfel de filme, trebuie să selectăm toate rândurile cu „În spatele scenei” când любых valori de matrice special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Operator de cuibărire @> verifică dacă partea dreaptă este un subset al părții stângi.
Solicitare plan:
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 solicită o scanare completă cu un cost de 67.
Să vedem dacă un index B-tree obișnuit ne ajută:
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)Indicele nici nu a fost luat în considerare. Indicele B-tree nu este conștient de 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)Indicele GIN acceptă maparea valorilor unice față de valorile compozite indexate, rezultând un cost al planului de interogare care este mai mult de jumătate.
Scaparea de indici duplicati
Indicii se acumulează în timp și, uneori, un nou index poate conține aceeași definiție ca unul dintre cei anteriori. Puteți utiliza vizualizarea catalog pentru a obține definiții SQL care pot fi citite de către om pentru indici. pg_indexes. De asemenea, puteți 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;
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)
Indici de supraseturi
Se poate întâmpla să ajungeți cu mulți indecși, dintre care unul indexează un superset de coloane care indexează alți indici. Acest lucru poate fi sau nu de dorit — supersetul poate duce la scanări numai indexate, ceea ce este bine, dar poate ocupa prea mult spațiu sau interogarea pe care supersetul a fost intenționat să o optimizeze nu mai este utilizată.
Dacă trebuie să automatizați definirea unor astfel de indici, puteți începe cu de la masă pg_catalog.
Indici neutilizați
Pe măsură ce aplicațiile care folosesc baze de date evoluează, la fel evoluează și interogările pe care le folosesc. Indecșii adăugați mai devreme nu mai pot fi utilizați de nicio interogare. De fiecare dată când un index este scanat, acesta este marcat de managerul de statistici și în vizualizarea catalogului de sistem pg_stat_user_indexes poti vedea valoarea idx_scan, care este un contor cumulativ. Urmărirea acestei valori pe o perioadă de timp (să zicem o lună) va oferi o idee bună despre indici care nu sunt utilizați și ar putea fi abandonați.
Iată o interogare pentru a obține numărul curent de scanare a tuturor indecșilor din schemă ‘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)Reconstruirea indicilor cu mai puține blocări
Adesea, indicii trebuie reconstruiți, de exemplu atunci când devin umflați, iar reconstrucția poate accelera scanarea. De asemenea, indexurile pot fi corupte. Modificarea parametrilor indexului poate necesita, de asemenea, reconstruirea acestuia.
Activați crearea de index paralel
În PostgreSQL 11, crearea unui index B-Tree este concurentă. Pentru a accelera procesul de creare, pot fi folosiți mai mulți lucrători paraleli. Cu toate acestea, asigurați-vă că aceste opțiuni de configurare sunt setate corect:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Valorile implicite sunt prea mici. În mod ideal, aceste numere ar trebui să crească odată cu numărul de nuclee de procesor. Citiți mai multe în .
Crearea indexului de fundal
Puteți crea un index în fundal folosind opțiunea CONCURRENTLY comenzi CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXAceastă procedură de creare a indexului diferă de cea obișnuită prin faptul că nu necesită o blocare pe tabel și, prin urmare, nu blochează operațiunile de scriere. Pe de altă parte, este nevoie de mai mult timp și consumă mai multe resurse.
Postgres oferă multă flexibilitate pentru crearea de indexuri și modalități de a rezolva orice cazuri speciale, precum și modalități de a gestiona baza de date în cazul în care aplicația dvs. crește exploziv. Sperăm că aceste sfaturi vă vor ajuta să obțineți rapid interogările și baza de date pregătită pentru scalare.
Sursa: www.habr.com