Čtení prováděcích plánů v PostgreSQL
Každý, kdo trochu déle pracuje s SQL databázemi, časem narazí na tzv. "prováděcí plány dotazů". Zatímco SQL dotazy je možné navrhovat bez hlubší znalosti konkrétní databázového systému, při optimalizaci dotazů se pak bez hlubších znalostí neobejdeme. Prvním krokem při optimalizaci dotazů je právě analýza prováděcích plánů dotazů. Na základě plánu dotazu bychom měli dokázat posoudit nutnost vytvoření indexů, zvýšení statistik nebo přeformulování dotazu
Prakticky všechny SQL databáze mají shodný proces zpracování SQL příkazu (samozřejmě, s jistými odlišenostmi, které určují úspěšnost té či oné databáze v určitém segmentu užití). Do jisté míry je to dáno společným teoretickým základem a do jisté míry i sdílenou bází kódu ze začátku osmdesátých let (i když z tohoto původního kódu patrně v aktuálních verzích již mnoho nezůstalo). Bez následků nezůstal ani personální kolotoč mezi komerčními firmami v osmdesátých letech.
Proces zpracování SQL příkazu
Prvním krokem je lexikální a syntaktická analýza SQL příkazu. Poté se SQL příkaz transformuje na posloupnost relačních operací (logických operací) jako je filtr, projekce, join, semijoin, antijoin (viz relační algebra - http://en.wikipedia.org/wiki/Relational_algebra). Logické operace je třeba převést na tzv. fyzické operace executoru (např. scan -> seq scan | index scan, join -> nested loop, merge join, hash join). Vykonáním posloupnosti fyzických operací executoru získáme výsledek SQL příkazu. Posloupnost fyzických operací, která určuje výsledek, dále budu označovat jako prováděcí plán dotazu. Na prováděcí plán dotazu se můžeme dívat jako na jednosměrný kořenový strom, kde každý uzel stromu má přiřazenu jednu fyzickou operaci. Každý uzel stromu si udržuje seznam potomků - každá operace si udržuje seznam operací, které jí budou poskytovat vstupní data. Koncové vrcholy jsou obsazeny operacemi, které data získávají z fyz. zařízení (seq_scan, index_scan) nebo generují (čtení výstupu SRF funkce). Data tečou od koncových vrcholů ke kořenu (na vyžádání) - naopak proces zpracování dat se aktivuje od kořenu směrem k listům.
Z hlediska počtu vstupů můžeme rozdělit fyzické operace do čtyř tříd. První třídou jsou koncové operace, které získávají data jinak než voláním následující fyz. operace. Druhou, relativně obsáhlou třídou, jsou operace, které bychom mohli nazvat filtry - mají jeden vstup, který transformují (sort, groupby, distinct). Třetí třídou jsou operace, které mají dva vstupy - ukázkovým příkladem jsou implementace logické operace join (merge join, hash join). Zástupcem poslední třídy operací s více než dvěma vstupy je operace append (implementace klauzule union).
postgres=# create table a(v integer);
CREATE TABLE
postgres=# create table b(v integer);
CREATE TABLE
postgres=# insert into a values(10);
INSERT 0 1
postgres=# insert into b values(10);
INSERT 0 1
postgres=# ANALYZE ;
ANALYZE
postgres=# explain select *
from a; -- trivialni plan
QUERY PLAN
-----------------------------------------------------
Seq Scan on a (cost=0.00..34.00 rows=2400 width=4)
(1 row)
Všimněte si, že operace Seq Scan nemá žádnou podřízenou operaci - data získává voláním storage manageru (má na starosti plnění a správu obsahu datové cache).
postgres=# explain select *
from a
order by v;
QUERY PLAN
-------------------------------------------------------
Sort (cost=1.02..1.02 rows=1 width=4)
Sort Key: v
-> Seq Scan on a (cost=0.00..1.01 rows=1 width=4)
(3 rows)
postgres=# explain select *
from a
join b on a.v = b.v;
QUERY PLAN
-------------------------------------------------------
Nested Loop (cost=0.00..2.03 rows=1 width=8)
Join Filter: (a.v = b.v)
-> Seq Scan on a (cost=0.00..1.01 rows=1 width=4)
-> Seq Scan on b (cost=0.00..1.01 rows=1 width=4)
(4 rows)
Konečně ukázka operace append:
postgres=# explain select * from a
union all
select * from b
union all
select * from b;
QUERY PLAN
-------------------------------------------------------
Append (cost=0.00..3.06 rows=3 width=4)
-> Seq Scan on a (cost=0.00..1.01 rows=1 width=4)
-> Seq Scan on b (cost=0.00..1.01 rows=1 width=4)
-> Seq Scan on b (cost=0.00..1.01 rows=1 width=4)
(4 rows)
Ve výpisu prováděcího plánu (výstup příkazu EXPLAIN) vidíme atributy operací cost, rows a width. Pro začátečníky bývá nepochopitelný atribut cost (cena za operaci). Prozatím popis tohoto atributu odbydu s tím, že je důležité vědět, že nízká hodnota signalizuje rychlou operaci, vysoká hodnota (nad 5000) operaci pomalou. Pro pochopení není nezbytně nutné znát algoritmus určení ceny. Stačí vědět k čemu se cena operace používá a co signalizuje. Atribut rows obsahuje předpokládaný počet řádků výstupu a aribut width předpokládanou šířku výstupního řádku v bytech.
Executor v PostgreSQL
V PostgreSQL je executor poměrně jednoduchý kus kódu. PostgreSQL je napsáno v ANSI C, takže následující přirovnání mírně pokulhává, nicméně by se dalo říci, že každá fyzická operace je implementována jako třída obsahující ukazatele lefttree, righttree na instance operací poskytujících vstupní data a metody init, exec a end. Proces zpracování je inicializován opakovaným voláním metody exec kořenové první operace, a to dokud tato metoda nevrátí NULL (metoda exec vrací odkaz na jeden záznam, který se považuje za výstup operace). Následující kód představuje pseudoimplementaci operace limit.
limit(plan *lefttree)
{
// Uloží odkaz na následující operaci -
// tu z které se budou čerpat data.
self.lefttree = lefttree;
}
void limit.init()
{
self.n = 0;
self.lefttree->init(); // Limit má pouze jeden vsup
}
void limit.end()
{
self.lefttree->end();
}začne
// Pro n < limit vrať řádek získaný z levé větve plánu
void limit.exec()
{
if (self.n++ < self.limit)
return self.lefttree->exec();
else
return NULL;
}
provádění dotazu select * from a limit 10 v pseudokódu by mohlo vypadat takto:
/* Generuj prováděcí plán - vytvoření instance tříd fyzických operací */
limit_plan = new limit(seq_scan_plan);
top_plan = limit_plan;
/* Inicializace operací */
top_plan->init();
/* Čtení dat */
for (;;)
{
result = top_plan->exec();
if (result != NULL)
store_tuple(result);
else
break;
}
/* uvolnění zdrojů */
top_plan->end();
show_resultset();
Vlastní kód vypadá úplně jinak - vývojáři PostgreSQL píší "objektově" v ANSI C. Zájemce odkazuji na zdrojové kódy PostgreSQL - konkrétně soubor nodeLimit.c, kde jsou funkce implementující fyzickou operaci limit. Na ukázku přidávám prováděcí plán k výše uvedenému SQL příkazu:
postgres=# explain select * from a limit 10;
QUERY PLAN
-------------------------------------------------------
Limit (cost=0.00..1.01 rows=1 width=4)
-> Seq Scan on a (cost=0.00..1.01 rows=1 width=4)
(2 rows)
Úplný a relativně obsáhlý popis fyzických operací naleznete na stránce http://www.gtsm.com/oscon2003/ops.html (výraznější inovací seznamu operací si vynutí až PostgreSQL 8.4, kde došlo a patrně ještě dojde k přidání nových operací executoru.
Výběr optimálního prováděcího plánu v PostgreSQL
V případě netriviálních SQL příkazů dokážeme ke každému příkazu vytvořit více variant prováděcích plánů. Úkolem optimalizátoru je vybrat takovou variantu, která bude minimalizovat počet a objem diskových operací a nebude požadovat více než je přiděleno operační paměti. V zásadě nesmí dojít k použití swapu (v tu chvíli jde výkon všech aplikací, nejen databází, rapidně dolů).
Intermezzo - řazení a vysvětlení významu proměnné work_mem
Klasickou operací je řazení. V SQL se uplatňuje při zpracování klauzulí DISTINCT, JOIN, ORDER BY a GROUP BY příkazu SELECT a v příkazu CREATE INDEX. Jedná se o relativně častou operaci a časově náročnou operaci, a její optimalizaci se proto věnuje hodně pozornosti a úsilí. V PostgreSQL se ve většině případů použije algoritmus Quick sort. Jde o známý algoritmus, který je implementovaný ve standardní knihovně libc. Komplikace nastanou v okamžiku, kdy se řazená tabulka nevejde do vyhrazené operační paměti. Potom nelze operaci provést v jedné iteraci - tabulka se musí rozdělit do bloků, a řazení probíhá nad těmito bloky opakovaně. Situace se komplikuje ve víceuživatelských databázích, kde vedle sebe může docházet k souběžnému řazení několika tabulek vyplývajícím z paralelního (multiuser) zpracování SQL příkazů. V tomto případě se dostupná operační paměť musí rozdělit mezi více operací. Zákonitě je třeba řešit otázku, do kolika (jak velkých) bloků se mají dělit tabulky. Větší bloky snižují počet iterací (řazení je rychlejší), naopak ale vyžadují alokaci více paměti. Stále však musí zůstat dostatek paměti pro souběžně zpracovávané SQL příkazy, přičemž nesmí dojít k vyčerpání operační paměti (potom systém začne swapovat a z jistého pohledu dojde k zastavení systému - asi tak jako kdybyste přestoupili z dopravního letadla (cca 600km/h) do tramvaje (cca 40km/h) "cesta z Milána do Prahy cca 45min, cesta z Ruzyně na Hl. nádraží cca 45min".
Pokud bychom dokázali předpovědět tok SQL příkazů mohli bychom optimalizovat přidělení paměti - např. preferovat rychlé a nenáročné dotazy, časté dotazy. To ovšem nedokážeme, a proto PostgreSQL používá rovnostářský princip - každému procesu je přiděleno stejně paměti. Její velikost je určena hodnotou systémové proměnné work_mem. Ta je určena v konfiguraci - a nastavuje se jako podíl přidělené paměti pro PostgreSQL a maximálního počtu souběžně zpracovávaných dotazů (v PostgreSQL maximální počet přihlášených uživatelů). Díky tomu by Postgres nikdy neměl jít za přidělenou paměť, za předpokladu, že administrátor nepřestřelí nastavení work_mem. Proměnnou work_mem lze dočasně zvýšit. Typicky u pomalých nočních dotazů, kdy s databází pracuje omezený počet uživatelů.
Podívejme se na dotaz select * from a where v < 10 order by v;
postgres=# explain select * from a where v < 5 order by v;
QUERY PLAN
-------------------------------------------------------
Sort (cost=1.02..1.03 rows=1 width=4)
Sort Key: v
-> Seq Scan on a (cost=0.00..1.01 rows=1 width=4)
Filter: (v < 5)
(4 rows)
Je pochopitelné, že systém předřadí filtrování před paměťově a výpočetně náročnou operaci sort. Ke stejnému výsledku bych se dostal i tak, že bych napřed seřadil data a pak na tyto seřazená data aplikoval filtr. To je ovšem neefektivní - řadí se i ta data, která se později zahodí.
postgres=# explain select * from (select * from a order by v) s where v < 5;
QUERY PLAN
-------------------------------------------------------
Sort (cost=1.02..1.03 rows=1 width=4)
Sort Key: a.v
-> Seq Scan on a (cost=0.00..1.01 rows=1 width=4)
Filter: (v < 5)
(4 rows)
Všimněte si, že PostgreSQL ignoroval závorky a fakticky přepsal SQL příkaz na první variantu (viz http://www.root.cz/clanky/zakys-jmenem-flattening/). Flattening zablokuji klauzulí OFFSET:
postgres=# explain select * from (select * from a order by v offset 0) s where v < 5;
QUERY PLAN
-------------------------------------------------------------------
Subquery Scan s (cost=1.02..1.04 rows=1 width=4)
Filter: (s.v < 5)
-> Limit (cost=1.02..1.02 rows=1 width=4)
-> Sort (cost=1.02..1.02 rows=1 width=4)
Sort Key: a.v
-> Seq Scan on a (cost=0.00..1.01 rows=1 width=4)
(6 rows)
Přetlačování mezi mnou a optimalizátorem skončilo mým vítězstvím - výsledkem je ovšem neoptimální dotaz. Celková cena dotazu je o 0.01 vyšší, a to tabulka a obsahuje pouze jeden řádek. Rozdíl mezi výše uvedenými variantami závisí v účinnosti filtru. Vždy však bude první varianta lepší a tudíž ji můžeme označit jako optimální.
Optimalizaci ztížím vytvořením indexu ix:
postgres=# create index ix on a(v); CREATE INDEX
Nyní se systém musí rozhodnout mezi sekvenčním čtením s následným řazením nebo čtením z indexu. Krátký test ukáže, že systém index nepoužije:
postgres=# explain select * from a where v < 5 order by v;
QUERY PLAN
-------------------------------------------------------
Sort (cost=1.02..1.03 rows=1 width=4)
Sort Key: v
-> Seq Scan on a (cost=0.00..1.01 rows=1 width=4)
Filter: (v < 5)
(4 rows)
Zkusím penalizovat sekvenční čtení - a nyní již dojde k použití indexu:
postgres=# set enable_seqscan to off;
SET
postgres=# explain select * from a where v < 5 order by v;
QUERY PLAN
------------------------------------------------------------
Index Scan using ix on a (cost=0.00..8.27 rows=1 width=4)
Index Cond: (v < 5)
(2 rows)
Intermezzo - použití indexu versus sekvenční čtení datového souboru
Celková cena dotazu je ovšem vyšší - dá se tedy čekat, že díky indexu nebude SQL příkaz rychlejší. Proč? PostgreSQL nepodporuje klastrované indexy (http://www.blurtit.com/q280446.html), kde data jsou uložené spolu s klíčem. Index v PostgreSQL obsahuje pouze klíč a pozici v datovém souboru - tudíž každý nalezený záznam pomocí indexu způsobí ještě čtení vlastního datového souboru v režimu náhodného přístupu. Kromě toho se ještě načítá minimálně jedna datová stránka indexu. PostgreSQL (a až na Firebird 1.5 samozřejmě i ostatní db systémy) tuto režii zahrnuje do výběru plánu. Vychází se zhruba z předpokladu, že indexy má smysl použít pro tabulky větší než 1000 (řazení) - 10000 (výběr) řádků a v případě, že poměr vybraných řádků k celé tabulce je mezi 20-30%. Faktorů ovlivňující výběr a použití indexů je víc - záleží na konfiguraci - a konfiguraci by měla vycházet z vlastností hardware (rychlost čtení z disků, poměr rychlosti sekvenčního čtení a čtení s náhodným přístupem a velikosti cache).
Z variant prováděcích plánů optimalizátor vybírá variantu s nejnižší cenou. K ničemu jinému, než k porovnání dvou variant, se tato hodnota nedá použít. Jedná se o tzv. "cost based" optimalizaci. Předchůdcem této metody je tzv. "rule based" optimalizace. Dnes se tato metoda používá minimálně - cost based díky tomu, že vycházejí z analýzy (statistik) skutečných dat, produkují kvalitnější plány. Při změně statistik dochází i ke změně prováděcích plánu - db systém se adaptuje na obsah a objem dat - což je vlastnost, která "rule based" systémům chybí. Na druhou stranu, tato dynamika může v určitých specifických případech způsobit problémy. Nevýhodou "rule based" optimalizace je nutnost respektování (a tudíž i znalost) určitých pokynů ohledně zápisu SQL příkazu - např. do klauzule from je třeba umisťovat tabulky v pořadí od největší po nejmenší, na začátek klauzule where umisťovat predikáty s největší selektivitou, predikáty s nízkou selektivitou umisťovat na konec klauzule where. Tato alchymie je v případě "cost based" optimalizace zbytečná - optimalizátor se rozhoduje na základě statistik, nikoliv na základě zápisu SQL příkazu. Pokud sedí statistiky, tak se optimalizátor dokáže trefit překvapivě často a překvapivě dobře. K ověření shody předpokladů a reality slouží, v PostgreSQL, příkaz EXPLAIN ANALYZE.
pagila=# \i ~/Desktop/pagila-0.10.1/pagila-schema.sql
pagila=# \i ~/Desktop/pagila-0.10.1/pagila-data.sql
pagila=# analyze;
ANALYZE
pagila=# explain analyze select * from film f where film_id in (select film_id from film_actor);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=117.26..195.78 rows=977 width=390) (actual time=21.067..26.570 rows=997 loops=1)
Hash Cond: (f.film_id = film_actor.film_id)
-> Seq Scan on film f (cost=0.00..65.00 rows=1000 width=390) (actual time=0.016..1.699 rows=1000 loops=1)
-> Hash (cost=105.05..105.05 rows=977 width=2) (actual time=21.029..21.029 rows=997 loops=1)
-> HashAggregate (cost=95.28..105.05 rows=977 width=2) (actual time=17.598..19.298 rows=997 loops=1)
-> Seq Scan on film_actor (cost=0.00..81.62 rows=5462 width=2) (actual time=0.012..8.019 rows=5462 loops=1)
Total runtime: 28.020 ms
Zde se je odhad 977 řádků vůči skutečnému výsledku 997 řádků. Dá se tedy očekávat, že vybraný plán bude skutečně optimální. O tom, že plán nemusí přímo vycházet z SQL vás přesvědčí následující výpis plánu (místo poddotazu s "in" se použil korelovaný poddotaz s "exists").
pagila=# explain analyze select * from film f where exists (select film_id from film_actor where f.film_id = film_id);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=117.26..195.78 rows=977 width=390) (actual time=45.449..50.894 rows=997 loops=1)
Hash Cond: (f.film_id = film_actor.film_id)Výpočet ceny
-> Seq Scan on film f (cost=0.00..65.00 rows=1000 width=390) (actual time=0.023..1.681 rows=1000 loops=1)
-> Hash (cost=105.05..105.05 rows=977 width=2) (actual time=45.386..45.386 rows=997 loops=1)
-> HashAggregate (cost=95.28..105.05 rows=977 width=2) (actual time=41.876..43.418 rows=997 loops=1)
-> Seq Scan on film_actor (cost=0.00..81.62 rows=5462 width=2) (actual time=0.023..26.981 rows=5462 loops=1)
Total runtime: 52.516 ms
(7 rows)
Prováděcí plán je identický. Stačí ovšem aby chyběla podpora určité operace (v příkladu antijoin pro "in") a plán je zaručeně problematický:
pagila=# explain analyze select * from film f where film_id not in (select film_id from film_actor);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Seq Scan on film f (cost=95.28..162.78 rows=500 width=390) (actual time=33.902..34.584 rows=3 loops=1)
Filter: (NOT (hashed subplan))
SubPlan
-> Seq Scan on film_actor (cost=0.00..81.62 rows=5462 width=2) (actual time=0.027..20.144 rows=5462 loops=1)
Total runtime: 34.774 ms
(5 rows)
Odhad je úplně mimo - pak je nutné přeformulovat SQL příkaz:
pagila=# explain analyze select * from film f where not exists (select film_id from film_actor where f.film_id = film_id);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Hash Anti Join (cost=149.90..242.62 rows=23 width=390) (actual time=25.497..28.016 rows=3 loops=1)
Hash Cond: (f.film_id = film_actor.film_id)
-> Seq Scan on film f (cost=0.00..65.00 rows=1000 width=390) (actual time=0.022..1.707 rows=1000 loops=1)
-> Hash (cost=81.62..81.62 rows=5462 width=2) (actual time=24.558..24.558 rows=5462 loops=1)
-> Seq Scan on film_actor (cost=0.00..81.62 rows=5462 width=2) (actual time=0.026..12.073 rows=5462 loops=1)
Total runtime: 28.291 ms
(6 rows)
nebo
pagila=# explain select f.* from film f left join film_actor fa on f.film_id = fa.film_id where fa.film_id is null;
QUERY PLAN
-----------------------------------------------------------------------------
Hash Anti Join (cost=149.90..242.62 rows=23 width=390)
Hash Cond: (f.film_id = fa.film_id)
-> Seq Scan on film f (cost=0.00..65.00 rows=1000 width=390)
-> Hash (cost=81.62..81.62 rows=5462 width=2)
-> Seq Scan on film_actor fa (cost=0.00..81.62 rows=5462 width=2)
(5 rows)
V prováděcím plánu je třeba ověřit zda odhad počtu řádků a reálný počet řádků alespoň řádově souhlasí. Proč je to tak důležité? Protože chybný odhad signalizuje problém statistik. Statistiky jsou vstupem výpočtu ceny - tudíž pokud jsou statistiky chybné, vypočítá se neadekvátní cena. Plán, který je postavený na takovýchto cenách, patrně nebude optimální - i když jej systém za takový bude považovat. Neoptimální plán je příčinou pomalých dotazů a ty zas příčinou pomalých aplikací. Je naprosto zásadní, aby v denních dávkách (kdy s aplikacemi uživatelé pracují interaktivně) databázový systém zpracovával pouze rychlé dotazy.
Výpočet ceny
Asi nepřekvapí, že každá fyzická operace má vlastní vzorec pro výpočet ceny. U ceny se rozlišuje mezi cenou za získání prvního záznamu a celkovou cenou. Cena za první záznam (startup cost) se používá primárně pro optimalizaci plánů kurzorů. Naopak při hledání optimálního plánu dotazu se uplatní celková cena (total cost). V ceně se musí projevit počet nutných I/O operací a také chceme, aby cena přihlédla k rozdílu v rychlostech mezi sekvenčním čtením a náhodným čtením. Výchozí nastavení počítá s následujícími poměry nákladů přístupů ke zdrojům:
| sekvenční čtení datové stránky | 1.0 | seq_page_cost | |
| náhodné čtení datové stránky | 4.0 | 4x pomalejší | random_page_cost |
| cena za procesor za řádek | 0.01 | 100x rychlejší | cpu_tuple_cost |
| cena za jedno volání funkce | 0.0025 | 400x rychlejší | cpu_operator_cost |
| cena za index na řádek | 0.005 | 200x rychlejší | cpu_index_tuple_cost |
Jednotky zde uvedených cen jsou datová stránka a řádek (I/O operace probíhají blokově). Jelikož má cena za diskovou operaci cenu 1 a ostatní zdroje jsou "téměř" zanedbatelné, můžeme výslednou cenu do jisté míry chápat jako celkový počet diskových operací. Pozor - algoritmus nebere v úvahu diskovou cache. Poměrně dlouho se řeší, zda o něco nesnížit jednotkovou cenu za náhodné čtení datové stránky a jednotkové ceny procesoru.
Příklad určení ceny nejjednodušší fyzické operace - sekvenčního čtení. Vzorec je následující:
seq_scan_cost = relpages * seq_page_cost + reltuples * cpu_tuple_cost
Jako příklad si vezmu přečtení celé tabulky film z demo databáze pagila.
pagila=# explain select * from film;
QUERY PLAN
----------------------------------------------------------
Seq Scan on film (cost=0.00..65.00 rows=1000 width=390)
(1 row)
pagila=# SELECT relpages * 1 + reltuples * 0.01 from pg_class where relname = 'film';
?column?
----------
65
(1 row)
pagila=# SELECT relpages* 1,reltuples * 0.01 from pg_class where relname = 'film';
?column? | ?column?
----------+----------
55 | 10
(1 row)
Cena je 65 a z toho 55 jednotek připadá na diskové operace, zbytek na procesor. Sloupec relpages tabulky pg_class obsahuje počet datových stránek, reltuples pak počet záznamů ke každé tabulce. Tyto hodnoty se aktualizují příkazem ANALYZE (tzv. autovacuum, vnitřní proces PostgreSQL, který monitoruje změny dat, může, kromě spouštění příkazu VACUUM, také volat příkaz ANALYZE. V starších verzích (před 8.3) je nutné autovacuum explicitně povolit v konfiguraci.).
Přidáním podmínky se zvýší cena za procesor (vyhodnocení predikátu má svou režii):
pagila=# explain select * from film where film_id > 10;
QUERY PLAN
---------------------------------------------------------
Seq Scan on film (cost=0.00..67.50 rows=991 width=390)
Filter: (film_id > 10)
(2 rows)
pagila=# SELECT relpages * 1 + reltuples * (0.01 + 0.0025) from pg_class where relname = 'film';
?column?
----------
67.5
(1 row)
Další příklad operace sort (v případě, že se celá operace provede v jedné iteraci):
* startup_cost = (inherited) + 2.0 * cpu_operator_cost * tuples * (ln(x) / 0.693147180559945) * run_cost = cpu_operator_cost * tuples;
V podobném duchu se určuje cena všech operací (vzorce jsou složitější, do výpočtu vstupují selektivity indexů, účinnosti predikátů) Tento model je okatě teoretický a silně zjednodušující a, kupodivu, většinou překvapivě funkční.
Použití prováděcího plánu
Nyní by již mělo být jasné, co prováděcí plán představuje a co z něj lze vyčíst. Zbývá poslední otázka - jak informace z prováděcího plánu dokážeme využít?
- Identifikace nedostatečných nebo neaktuálních statistik. Tato situace se rozpozná jednoduše - rozchází se odhad počtu řádků se skutečným počtem řádků (EXPLAIN ANALYZE). Pokud nepomůže příkaz ANALYZE, kterým se aktualizují statistiky, je potřeba si pohrát s počtem tříd ve statistikách v dotčených sloupcích (viz ALTER TABLE ALTER COLUMN SET STATISTICS).
- Identifikace nedostatečně implementovaných funkcí v PostgreSQL. Výsledek některých operací nebo funkcí nedokáže PostgreSQL dostatečně věrně predikovat.
pagila=# explain analyze select * from film f where film_id not in (select film_id from film_actor);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Seq Scan on film f (cost=95.28..162.78 rows=500 width=390) (actual time=33.902..34.584 rows=3 loops=1)
Filter: (NOT (hashed subplan))
SubPlan
-> Seq Scan on film_actor (cost=0.00..81.62 rows=5462 width=2) (actual time=0.027..20.144 rows=5462 loops=1)
Total runtime: 34.774 ms
(5 rows)
Všimněte si, že odhad je 500 řádků, to je evidentně magická konstanta (tento dotaz se musí přepsat). Dalším takovým místem je fulltext (kde zatím statistiky vůbec nejsou implementovány):
postgres=# explain analyze select * from film where fulltext @@ to_tsquery('english','car');
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Index Scan using film_fulltext_idx on film (cost=0.00..8.27 rows=1 width=390) (actual time=0.111..1.626 rows=78 loops=1)
Index Cond: (fulltext @@ '''car'''::tsquery)
Total runtime: 2.014 ms
(3 rows)
postgres=# explain analyze select * from film where fulltext @@ to_tsquery('english','man');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using film_fulltext_idx on film (cost=0.00..8.27 rows=1 width=390) (actual time=0.062..1.640 rows=118 loops=1)
Index Cond: (fulltext @@ '''man'''::tsquery)
Total runtime: 2.058 ms
(3 rows)
Zde lze použít trik založený na použití immutable sql funkci. Zkuste pochopit, jak tento trik funguje.
create or replace function search_fxtxt(text)
returns int[] as $$
select array(select film_id
from film
where fulltext @@ to_tsquery('english',$1))
$$ language sql immutable;
postgres=# explain select * from film where film_id = any(search_fxtxt('rocket'));
QUERY PLAN
------------------------------------------------------------------------
Bitmap Heap Scan on film (cost=8.52..15.40 rows=2 width=390)
Recheck Cond: (film_id = ANY ('{738,470}'::integer[]))
-> Bitmap Index Scan on film_pkey (cost=0.00..8.52 rows=2 width=0)
Index Cond: (film_id = ANY ('{738,470}'::integer[]))
(4 rows)
- Identifikace chybějících indexů. Zpracování SELECTu jakékoliv větší tabulky by mělo být vázáno na použití indexu. Sekvenční zpracování velké tabulky způsobí pokrytí cache datovými stránkami tabulky. Následující dotazy musí opět naplnit cache. Indexy je třeba přidávat s rozumem. Index sice zrychlí čtení tabulky, zároveň ale zpomalí aktualizaci obsahu tabulky. Je nutné se rozhodnout, co je v daném případě menší zlo.
postgres=# explain select * from film where title like 'IS%';
QUERY PLAN
-------------------------------------------------------
Seq Scan on film (cost=0.00..67.50 rows=1 width=390)
Filter: ((title)::text ~~ 'IS%'::text)
(2 rows)
postgres=# create index film_title on film(title varchar_pattern_ops);
CREATE INDEX
postgres=# explain select * from film where title like 'IS%';
QUERY PLAN
------------------------------------------------------------------------------------
Index Scan using film_title on film (cost=0.00..8.27 rows=1 width=390)
Index Cond: (((title)::text ~>=~ 'IS'::text) AND ((title)::text ~<~ 'IT'::text))
Filter: ((title)::text ~~ 'IS%'::text)
(3 rows)
Pro optimalizaci SQL příkazů platí několik pravidel
- Důkladnou kontrolu a optimalizaci vyžadují pouze pomalé nebo časté dotazy.
- Absolutně bezcenná je optimalizace na neadekvátní testovací množině dat.
- Kromě optimalizace na testovacích datech je nutné optimalizovat i na skutečných datech. Jelikož se data v db mění, je nutné opakovaně provádět optimalizaci dotazů (minimálně kontrolu pomalých dotazů).
- Optimalizace dotazů je nutná vždy. Neoptimalizovaný dotaz může být o několik řádů pomalejší než optimalizovaný dotaz. To se samozřejmě projeví na výkonu aplikace. Navíc pomalý dotaz nebrzdí pouze jednoho uživatele, ale prakticky všechny uživatele, všechny aplikace, které používají databázi.
Odkazy
- Referenční databáze Pagila - http://pgfoundry.org/projects/dbsamples/
- Prezentace Neil Conwaye - http://neilconway.org/talks/executor.pdf
- Datakon 2006 s příspěvkem věnovaným optimalizaci SQL příkazu - http://www.datakon.cz/2006/datakon2006_sbornik.pdf
Tučný text