PL/pgSQL efektivně
Autor: Pavel Stěhule, srpen 2011
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).
V následujícím článku bych chtěl zkompletovat 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 CONTINUE - což byla pro mne bomba (člověku stačí ke štěstí málo :)).
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. PL/SQL je fakticky hodně osekaná 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ý 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 SELECT, který zpracovává executor. PL/pgSQL obsahuje jen implementaci proměnných a řídících konstrukcí (IF, LOOP, RETURN, :=, ..). 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 (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 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č #option dump (výsledek najdeme v logu Postgresu):
Zaregistruji funkci iifn3 spuštěním příkazu:
[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
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 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 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 PL/Perl nebo 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 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:
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$
První verze funkce je psaná v sql, druhá v PL/pgSQL. Obě funkce jsou jinak téměř identické.
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
Pro větší názornost ukáži prováděcí plány obou dotazů:
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)
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,:
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
případně ještě horší variantu:
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
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:
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$
Funkce join_str1 je nejhorší možná varianta, join_str3 nejlepší, join_str2 je někde mezi:
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
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).
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
Je nutné upravit test - použít derivovanou tabulku:
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)
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:
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
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.
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
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.
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;
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ů DELETE. Díky tomu, že kód poběží ve stejném procesu, že se pro DELETE opakovaně používá jeden prováděcí plán tak, zpracování DELETE 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.
CREATE OR REPLACE FUNCTION delete_rows(ids int[])
RETURNS void AS $$
BEGIN
DELETE
FROM mytable
WHERE mytable.id = ANY($1);
END;
LANGUAGE plpgsql;
V případě druhé varianty, bez ohledu na velikost vstupního pole, se provede vždy přesně jeden příkaz DELETE. 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 dynamické SQL.
O dynamickém SQL se obvykle píše v souvislosti s 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):
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
..
Prováděcí plán pro výše uvedený SELECT se vygeneruje při prvním zpracování konstrukce FOR SELECT. 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).
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
..
FOR EXECUTE 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ě:
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;
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.
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
..
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: "jmeno = $1" (pokud je $1 not NULL) nebo "true" (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:
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
..
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 auto_explain).