Ve světě Postgres jsou indexy nezbytné pro efektivní navigaci v úložišti databáze (nazývané „hromada“). Postgres pro něj nepodporuje shlukování a architektura MVCC způsobuje, že skončíte s mnoha verzemi stejné n-tice. Proto je velmi důležité umět vytvářet a udržovat efektivní indexy pro podporu aplikací.
Zde je několik tipů pro optimalizaci a zlepšení používání indexů.
Poznámka: níže uvedené dotazy fungují na nezměněné .
Použití krycích indexů
Podívejme se na žádost o extrakci e-mailových adres pro neaktivní uživatele. Stůl customer je tam sloupec 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 vyvolá celou sekvenci skenování tabulky customer. Vytvořme index na sloupci 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 to, následný sken se změnil na "index scan". To znamená, že Postgres prohledá index "idx_cust1“, a poté pokračujte v prohledávání haldy tabulky, abyste si přečetli hodnoty dalších sloupců (v tomto případě sloupec email), které dotaz potřebuje.
Krycí indexy jsou zavedeny v PostgreSQL 11. Umožňují zahrnout jeden nebo více dalších sloupců do samotného indexu - jejich hodnoty jsou uloženy v úložišti dat indexu.
Pokud bychom využili této funkce a přidali e-mailovou hodnotu do indexu, Postgres by nemusel hledat hodnotu v hromadě tabulky. email. Uvidíme, 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 dotaz nyní potřebuje pouze index, což pomáhá vyhnout se všem diskovým I/O při čtení haldy tabulky.
Krycí indexy jsou aktuálně dostupné pouze pro B-stromy. V tomto případě však budou náklady na údržbu vyšší.
Použití částečných indexů
Částečné indexy indexují pouze podmnožinu řádků v tabulce. To šetří velikost indexů a urychluje skenování.
Řekněme, že chceme získat seznam e-mailových adres našich zákazníků v Kalifornii. Žádost bude vypadat 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)Co nám dají běž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)
Skenování address bylo nahrazeno indexovým skenováním idx_address1a pak naskenoval hromadu address.
Protože se jedná o častý dotaz a je potřeba jej optimalizovat, můžeme použít částečný index, který indexuje pouze ty řádky s adresami, ve kterých je okres ‘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)
Nyní dotaz pouze čte idx_address2 a nedotýká se stolu address.
Použití vícehodnotových indexů
Některé sloupce, které mají být indexovány, nemusí obsahovat skalární datový typ. Typy sloupců jako jsonb, arrays и tsvector obsahovat složené nebo vícenásobné hodnoty. Pokud potřebujete indexovat takové sloupce, musíte obvykle prohledat všechny jednotlivé hodnoty v těchto sloupcích.
Pokusme se najít názvy všech filmů obsahujících střihy z neúspěšných záběrů. Stůl film existuje textový sloupec nazvaný special_features. Pokud má film tuto "zvláštní vlastnost", sloupec obsahuje prvek jako textové pole Behind The Scenes. Chcete-li vyhledat všechny takové filmy, musíme vybrat všechny řádky s nápisem „Behind The Scenes“, kdy jakýkoli hodnoty pole special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Operátor hnízdění @> zkontroluje, zda je pravá strana podmnožinou levé strany.
Žádost o plán:
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)Což vyžaduje úplné skenování haldy s cenou 67.
Podívejme se, zda nám pomáhá 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 indexem se ani nepočítalo. Index B-stromu si není vědom existence jednotlivých prvků v 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"}';
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 mapování jednotlivých hodnot na indexované složené hodnoty, což vede k více než polovičním nákladům na plán dotazů.
Zbavte se duplicitních indexů
Indexy se v průběhu času hromadí a někdy může nový index obsahovat stejnou definici jako jeden z předchozích. Zobrazení katalogu můžete použít k získání definic indexů SQL čitelných pro člověka. pg_indexes. Můžete také snadno najít stejné definice:
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 se stát, že skončíte s mnoha indexy, z nichž jeden indexuje nadmnožinu sloupců, které indexují jiné indexy. To může nebo nemusí být žádoucí – nadmnožina může mít za následek skenování pouze na základě indexu, což je dobré, ale může to zabírat příliš mnoho místa nebo se dotaz, který měla nadmnožina optimalizovat, již nepoužívá.
Pokud potřebujete automatizovat definici takových indexů, můžete začít s od stolu pg_catalog.
Nepoužité indexy
Jak se vyvíjejí aplikace využívající databáze, vyvíjejí se i dotazy, které používají. Dříve přidané indexy již nesmí být používány žádným dotazem. Pokaždé, když je index skenován, je označen správcem statistik a v zobrazení systémového katalogu pg_stat_user_indexes můžete vidět hodnotu idx_scan, což je kumulativní počítadlo. Sledování této hodnoty po určitou dobu (řekněme měsíc) poskytne dobrou představu o tom, které indexy se nepoužívají a mohly by být vypuštěny.
Zde je dotaz k získání aktuálních počtů skenování všech indexů ve schématu ‘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)Přestavba indexů s menším počtem zámků
Indexy je často potřeba znovu sestavit, například když se nafouknou, a opětovné sestavení může skenování urychlit. Indexy se také mohou poškodit. Změna parametrů indexu může také vyžadovat jeho opětovné sestavení.
Povolit vytváření paralelního indexu
V PostgreSQL 11 je vytváření indexu B-Stromu souběžné. Pro urychlení procesu vytváření lze použít několik paralelních pracovníků. Ujistěte se však, že jsou tyto možnosti konfigurace nastaveny správně:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Výchozí hodnoty jsou příliš malé. V ideálním případě by se tato čísla měla zvyšovat spolu s počtem procesorových jader. Přečtěte si více v .
Vytvoření indexu na pozadí
Pomocí této možnosti můžete vytvořit index na pozadí CONCURRENTLY týmy CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXTento postup vytváření indexu se liší od obvyklého v tom, že nevyžaduje zámek tabulky, a proto neblokuje operace zápisu. Na druhou stranu to zabere více času a spotřebuje více zdrojů.
Postgres poskytuje velkou flexibilitu pro vytváření indexů a způsoby řešení jakýchkoli speciálních případů, stejně jako způsoby správy databáze v případě, že vaše aplikace exploduje. Doufáme, že vám tyto tipy pomohou rychle získat vaše dotazy a připravit databázi na škálování.
Zdroj: www.habr.com