Vo svete Postgres sú indexy mimoriadne dôležité pre efektívnu navigáciu po úložisku databázy (to sa nazýva „hromada“, heap). Postgres nepodporuje pre ne klasifikáciu, a architektúra MVCC vedie k tomu, že sa vám hromadí veľa verzií jedného a toho istého záznamu. Preto je veľmi dôležité vedieť efektívne vytvárať a spravovať indexy na podporu aplikácií.
Predkladám vám niekoľko tipov na optimalizáciu a zlepšenie využitia indexov.
Poznámka: nižšie uvedené dopyty fungujú na nemodifikovanej .
Použitie pokrývajúcich indexov (Covering Indexes)
Pozrime sa na dopyt na získanie e-mailových adries neaktívnych používateľov. V tabuľke customer sa nachádza stĺpec active, a dopyt je nekomplikovaný:
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) Dopyt vykonáva plné sekvenčné skenovanie tabuľky. customerVytvorme index pre stĺpec 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) Pomohlo, následné skenovanie sa zmenilo na „index scan“. To znamená, že Postgres prehľadá index „idx_cust1“, a potom bude pokračovať v hľadaní v hromade tabuľky, aby si prečítal hodnoty ďalších stĺpcov (v tomto prípade, stĺpec email), ktoré sú potrebné dopytu.
V PostgreSQL 11 sa objavili pokrývajúce indexy. Tieto umožňujú zahrnúť do indexu jeden alebo viac dodatočných stĺpcov — ich hodnoty sú uložené v úložisku dát indexu.
Keby sme využili túto možnosť a pridali hodnotu e-mailu do indexu, Postgres by nemusel hľadať v hromade tabuľky hodnotu email. Pozrime sa, či to bude fungovať:
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» nám hovorí, že požiadavke stačí teraz len jeden index, čo pomáha vyhýbať sa všetkým operáciám čítania a zápisu z disku pri načítaní hromady tabuliek.
Dnes sú pokrývajúce indexy dostupné len pre B-stromy. Avšak v tomto prípade budú náklady na údržbu vyššie.
Používanie čiastočných indexov
Čiastočné indexy indexujú len podmnožinu riadkov v tabuľke. To umožňuje šetriť na veľkosti indexov a zrýchliť vyhľadávanie.
Predpokladajme, že potrebujeme získať zoznam e-mailových adries našich zákazníkov z Kalifornie. Dotaz bude vyzerať takto:
SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
ktorý má plán dotazu, ktorý zahŕňa skenovanie oboch tabuliek, ktoré sú spojené:
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
PLÁN DOTAZU
----------------------------------------------------------------------
Hash Join (náklady=15.65..32.22 riadky=9 šírka=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan na customer c (náklady=0.00..14.99 riadky=599 šírka=34)
-> Hash (náklady=15.54..15.54 riadky=9 šírka=4)
-> Seq Scan na address a (náklady=0.00..15.54 riadky=9 šírka=4)
Filter: (district = 'California'::text)
(6 riadkov)Čo nám prinesú bežné indexy:
pagila=# CREATE INDEX idx_address1 ON address(district);
VYTVORIŤ 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';
PLÁN DOTAZU
---------------------------------------------------------------------------------------
Hash Join (náklady=12.98..29.55 riadky=9 šírka=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan na customer c (náklady=0.00..14.99 riadky=599 šírka=34)
-> Hash (náklady=12.87..12.87 riadky=9 šírka=4)
-> Bitmap Heap Scan na address a (náklady=4.34..12.87 riadky=9 šírka=4)
Recheck Cond: (district = 'California'::text)
-> Bitmap Index Scan na idx_address1 (náklady=0.00..4.34 riadky=9 šírka=0)
Index Cond: (district = 'California'::text)
(8 riadkov) Skenovanie address bolo nahradené skenovaním indexu idx_address1, a potom bola preskanovaná hromada address.
Keďže ide o častý dotaz a je potrebné ho optimalizovať, môžeme použiť čiastočný index, ktorý indexuje len riadky s adresami, kde je obvod ‘California’:
pagila=# CREATE INDEX idx_address2 ON address(address_id) WHERE district='California';
VYTVORIŤ 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';
PLÁN DOTAZU
------------------------------------------------------------------------------------------------
Hash Join (náklady=12.38..28.96 riadky=9 šírka=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan na customer c (náklady=0.00..14.99 riadky=599 šírka=34)
-> Hash (náklady=12.27..12.27 riadky=9 šírka=4)
-> Index Only Scan pomocou idx_address2 na address a (náklady=0.14..12.27 riadky=9 šírka=4)
(5 riadkov) Teraz dotaz číta len idx_address2 a neovplyvňuje tabuľku address.
Použitie viacnásobných indexov (Multi-Value Indexes)
Niektoré stĺpce, ktoré je potrebné indexovať, nemusia obsahovať skalarne typy údajov. Typy stĺpcov ako napríklad jsonb, arrays a tsvector môžu obsahovať zložené alebo viaceré hodnoty. Ak potrebujete indexovať takéto stĺpce, zvyčajne je potrebné hľadať po všetkých jednotlivých hodnotách v týchto stĺpcoch.
Skúsime nájsť názvy všetkých filmov, ktoré obsahujú zostrihy z neúspešných dabingov. V tabuľke film je textový stĺpec nazvaný special_features. Ak má film toto „špeciálne vlastnosť“, potom v stĺpci sa nachádza prvok vo forme textového poľa Behind The Scenes. Na nájdenie všetkých takýchto filmov musíme vybrať všetky riadky s „Behind The Scenes“ pri akékoľvek hodnoty poľa special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}'; Operátor obsahu (containment operator) @> kontroluje, či je pravá strana podmnožinou ľavej strany.
Plán dotazu:
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)Ktorý požaduje úplné skenovanie haldy s nákladom 67.
Pozrime sa, či nám pomôže obyčajný index B-stromu:
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)Index sa ani nezohľadňoval. Index B-stromu netuší o existencii jednotlivých prvkov v indexovaných hodnotách.
Potrebujeme GIN-index.
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-index podporuje porovnávanie jednotlivých hodnôt so zindexovanými zloženými hodnotami, v dôsledku čoho sa náklady na plán dotazu znížia viac ako na polovicu.
Zbavujeme sa duplikovania indexov
Indexy sa hromadia časom a niekedy nový index môže obsahovať to isté definovanie ako jeden z predchádzajúcich. Na získanie ľahko čitateľných SQL definícií indexov môžete použiť katalógové zobrazenie pg_indexes. Taktiež budete môcť ľahko nájsť rovnaké definície:
SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
FROM pg_indexes
GROUP BY defn
HAVING count(*) > 1;
A tu je výsledok pri spustení na predvolenej databáze pagila:
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)
Indexy nadmnožín
Môže sa stať, že sa vám nahromadí veľa indexov, z ktorých jeden indexuje nadmnožinu stĺpcov, ktoré indexujú iné indexy. Môže to byť ako žiaduce, tak aj nie — nadmnožina môže viesť k skenovaniu len podľa indexov, čo je dobré, ale zároveň môže zaberať príliš veľa miesta, alebo dotaz, na optimalizáciu ktorého bola nadmnožina určená, už nie je používaný.
Ak potrebujete automatizovať definovanie takýchto indexov, môžete začať s z tabuľky pg_catalog.
Nevyužívané indexy
S rozvojom aplikácií, ktoré používajú databázy, sa vyvíjajú aj dotazy, ktoré používajú. Predtým pridané indexy sa už nemusia aplikovať žiadnym dotazom. Pri každého skenovaní indexu je označený správcovským štatistikom a v zobrazení systémového katalógu pg_stat_user_indexes môžete vidieť hodnotu idx_scan, ktorý je akumulačnými počítadlom. Sledovanie tejto hodnoty za určitý časový úsek (napríklad mesiac) poskytne dobrý prehľad o tom, ktoré indexy sa nepoužívajú a môžu byť odstránené.
Tu je dotaz na získanie aktuálnych počítadiel skenovania všetkých indexov v schéme ‘public’:
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
Výstup bude vyzerať takto:
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 riadkov)Znovu vytvorenie indexov s menším počtom zámkov
Často je potrebné indexy znova vytvoriť, napríklad keď sa zväčšujú na veľkosti a znovu vytvorenie môže urýchliť skenovanie. Indexy môžu byť tiež poškodené. Zmena parametrov indexu si môže tiež vyžadovať jeho znovu vytvorenie.
Povoľujeme paralelné vytváranie indexov
Vo PostgreSQL 11 je vytváranie indexu B-Tree konkurenčné. Na urýchlenie procesu vytvárania môže byť použitých niekoľko paralelne pracujúcich pracovníkov. Uistite sa však, že tieto konfiguračné parametre sú nastavené správne:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Predvolené hodnoty sú príliš malé. Ideálne by sa tieto čísla mali zvyšovať spolu s počtom jadier procesora. Podrobnejšie informácie nájdete v .
Vytváranie indexov na pozadí
Môžete vytvoriť index na pozadí pomocou parametra CONCURRENTLY príkazy CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXTáto procedúra vytvárania indexu sa líši od bežnej tým, že nevyžaduje zamykania tabuľky, čo znamená, že nezamyká operácie zápisu. Na druhej strane to trvá dlhšie a spotrebuje viac zdrojov.
Postgres ponúka množstvo flexibilných možností na vytváranie indexov a riešení rôznych špecifických prípadov, ako aj spôsoby správy databázy pre prípad výbuchového rastu vašej aplikácie. Dúfame, že tieto tipy vám pomôžu zefektívniť vaše dopyty a pripraviť databázu na škálovanie.
Zdroj: habr.com
Russian
Italian
German
Polish
Estonian
Albanian
Bulgarian
Croatian
Czech
Danish
Dutch
English
French
Hungarian
Latvian
Norwegian
Portuguese
Romanian
Slovak
Slovenian
Spanish
Swedish