PostgreSQL: Optimalizace dotazů a porozumění fungování indexů

PostgreSQL: Proč jsou indexy ignorovány a jak optimalizovat dotazy

Vytvoření indexu v PostgreSQL nezaručuje vždy jeho použití. Vývojáři se často setkávají se situací, kdy i přes existenci indexu jsou dotazy prováděny pomalu, s využitím plného skenování tabulky (Seq Scan). Pro optimalizaci výkonu databází je klíčové porozumět mechanismům fungování plánovače dotazů PostgreSQL, interpretovat metriky EXPLAIN ANALYZE a znát faktory ovlivňující výběr strategie provádění. V tomto článku se podrobně podíváme na tyto aspekty na reálných příkladech s velkým objemem dat.

Příprava: 4 miliony řádků pro experimenty

Pro demonstraci fungování indexů vytvoříme testovací tabulku t_test bez primárního klíče a indexů, obsahující 4 miliony záznamů. To nám umožní názorně ukázat rozdíl ve výkonu před a po indexování.

DROP TABLE IF EXISTS t_test;
CREATE TABLE t_test (id serial, name text);
INSERT INTO t_test (name) SELECT 'hans' FROM generate_series(1, 2000000);
INSERT INTO t_test (name) SELECT 'paul' FROM generate_series(1, 2000000);
SELECT name, count(*) FROM t_test GROUP BY name;

Při spuštění dotazu bez indexu, například EXPLAIN ANALYZE SELECT * FROM t_test WHERE id = 432332;, uvidíme Seq Scan, který projde všech 4 miliony řádků a zabere přibližně 126 milisekund. To je klasický příklad problému, který mají indexy řešit.

Pochopení EXPLAIN a metriky cost

EXPLAIN ANALYZE je základní nástroj pro analýzu plánů provádění dotazů v PostgreSQL. Poskytuje podrobné informace o tom, jak plánovač hodlá dotaz provést, včetně vybraných operátorů (například Seq Scan, Index Scan), doby provádění a, což je obzvláště důležité, metriky cost.

Podívejme se na výstup cost=0.00..71622.00 z předchozího příkladu. Toto číslo není skutečný čas v milisekundách, ale podmíněný koeficient, který PostgreSQL používá k porovnání různých plánů provádění. Lze si jej představit jako „papoušky“ – abstraktní jednotky nákladů. Pro čistotu experimentu je vhodné vypnout paralelismus:

SET max_parallel_workers_per_gather TO 0;

Náklady se skládají z několika komponent, jako je počet bloků na disku a náklady na zpracování každého řádku:

SELECT pg_relation_size('t_test') / 8192.0; -- ~21622 bloků po 8 KB
SHOW cpu_tuple_cost;    -- 0.01 (náklady na zpracování řádku)
SHOW cpu_operator_cost; -- 0.0025 (náklady na operátor/funkci)

Přibližný vzorec nákladů pro náš Seq Scan bude vypadat takto:

SELECT (pg_relation_size('t_test') / 8192.0) * 1
       + count(id) * 0.01
       + count(id) * 0.0025
FROM t_test;

Výsledek bude blízký 71622. Je důležité si uvědomit, že cost nezohledňuje hardwarové specifika systému, proto porovnávat cost dvou různých dotazů mezi sebou pro odhad skutečné doby provádění není správné. Nicméně v rámci jednoho dotazu je užitečný pro identifikaci nejdražších částí plánu.

Základní použití indexů: BTree a jeho výhody

Nejběžnějším typem indexu v PostgreSQL je BTree. Zajišťuje efektivní vyhledávání, řazení a vysokou souběžnost. Vytvoříme BTree index pro sloupec id:

CREATE INDEX idx_id ON t_test (id);
EXPLAIN SELECT * FROM t_test WHERE id = 43242;

Po vytvoření indexu se cost dotazu výrazně sníží (ze 71 622 na 8.45) a doba výběru se zkrátí na zlomky milisekund. BTree indexy jsou také efektivní pro operace řazení a hledání minimálních/maximálních hodnot:

• Řazení: PostgreSQL může použít index pro provedení ORDER BY, jednoduše procházením indexu v požadovaném směru (Index Scan Backward pro DESC) a zastavením po dosažení LIMIT.

```sql

EXPLAIN SELECT * FROM t_test ORDER BY id DESC LIMIT 10;

```

• Min/Max: Pro určení min(id) nebo max(id) plánovač použije Index Only Scan, načítající první nebo poslední záznam z indexu.

```sql

EXPLAIN SELECT min(id), max(id) FROM t_test;

```

Zpracování více podmínek: Bitmap Scan

PostgreSQL je schopen efektivně zpracovávat dotazy s více podmínkami OR na jednom indexu pomocí Bitmap Scan.

EXPLAIN SELECT * FROM t_test WHERE id = 30 OR id = 50;

V tomto případě PostgreSQL provede Bitmap Index Scan pro každou podmínku, poté sloučí získané výsledky do bitmapy pomocí BitmapOr a teprve poté přistoupí k hlavní tabulce (Bitmap Heap Scan) pro extrakci kompletních řádků. To umožňuje vyhnout se opakovanému skenování tabulky a optimalizovat přístup k datům.

Proč plánovač ignoruje index: Selektivita a statistika

Jedním z nejčastějších důvodů, proč index není použit, je nízká selektivita podmínky dotazu. Selektivita je podíl řádků v tabulce, které splňují danou podmínku. Pokud podmínka ovlivňuje velkou část tabulky, plánovač se může rozhodnout, že Seq Scan bude efektivnější než Index Scan.

Pojďme se podívat na příklad. Vytvoříme index pro pole name:

CREATE INDEX idx_name ON t_test (name);

Pokud hledáme neexistující jméno (EXPLAIN SELECT * FROM t_test WHERE name = 'hans2';), index bude použit a rows bude rovno 1, protože PostgreSQL vždy očekává alespoň jeden řádek. Pokud však dotaz zahrnuje velkou část tabulky, například 'hans' OR 'paul' (což tvoří 100 % naší testovací tabulky):

EXPLAIN SELECT * FROM t_test WHERE name = 'hans' OR name = 'paul';

V tomto případě PostgreSQL provede Seq Scan. Důvod je jednoduchý: skenování celého indexu a následné přecházení k tabulce pro každý ze 4 milionů řádků je dražší než jednoduše sekvenčně přečíst celou tabulku. Plánovač se rozhoduje na základě statistik o distribuci dat. Pokud jsou statistiky zastaralé (například po velkém objemu změn), rozhodnutí nemusí být optimální.

Vliv fyzického uspořádání dat: Korelace a CLUSTER

Efektivita indexu silně závisí na fyzickém uspořádání dat na disku. Pokud jsou data, na která index odkazuje, silně rozptýlena, může to výrazně zpomalit výběr. Vytvoříme kopii naší tabulky, ale s náhodným pořadím řádků:

CREATE TABLE t_random AS SELECT * FROM t_test ORDER BY random();
CREATE INDEX idx_random ON t_random (id);
VACUUM ANALYZE t_random;

Porovnejme výkon dotazu na získání prvních 10 000 záznamů pro původní (seřazenou) a náhodně uspořádanou tabulku:

• Originální tabulka (data v pořadí):

```sql

EXPLAIN (analyze true, buffers true) SELECT * FROM t_test WHERE id < 10000;

```

Zde uvidíme nízký počet přístupů k bufferům (například Buffers: shared hit=3 read=82), což naznačuje sekvenční čtení dat.

• Náhodně uspořádaná tabulka (data promíchaná):

```sql

EXPLAIN (analyze true, buffers true) SELECT * FROM t_random WHERE id < 10000;

```

V tomto případě bude počet přístupů k bufferům výrazně vyšší (například Buffers: shared hit=801 read=7210). Děje se tak proto, že data jsou rozptýlena a PostgreSQL musí provádět mnoho náhodných čtení z disku, což výrazně prodlužuje dobu provádění. Plánovač se může dokonce přepnout na Bitmap Heap Scan.

Korelace

PostgreSQL sleduje míru uspořádanosti dat pomocí metriky korelace, která je dostupná v pg_stats:

SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename IN ('t_test', 't_random') AND attname = 'id'
ORDER BY 1, 2;

• correlation ~ 1: Data jsou fyzicky uspořádána, což umožňuje sekvenční čtení bloků z disku.
• correlation ~ 0: Data jsou náhodně rozptýlena, což vede k mnoha jednotlivým přístupům na disk pro každý řádek.

CLUSTER

Příkaz CLUSTER umožňuje fyzicky seřadit data v tabulce podle zadaného indexu:

CLUSTER t_random USING idx_random;
VACUUM ANALYZE t_random;

Po CLUSTER se výběr dat opět zrychlí. Příkaz CLUSTER má však podstatné nevýhody:

• Blokování tabulky: Operace blokuje celou tabulku, včetně SELECT, po dobu provádění.
• Omezení: Funguje pouze pro jeden index.
• Neautomatická podpora: Pořadí dat není automaticky udržováno; po nových vkladech nebo aktualizacích se data mohou opět stát neuspořádanými.

Optimalizace pomocí Index Only Scan a INCLUDE

Pokud dotaz přistupuje pouze ke sloupcům, které jsou plně obsaženy v indexu, PostgreSQL může provést Index Only Scan. To umožňuje vyhnout se přístupu k hlavní tabulce (heap), což výrazně urychluje provádění dotazu.

EXPLAIN SELECT id FROM t_test WHERE id = 34234;

Zde je id již v indexu idx_id, takže Index Only Scan je možný. Pokud však požádáme o všechny sloupce, včetně name, který v indexu idx_id není:

EXPLAIN SELECT * FROM t_test WHERE id = 34234;

PostgreSQL provede běžný Index Scan, protože bude muset přistoupit k tabulce pro sloupec name. Pro zajištění Index Only Scan i pro SELECT * lze použít covering index s klauzulí INCLUDE:

CREATE INDEX idx_random_cover ON t_random (id) INCLUDE (name);
EXPLAIN SELECT * FROM t_random WHERE id = 34234;

Nyní je name zahrnut v indexu a opět se provádí Index Only Scan, což minimalizuje přístupy na disk.

Pokročilé techniky indexování: Funkční a částečné indexy

Kromě standardních BTree indexů nabízí PostgreSQL i specializovanější řešení:

• Funkční indexy: Umožňují indexovat výsledky funkcí. Jediným požadavkem je, aby funkce byla deterministická (vždy vracela stejný výsledek pro stejné vstupní údaje).

```sql

CREATE INDEX idx_cos ON t_random (cos(id));

EXPLAIN SELECT * FROM t_random WHERE cos(id) = 10;

```

Typickým scénářem použití je indexování lower(email) pro vyhledávání bez ohledu na velikost písmen.

• Částečné indexy: Indexy, které pokrývají pouze podmnožinu řádků tabulky splňujících určitou podmínku WHERE.

```sql

CREATE INDEX idx_name ON t_test (name) WHERE name NOT IN ('hans', 'paul');

```

Takový index bude výrazně menší a bude se aktualizovat méně často, což je užitečné, když se hlavní část dat zřídka účastní vyhledávacích dotazů.

Další typy indexů: GiST a pg_trgm

PostgreSQL podporuje různé typy indexů, z nichž každý je optimalizován pro konkrétní úlohy. Kromě BTree existují GIN, GiST, SP-GiST, BRIN, Bloom. Například GiST indexy se často používají pro geoprostorová data, fulltextové vyhledávání a další složité datové typy.

Rozšíření pg_trgm umožňuje provádět fuzzy vyhledávání, rozdělením řetězců na trigramy a výpočtem vzdálenosti mezi nimi. To je užitečné pro vyhledávání s překlepy nebo částečnou shodu řetězců.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT 'abcde' <-> 'abdeacb'; -- číslo od 0 do 1
SELECT show_trgm('abcdef');

Co je důležité

• EXPLAIN ANALYZE – váš nejlepší přítel: Použijte jej k pochopení plánů dotazů a identifikaci úzkých míst. cost je podmíněná metrika, užitečná pro porovnání částí jednoho plánu.
• Selektivita určuje výběr: Indexy jsou efektivní pro vysoce selektivní dotazy (málo řádků). Při nízké selektivitě (mnoho řádků) může PostgreSQL preferovat Seq Scan.
• Fyzické uspořádání dat je důležité: Vysoká korelace mezi logickým a fyzickým pořadím dat zlepšuje výkon Index Scan. CLUSTER může pomoci, ale má vážné nevýhody.
• Index Only Scan a INCLUDE: Použijte tyto mechanismy k minimalizaci přístupů k hlavní tabulce, když jsou všechny potřebné sloupce již v indexu.
• Pokročilé indexy: Funkční a částečné indexy umožňují vytvářet specializovanější a efektivnější struktury pro konkrétní scénáře.

— Editorial Team