imported>Pavel v 27. 8. 2013, 04:14

2013-08-27T04:14:44Z

Nová stránka

[[Category:Články]]
''Autor: Pavel Stěhule, srpen 2011''

<blockquote style="background-color: #eeeee0; border: solid thin lightgrey; padding:15px; ">
Poznámka autora: Srpen 2013 - Počínaje PostgreSQL 9.2 by měly být nejčastější problémy způsobené slepou optimalizací (generickým plánem) částečně odstraněné. Minimálně prvních 5 exekucí je provedeno s adhoc plánem - a jedině v případě, že průměrná cena plánu adhoc plánů je vyšší než cena generického plánu, tak se začne používat generický plán. Průměr se neustále kontroluje, pokud převýší cenu generického plánu, tak už se používá nevratně generický plán (v rámci session). Ve výsledku se ovšem pro programátora nic moc nemění - tam, kde bylo nutné dříve použít dynamické SQL, zůstává nutnost jeho použítí i dnes (pokud si nejsme jisti, že SQL příkaz nebude vykonán méně než 5x za dobu existence session).
</blockquote>

V následujícím článku bych chtěl zkompletovat [http://www.root.cz/clanky/jemny-uvod-do-plpgsql sérii článků], které jsem o
PL/pgSQL pro root napsal. Kvůli PL/pgSQL jsem si vybral PostgreSQL a vlastně
kvůli PL/pgSQL jsem se stal jedním z vývojářů PostgreSQL - při práci na jednom
projektu mne PL/pgSQL drobet štval, tak jsem napsal patch, který byl přijat
a v další verzi PL/pgSQL se objevil příkaz <tt>CONTINUE</tt> - což byla pro mne bomba
(člověku stačí ke štěstí málo :)).

[http://www.postgresql.org/docs/9.0/interactive/plpgsql.html PL/pgSQL] je docela věrná kopie starších verzí programovacího jazyka PL/SQL,
což je jazyk pro vývoj uložených procedur fy. Oracle. [http://en.wikipedia.org/wiki/PL/SQL PL/SQL] je fakticky
hodně osekaná [http://en.wikipedia.org/wiki/Ada_%28programming_language%29 ADA] rozšířená o SQL. Syntaxe PL/pgSQL a PL/SQL jsou si hodně
podobné - zásadně se liší implementace. PL/pgSQL je velice jednoduchý [http://en.wikipedia.org/wiki/Interpreter_%28computer_software%29#Abstract_Syntax_Tree_interpreters interpret abstraktního syntaktického stromu],
který běží ve stejném procesu, v kterém probíhá zpracování SQL příkazu. Procedury
PL/SQL běží ve svém vlastním procesu, přičemž PL/SQL je překládáno do strojového
kódu. Každý přístup má své výhody a nevýhody - a má samozřejmě i jinou motivaci
a jiné historické pozadí. PL/pgSQL je úzce integrován s PostgreSQL - má zanedbatelnou
režii, pro přístup k datům není nutné používat interprocess komunikaci, snadno se
udržuje, snadno se rozšiřuje, snadno se učí. Sílou PL/SQL je jeho bohatost a fakt, že je překládán
do strojového kódu (resp. používá interpret tzv M-Code (interpret se nazývá PVM), který lze případně převést do strojového kódu). Přeci jen PL/SQL je méně osekanější ADA než PL/pgSQL a i
díky tomu je úplnější, univerzálnější než PL/pgSQL a také náročnější na naučení. Jinak oba jazyky jsou prověřené časem
- v PL/SQL se napsaly milióny řádků kódu a myslím si, že i v PL/pgSQL jsou nepochybně napsány stovky tisíc řádků.

PL/pgSQL je založen na jednoduchém interpretu - tak jednoduchém, že neimplementuje
ani základní aritmetické a logické operace - každý výraz se převádí na <tt>SELECT</tt>,
který zpracovává executor. PL/pgSQL obsahuje jen implementaci proměnných
a řídících konstrukcí (<tt>IF</tt>, <tt>LOOP</tt>, <tt>RETURN</tt>, <tt>:=</tt>, ..).
Naštěstí jednodušší SELECTy,
které odpovídají výrazům - tj neobsahují odkaz na tabulky, dokáže interpret
PL/pgSQL spouštět řádově efektivněji než typické dotazy - tj dotazy
do tabulek. Z této implementace vychází i efektivní použití PL/pgSQL. Je to
bezvadné lepidlo pro SQL příkazy. Na druhou stranu, PL/pgSQL se vůbec nehodí
pro numerické úlohy, které vyžadují velký počet aritmetických operací. PL/pgSQL
se nehodí pro náročnější operace, kde se intenzivně modifikují řetězce nebo pole.
Každá úprava řetězce nebo pole znamená vytvoření upravené kopie původních dat, kterou
jsou původní data nahrazena. Tento přístup je v případě
většího objemu nebo většího počtu operací neefektivní.

V PL/pgSQL se setkávají dva programovací jazyky - dva interpretované programovací
jazyky - PL/pgSQL a SQL. Při prvním použití funkce (v rámci session) se kód PL/pgSQL
převede do syntaktického stromu ([http://en.wikipedia.org/wiki/Abstract_syntax_tree Abstract Syntax Tree]), při prvním použití SQL příkazu se generuje prováděcí plán SQL příkazu. Parser PL/pgSQL (stejně jako PostgreSQL) je postavený nad [http://en.wikipedia.org/wiki/GNU_bison GNU Bisonem]. Získaný syntaktický strom a vytvořené prováděcí plány
se používají opakovaně dokud nedojde ke změně kódu funkce nebo k ukončení session.

Přeložený kód lze zpětně zrekonstruovat a zobrazit. K tomu slouží přepínač
<tt>#option dump</tt> (výsledek najdeme v logu Postgresu):

Zaregistruji funkci iifn3 spuštěním příkazu:

<pre>[pavel@nemesis ~]$ psql.5491 postgres
psql.5491 (9.1devel)
Type "help" for help.

postgres=# CREATE OR REPLACE FUNCTION public.iifn3(boolean, integer, integer)
RETURNS integer
LANGUAGE plpgsql
IMMUTABLE STRICT
AS $function$#option dump
BEGIN
IF $1 THEN
RETURN $2;
ELSE
RETURN $3;
END IF;
END;
$function$;

CREATE FUNCTION

[root@nemesis pavel]# tail -n 20 /usr/local/pgsql91/data/serverlog

Execution tree of successfully compiled PL/pgSQL function iifn3:

Function's data area:
entry 0: VAR $1 type bool (typoid 16) atttypmod -1
entry 1: VAR $2 type int4 (typoid 23) atttypmod -1
entry 2: VAR $3 type int4 (typoid 23) atttypmod -1
entry 3: VAR found type bool (typoid 16) atttypmod -1

Function's statements:
2:BLOCK <<*unnamed*>>
3: IF 'SELECT $1' THEN
4: RETURN 'SELECT $2'
ELSE
6: RETURN 'SELECT $3'
ENDIF
END -- *unnamed*

End of execution tree of function iifn3</pre>

[http://www.root.cz/clanky/cteni-provadecich-planu-v-postgresql/ Prováděcí plány SQL příkazů] se generují podle potřeby - před prvním vyhodnocením
SQL příkazu. Generování prováděcích plánů je výpočetně náročná úloha, a díky
výše popsanému chování můžeme ušetřit milisekundy tím, že negenerujeme plány
pro dotazy, které nebudou prováděny. Na druhou stranu pouze generování prováděcího
plánu provede skutečně důslednou kontrolu SQL příkazu (zda-li sedí názvy tabulek,
názvy sloupců), a pokud ke generování plánu nedochází, tak si nemůžeme být jistí,
zda-li jsou SQL příkazy uvnitř funkce správně zapsány. Pro důslednější kontrolu
jsem napsal rozšíření (doplněk) pro PostreSQL - modul [http://groups.google.com/group/postgresql-cz/browse_thread/thread/6372b393ced4d2be?hl=cs plpgsql_lint]. Ten kromě
jiného si vynutí generování plánů pro všechny "embeded" SQL příkazy uvnitř funkce
při jejím prvním spuštění (výše zmíněný modul neinstalujte na produkční servery,
zpomaluje první spuštění funkce).

Výhodou PL/pgSQL je jeho integrace s PostgreSQL. Proměnné v tomto jazyce jsou
datově kompatibilní s [[C_a_PostgreSQL_-_intern%C3%AD_mechanismy|interními formáty]] Postgresu. Odpadá nutnost konverzí.
Výhodou PL/pgSQL je paměťová nenáročnost interpretu a relativní rychlost spouštění
funkcí. V řadě případů může zavolání funkce v PL/pgSQL vyžadovat méně času
než volání funkce v [http://www.postgresql.org/docs/9.0/interactive/plperl.html PL/Perl] nebo [http://www.postgresql.org/docs/9.0/interactive/plpython.html PL/Python]. Vždy záleží na vzájemných proporcích
faktorů, které ovlivňují dobu provádění funkce.

Nejhorší chybou je nevhodné použití PL/pgSQL. Kromě PL/pgSQL můžeme použít i jazyk
SQL. Pro jednodušší funkce v jazyce SQL umí optimalizátor použít techniku, která
je podobná tzv [http://en.wikipedia.org/wiki/Inlining inliningu] - tělo funkce se přímo zkopíruje do SQL příkazu, který
používá funkci. Tím naprosto odpadá režie spojená s voláním funkce. Pro ilustraci
použiji jednoduchou funkci iifn:

<pre>postgres=# \sf iifn1
CREATE OR REPLACE FUNCTION public.iifn1(boolean, integer, integer)
RETURNS integer
LANGUAGE sql
AS $function$
SELECT CASE WHEN $1 THEN $2 ELSE $3 END;
$function$

postgres=# \sf iifn2
CREATE OR REPLACE FUNCTION public.iifn2(boolean, integer, integer)
RETURNS integer
LANGUAGE plpgsql
IMMUTABLE STRICT
AS $function$
BEGIN
RETURN CASE WHEN $1 THEN $2 ELSE $3 END;
END;
$function$</pre>

První verze funkce je psaná v sql, druhá v PL/pgSQL. Obě funkce jsou jinak
téměř identické.

<pre>postgres=# select sum(iifn1((random()::int)::bool, 1, 0))
from generate_series(1,100000);
sum
-------
49904
(1 row)

Time: 139.824 ms

postgres=# select sum(iifn2((random()::int)::bool, 1, 0))
from generate_series(1,100000);
sum
-------
50030
(1 row)

Time: 581.466 ms</pre>

Pro větší názornost ukáži prováděcí plány obou dotazů:
<pre>
postgres=# explain verbose select sum(iifn1((random()::int)::bool, 1, 0))
from generate_series(1,100000);
QUERY PLAN
-----------------------------------------------------------------------------------------
Aggregate (cost=12.50..12.52 rows=1 width=0)
Output: sum(CASE WHEN ((random())::integer)::boolean THEN 1 ELSE 0 END)
-> Function Scan on pg_catalog.generate_series (cost=0.00..10.00 rows=1000 width=0)
Output: generate_series
Function Call: generate_series(1, 100000)
(5 rows)

postgres=# explain verbose select sum(iifn2((random()::int)::bool, 1, 0))
from generate_series(1,100000);
QUERY PLAN
-----------------------------------------------------------------------------------------
Aggregate (cost=12.50..12.77 rows=1 width=0)
Output: sum(iifn2(((random())::integer)::boolean, 1, 0))
-> Function Scan on pg_catalog.generate_series (cost=0.00..10.00 rows=1000 width=0)
Output: generate_series
Function Call: generate_series(1, 100000)
(5 rows)
</pre>
V prvém případě se tělo funkce vkopírovalo přímo do dotazu.

Díky inliningu je funkce napsaná v SQL téměř 4x rychlejší. V situaci, kdy
voláme funkci pro každý řádek výsledku dotazu musíme brát v potaz rychlost
provádění funkce. Mám tu kód, který je 4x pomalejší, a ten je ještě navržen
maximálně efektivně - obsahuje pouze jediný výraz. Dovedu si dobře představit,
že programátor, který nezná dobře PL/pgSQL napíše "C" style kód,:

<pre>postgres=# \sf iifn3
CREATE OR REPLACE FUNCTION public.iifn3(boolean, integer, integer)
RETURNS integer
LANGUAGE plpgsql
IMMUTABLE STRICT
AS $function$
BEGIN
IF $1 THEN
RETURN $2;
ELSE
RETURN $3;
END IF;
END;
$function$

postgres=# select sum(iifn3((random()::int)::bool, 1, 0))
from generate_series(1,100000);
sum
-------
50151
(1 row)

Time: 630.804 ms</pre>

případně ještě horší variantu:

<pre>postgres=# \sf iifn4
CREATE OR REPLACE FUNCTION public.iifn4(boolean, integer, integer)
RETURNS integer
LANGUAGE plpgsql
IMMUTABLE STRICT
AS $function$
DECLARE prom int;
BEGIN
IF $1 THEN
prom := $2;
ELSE
prom := $3;
END IF;
RETURN prom;
END;
$function$

postgres=# select sum(iifn4((random()::int)::bool, 1, 0))
from generate_series(1,100000);
sum
-------
49728
(1 row)

Time: 668.183 ms</pre>

Přičemž z pohledu respektování obecných doporučení pro návrh kódu je kód
použitý ve funkci iifn4 perfektní - ale je nejpomalejší, protože obsahuje
největší počet výrazů. Zas tak pomalé to není - 100 tis volání netrvá ani 1 sec
na mém obstarožním Prestigio Nobile 156 (i jako nové, to bylo ořezávátko). Nicméně můžete napsat kód, který bude
4-5 rychlejší, pokud se budete držet zásady, že jednoduché funkce se navrhují
v jazyce sql.

Připravil jsem ještě jeden příklad demonstrující vliv počtu výrazů uvnitř funkce
na rychlost funkce. Napsal jsem funkci, jejíchž výsledkem je spojení dvou řeězců
oddělených mezerou. Pokud jeden z řetězců je NULL, pak výsledkem je druhý řetězec.
Pokud oba řetězce jsou NULL, pak výsledkem je NULL:

<pre>CREATE OR REPLACE FUNCTION public.join_str1(text, text)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE
AS $function$
DECLARE aux text;
BEGIN
IF $1 IS NOT NULL THEN
aux := $1;
END IF;
IF $2 IS NOT NULL THEN
IF aux IS NOT NULL THEN
aux := aux || ' ';
ELSE
aux := '';
END IF;
aux := aux || $2;
END IF;
RETURN aux;
END;
$function$

postgres=# \sf join_str2
CREATE OR REPLACE FUNCTION public.join_str2(text, text)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE
AS $function$
DECLARE aux text;
BEGIN
IF $1 IS NOT NULL AND $2 IS NOT NULL THEN
RETURN $1 || ' ' || $2;
ELSEIF $1 IS NOT NULL AND $2 IS NULL THEN
RETURN $1;
ELSEIF $1 IS NULL AND $2 IS NOT NULL THEN
RETURN $2;
ELSE
RETURN NULL;
END IF;
END;
$function$

postgres=# \ef join_str2
postgres-# ;
CREATE FUNCTION
postgres=# \sf join_str3
CREATE OR REPLACE FUNCTION public.join_str3(text, text)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE
AS $function$
DECLARE aux text;
BEGIN
RETURN COALESCE($1 || ' '|| $2, $1, $2);
END;
$function$</pre>

Funkce join_str1 je nejhorší možná varianta, join_str3 nejlepší,
join_str2 je někde mezi:

<pre>postgres=# select count(join_str1(case when random() > 0.2 then 'aaa' end,
case when random() > 0.2 then 'bbb' end))
from generate_series(1,100000);
count
-------
95950
(1 row)

Time: 1006.042 ms

postgres=# select count(join_str2(case when random() > 0.2 then 'aaa' end,
case when random() > 0.2 then 'bbb' end))
from generate_series(1,100000);
count
-------
96027
(1 row)

Time: 728.356 ms

postgres=# select count(join_str3(case when random() > 0.2 then 'aaa' end,
case when random() > 0.2 then 'bbb' end))
from generate_series(1,100000);
count
-------
95884
(1 row)

Time: 618.247 ms</pre>

Po převedení funkce join_str3 do sql (což je další krok při optimalizaci) ovšem zjistíme, že test trvá
delší dobu. Explain nám prozradí, že nedošlo k inliningu (z důvodu použití
volatile funkce random).

<pre>CREATE OR REPLACE FUNCTION public.join_str4(text, text)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $function$
SELECT COALESCE($1 || ' '|| $2, $1, $2);
$function$

postgres=# select count(join_str4(case when random() > 0.2 then 'aaa' end,
case when random() > 0.2 then 'bbb' end))
from generate_series(1,100000); count
-------
96018
(1 row)

Time: 1300.636 ms</pre>

Je nutné upravit test - použít derivovanou tabulku:

<pre>postgres=# select count(join_str4(a,b))
from (select case when random() > 0.2 then 'aaa' end as a,
case when random() > 0.2 then 'bbb' end as b
from generate_series(1,100000)) x;
count
-------
95992
(1 row)

Time: 225.656 ms

postgres=# explain verbose select count(join_str4(a,b))
from (select case when random() > 0.2 then 'aaa' end as a,
case when random() > 0.2 then 'bbb' end as b
from generate_series(1,100000)) x;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=32.51..32.52 rows=1 width=64)
Output: count(COALESCE((((CASE WHEN (random() > 0.2::double precision) THEN 'aaa'::text ELSE NULL::text END) || ' '::text) || (CASE WHEN (random() > 0.2::double precision) THEN 'bbb'::text ELSE NULL::text END)), (CASE WHEN (random() > 0.2::double precision) THEN 'aaa'::text ELSE NULL::text END), (CASE WHEN (random() > 0.2::double precision) THEN 'bbb'::text ELSE NULL::text END)))
-> Function Scan on pg_catalog.generate_series (cost=0.00..20.00 rows=1000 width=0)
Output: CASE WHEN (random() > 0.2::double precision) THEN 'aaa'::text ELSE NULL::text END, CASE WHEN (random() > 0.2::double precision) THEN 'bbb'::text ELSE NULL::text END
Function Call: generate_series(1, 100000)
(5 rows)
</pre>

Volatile funkcí je naštěstí minimum - pokud je použijeme, tak je vhodné zkontrolovat prováděcí plán.
Nelze přehlédnout, že bez inliningu je sql funkce cca 2x pomalejší než funkce v PL/pgSQL.

Další zásadní chybou je použití PL/pgSQL místo využití nativní funkce,
funkcionality v PostgreSQL. Interně PostgreSQL obsahuje "tuny" optimalizovaného
kódu a je chybou jej nevyužít. Mým oblíbeným příkladem je bublesort, který
používám při svých školeních. Bublesort ovšem není určen pro řazení větších
polí, takže zde budu demonstrovat rozdíl v rychlosti vestavěného quicksortu
a quicksortu implementovaného v PL/pgSQL:

<pre>postgres=# \sf quicksort (int, int, int[])
CREATE OR REPLACE FUNCTION public.quicksort(l integer, r integer, a integer[])
RETURNS integer[]
LANGUAGE plpgsql
IMMUTABLE STRICT
AS $function$
DECLARE akt int[] = a;
i integer := l; j integer := r; x integer = akt[(l+r) / 2];
w integer;
BEGIN
LOOP
WHILE akt[i] < x LOOP i := i + 1; END LOOP;
WHILE x < akt[j] loop j := j - 1; END LOOP;
IF i <= j THEN
w := akt[i];
akt[i] := akt[j]; akt[j] := w;
i := i + 1; j := j - 1;
END IF;
EXIT WHEN i > j;
END LOOP;
IF l < j THEN akt := quicksort(l,j,akt); END IF;
IF i < r then akt := quicksort(i,r,akt); END IF;
RETURN akt;
END;
$function$

postgres=# \sf quicksort(int[])
CREATE OR REPLACE FUNCTION public.quicksort(integer[])
RETURNS integer[]
LANGUAGE sql
AS $function$
SELECT quicksort(array_lower($1,1), array_upper($1,1), $1);
$function$

postgres=# select (quicksort(array(select generate_series(10000,1,-1))))[1];
quicksort
-----------
1
(1 row)

Time: 2712.860 ms</pre>

Seřazení pole s deseti tisící prvky trvá cca 2.5 sec. PostgreSQL, ostatně jako
jakákoliv jiná databáze umí perfektně řadit - bohužel tabulky, nikoliv pole.
Naštěstí lze docela jednoduše transformovat pole na tabulku a tabulku na pole.

<pre>postgres=# \sf quicksort (anyarray)
CREATE OR REPLACE FUNCTION public.quicksort(anyarray)
RETURNS anyarray
LANGUAGE sql
AS $function$
SELECT ARRAY(SELECT unnest($1) ORDER BY 1)
$function$

postgres=# select (quicksort(array(select generate_series(10000,1,-1))))[1];
quicksort
-----------
1
(1 row)

Time: 52.980 ms</pre>

Kód je cca 50x rychlejší, a navíc díky jednodušší implementaci mohu použít
polymorfní typy. Vtip je ve využití interní funkcionality PostgreSQL. Viděl jsem funkce,
které v PL/pgSQL analyzovaly formát řetězce. Chyba. Přeci mohu použít vestavěné funkce
pro práci s regulárními výrazy nebo mohu použít PL/Perl.

Následující chyba se může vyskytnout jak v uložených procedurách, tak i na straně
klienta. Jedná se o tzv ISAM přístup. Při psaní kódu musíme myslet i na počet
SQL příkazů, kterými zatěžujeme server. V řadě případů lze nějakým způsobem
počet SQL příkazů snížit - pozor - není cílem mít několik málo monstrer
- SQL příkazů o desítkách nebo stovkách řádků.

Na internetu jsem našel příklad funkce, kde programátor uvnitř volal uvnitř funkce
mazal řádky na základě předaných ídéček.

<pre>CREATE OR REPLACE FUNCTION delete_rows(ids int[])
RETURNS void AS $$
BEGIN
FOR i IN array_lower($1,1)..array_upper($1,1)
LOOP
DELETE
FROM mytable
WHERE mytable.id = ids[i];
END LOOP;
END;
LANGUAGE plpgsql;</pre>

Pokud seznam ídeček bude malý, tak je výše uvedená funkce ok. Pokud ovšem bude mít
spíš stovky nebo tisíce hodnot, tak funkce delete_rows bude serveru podsouvat
stovky, tisíce příkazů <tt>DELETE</tt>. Díky tomu, že kód poběží ve stejném procesu, že
se pro <tt>DELETE</tt> opakovaně používá jeden prováděcí plán tak, zpracování <tt>DELETE</tt> bude rychlejší
než, kdyby se o totéž pokoušel klient. Ale stále se bude jednat o neefektivní kód.
Drobnou úpravou můžeme významně snížit zatížení serveru.

<pre>CREATE OR REPLACE FUNCTION delete_rows(ids int[])
RETURNS void AS $$
BEGIN
DELETE
FROM mytable
WHERE mytable.id = ANY($1);
END;
LANGUAGE plpgsql;</pre>

V případě druhé varianty, bez ohledu na velikost vstupního pole, se provede vždy
přesně jeden příkaz <tt>DELETE</tt>. Tuto chybu můžeme udělat v libovolném jazyce, v libovolném
prostředí - pozor na SQL příkazy uvnitř těla cyklu - někdy se jim nevyhneme, ale
pokud se jim lze vyhnout, tak bychom se jim měli vyhnout.

Rychlost zpracování prováděcího plánu dotazu nezávisí na tom, zda-li byl dotaz
odeslán z uložené procedury (či zákaznické funkce) nebo z klasické aplikace. Jelikož
uložené procedury běží na stejném železe jako běží samotná databáze, tak úplně odpadá
síťová komunikace (překvapivě pořádná brzda i v dnešní době - o co se zrychlily sítě, o to se zvětšil
počet SQL dotazů a objem přenášených dat). V případě PostgreSQL odpadá i interprocess
komunikace, která zase pro větší objemy dat může znamenat významnou zátěž - viz
google a klíčová slova - BULK COLLECT ORACLE. Pro prováděcí plány všech statických
SQL dotazů je použitá cache, čímž se také může ušetřit pár milisekund.

Používání cache prováděcích plánů má také své stinné stránky. Jelikož se prováděcí
plán použije opakovaně s potenciálně hodně odlišnými parametry, optimalizuje se nikoliv
vůči známým hodnotám parametrů, ale vůči průměrným hodnotám atributů. Pokud bude
v databázi hodně Nováků, tak přestože se budu dotazovat na Stěhuleho, tak prováděcí
plán bude optimální pro dotaz na Nováka. Toto chování občas působí výkonnostní
problémy. Naštěstí je k dispozici nástroj, kterým si můžeme vynutit vytvoření
prováděcího plánu vůči aktuálním hodnotám parametru dotazu - tímto nástrojem je
[http://www.postgresql.org/docs/9.0/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN dynamické SQL].

O dynamickém SQL se obvykle píše v souvislosti s [http://cs.wikipedia.org/wiki/SQL_injection SQL injection]. Pozor na to. Pro
řešení problému popsaném v předchozím odstavci můžeme (díky klauzuli USING) použít
dynamické SQL bezpečně (pokud řetězec SQL příkazu neskládáme, tak se SQL injection
nemusíme obávat):

<pre>CREATE OR REPLACE FUNCTION foo(jmeno text)
RETURNS void AS $$
DECLARE r record;
BEGIN
FOR r IN SELECT *
FROM obcane
WHERE obcane.jmeno = foo.jmeno
LOOP
..</pre>

Prováděcí plán pro výše uvedený SELECT se vygeneruje při prvním zpracování
konstrukce <tt>FOR SELECT</tt>. V ten okamžik vím a db ví, že proměnná jmeno obsahuje řetězec
"Stěhule". Tato znalost se nezužitkuje, protože je možné, že příště bude proměnná
jmeno obsahovat řetězec "Novák". Pokud bych měl s funkcí foo výkonnostní problém,
mohu použít cyklus FOR EXECUTE (pozor na předčasnou optimalizaci).

<pre>CREATE OR REPLACE FUNCTION foo(jmeno text)
RETURNS void AS $$
DECLARE r record;
BEGIN
FOR r IN EXECUTE 'SELECT *
FROM obcane
WHERE jmeno = $1'
USING jmeno
LOOP
..</pre>

<tt>FOR EXECUTE</tt> nepoužívá cache pro prováděcí plány a tudíž prováděcí plán bude vždy
odpovídat hodnotám parametrů. Dynamické SQL by se mělo používat pouze tehdy, když
zjistíte výkonnostní problém. Vím pouze o jedné situaci, kdy dopředu vím, že je
vhodné použít dynamické SQL. NULL lze použít i jako příznak pro zanedbání některého
z parametru funkce (je to čistší než použití magických konstant). Kód funkce foo
by mohl vypadat následovně:

<pre>CREATE OR REPLACE FUNCTION foo(jmeno text)
RETURNS void AS $$
DECLARE r record;
BEGIN
IF jmeno IS NULL THEN
FOR r IN SELECT *
FROM obcane
LOOP
..
END IF;
ELSE
FOR r IN EXECUTE 'SELECT *
FROM obcane
WHERE jmeno = $1'
USING jmeno
LOOP
..
END LOOP;
END IF;</pre>

Je zřejmé, že takový přístup vede k redundantnímu kódu. Lze použít starý trik, pro
který doporučuji vždy použít dynamické SQL.

<pre>CREATE OR REPLACE FUNCTION foo(jmeno text)
RETURNS void AS $$
DECLARE r record;
BEGIN
FOR r IN EXECUTE 'SELECT *
FROM obcane
WHERE (jmeno = $1 OR $1 IS NULL)'
USING jmeno
LOOP
..</pre>

Pro tento trik není v PostgreSQL žádná optimalizace (která je např. ve Firebirdu).
Pokud se nepoužívají prepared statements nebo nebo nakešované prováděcí plány, tak
se nic neděje. Optimalizátor na základě známé hodnoty parametru upraví predikát do
tvaru: "<tt>jmeno = $1</tt>" (pokud je $1 not NULL) nebo "<tt>true</tt>" (pokud je $1 NULL). PL/pgSQL
používá cache pro prováděcí plány, a tak se vytvoří plán pro obecnější případ, což je
zanedbání predikátu a tudíž vždy sekvenční čtení. Dynamické SQL je perfektním řešením:

<pre>CREATE OR REPLACE FUNCTION foo(jmeno text, prijmeni text, vek int[])
RETURNS void AS $$
DECLARE r record;
BEGIN
FOR r IN EXECUTE 'SELECT *
FROM obcane
WHERE (jmeno = $1 OR $1 IS NULL)
AND (prijmeni = $2 OR $2 IS NULL)
AND (vek = ANY($3) OR $3 IS NULL)'
USING jmeno,
prijmeni,
vek
LOOP
..</pre>

PL/pgSQL není komplikovaný jazyk a není ani nijak záludný. Tento článek pokrývá
velkou většinu výkonnostních problémů, na které můžeme narazit. Základem je zužitkovat
optimalizovaný kód Postgresu a dát si pozor na pomalé SQL příkazy (užitečným pomocníkem je contrib modul [http://www.postgresql.org/docs/9.0/interactive/auto-explain.html auto_explain]).

PL/pgSQL efektivně - Historie editací

imported>Pavel v 27. 8. 2013, 04:14