Vo svete Postgres sú indexy rozhodujúce pre efektívnu navigáciu v úložisku databázy (nazývanej halda). Postgres preň nepodporuje klastrovanie a architektúra MVCC spôsobuje, že skončíte s mnohými verziami tej istej n-tice. Preto je veľmi dôležité vedieť vytvárať a udržiavať efektívne indexy na podporu aplikácií.
Dávam do pozornosti niekoľko tipov na optimalizáciu a zlepšenie používania indexov.
Poznámka: Dotazy zobrazené nižšie fungujú nezmenené .
Používanie krycích indexov
Pozrime sa na žiadosť o získanie e-mailových adries pre neaktívnych používateľov. V tabulke customer je tam stĺpec activea dotaz je jednoduchý:
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)
Dotaz volá celú sekvenciu skenovania tabuľky customer. Vytvorme index na stĺpci 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 bude indexovo prehľadávať "idx_cust1“ a potom pokračujte v prehľadávaní haldy tabuľky, aby ste si prečítali hodnoty iných stĺpcov (v tomto prípade stĺpca email), ktoré žiadosť potrebuje.
PostgreSQL 11 zaviedol krycie indexy. Umožňujú vám zahrnúť jeden alebo viac ďalších stĺpcov do samotného indexu - ich hodnoty sú uložené v úložisku údajov indexu.
Ak by sme použili túto funkciu a pridali e-mailovú hodnotu do indexu, Postgres by nemusel hľadať hodnotu v tabuľke email. Pozrime sa, či to funguje:
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 dotaz teraz potrebuje iba index, čo pomáha vyhnúť sa všetkým vstupom/výstupom na disku na čítanie haldy tabuľky.
Dnes sú indexy pokrytia dostupné len pre B-stromy. V tomto prípade však budú náklady na údržbu vyššie.
Použitie čiastočných indexov
Čiastočné indexy indexujú iba podmnožinu riadkov tabuľky. To vám umožňuje uložiť veľkosť indexu a vykonávať skenovanie rýchlejšie.
Povedzme, že potrebujeme získať zoznam e-mailových adries pre našich zákazníkov v Kalifornii. Žiadosť bude vyzerať takto:
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)Čo nám dajú bežné indexy:
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)
skenovať address bolo nahradené indexovým skenovaním idx_address1a potom bola halda naskenovaná address.
Keďže ide o častý dopyt a je potrebné ho optimalizovať, môžeme použiť čiastočný index, ktorý indexuje len tie riadky s adresami, v ktorých je oblasť ‘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)
Teraz sa žiadosť len číta idx_address2 a nedotýka sa stola address.
Používanie indexov s viacerými hodnotami
Niektoré stĺpce, ktoré je potrebné indexovať, nemusia obsahovať skalárny typ údajov. Typy stĺpcov ako jsonb, arrays и tsvector obsahujú zložený alebo viacnásobný význam. Ak potrebujete indexovať takéto stĺpce, zvyčajne musíte hľadať všetky jednotlivé hodnoty v týchto stĺpcoch.
Pokúsme sa nájsť názvy všetkých filmov obsahujúcich klipy z neúspešných záberov. V tabulke film existuje textový stĺpec tzv special_features. Ak má film túto „špeciálnu vlastnosť“, stĺpec obsahuje prvok vo forme textového poľa Behind The Scenes. Ak chcete vyhľadať všetky takéto filmy, musíme vybrať všetky riadky s nápisom „Behind The Scenes“. akýkoľvek hodnoty poľa special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Operátor kontajnmentu @> skontroluje, či je pravá strana podmnožinou ľavej strany.
Plán dopytov:
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)Čo si vyžaduje úplné skenovanie haldy s cenou 67.
Uvidíme, či nám pomôže pravidelný 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)S indexom sa ani nepočítalo. Index B-tree netuší o existencii jednotlivých prvkov v hodnotách, ktoré indexuje.
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)Index GIN podporuje mapovanie jednotlivých hodnôt na indexované zložené hodnoty, čo vedie k viac ako polovičným nákladom na plán dotazov.
Zbavte sa duplicitných indexov
Indexy sa časom hromadia a niekedy môže nový index obsahovať rovnakú definíciu ako jeden z predchádzajúcich. Na získanie ľudsky čitateľných definícií indexu SQL môžete použiť zobrazenie katalógu pg_indexes. Môžete tiež ľ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;
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)
Indexy nadmnožiny
Môže sa stať, že nahromadíte veľa indexov, z ktorých jeden indexuje nadmnožinu stĺpcov, ktoré indexujú iné indexy. To môže, ale nemusí byť žiaduce – nadmnožina môže viesť k skenovaniu iba indexu, čo je dobré, ale môže zaberať príliš veľa miesta alebo dopyt, ktorý mala nadmnožina optimalizovať, sa už nepoužíva.
Ak potrebujete automatizovať definíciu takýchto indexov, môžete začať s od stola pg_catalog.
Nepoužité indexy
Ako sa vyvíjajú aplikácie, ktoré používajú databázy, vyvíjajú sa aj dotazy, ktoré používajú. Predtým pridané indexy už nemôžu byť použité pre žiadne dotazy. Zakaždým, keď je index skenovaný, je zaznamenaný správcom štatistík a v zobrazení systémového katalógu pg_stat_user_indexes môžete vidieť hodnotu idx_scan, čo je kumulatívne počítadlo. Sledovanie tejto hodnoty za určité časové obdobie (povedzme mesiac) poskytne dobrú predstavu o tom, ktoré indexy sa nepoužívajú a mohli by byť vypustené.
Tu je požiadavka na získanie aktuálnych počtov skenov všetkých indexov v schéme ‘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)Prestavba indexov s menším počtom zámkov
Indexy je často potrebné znova vytvoriť, napríklad keď sa nafúknu, a opätovné vytvorenie môže urýchliť skenovanie. Indexy sa môžu tiež poškodiť. Zmena parametrov indexu môže tiež vyžadovať jeho opätovné vytvorenie.
Povoliť vytváranie paralelného indexu
V PostgreSQL 11 je vytváranie indexu B-Tree súbežné. Na urýchlenie procesu tvorby je možné použiť niekoľko paralelných pracovníkov. Uistite sa však, že tieto konfiguračné nastavenia sú nastavené správne:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Predvolené hodnoty sú príliš malé. V ideálnom prípade by sa tieto čísla mali zvyšovať spolu s počtom jadier procesora. Prečítajte si viac v .
Vytvorenie indexu na pozadí
Pomocou tejto možnosti môžete vytvoriť index na pozadí CONCURRENTLY príkazy CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXTento postup vytvárania indexu sa líši od bežného v tom, že nevyžaduje uzamknutie tabuľky, a preto neblokuje operácie zápisu. Na druhej strane to trvá dlhšie a spotrebuje viac zdrojov.
Postgres poskytuje veľa flexibilných možností na vytváranie indexov a riešení pre akékoľvek špeciálne prípady a tiež poskytuje spôsoby správy databázy, ak vaša aplikácia explozívne rastie. Dúfame, že tieto tipy vám pomôžu urýchliť vaše dopyty a vašu databázu pripraviť na škálovanie.
Zdroj: hab.com