Praktický návod

Jak najít pomalý SQL dotaz pomocí EXPLAIN ANALYZE

Nečti plán jen podle prvního řádku. Najdi uzel, ve kterém mizí čas, řádky nebo databázové buffery.

25 minut · PostgreSQL

Nejdřív stručně

EXPLAIN odhaduje, ANALYZE měří

PostgreSQL sestaví query plan jako strom operací. Samotný EXPLAIN ukáže odhad planneru; EXPLAIN ANALYZE dotaz skutečně vykoná a doplní reálný čas, počet řádků a počet opakování.

Proto buď opatrný u zápisového SQL. UPDATE nebo DELETE se při ANALYZE opravdu provede. V produkci začni bezpečným EXPLAIN a měření zápisu dělej v kontrolovaném prostředí.

Připrav si

Co budeš potřebovat

Plán musí odpovídat skutečnému dotazu, parametrům a datům. Anonymizovaný dotaz s jinými hodnotami může mít jiný plán.

Přesné SQL a typické parametry z pomalého požadavku nebo pg_stat_statements.
Reprezentativní data, stejné indexy a podobné nastavení PostgreSQL jako v produkci.
Možnost spustit SELECT bez dopadu; zápisové dotazy analyzuj na kopii nebo uvnitř bezpečně vrácené transakce.
Výchozí délku dotazu a kontext: četnost, souběh, počet vracených řádků a očekávaný výsledek.

Kroky 1 až 3

Čti plán od skutečné práce

Cena cost není v milisekundách. Pro výkon sleduj actual time, rows, loops, buffery a jejich násobení.

1. Spusť měření bezpečně

Nejdřív spusť EXPLAIN bez ANALYZE. Ověříš tvar plánu, aniž by se dotaz vykonal.
U bezpečného SELECTu přidej ANALYZE a BUFFERS. FORMAT JSON použij, pokud plán zpracovává nástroj.
ANALYZE přidává režii a dotaz opravdu spouští. Nepouštěj bez rozmyslu drahý dotaz během špičky.
Zápisový dotaz můžeš v testovacím prostředí obalit BEGIN a ROLLBACK, ale rollback nevrátí externí efekty triggerů mimo databázi.

EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS) SELECT ...;

Oficiální PostgreSQL návod k EXPLAIN

2. Najdi uzel s největší skutečnou prací

Plán čti od nejnižších uzlů nahoru. Každý rodič spotřebovává řádky svých potomků.
Actual time je čas jednoho spuštění uzlu a loops říká, kolikrát proběhl. Malý čas krát vysoké loops může být hlavní problém.
Hledej odfiltrované řádky, velké sekvenční scany, opakovaný vnitřek Nested Loop, drahé Sort a dočasné soubory.
Shared read znamená čtení z úložiště, shared hit z cache. Porovnávej plány po zahřátí i se studenější cache podle reality provozu.

actual time × loops; actual rows proti odhadovaným rows; Buffers: shared hit/read

Oficiální reference příkazu EXPLAIN

3. Oprav příčinu a plán znovu změř

Pokud se odhad řádků zásadně liší od reality, obnov statistiky nebo zvaž vyšší statistický cíl pro problematický sloupec.
Index přidej jen pro konkrétní filtr, spojení nebo řazení. Sekvenční scan nad velkou částí tabulky může zůstat správně.
Omez načítané sloupce a řádky, odstraň zbytečný sort nebo uprav JOIN. Někdy je problém v tvaru dotazu, ne v databázi.
Po každé změně spusť stejný plán se stejnými parametry a ověř i správnost výsledku.

ANALYZE orders;

Oficiální PostgreSQL dokumentace k ANALYZE

Krok 4

Porovnej stejné podmínky

Jedno rychlé spuštění není důkaz. Plán ověř s různými typickými parametry a realistickým souběhem.

Ulož plán před změnou a po ní

Porovnej execution time, rows, loops, buffery, sort metodu a dočasné soubory.
```
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;
```
Vyzkoušej různé hodnoty parametrů

Častá a vzácná hodnota mohou potřebovat jiný plán. Ověř oba důležité případy.
Sleduj dotaz po nasazení

Porovnej četnost, průměr, horní percentily a celkový čas v pg_stat_statements. Optimalizace jednoho běhu nesmí zhoršit běžný provoz.
```
SELECT query, calls, mean_exec_time, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;
```

Když to zlobí

Nejčastější chyby

Cost vypadá vysoký, ale dotaz je rychlý

Cost je relativní odhad planneru, ne čas v milisekundách. Hodnoť actual time, buffery a chování pod zátěží.

Odhad rows je úplně jiný než skutečnost

Obnov statistiky a zkontroluj korelaci sloupců, datové typy a rozložení hodnot. Špatný odhad může zvolit špatný JOIN i scan.

ANALYZE table_name;

Plán je rychlý podruhé, ale pomalý poprvé

Druhé spuštění těží z cache. Sleduj shared read a hit a porovnávej stav odpovídající produkčnímu provozu.

EXPLAIN ANALYZE změnil data

ANALYZE dotaz opravdu vykonává. Zápisy zkoušej na bezpečné kopii nebo v transakci s rollbackem a ověř, zda nejsou přítomné externí vedlejší efekty.

BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK;

Hotovo

Pomalé místo je změřené, ne odhadnuté.

SQL dotaz teď hodnotíš podle skutečných řádků, opakování a I/O. Příští optimalizaci začni stejným způsobem: přesný dotaz, bezpečný plán, jedna změna a nové měření.

Přečíst SQL ve slovníku Všechny návody