Ve světě Postgres jsou indexy mimořádně důležité pro efektivní navigaci v databázovém úložišti (nazývá se „hromada“, heap). Postgres nepodporuje jeho klasifikaci a architektura MVCC způsobuje, že se hromadí mnoho verzí stejného tuple. Proto je velmi důležité umět vytvářet a udržovat efektivní indexy pro podporu aplikací.
Předkládám vám několik tipů na optimalizaci a zlepšení využití indexů.
Poznámka: Níže uvedené dotazy fungují na nemodifikovaném .
Využití pokrývajících indexů (Covering Indexes)
Podívejme se na dotaz pro získání e-mailových adres neaktivních uživatelů. V tabulce customer je sloupec aktivní, a dotaz je poměrně 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) V dotazu se provádí kompletní sekvenční skenování tabulky. customerPojďme vytvořit index pro sloupec aktivní:
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 to, následné skenování se změnilo na „index scan“. To znamená, že Postgres prohledá index „idx_cust1“, a poté pokračuje v hledání v hromadě tabulky, aby přečetl hodnoty dalších sloupců (v tomto případě sloupec email), které jsou pro dotaz potřeba.
V PostgreSQL 11 byly zavedeny pokrývající indexy. Umožňují zahrnout do samotného indexu jeden nebo více dodatečných sloupců — jejich hodnoty jsou uložené v úložišti dat indexu.
Pokud bychom tuto možnost využili a přidali hodnotu e-mailu do indexu, Postgres by nemusel hledat v hromadě tabulky hodnotu email. Podívejme se, jestli to bude fungovat:
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 říká, že dotazu nyní postačuje pouze index, což pomáhá vyhnout se všem diskovým operacím vstupu/výstupu pro čtení hromady tabulky.
V současnosti jsou indexy pokrytí dostupné pouze pro B-stromy. Nicméně v tomto případě budou náklady na údržbu vyšší.
Použití částečných indexů
Částečné indexy indexují pouze podmnožinu řádků tabulky. To pomáhá šetřit velikost indexů a zrychluje skenování.
Představme si, že potřebujeme získat seznam e-mailových adres našich klientů z Kalifornie. Dotaz by byl následující:
SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
což má plán dotazu, který zahrnuje skenování obou tabulek:
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 řádků=9 šířka=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan na customer c (náklady=0.00..14.99 řádků=599 šířka=34)
-> Hash (náklady=15.54..15.54 řádků=9 šířka=4)
-> Seq Scan na address a (náklady=0.00..15.54 řádků=9 šířka=4)
Filtr: (district = 'California'::text)
(6 řádků)Co nám dají běžné indexy:
pagila=# CREATE INDEX idx_address1 ON address(district);
VYTVOŘIT 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 řádků=9 šířka=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan na customer c (náklady=0.00..14.99 řádků=599 šířka=34)
-> Hash (náklady=12.87..12.87 řádků=9 šířka=4)
-> Bitmap Heap Scan na address a (náklady=4.34..12.87 řádků=9 šířka=4)
Znovu kontrolovat podmínku: (district = 'California'::text)
-> Bitmap Index Scan na idx_address1 (náklady=0.00..4.34 řádků=9 šířka=0)
Index Cond: (district = 'California'::text)
(8 řádků) Skenování address bylo nahrazeno skenováním indexu idx_address1, a poté byla skenována hromada address.
Jelikož se jedná o častý dotaz a je nutné jej optimalizovat, můžeme použít částečný index, který indexuje pouze ty řádky s adresami, kde okres ‘California’:
pagila=# CREATE INDEX idx_address2 ON address(address_id) WHERE district='California';
VYTVOŘIT 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 řádků=9 šířka=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan na customer c (náklady=0.00..14.99 řádků=599 šířka=34)
-> Hash (náklady=12.27..12.27 řádků=9 šířka=4)
-> Index Only Scan používající idx_address2 na address a (náklady=0.14..12.27 řádků=9 šířka=4)
(5 řádků) Nyní dotaz čte pouze idx_address2 a nezasahuje do tabulky address.
Použití vícerozměrných indexů (Multi-Value Indexes)
Některé sloupce, které je třeba indexovat, nemusí obsahovat skalární datové typy. Typy sloupců, jako jsou jsonb, pole a tsvector mohou obsahovat složené nebo vícerozměrné hodnoty. Pokud potřebujete indexovat takové sloupce, obvykle je nutné procházet jednotlivé hodnoty v těchto sloupcích.
Zkusíme najít názvy všech filmů, které obsahují ukázky z neúspěšných dabingů. V tabulce film je textový sloupec nazvaný special_features. Pokud má film toto „speciální vlastnost“, obsahuje sloupec prvek ve formě textového pole Behind The Scenes. Pro vyhledání všech těchto filmů potřebujeme vybrat všechny řádky s „Behind The Scenes“ při jakýchkoliv hodnotách pole special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}'; Operátor zaplnění (containment operator) @> ověřuje, zda je pravá část podmnožinou levé části.
Plán dotazu:
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
DOTAZOVÝ PLÁN
-----------------------------------------------------------------
Seq Scan on film (cost=0.00..67.50 rows=5 width=15)
Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)Který vyžaduje plné skenování hromady s cenou 67.
Podíváme se, zda nám pomůže běžný 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"}';
DOTAZOVÝ PLÁN
-----------------------------------------------------------------
Seq Scan on film (cost=0.00..67.50 rows=5 width=15)
Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)Index nebyl ani zvažován. Index B-stromu nezjistí existenci jednotlivých prvků ve indexovaných hodnotách.
Potřebujeme 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"}';
DOTAZOVÝ PLÁN
---------------------------------------------------------------------------
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ávání jednotlivých hodnot s indexovanými složenými hodnotami, což vedlo k poklesu ceny dotazového plánu více než na polovinu.
Zbavujeme se duplikace indexů
Indexy se s časem hromadí a někdy nový index může obsahovat stejné definice jako jeden z předchozích. Pro čitelnější SQL definice indexů lze použít katalogové zobrazení. pg_indexes. Budete také moci snadno najít shodné definice:
SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
FROM pg_indexes
GROUP BY defn
HAVING count(*) > 1;
A zde je výsledek při provedení na základní databázi 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žství (Superset Indexes)
Může se stát, že se vám nahromadí mnoho indexů, z nichž jeden indexuje nadmnožství sloupců, které indexují další indexy. To může být žádoucí, ale také ne — nadmnožství může vést ke skenování pouze přes indexy, což je dobré, ale také může zabírat příliš mnoho místa, nebo dotaz, pro jehož optimalizaci bylo toto nadmnožství určeno, už nemusí být používán.
Pokud potřebujete automatizovat určení takových indexů, můžete začít s z tabulky pg_catalog.
Nepoužívané indexy
Jak se vyvíjejí aplikace, které používají databáze, vyvíjejí se také dotazy, které používají. Dříve přidané indexy mohou být nyní použity žádným dotazem. Při každém skenování indexu jej označuje správce statistik a ve zobrazení systémového katalogu pg_stat_user_indexes můžete zkontrolovat hodnotu idx_scan, který je akumulačním počítadlem. Sledování této hodnoty za určité časové období (řekněme měsíc) poskytne dobrý přehled o tom, které indexy nejsou používané a mohou být odstraněny.
Zde je dotaz na získání aktuálních počítadel skenování všech indexů ve schématu 'public':
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
s výstupem jako je tento:
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 řádků)Přecreation indexů s menším počtem blokací
Indexy je často nutné znovu vytvářet, například když se zvětší na velikosti a opětovné vytvoření může urychlit skenování. Také indexy se mohou poškodit. Změna parametrů indexu může také vyžadovat jeho přepracování.
Povolujeme paralelní vytváření indexů
V PostgreSQL 11 je vytváření indexu B-Tree konkurentní. K urychlení procesu vytváření může být použito několik paralelně pracujících pracovníků. Ujistěte se však, že jsou tyto konfigurační parametry nastaveny správně:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Výchozí hodnoty jsou příliš nízké. Ideálně byste měli tato čísla zvyšovat spolu s počtem jáder procesoru. Podrobnosti najdete v .
Pozadí pro vytváření indexů
Můžete vytvořit index na pozadí pomocí parametru CONCURRENTLY příkazů. CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXTento postup vytváření indexu se liší od běžného tím, že nevyžaduje blokaci tabulky, což znamená, že neblokuje operace zápisu. Na druhé straně to zabere více času a spotřebovává více zdrojů.
Postgres nabízí řadu flexibilních možností pro vytváření indexů a řešení různých specifických případů, stejně jako možnosti správy databáze pro případ explozivního růstu vaší aplikace. Doufáme, že tyto tipy vám pomohou učinit dotazy rychlejšími a databázi připravenou na škálování.
Zdroj: habr.com
