Cílem je minimalizovat počet IO operací, sekundárně počet CPU operací. Optimalizátor vybírá mezi možnými způsoby realizace dotazu. Např. JOIN může být realizován pomocí hash tabulek – hashjoin, pomocí iterace a indexu – nested loop s index scanem, pomocí iterace nad seřazenými daty – mergejoin. Každý způsob má jiné požadavky, jinou režii a hodí se pro určitý rozsah počtu vrácených řádek. Výsledek samozřejmě dopředu neznáme, ale můžeme jej odhadnout – a k tomu potřebujeme odhady a statistiky.
Prováděcí plán dotazu (způsob, jak databáze zpracovává SQL příkaz) si můžeme nechat zobrazit příkazem EXPLAIN (bez vykonání příkazu) nebo příkazem EXPLAIN ANALYZE (s vykonáním dotazu, vidíme vedle sebe odhady i skutečnost). O prováděcích plánech, executoru a příkazu EXPLAIN se dočtete více v článku Čtení prováděcích plánů v PostgreSQL.
-- očekávaný výsledek několika málo řádků
world=# EXPLAIN SELECT * FROM city ci JOIN country co ON ci.countrycode = co.code WHERE co.code = 'CZE';
QUERY PLAN
-------------------------------------------------------------------------------------------------
Nested Loop (cost=0.28..23.02 rows=4 width=144)
-> Seq Scan on country co (cost=0.00..7.99 rows=1 width=113)
Filter: (code = 'CZE'::bpchar)
-> Index Scan using city_countrycode_idx on city ci (cost=0.28..15.00 rows=4 width=31)
Index Cond: (countrycode = 'CZE'::bpchar)
Planning time: 0.459 ms
(8 rows)
-- očekávaný větší výsledek s dostatkem paměti pro hash tabulku
world=# EXPLAIN SELECT * FROM city ci JOIN country co ON ci.countrycode = co.code;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Hash Join (cost=10.38..139.25 rows=4079 width=144)
Hash Cond: (ci.countrycode = co.code)
-> Seq Scan on city ci (cost=0.00..72.79 rows=4079 width=31)
-> Hash (cost=7.39..7.39 rows=239 width=113)
-> Seq Scan on country co (cost=0.00..7.39 rows=239 width=113)
Planning time: 1.127 ms
(8 rows)
-- simulace nedostatečné RAM penalizací hashjoinu - mergejoin pro větší výsledek
world=# SET enable_hashjoin TO off;
SET
world=# EXPLAIN SELECT * FROM city ci JOIN country co ON ci.countrycode = co.code;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Merge Join (cost=0.43..281.72 rows=4079 width=144)
Merge Cond: (co.code = ci.countrycode)
-> Index Scan using country_code_idx on country co (cost=0.14..26.17 rows=239 width=113)
-> Index Scan using city_countrycode_idx1 on city ci (cost=0.28..203.97 rows=4079 width=31)
Planning time: 0.640 ms
(6 rows)
Vůči tomu, co se reálně v databázi děje, je tento model opravdu hodně zjednodušený. Nebere v potaz, jestli jsou data v RAMce, nebere se v potaz skutečné uspořádání dat v datovém souboru, zanedbávají se korelace, pracuje se s průměry a rovnoměrným rozdělením. Relativně dlouho jsem nechápal, jak to vůbec může fungovat – než jsem si uvědomil, že optimální plán dotazu není něco jako svatý grál, který se vyjímá nade všemi, ale je to většinou jeden z plánů dotazu (způsobu jak fyzicky realizovat dotaz), který je dostatečně dobrý. Optimální plán může být (z definice) pouze jeden, nicméně dobrých plánů (s uspokojivou rychlostí dotazu) může být více. Často jsou rozdíly v rychlostech různých plánů relativně zanedbatelné v porovnání s dalšími faktory, jako je aktuální obsah cache, aktuální parametry IO, aktuální počet zpracovávaných dotazů, aktuální rychlost sítě a aktuální rychlost klienta. Rychlost dotazů pod 50 ms je v produkci značně nestabilní a jen málokdy má smysl se takovým dotazům věnovat podrobněji. Také nás takhle rychlé dotazy málokdy pálí (a pokud nás pálí, tak má smysl přemýšlet o cache, nebo jestli něco neděláme špatně – relativně časté jsou zbytečné SQL dotazy, které je potřeba eliminovat a nikoliv zrychlovat).
Cílem optimalizátoru není ani tak najít optimální (nejlepší) plán (to ani není možné vzhledem k dynamickému prostředí databáze), ale eliminovat špatné (nevýhodné, pomalé) plány. Což si myslím, že se docela daří – když si uvědomím, kde všude se používají relativně úspěšně relační SQL databáze a jaké jsou běžné znalosti této technologie u vývojářů. Běžně se to daří – tipoval bych, že u 90 % případů. Ne u všech dotazů, kde optimalizace nezafungovala, si všimneme, že je dotaz pomalý – máme relativně rychlá CPU, máme relativně hodně paměti. Někdy ovšem ani hrubá síla nepomůže, a my si všimneme, že nějaká činnost (nějaký dotaz) trvá nepříjemně dlouho a snižuje komfort uživatele. Důvodem může být dysfunkce optimalizátoru, ale také zatížení serveru.
Pokud máme databázi větší než je RAM – a data nám nezůstanou v cache, tak je klíčovým parametrem rychlost čtení z disku (sekvenční, s náhodným přístupem). Ta silně kolísá – snižuje ji počet random IO (neplatí pro SSD), snižuje ji zápis (checkpointy, transakční log). U nás na relativně slušném hw rychlost čtení kolísá od 20MB/s do 200MB/s. Což znamená, že jen přečtení 1GB tabulky může trvat 5 až 50 sec. Potom není překvapením, že počítání některých dotazů může trvat 5 minut na nezatíženém serveru a 30 minut na zatíženém – na databázi o velikosti cca 100 GB. S tímto tématem souvisí i častá chyba benchmarkování – testuje se na jiném hw, v jiné konfiguraci, při jiném zatížení než má produkce. Výsledek takových testů je diskutabilní. Jednak může jen málo vyjadřovat realitu, za druhé nemusí zabránit nepříjemným překvapením na produkci. Setkávám se s tím, že testovací prostředí (včetně prostředí pro testovací databáze) je buďto stejné jako produkční, ale bez zátěže, nebo výrazně výkonnostně slabší než produkční (virtualizace, horší disky). V takovém prostředí jsou jakékoliv benchmarky potenciálně zatížené velkou chybou a je riskantní se jimi řídit. Nerad bych ale, aby to vyznělo tak, že doporučuji vývoj a testování na produkčním prostředí.
Samozřejmě, dotaz může být pomalý i z toho důvodu, že se optimalizace dotazu nepovede. Důvodů může být vícero:
- sémantika dotazu neumožňující optimalizaci
- nelze použít antijoin s predikátem
NOT IN - použití klauzule
OFFSET - klauzule
WITH(v Postgresu záměrně – optimization barier)
- nelze použít antijoin s predikátem
- nepodporovaná optimalizace (průběžně se zlepšuje)
- např. starší verze Postgresu nepodporovaly antijoin, join při použití korelovaného poddotazu
- stále se nepodporují všechny možné formy zjednodušení dotazu
- postgres vždy provede napřed slučování tabulek (join) a následně agregaci, a to i v případech, že je možné prohodit pořadí a je to výhodné.
- sw chyba – optimalizaci by PostgreSQL měla podporovat (a třeba i ji podporovala), ale aktuálně ji z důvodu chyby neprovede (regrese).
- závažná chyba v odhadu výsledku dotazu (filtrování a počet unikátních hodnot)
- chyba v konfiguraci Postgresu
work_mem,effective_cache_size(výchozí hodnoty jsou malé)random_page_cost,seq_page_cost,cpu_operator_cost,cpu_tuple_cost(pozor na přeučenost modelu)join_collapse_limit,from_collapse_limit(nízké hodnoty předčasně omezují optimalizaci, vysoké hodnoty zvyšují náročnost plánování složitějších dotazů)
- nízká nebo naopak vysoká podrobnost statistik (slabý nebo přeučený model) viz
ALTER TABLE ALTER COLUMN SET STATISTICS
V následujícím textu se budu věnovat hlavně chybám odhadů, proč vznikají a jaké mohou mít důsledky. Na pomoc si vezmu volně dostupnou databázi hlavních měst world.
Uložení statistik
Základem pro odhady jsou persistentní statistiky nad každým sloupcem každé tabulky. Statistiky se aktualizují příkazem ANALYZE nebo VACUUM ANALYZE. V novějších verzích PostgreSQL se o jejich aktuálnost stará proces autovacuum. Jak staré statistiky jsou k té či oné tabulce, zjistíme z tabulky pg_stat_user_tables:
postgres=# \x Expanded display is on. postgres=# SELECT * FROM pg_stat_user_tables WHERE relname = 'city'; -[ RECORD 1 ]-------+------------------------------ relid | 16446 schemaname | public relname | city seq_scan | 8 seq_tup_read | 28563 idx_scan | 0 idx_tup_fetch | 0 n_tup_ins | 4079 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 4079 n_dead_tup | 0 n_mod_since_analyze | 0 last_vacuum | last_autovacuum | last_analyze | 2014-07-30 11:13:17.025515+02 last_autoanalyze | vacuum_count | 0 autovacuum_count | 0 analyze_count | 1 autoanalyze_count | 0
Statistiku ke každému sloupci si můžeme prohlédnout v pohledu pg_stats:
postgres=# ALTER TABLE city ALTER COLUMN name SET STATISTICS 10;
ALTER TABLE
postgres=# SELECT * FROM pg_stats WHERE tablename = 'city' AND attname = 'name';
-[ RECORD 1 ]----------+--------------------------------------------------------------------------------------------------------------------------
schemaname | public
tablename | city
attname | name
inherited | f
null_frac | 0
avg_width | 9
n_distinct | -0.980878
most_common_vals | {Cambridge,Córdoba,Hamilton,"La Paz",León,Richmond,"San Fernando","San Jose","San Miguel",Springfield}
most_common_freqs | {0.000735474,0.000735474,0.000735474,0.000735474,0.000735474,0.000735474,0.000735474,0.000735474,0.000735474,0.000735474}
histogram_bounds | {Aachen,Benoni,Constanta,Göttingen,"Johor Baharu","Long Beach",Navadwip,Poznan,Sendai,"Torbat-e Heydariyeh",Zytomyr}
correlation | 0.0283845
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
postgres=# SELECT * FROM pg_stats WHERE tablename = 'city' AND attname = 'id';
-[ RECORD 1 ]----------+----------------------------------------------------
schemaname | public
tablename | city
attname | id
inherited | f
null_frac | 0
avg_width | 4
n_distinct | -1
most_common_vals |
most_common_freqs |
histogram_bounds | {1,408,816,1224,1632,2040,2447,2855,3263,3671,4079}
correlation | 1
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
Pro zobrazení jsem snížil počet sledovaných tříd (příkazem ALTER TABLE ALTER COLUMN SET STATISTICS na 10 (tj pro skalární hodnoty počet položek v atributech: most_common_vals, most_common_freqs, histogram_bounds). Jinak výchozí hodnotu (v PostgreSQL 9.x) 100 považuji za docela rozumnou a nerad ji zvyšuji. Zvýšení podrobnosti statistik má pozitivní vliv na přesnost odhadu, ale má dva negativní efekty:
- mírně zpomaluje optimalizaci, která má navíc větší požadavky na paměť (je rozdíl, jestli kopírujete pole o 10 prvcích nebo 1000, případně 10 000 prvcích).
- počet vzorků, které používá příkaz
ANALYZEje úměrný počtu tříd – větší počet vzorků znamená pomalejšíANALYZE(to bych ale asi neřešil u tabulek pod 10GB)
Výpočet odhadu
Hodnoty z atributu histogram_bounds se používají pro odhady počtu hodnot menších nebo větších než zadaná konstanta – zjištěním počtu tříd nalevo nebo napravo ku celkovému počtu tříd krát počet řádek ( reltuples z tabulky pg_class) dostaneme odhad počtu řádek.
Odhad na rovnost je založený na seznamu pravděpodobností nejčastějších hodnot (sloupec most_common_vals). Pokud hledaná hodnota nepatří do tohoto seznamu, tak se pravděpodobnost na základě úvahy, že součet pravděpodobností všech hodnot, které nejsou v seznamu nejčastějších hodnot a součet pravděpodobností nejčastějších hodnot musí být rovno 1. Známe počet unikátních hodnot (sloupec n_distinct) a předpokládáme, že hodnoty mimo seznam nejčastějších hodnot mají stejnou pravděpodobnost výskytu.
-- CZE není v seznamu MCV
world=# EXPLAIN SELECT * FROM city WHERE countrycode = 'CZE';
QUERY PLAN
------------------------------------------------------------------------------------
Index Scan using city_countrycode_idx1 on city (cost=0.28..15.00 rows=4 width=31)
Index Cond: (countrycode = 'CZE'::bpchar)
Planning time: 0.157 ms
(3 rows)
-- suma MCF
world=# SELECT sum(su), count(su)
FROM (SELECT unnest(most_common_freqs) su
FROM pg_stats
WHERE tablename = 'city' AND attname = 'countrycode') s;
sum | count
---------+-------
0.81417 | 36
(1 row)
-- získání počtu unikátních hodnot
world=# SELECT n_distinct FROM pg_stats WHERE tablename = 'city' AND attname = 'countrycode';
n_distinct
------------
232
(1 row)
-- Získání celkového počtu řádek
world=# SELECT reltuples FROM pg_class WHERE relname = 'city';
reltuples
-----------
4079
(1 row)
-- Výpočet selektivity (1 - sum(mvf))/(num_distinct - num_mcv)
world=# SELECT (1 - 0.81417)/(232 - 36);
?column?
------------------------
0.00094811224489795918
(1 row)
-- Celkový odhad
world=# SELECT 0.00094811224489795918 * 4079;
?column?
------------------------
3.86734984693877549522 -- ~ 4
(1 row)
Odhady nad statistikami
V Postgresu má každý sloupeček vlastní statistiku – hranice přibližně početně stejně velkých tříd, počet unikátních hodnot a četnosti nejčastěji se vyskytujících hodnot.Odhady, které jdou přímo ke statistikám, jsou většinou velice dobré:
world=# EXPLAIN ANALYZE SELECT * FROM city WHERE countrycode = 'CZE';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Index Scan using city_countrycode_idx on city (cost=0.28..15.00 rows=4 width=31) (actual time=0.048..0.058 rows=10 loops=1)
Index Cond: (countrycode = 'CZE'::bpchar)
Planning time: 0.181 ms
Execution time: 0.110 ms
(4 rows)
Time: 1.245 ms
world=# EXPLAIN ANALYZE SELECT * FROM city WHERE countrycode = 'CHN';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on city (cost=11.09..47.63 rows=363 width=31) (actual time=0.219..0.397 rows=363 loops=1)
Recheck Cond: (countrycode = 'CHN'::bpchar)
Heap Blocks: exact=4
-> Bitmap Index Scan on city_countrycode_idx (cost=0.00..11.00 rows=363 width=0) (actual time=0.196..0.196 rows=363 loops=1)
Index Cond: (countrycode = 'CHN'::bpchar)
Planning time: 0.247 ms
Execution time: 0.524 ms
(7 rows)
Time: 1.798 ms
Za povšimnutí stojí, že vykonání dotazu na serveru trvá cca 0,2 nebo 0,7 ms, nicméně dotaz pro klienta trval o 1ms déle. To je režie transferu dat na klienta (síťové vrstvy, zobrazení). V tomto případě se jedná o ideální stav – přenáší se pouze lokálně několik málo řádků. Pokud by se nejednalo o lokální připojení, tak tato režie může být výrazně vyšší (úkolem vývojáře je minimalizovat objem komunikace mezi serverem a klientem).
Pokud si pohraji s parametry optimalizátoru mohu dostat jiný plán pro Čínu (CHN):
world=# SETrandom_page_costTO 1; SET world=# EXPLAIN ANALYZE SELECT * FROM city WHERE countrycode = 'CHN'; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Index Scan using city_countrycode_idx on city (cost=0.28..27.69rows=363width=31) (actual time=0.071..0.294rows=363loops=1) Index Cond: (countrycode = 'CHN'::bpchar) Planning time: 0.169 ms Execution time: 0.381 ms (4 rows) Time: 1.455 ms
Tento dotaz je rychlejší. Má to svou logiku. Velikost tabulek je
world=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-----------------+-------+-------+--------+-------------
public | city | table | pavel | 264 kB |
public | countrylanguage | table | pavel | 56 kB |
public | country | table | pavel | 48 kB |
(3 rows)
world=# \di+
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+----------------------+-------+-------+-----------------+--------+-------------
public | city_name_idx | index | pavel | city | 128 kB |
public | city_countrycode_idx | index | pavel | city | 112 kB |
public | city_pkey | index | pavel | city | 112 kB |
public | countrylanguage_pkey | index | pavel | countrylanguage | 48 kB |
public | country_code_idx | index | pavel | country | 16 kB |
public | country_name_idx | index | pavel | country | 16 kB |
public | country_pkey | index | pavel | country | 16 kB |
(7 rows)
v součtu menší než 1MB, takže po prvním přečtení z disku data zůstanou v cache, a tudíž má smysl snížit cenu za random IO, jelikož výrazná většina random IO operací půjde přes cache.
Mezi posledními dvěma plány je ale další rozdíl. Až na výjimky bude index scan citlivější na podcenění odhadu než bitmap index scan (z každého pravidla existují výjimky). A co mi může snížit odhad? Například závislosti mezi sloupci. Optimalizátor v Postgresu předpokládá, že každé dva sloupce jsou na sobě nezávislé. Což často nebude pravda. Důvodem může být neúplná normalizace, denormalizace, přirozená závislost v datech nebo hiearchická data:
-- zhoršený odhad z důvodu korelace countrycode, district world=# EXPLAIN ANALYZE SELECT * FROM city WHEREcountrycode = 'CHN' AND district = 'Liaoning'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on city (cost=11.00..48.45rows=2width=31) (actual time=0.233..0.496rows=21loops=1) Recheck Cond: (countrycode = 'CHN'::bpchar) Filter: (district = 'Liaoning'::text) Rows Removed by Filter: 342 Heap Blocks: exact=4 -> Bitmap Index Scan on city_countrycode_idx (cost=0.00..11.00 rows=363 width=0) (actual time=0.196..0.196 rows=363 loops=1) Index Cond: (countrycode = 'CHN'::bpchar) Planning time: 0.311 ms Execution time: 0.598 ms (9 rows) Time: 2.221 ms
Všimněte si, že odhad je znatelně (nikoliv ale tak, aby nám to působilo problémy) nižší než skutečný počet řádků. V takto jednoduchém dotazu tato chyba nic nezpůsobí a fakticky vlastně ani nic neovlivní (protože nemáme index nad sloupcem district). Dotaz je stále velice rychlý. Jak opravit odhad? Odstraníme redundantní podmínku nad sloupcem countrycode ( district je vždy podmnožinou):
-- oprava odhadu, redukce korelace world=# EXPLAIN ANALYZE SELECT * FROM city WHEREdistrict = 'Liaoning'; QUERY PLAN -------------------------------------------------------------------------------------------------- Seq Scan on city (cost=0.00..82.99rows=21width=31) (actual time=1.145..2.377rows=21loops=1) Filter: (district = 'Liaoning'::text) Rows Removed by Filter: 4058 Planning time: 0.154 ms Execution time: 2.424 ms (5 rows)
Dotaz je ale pomalejší (chybí mi tam index nad sloupcem district). Po přidání indexu máme opět velice rychlý dotaz:
world=# EXPLAIN ANALYZE SELECT * FROM city WHERE district = 'Liaoning';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on city (cost=4.44..34.76 rows=21 width=31) (actual time=0.070..0.086 rows=21 loops=1)
Recheck Cond: (district = 'Liaoning'::text)
Heap Blocks: exact=4
-> Bitmap Index Scan on city_district_idx (cost=0.00..4.44 rows=21 width=0) (actual time=0.061..0.061 rows=21 loops=1)
Index Cond: (district = 'Liaoning'::text)
Planning time: 0.352 ms
Execution time: 0.139 ms
(7 rows)
Je dobré se ale zamyslet. Indexy nejsou zadarmo. Zpomalují každý UPDATE a INSERT. U tabulek, které často modifikujeme, se snažíme udržovat počet indexů co nejmenší. Patrně bych kvůli zrychlení dotazu ze 2ms na 0,5 ms index nikdy nepřidával. Odhady výsledku filtrování nad atributem (sloupcem), ke kterému máme statistiku, bývají docela přesné.
Jiným typem odhadu je odhad počtu unikátních hodnot, který se použije při plánování agregace nebo při použití klauzule DISTINCT.
world=# EXPLAIN ANALYZE SELECT DISTINCT name FROM city;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
HashAggregate (cost=82.99..123.00 rows=4001 width=9) (actual time=5.738..7.538 rows=4001 loops=1)
Group Key: name
-> Seq Scan on city (cost=0.00..72.79 rows=4079 width=9) (actual time=0.023..2.291 rows=4079 loops=1)
Planning time: 0.197 ms
Execution time: 8.463 ms
(5 rows)
world=# EXPLAIN ANALYZE SELECT COUNT(*) FROM city GROUP BY countrycode;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
HashAggregate (cost=93.19..95.50 rows=232 width=4) (actual time=5.485..5.626 rows=232 loops=1)
Group Key: countrycode
-> Seq Scan on city (cost=0.00..72.79 rows=4079 width=4) (actual time=0.026..1.515 rows=4079 loops=1)
Planning time: 0.161 ms
Execution time: 5.795 ms
(5 rows)
Time: 7.221 ms
K čemu potřebujeme tento typ odhadu? Optimalizátor se musí rozhodnout mezi rychlejší (a paměťově náročnější) metodou hashaggregate a pomalejší (zato paměťově úspornou) metodou groupaggregate. Pokud by se rozhodl špatně, tak by byl dotaz zbytečně pomalý (bez indexu s externím řazením výrazně pomalejší):
world=# SET enable_hashagg TO off;
SET
world=# EXPLAIN ANALYZE SELECT COUNT(*) FROM city GROUP BY countrycode;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=0.28..226.68 rows=232 width=4) (actual time=0.151..6.438 rows=232 loops=1)
Group Key: countrycode
-> Index Only Scan using city_countrycode_idx on city (cost=0.28..203.97 rows=4079 width=4) (actual time=0.132..4.113 rows=4079 loops=1)
Heap Fetches: 4079
Planning time: 0.188 ms
Execution time: 6.611 ms
(6 rows)
nebo naopak by mohl skončit přerušením dotazu z důvodu nedostatku paměti (v extrému killem od OOM) při nevhodném použití hashaggregate.
Odhady bez statistik procentem
Čím máme složitější dotaz, tím dále máme ke sloupcovým statistikám a odhady mají větší rozptyl. Chyby se někdy neutralizují, někdy multiplikují. Nevhodným zápisem se od statistik můžeme utrhnout okamžitě:
-- odhad se statistikou
world=# EXPLAIN ANALYZE SELECT * FROM city WHERE countrycode = 'NLD';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on city (cost=4.50..37.41 rows=28 width=31) (actual time=0.063..0.070 rows=28 loops=1)
Recheck Cond: (countrycode = 'NLD'::bpchar)
Heap Blocks: exact=1
-> Bitmap Index Scan on city_countrycode_idx (cost=0.00..4.49 rows=28 width=0) (actual time=0.047..0.047 rows=28 loops=1)
Index Cond: (countrycode = 'NLD'::bpchar)
Planning time: 0.186 ms
Execution time: 0.133 ms
(7 rows)
-- odhad bez statistiky procentem -- použití funkce
world=# EXPLAIN ANALYZE SELECT * FROM city WHERE upper(countrycode) = 'NLD';
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on city (cost=0.00..103.38 rows=20 width=31) (actual time=0.040..5.385 rows=28 loops=1)
Filter: (upper((countrycode)::text) = 'NLD'::text)
Rows Removed by Filter: 4051
Planning time: 0.100 ms
Execution time: 5.416 ms
(5 rows)
-- odhad se statistikou
world=# EXPLAIN ANALYZE SELECT * FROM city WHERE population > 1000000;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Seq Scan on city (cost=0.00..82.99 rows=234 width=31) (actual time=0.032..1.915 rows=237 loops=1)
Filter: (population > 1000000)
Rows Removed by Filter: 3842
Planning time: 0.168 ms
Execution time: 2.004 ms
(5 rows)
-- odhad bez statistiky procentem -- použití výrazu
world=# EXPLAIN ANALYZE SELECT * FROM city WHERE population + 1 > 1000000;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Seq Scan on city (cost=0.00..93.19 rows=1360 width=31) (actual time=0.036..3.350 rows=238 loops=1)
Filter: ((population + 1) > 1000000)
Rows Removed by Filter: 3841
Planning time: 0.153 ms
Execution time: 3.448 ms
(5 rows)
V situaci, kdy PostgreSQL nemůže použít sloupcové statistiky, tak optimalizátor použije definované procento z počtu řádků pro použitý operátor (0.5% pro „=“, 33% pro „>“). Pozor na implicitní přetypování, které může ovlivnit odhad:
-- odhad bez statistiky -- neshoda typů world=# EXPLAIN ANALYZE SELECT * FROM city WHERE population >1e6; QUERY PLAN ------------------------------------------------------------------------------------------------------ Seq Scan on city (cost=0.00..103.38rows=1360width=31) (actual time=0.042..6.181rows=238loops=1) Filter: (((population + 1))::numeric > 1000000::numeric) Rows Removed by Filter: 3841 Planning time: 0.163 ms Execution time: 6.268 ms (5 rows)
Zápis „1e6“ si vynutí konstantu typu numeric. Ten je obecnější než typ integer, ve kterém jsou statistiky pro sloupec population, a které se tudíž nepoužijí. Postgres je hodně restriktivní ohledně shody typů jak při použití sloupcových statistik, tak při použití indexů.
world=# \d city
Table "public.city"
Column | Type | Modifiers
-------------+--------------+-----------
id | integer | not null
name | text | not null
countrycode | character(3) | not null
district | text | not null
population | integer | not null
Indexes:
"city_pkey" PRIMARY KEY, btree (id)
"city_countrycode_idx" btree (countrycode)
"city_name_idx" btree (name)
Referenced by:
TABLE "country" CONSTRAINT "country_capital_fkey" FOREIGN KEY (capital) REFERENCES city(id)
Jiný datový typ si do výrazu můžeme zanést použitím funkce. Funkce upper vrací typ text. Sloupec countrycode je typu character(3):
-- odhad se statistikou
world=# EXPLAIN ANALYZE SELECT * FROM city WHERE countrycode = 'NLD';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on city (cost=4.50..37.41 rows=28 width=31) (actual time=0.077..0.114 rows=28 loops=1)
Recheck Cond: (countrycode = 'NLD'::bpchar)
Heap Blocks: exact=1
-> Bitmap Index Scan on city_countrycode_idx (cost=0.00..4.49 rows=28 width=0) (actual time=0.058..0.058 rows=28 loops=1)
Index Cond: (countrycode = 'NLD'::bpchar)
Planning time: 0.220 ms
Execution time: 0.189 ms
(7 rows)
Výsledkem volání funkce upper nad konstantou je konstanta. Jenomže POZOR. Tato konstanta je typu text:
-- odhad bez statistiky procentem -- neshoda typů
world=# EXPLAIN ANALYZE SELECT * FROM city WHERE countrycode = upper('nld');
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on city (cost=0.00..93.19 rows=20 width=31) (actual time=0.023..3.447 rows=28 loops=1)
Filter: ( (countrycode)::text = 'NLD'::text)
Rows Removed by Filter: 4051
Planning time: 0.105 ms
Execution time: 3.489 ms
(5 rows)
A tak i u relativně jednoduchého dotazu mohu mít problém s výkonem – a to z toho důvodu, že typ char, varchar není identický s typem text. Po explicitním přetypování dostanu kýžený výsledek:
world=# EXPLAIN ANALYZE SELECT * FROM city WHERE countrycode =upper('nld')::char(3); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on city (cost=4.50..37.41rows=28width=31) (actual time=0.077..0.084rows=28loops=1) Recheck Cond: (countrycode = 'NLD'::character(3)) Heap Blocks: exact=1 -> Bitmap Index Scan on city_countrycode_idx (cost=0.00..4.49 rows=28 width=0) (actual time=0.057..0.057 rows=28 loops=1) Index Cond: (countrycode = 'NLD'::character(3)) Planning time: 0.274 msExecution time: 0.155 ms(7 rows)
Nad sloupcem name se s tímto problémem nesetkám:
world=# EXPLAIN ANALYZE SELECT * FROM city WHERE name = initcap('praha');
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Index Scan using city_name_idx on city (cost=0.28..8.30 rows=1 width=31) (actual time=0.060..0.063 rows=1 loops=1)
Index Cond: (name = 'Praha'::text)
Planning time: 0.263 ms
Execution time: 0.151 ms
(4 rows)
Když už jsem u funkcí upper/ lower. Oblíbeným doporučovaným řešením case insensive vyhledávání je použití funkcionálního indexu. To funguje téměř dokonale. K dokonalosti chybí statistiky nad funkcionálními indexy. Ty bohužel Postres nemá.
world=# EXPLAIN ANALYZE SELECT * FROM city WHERE upper(name) = upper('praha');
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on city (cost=0.00..93.19 rows=20 width=31) (actual time=5.650..6.871 rows=1 loops=1)
Filter: (upper(name) = 'PRAHA'::text)
Rows Removed by Filter: 4078
Planning time: 0.166 ms
Execution time: 6.919 ms
(5 rows)
Zde vidíme generický odhad. Navíc tento dotaz je celkem náročný z důvodu opakovaného volání funkce upper – pro každý řádek sekvenčně čtené tabulky se volá upper(name). Funkcionálním indexem zabráníme opakovanému volání relativně drahé funkce:
world=# CREATE INDEX ON city((upper(name))); CREATE INDEX Time: 153.441 ms world=# EXPLAIN ANALYZE SELECT * FROM city WHERE upper(name) = upper('praha'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Bitmap Heap Scan on city (cost=4.44..34.80rows=20width=31) (actual time=0.067..0.067rows=1loops=1) Recheck Cond: (upper(name) = 'PRAHA'::text) Heap Blocks: exact=1 -> Bitmap Index Scan on city_upper_idx (cost=0.00..4.43 rows=20 width=0) (actual time=0.058..0.058 rows=1 loops=1) Index Cond: (upper(name) = 'PRAHA'::text) Planning time: 0.478 msExecution time: 0.123 ms(7 rows)
Zde stojí za povšimnutí, že plán, který dostaneme, není postaven nad perfektním odhadem – použije se odhad procentem, což tady zjevně přestřeluje. Nicméně ve výsledku dostanu velice rychlý dotaz – totiž většina operací, které se používají, mají určitou toleranci (větší/menší) vůči nepřesným odhadům (vůči chybě v odhadu).
Rekurzivní odhady bez statistik
Zatím jsem tu ukazoval pouze SELECT s filtrem – téměř nejjednodušší netriviální SQL dotaz. V dalších příkladech se budu snažit ukázat chování optimalizátoru, když zkouší generovat odhady z odhadů. Budou mne zajímat města v ČR a v USA, a budu ukazovat možné variace SQL dotazů na toto téma:
world=# EXPLAIN ANALYZE SELECT * FROM city WHERE countrycode = 'USA';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on city (cost=6.40..41.83 rows=274 width=31) (actual time=0.188..0.312 rows=274 loops=1)
Recheck Cond: (countrycode = 'USA'::bpchar)
Heap Blocks: exact=3
-> Bitmap Index Scan on city_countrycode_idx (cost=0.00..6.33 rows=274 width=0) (actual time=0.156..0.156 rows=274 loops=1)
Index Cond: (countrycode = 'USA'::bpchar)
Planning time: 0.227 ms
Execution time: 0.450 ms
(7 rows)
Time: 2.169 ms
world=# EXPLAIN ANALYZE SELECT * FROM city WHERE countrycode = 'CZE';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Index Scan using city_countrycode_idx on city (cost=0.28..15.00 rows=4 width=31) (actual time=0.057..0.069 rows=10 loops=1)
Index Cond: (countrycode = 'CZE'::bpchar)
Planning time: 0.231 ms
Execution time: 0.134 ms
(4 rows)
Time: 1.660 ms
Zde dostávám nejpřesnější odhady – zatím nedošlo k odhadům z odhadů
world=# EXPLAIN ANALYZE SELECT * FROM city ci JOIN country co ON ci.countrycode = co.code WHERE co.name = 'United States';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=4.42..42.00 rows=17 width=144) (actual time=0.361..0.738 rows=274 loops=1)
-> Seq Scan on country co (cost=0.00..7.99 rows=1 width=113) (actual time=0.140..0.148 rows=1 loops=1)
Filter: (name = 'United States'::text)
Rows Removed by Filter: 238
-> Bitmap Heap Scan on city ci (cost=4.42..33.84 rows=18 width=31) (actual time=0.189..0.346 rows=274 loops=1)
Recheck Cond: (countrycode = co.code)
Heap Blocks: exact=3
-> Bitmap Index Scan on city_countrycode_idx (cost=0.00..4.42 rows=18 width=0) (actual time=0.158..0.158 rows=274 loops=1)
Index Cond: (countrycode = co.code)
Planning time: 1.178 ms
Execution time: 0.916 ms
(11 rows)
Time: 3.764 ms
world=# EXPLAIN ANALYZE SELECT * FROM city ci JOIN country co ON ci.countrycode = co.code WHERE co.name = 'Czech Republic';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=4.42..42.00 rows=17 width=144) (actual time=0.187..0.218 rows=10 loops=1)
-> Seq Scan on country co (cost=0.00..7.99 rows=1 width=113) (actual time=0.129..0.148 rows=1 loops=1)
Filter: (name = 'Czech Republic'::text)
Rows Removed by Filter: 238
-> Bitmap Heap Scan on city ci (cost=4.42..33.84 rows=18 width=31) (actual time=0.045..0.050 rows=10 loops=1)
Recheck Cond: (countrycode = co.code)
Heap Blocks: exact=1
-> Bitmap Index Scan on city_countrycode_idx (cost=0.00..4.42 rows=18 width=0) (actual time=0.032..0.032 rows=10 loops=1)
Index Cond: (countrycode = co.code)
Planning time: 1.163 ms
Execution time: 0.343 ms
(11 rows)
Time: 3.191 ms
V tuto chvíli si všimněte, že pro USA je odhad nízko a pro ČR je skoro ok. Pokud byste analyzovali dotaz vůči ČR, tak si nevšimnete ničeho špatného. Proč je tomu tak vysvětlím za chvilku.
Místo JOINu mohu použít poddotaz:
world=# EXPLAIN ANALYZE SELECT * FROM city WHERE countrycode= (SELECT code FROM country WHERE name = 'United States'); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on city (cost=12.41..41.82rows=18width=31) (actual time=0.237..0.322rows=274loops=1) Recheck Cond: (countrycode = $0) Heap Blocks: exact=3 InitPlan 1 (returns $0) -> Seq Scan on country (cost=0.00..7.99 rows=1 width=4) (actual time=0.104..0.108 rows=1 loops=1) Filter: (name = 'United States'::text) Rows Removed by Filter: 238 -> Bitmap Index Scan on city_countrycode_idx (cost=0.00..4.42 rows=18 width=0) (actual time=0.217..0.217 rows=274 loops=1) Index Cond: (countrycode = $0) Planning time: 0.277 ms Execution time: 0.413 ms (11 rows) Time: 1.965 ms world=# EXPLAIN ANALYZE SELECT * FROM city WHERE countrycode= (SELECT code FROM country WHERE name = 'Czech Republic'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on city (cost=12.41..41.82rows=18width=31) (actual time=0.255..0.260rows=10loops=1) Recheck Cond: (countrycode = $0) Heap Blocks: exact=1 InitPlan 1 (returns $0) -> Seq Scan on country (cost=0.00..7.99 rows=1 width=4) (actual time=0.175..0.192 rows=1 loops=1) Filter: (name = 'Czech Republic'::text) Rows Removed by Filter: 238 -> Bitmap Index Scan on city_countrycode_idx (cost=0.00..4.42 rows=18 width=0) (actual time=0.245..0.245 rows=10 loops=1) Index Cond: (countrycode = $0) Planning time: 0.354 ms Execution time: 0.347 ms (11 rows)
Dostanu o něco málo jiný plán, ale se stejně špatným odhadem pro USA. Zrovna tak mohu použít predikát IN, ale bez větší změny odhadu pro USA:
world=# EXPLAIN ANALYZE SELECT * FROM city WHEREcountrycode IN (SELECT code FROM country WHERE name = 'United States'); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=4.42..42.00rows=18width=31) (actual time=0.341..0.677rows=274loops=1) -> Seq Scan on country (cost=0.00..7.99 rows=1 width=4) (actual time=0.142..0.149 rows=1 loops=1) Filter: (name = 'United States'::text) Rows Removed by Filter: 238 -> Bitmap Heap Scan on city (cost=4.42..33.84 rows=18 width=31) (actual time=0.189..0.343 rows=274 loops=1) Recheck Cond: (countrycode = country.code) Heap Blocks: exact=3 -> Bitmap Index Scan on city_countrycode_idx (cost=0.00..4.42 rows=18 width=0) (actual time=0.157..0.157 rows=274 loops=1) Index Cond: (countrycode = country.code) Planning time: 1.172 ms Execution time: 0.834 ms (11 rows) Time: 3.548 ms world=# EXPLAIN ANALYZE SELECT * FROM city WHEREcountrycode IN (SELECT code FROM country WHERE name = 'Czech Republic'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=4.42..42.00rows=18width=31) (actual time=0.209..0.241rows=10loops=1) -> Seq Scan on country (cost=0.00..7.99 rows=1 width=4) (actual time=0.150..0.169 rows=1 loops=1) Filter: (name = 'Czech Republic'::text) Rows Removed by Filter: 238 -> Bitmap Heap Scan on city (cost=4.42..33.84 rows=18 width=31) (actual time=0.048..0.053 rows=10 loops=1) Recheck Cond: (countrycode = country.code) Heap Blocks: exact=1 -> Bitmap Index Scan on city_countrycode_idx (cost=0.00..4.42 rows=18 width=0) (actual time=0.032..0.032 rows=10 loops=1) Index Cond: (countrycode = country.code) Planning time: 1.256 ms Execution time: 0.348 ms (11 rows) Time: 3.168 ms
Tyto plány jsou totožné s použitím JOINu. Zrovna tak stejné plány dostanu s použitím predikátu EXISTS (v této formě jsou sémanticky ekvivalentní, a optimalizátor vygeneruje stejné plány):
world=# EXPLAIN ANALYZE SELECT * FROM city ci WHEREEXISTS (SELECT * FROM country co WHERE name = 'United States' AND ci.countrycode = co.code); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=4.42..42.00rows=18width=31) (actual time=0.341..0.679rows=274loops=1) -> Seq Scan on country co (cost=0.00..7.99 rows=1 width=4) (actual time=0.143..0.150 rows=1 loops=1) Filter: (name = 'United States'::text) Rows Removed by Filter: 238 -> Bitmap Heap Scan on city ci (cost=4.42..33.84 rows=18 width=31) (actual time=0.187..0.337 rows=274 loops=1) Recheck Cond: (countrycode = co.code) Heap Blocks: exact=3 -> Bitmap Index Scan on city_countrycode_idx (cost=0.00..4.42 rows=18 width=0) (actual time=0.155..0.155 rows=274 loops=1) Index Cond: (countrycode = co.code) Planning time: 1.212 ms Execution time: 0.834 ms (11 rows) Time: 3.758 ms world=# EXPLAIN ANALYZE SELECT * FROM city ci WHEREEXISTS (SELECT * FROM country co WHERE name = 'Czech Republic' AND ci.countrycode = co.code); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=4.42..42.00rows=18width=31) (actual time=0.104..0.121rows=10loops=1) -> Seq Scan on country co (cost=0.00..7.99 rows=1 width=4) (actual time=0.072..0.083 rows=1 loops=1) Filter: (name = 'Czech Republic'::text) Rows Removed by Filter: 238 -> Bitmap Heap Scan on city ci (cost=4.42..33.84 rows=18 width=31) (actual time=0.026..0.029 rows=10 loops=1) Recheck Cond: (countrycode = co.code) Heap Blocks: exact=1 -> Bitmap Index Scan on city_countrycode_idx (cost=0.00..4.42 rows=18 width=0) (actual time=0.018..0.018 rows=10 loops=1) Index Cond: (countrycode = co.code) Planning time: 0.644 ms Execution time: 0.183 ms (11 rows) Time: 1.772 ms
Proč je odhad pro USA horší než pro ČR? Je to efekt odhadu počítaného na základě jiného odhadu, a odhad pro USA je špatný proto, že počet měst z USA výrazně překračuje průměr počtu měst pro zemi v tabulce city:
world=# SELECT count(*), count(DISTINCT countrycode) FROM city;
count | count
-------+-------
4079 | 232
(1 row)
world=# SELECT 4079/232.0;
?column?
---------------------
17.5818965517241379 -- ~ 18
(1 row)
Postgres se snaží získat co nejpřesnější odhady – takže se snaží odpíchnout od sloupců se statistikou:
world=# EXPLAIN SELECT * FROM country WHERE name = 'United States';
QUERY PLAN
---------------------------------------------------------
Seq Scan on country (cost=0.00..7.99 rows=1 width=113)
Filter: (name = 'United States'::text)
Planning time: 0.217 ms
(3 rows)
Time: 1.400 ms
world=# EXPLAIN SELECT * FROM country WHERE name = 'Czech Republic';
QUERY PLAN
---------------------------------------------------------
Seq Scan on country (cost=0.00..7.99 rows=1 width=113)
Filter: (name = 'Czech Republic'::text)
Planning time: 0.232 ms
(3 rows)
Time: 1.586 ms
Každý z těchto odhadů vrátí 1 řádek. A jelikož Postgres předpokládá, že každý cizí klíč je v tabulce zastoupený rovnoměrně, tak odhad pro USA i pro CZ je 1 * 17.58 ~ 18. Bohužel Postgres při plánování dotazu neumí (nesmí) sáhnout do číselníku a udělat transformaci name → code.
Lepší odhady dostanu, pokud pro filtrování použiji sloupec continent (PROČ?)
world=# EXPLAIN ANALYZE SELECT * FROM city ci JOIN country co ON ci.countrycode = co.code WHEREco.continent = 'Asia'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Hash Join (cost=8.62..105.41rows=870width=144) (actual time=0.240..6.473rows=1766loops=1) Hash Cond: (ci.countrycode = co.code) -> Seq Scan on city ci (cost=0.00..72.79 rows=4079 width=31) (actual time=0.017..1.631 rows=4079 loops=1) -> Hash (cost=7.99..7.99 rows=51 width=113) (actual time=0.199..0.199 rows=51 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 8kB -> Seq Scan on country co (cost=0.00..7.99 rows=51 width=113) (actual time=0.015..0.159 rows=51 loops=1) Filter: (continent = 'Asia'::text) Rows Removed by Filter: 188 Planning time: 1.118 ms Execution time: 6.922 ms (10 rows) Time: 9.692 ms world=# EXPLAIN ANALYZE SELECT * FROM city ci JOIN country co ON ci.countrycode = co.code WHEREco.continent = 'Europe';; QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Hash Join (cost=8.56..104.50rows=785width=144) (actual time=0.222..4.521rows=841loops=1) Hash Cond: (ci.countrycode = co.code) -> Seq Scan on city ci (cost=0.00..72.79 rows=4079 width=31) (actual time=0.018..1.411 rows=4079 loops=1) -> Hash (cost=7.99..7.99 rows=46 width=113) (actual time=0.181..0.181 rows=46 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 7kB -> Seq Scan on country co (cost=0.00..7.99 rows=46 width=113) (actual time=0.015..0.151 rows=46 loops=1) Filter: (continent = 'Europe'::text) Rows Removed by Filter: 193 Planning time: 1.182 ms Execution time: 4.800 ms (10 rows) Time: 7.441 ms
Mechanismus pro odhady je stejný:
world=# EXPLAIN SELECT * FROM country WHERE continent = 'Asia';
QUERY PLAN
----------------------------------------------------------
Seq Scan on country (cost=0.00..7.99 rows=51 width=113)
Filter: (continent = 'Asia'::text)
Planning time: 0.225 ms
(3 rows)
world=# EXPLAIN SELECT * FROM country WHERE continent = 'Europe';
QUERY PLAN
----------------------------------------------------------
Seq Scan on country (cost=0.00..7.99 rows=46 width=113)
Filter: (continent = 'Europe'::text)
Planning time: 0.233 ms
(3 rows)
world=# SELECT 51 * 17, 46 * 17;
?column? | ?column?
----------+----------
867 | 782
(1 row)
Zpřesnění odhadů pomocí immutable transformační funkce
Postgres se při plánování dotazu nedívá do tabulek. Pokusí se ale spočítat každou IMMUTABLE funkci, pokud jsou všechny její parametry konstanty.
Z hlediska optimalizátoru je nejdůležitější, jestli je funkce označena jako VOLATILE nebo STABLE nebo IMMUTABLE. Pokud je funkce označena jako IMMUTABLE, tak se předpokládá, že nemá žádný vedlejší efekt a že její výsledek záleží pouze na parametrech funkce.
Optimalizátor pak může (skalární) IMMUTABLE funkci s konstantními parametry nahradit konstantou. U SQL SRF funkcí (Set Returning Functions – funkce vracející tabulky), které jsou označené jako IMMUTABLE, pak může optimalizátor provést inlining (redukci volání, kdy tělem funkce nahradí volání funkce ve vnějším, obalujícím dotazu).
Toto chování optimalizátorů můžeme využít pro zpřesnění odhadů:
-- odhad, kde během plánování došlo k dohledání kódu z číselníku -- bez tohoto triku by byl odhad 18 řádek pro USA i pro ČR world=# EXPLAIN ANALYZE SELECT * FROM city WHERE countrycode =country_name_to_code('United States'); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on city (cost=6.40..41.83rows=274width=31) (actual time=0.179..0.303rows=274loops=1) Recheck Cond: (countrycode = 'USA'::bpchar) Heap Blocks: exact=3 -> Bitmap Index Scan on city_countrycode_idx (cost=0.00..6.33 rows=274 width=0) (actual time=0.151..0.151 rows=274 loops=1) Index Cond: (countrycode = 'USA'::bpchar) Planning time: 0.634 ms Execution time: 0.402 ms (7 rows) Time: 2.316 ms -- odhad, kde během plánování došlo k dohledání kódu z číselníku world=# EXPLAIN ANALYZE SELECT * FROM city WHERE countrycode = country_name_to_code('Czech Republic'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Index Scan using city_countrycode_idx on city (cost=0.28..15.00rows=4width=31) (actual time=0.050..0.058rows=10loops=1) Index Cond: (countrycode = 'CZE'::bpchar) Planning time: 0.707 ms Execution time: 0.114 ms (4 rows) Time: 2.116 ms world=# \sf country_name_to_code CREATE OR REPLACE FUNCTION public.country_name_to_code(text) RETURNS character LANGUAGE sqlIMMUTABLEAS $function$ SELECT code FROM country WHERE name = $1 $function$
Tenhle trik má smysl používat pouze u pomalých, pomalejších dotazů, kde z toho, že jej použiji, mám zřetelný benefit. Chyba odhadu v jednom řádu se nebere nijak tragicky.
Zkreslení odhadů z důvodu použití SRF (Set Returning Functions) funkcí
Funkce v PostgreSQL mohou vracet tabulku. To jsou tabulkové funkce. Tyto funkce mají natvrdo zadrátovaný odhad počtu vrácených řádků. K tomu slouží atribut ROWS a jeho výchozí hodnota je 1000.
world=# EXPLAIN ANALYZE SELECT * FROM foo('CZE');
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Function Scan on foo (cost=0.25..10.25 rows=1000 width=88) (actual time=4.560..4.562 rows=10 loops=1)
Planning time: 0.069 ms
Execution time: 4.605 ms
(3 rows)
world=# \sf foo
CREATE OR REPLACE FUNCTION public.foo(text)
RETURNS SETOF city
LANGUAGE sql
AS $function$
SELECT * FROM city WHERE countrycode = $1
$function$
world=#
Správným řešením je nastavení atributu ROWS na řádově správnou hodnotu:
world=# CREATE OR REPLACE FUNCTION foo(text) RETURNS SETOF city
AS $$ SELECT * FROM city WHERE countrycode = $1 $$
LANGUAGE sql ROWS 100;
CREATE FUNCTION
world=# EXPLAIN ANALYZE SELECT * FROM foo('CZE');
QUERY PLAN
------------------------------------------------------------------------------------------------------
Function Scan on foo (cost=0.25..1.25 rows=100 width=88) (actual time=6.801..6.805 rows=10 loops=1)
Planning time: 0.071 ms
Execution time: 6.864 ms
(3 rows)
Dalším možným řešením je vynucení inliningu, a to nastavením flagu IMMUTABLE:
world=# CREATE OR REPLACE FUNCTION foo(text) RETURNS SETOF city
AS $$ SELECT * FROM city WHERE countrycode = $1 $$
LANGUAGE sql IMMUTABLE;
CREATE FUNCTION
world=# EXPLAIN ANALYZE SELECT * FROM foo('CZE');
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on city (cost=0.00..93.19 rows=20 width=31) (actual time=3.697..4.488 rows=10 loops=1)
Filter: ((countrycode)::text = 'CZE'::text)
Rows Removed by Filter: 4069
Planning time: 0.252 ms
Execution time: 4.529 ms
(5 rows)
Inlining je možný pouze pro SQL funkce.
Jsou i situace, kdy selhává i odhad nad sloupcem se statistikou. Může se stát, že data mají takový charakter, že počet sledovaných tříd je příliš nízký. Pomocí EXPLAIN ANALYZE tuto situaci můžeme relativně jednoduše identifikovat, a jednoduše řešit zvýšením počtu tříd příkazem ALTER TABLE ALTER COLUMN SET STATISTICS.
U jednoduchých SQL příkazů by se nám nemělo stávat, že odhady jsou natolik chybné, že vedou ke špatným plánům. Pokud se to stává, tak máme divná data (výrazně nehomogenní), nebo chybně (možná neúplně) provedenou normalizaci schématu, nebo něco děláme špatně (například používáme EAV model). U složitějších dotazů a větších dat problémy s odhady můžeme mít. Extrémně nízký odhad může způsobit zavlečení nested loopu. Příliš vysoký odhad naopak způsobí v lepším případě hashjoin se sekvenčním čtením, v horším mergejoin s externím řazením. Sort i hashjoin jsou blokové operace, které nelze omezit (zrychlit) klauzulí LIMIT (s výjimkou ntop sort).
Potom standardním řešením je rozbití dotazu do několika menších. Mezivýsledky si uložíme do dočasných tabulek, nad kterými nezapomeneme spustit příkaz ANALYZE. Lze i použít trik s transformačními (lookup) IMMUTABLE funkcemi.
Úvahy o tom, jak se chovají odhady v konkrétní databázi a proč se tak chovají, jsou dobrou školou, jak se dozvědět víc o vlastních datech, víc o fungovaní PostgreSQL a potažmo i většiny SQL RDBMS, které mají optimalizaci založenou na statistkách.
ČLÁNKY DO MAILU