Praktický návod

Jak vytvořit správné indexy v PostgreSQL

Index nenavrhuj podle názvu sloupce. Navrhni ho podle konkrétního filtru, řazení a objemu dat.

25 minut · PostgreSQL

Nejdřív stručně

Index je zrychlení za určitou cenu

Databázový index pomáhá PostgreSQL najít a seřadit řádky bez procházení celé tabulky. Každý index ale zabírá místo a zpomaluje INSERT, UPDATE i DELETE.

Správný index vychází ze skutečného SQL dotazu. Pořadí sloupců, podmínka částečného indexu i zahrnuté hodnoty musí odpovídat tomu, co aplikace opravdu filtruje a vrací.

Připrav si

Co budeš potřebovat

Nejdřív vyber pomalý nebo často volaný dotaz. Index bez konkrétního dotazu nemá ověřitelný cíl.

Přesné SQL včetně WHERE, JOIN, ORDER BY a LIMIT, ideálně z produkčních statistik.
Reprezentativní objem a rozložení dat. Index nad tisícem testovacích řádků může vypadat jinak než nad miliony.
Výstup EXPLAIN (ANALYZE, BUFFERS) před změnou a očekávaný čas odezvy.
Přehled existujících indexů a možnost sledovat jejich velikost i použití po nasazení.

Kroky 1 až 3

Navrhni index od dotazu

Začni běžným B-tree indexem. Specializovaný typ použij až tehdy, když odpovídá operátorům konkrétního dotazu.

1. Přečti filtr a řazení

Najdi sloupce porovnávané rovností, rozsahem a JOINem. Ověř také ORDER BY a zda dotaz vrací jen malou část tabulky.
Sekvenční scan není automaticky chyba. Pro velkou část malé tabulky může být levnější než skákání přes index.
Porovnej odhadované a skutečné řádky. Velký rozdíl může znamenat zastaralé statistiky, ne chybějící index.
Zapiš výchozí execution time, počet bufferů a počet vrácených řádků.

EXPLAIN (ANALYZE, BUFFERS) SELECT id, total FROM orders WHERE customer_id = 42 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

Oficiální PostgreSQL dokumentace k EXPLAIN

2. Zvol sloupce a typ indexu

U vícesloupcového B-tree indexu dej zpravidla nejdřív sloupce s rovností, potom rozsah nebo řazení. Ověř to na konkrétním plánu.
Částečný index použij, pokud dotazy opakovaně pracují s malou stabilní podmnožinou, například jen s nezpracovanými objednávkami.
INCLUDE může umožnit index-only scan pro vracené sloupce, ale zvětšuje index. Nezahrnuj automaticky celý řádek.
GIN, GiST nebo BRIN vybírej podle operátorů a dat, ne podle pověsti. B-tree je správný výchozí bod pro rovnost, rozsah a řazení.

CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC) INCLUDE (total) WHERE status = 'paid';

Oficiální PostgreSQL přehled indexů

3. Vytvoř index bezpečně a změř dopad

Na vytížené produkční tabulce zvaž CREATE INDEX CONCURRENTLY, aby běžné zápisy nebyly blokované po celou dobu sestavení.
CONCURRENTLY nelze spustit uvnitř transakčního bloku a po neúspěchu může zůstat neplatný index. Stav vždy zkontroluj.
Zopakuj stejný EXPLAIN ANALYZE a ověř změnu plánu, bufferů i času. Malý rozdíl nemusí ospravedlnit další index.
Po nasazení sleduj pg_stat_user_indexes, velikost indexu a latenci zápisů. Nepoužívaný index po ověření odstraň.

CREATE INDEX CONCURRENTLY idx_orders_customer_created ON orders (customer_id, created_at DESC) INCLUDE (total) WHERE status = 'paid';

Oficiální dokumentace k CREATE INDEX CONCURRENTLY

Krok 4

Ověř čtení i cenu zápisu

Nový index je užitečný jen tehdy, když pomůže důležitému dotazu a nezpůsobí nepřiměřenou režii jinde.

Porovnej plán před a po změně

Použij stejná data a parametry. Ověř skutečné řádky, loops, buffery a celkový čas, ne pouze název Index Scan.
```
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
```

Zkontroluj stav a velikost

Neplatný nebo překvapivě velký index nepropouštěj dál do deploye.

SELECT indexrelid::regclass, indisvalid, pg_size_pretty(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid = 'orders'::regclass;

Změř běžné zápisy

Porovnej INSERT a UPDATE před změnou a po ní. Sleduj také růst disku a replikační lag.

Když to zlobí

Nejčastější chyby

Planner stále volí Seq Scan

Může to být správně, pokud dotaz vrací velkou část tabulky. Ověř selektivitu, statistiky, datové typy parametrů a skutečný čas obou plánů.

ANALYZE orders;

Vícesloupcový index pomáhá jen některým dotazům

Zkontroluj pořadí sloupců a pravidla levého prefixu. Jeden široký index nenahrazuje různé přístupové vzory.

Po vytvoření indexu se zpomalily zápisy

Každý zápis musí udržovat další strukturu. Odstraň duplicitní a nepoužívané indexy a INCLUDE omez na skutečně potřebné sloupce.

CONCURRENTLY zanechalo neplatný index

Najdi ho přes pg_index.indisvalid, odstraň ho bezpečně a po vyřešení příčiny sestavení zopakuj. Neplatný index nenechávej bez kontroly.

Hotovo

Index má jasný důvod i měřitelný přínos.

Index teď odpovídá konkrétnímu dotazu a jeho dopad je ověřený na čtení i zápis. Stejný postup opakuj před každým dalším CREATE INDEX.

Přečíst databázový index ve slovníku Všechny návody