Dotazovací plán (Query plan)

Příklady z praxe

1. 01

   EXPLAIN ANALYZE odhalí chybějící index

   Výpis objednávek zákazníka trvá na produkci přes sekundu, i když tabulka má jen pár milionů řádků. Plán ukáže sekvenční průchod celou tabulkou a filtr, který zahodí 99,9 % řádků. Po vytvoření indexu nad sloupcem customer_id plánovač zvolí Index Scan a dotaz spadne na jednotky milisekund.

   EXPLAIN ANALYZE
   SELECT id, total, created_at
   FROM orders
   WHERE customer_id = 4211
   ORDER BY created_at DESC
   LIMIT 20;
   
   -- před indexem:
   -- Seq Scan on orders  (cost=0.00..48213.00 rows=1 width=24)
   --   (actual time=0.4..980.2 rows=37 loops=1)
   --   Filter: (customer_id = 4211)
   --   Rows Removed by Filter: 2999963

2. 02

   Špatný odhad kvůli funkci nad sloupcem

   Report filtruje faktury podle roku zápisem YEAR(issued_at) = 2024. Plánovač na výraz nemá statistiky ani index, takže odhadne pevný podíl řádků a zvolí nested loop nad velkou tabulkou. Přepsání podmínky na rozsah dat umožní použít index a odhad se přiblíží skutečnosti.

   -- plánovač neumí použít index nad issued_at
   WHERE YEAR(issued_at) = 2024
   
   -- rozsahová podoba, index je použitelný
   WHERE issued_at >= '2024-01-01'
     AND issued_at <  '2025-01-01'

Časté omyly

MýtusKdyž je na sloupci index, databáze ho vždycky použije.

Ve skutečnostiPlánovač index použije jen tehdy, když mu podle odhadu vyjde levněji než sekvenční průchod. Při dotazu vracejícím velkou část tabulky je čtení celé tabulky rychlejší, protože se vyhne náhodným skokům do datových stránek.

MýtusEXPLAIN mi ukáže, jak dlouho dotaz poběží.

Ve skutečnostiSamotný EXPLAIN vrací jen odhady optimalizátoru v abstraktních jednotkách ceny, nikoli čas. Skutečné časy a počty řádků dá až EXPLAIN ANALYZE, který dotaz opravdu vykoná (u zápisů proto raději v transakci s rollbackem).

MýtusKdyž dotaz zpomalil, musel se změnit kód aplikace.

Ve skutečnostiPlán se mění i bez zásahu do kódu: po přepočtu statistik, růstu dat, změně parametrů dotazu nebo přidání indexu jinde. Stejný SQL text tak může dostat jiný plán a řádově jiný čas.

Časté dotazy

Jak se čte výstup EXPLAIN v PostgreSQL?

Výstup EXPLAIN se čte odspodu nahoru a od nejhlubšího odsazení k nejmenšímu. Nejvíce odsazené uzly se vykonají první a předávají řádky nadřazenému uzlu. U každého uzlu jsou zajímavá čísla cost (odhad ceny startu a celku), rows (odhadovaný počet řádků) a width. S přepínačem ANALYZE přibude actual time a actual rows, takže lze porovnat odhad se skutečností. Ladění začíná u uzlu, který spotřebuje nejvíc času, a u uzlu s největším nepoměrem mezi odhadovanými a skutečnými řádky.

Proč databáze u stejného dotazu občas zvolí jiný dotazovací plán?

Dotazovací plán vzniká znovu na základě aktuálních statistik, takže se mění s daty. Po hromadném importu, po automatickém přepočtu statistik, po přidání či zrušení indexu nebo při jiné hodnotě parametru může optimalizátor vyhodnotit jako nejlevnější jinou variantu. Roli hraje i konfigurace paměti a nastavení nákladových konstant. Změna plánu je běžná a většinou žádoucí, problém nastává, když se odhady rozejdou se skutečností a nová varianta je výrazně pomalejší.

Co dělat, když se odhad počtu řádků v plánu výrazně liší od skutečnosti?

Prvním krokem je přepočet statistik (ANALYZE, respektive ekvivalentní příkaz dané databáze) a kontrola, zda autovacuum nebo obdobný proces vůbec běží. Dále pomáhá odstranit funkce a přetypování nad filtrovanými sloupci, protože nad výrazem databáze statistiky nemá. U korelovaných sloupců lze v PostgreSQL vytvořit rozšířené statistiky. Pokud problém způsobuje uložený plán pro jinou hodnotu parametru, řeší se přeplánováním dotazu nebo rozdělením na varianty podle typického rozsahu hodnot.

Lze dotazovací plán vynutit ručně?

Vynucení plánu je možné, ale považuje se za poslední možnost. Oracle a SQL Server nabízejí hinty přímo v dotazu a mechanismy pro uložení plánu, MySQL má indexové hinty, PostgreSQL hinty záměrně nemá a nabízí jen globální přepínače typu enable_nestloop pro diagnostiku. Vynucený plán zamrzne rozhodnutí, které platilo pro tehdejší objem dat, a po jejich růstu se často stane brzdou. Lepší je nejdřív opravit příčinu: statistiky, zápis podmínek nebo chybějící index.

Zdroje

1. Using EXPLAIN(otevře se v novém okně)PostgreSQL Global Development Group
2. Query Planning(otevře se v novém okně)PostgreSQL Global Development Group
3. Query Planning(otevře se v novém okně)SQLite
4. Explain Results(otevře se v novém okně)MongoDB
5. Query processing architecture guide(otevře se v novém okně)Microsoft Learn

Související pojmy

• Databázová transakceDatabázová transakce je řízená skupina databázových operací, která se má provést jako jeden logický celek. Pokud se všechny kroky podaří, změny se potvrdí commitem; při chybě se vrátí rollbackem. Transakce chrání konzistenci dat hlavně při souběžných zápisech, výpadcích a více navazujících změnách.
• IndexIndex je pomocná datová struktura, která databázi umožní najít řádky podle hodnoty sloupce bez procházení celé tabulky. Zrychluje čtení, zpomaluje zápis a zabírá místo, takže každý index je vědomý kompromis.
• JOINJOIN je operace v SQL, která skládá řádky ze dvou nebo více tabulek podle zadané podmínky, typicky podle shody primárního a cizího klíče. Umožňuje číst normalizovaná data jako jeden výsledek, ale může měnit počet řádků i výkon dotazu.
• SQLSQL je deklarativní jazyk pro práci s relačními databázemi, ve kterém se popisuje, jaká data chce uživatel vybrat, změnit nebo definovat, nikoli přesný postup jejich získání. Používá se pro dotazy, vkládání, aktualizace, mazání, tvorbu tabulek, oprávnění i transakční zpracování v mnoha databázových systémech.
• A/B testováníA/B testování je řízený experiment, který porovnává dvě varianty stránky, funkce, ceny nebo sdělení na podobných skupinách uživatelů. Měří předem zvolenou metriku, například konverzi nebo retenci, a pomáhá rozhodnout, zda změna skutečně zlepšuje výsledek, nebo zda pozorovaný rozdíl mohl vzniknout náhodou.
• ACIDACID je sada čtyř vlastností databázové transakce: atomicity, consistency, isolation a durability. Zaručuje, že skupina zápisů proběhne jako jeden celek, neporuší definovaná pravidla databáze, nebude se nebezpečně míchat s jinými transakcemi a po potvrzení přežije pád systému, například při převodu peněz.
• Adaptive IconAdaptivní ikona je formát ikony aplikace na Androidu, který se místo jedné hotové bitmapy skládá z oddělených vrstev: pozadí a popředí. Systém výsledný tvar sám ořízne podle masky výrobce zařízení a může vrstvy animovat nebo přebarvit. Vývojář tak dodá jeden zdroj a launcher z něj vytvoří kruh, čtverec i squircle.
• Adresa v pamětiAdresa v paměti je číslo, kterým procesor jednoznačně označuje konkrétní buňku operační paměti, obvykle jeden bajt. Program pomocí adresy říká, odkud má data načíst nebo kam je zapsat. Adresy tvoří souvislý číselný rozsah, jehož velikost určuje šířka adresní sběrnice a architektura, typicky 32 nebo 64 bitů.