SQL Triky II.
Starších dvacet triků Novějších dvacet triků
Přepočet času na jinou časovou zónu a jeho indexace
Nejsnadnější způsob určení času v konkrétní časové zóně spočívá v použití operátoru AT TIME ZONE. Timestamp obsahující časovou zónu nelze indexovat - důvod je nasnadě, aktuální časovou zónu lze měnit (v PostgreSQL se časová zóna neukládá absolutně, ale relativně - ukládá se offset). Řešením je převod Timestampu na něktorou konkrétní časovou zónu (tím se odstraní offset). Zdroj: pgsql_general, Tom Lane
CREATE INDEX created_tz_paris ON data((created_on AT TIME ZONE 'Europe/Paris'));
Použití IMMUTABLE funkce jako hintu
Pokud je rozdělení dat nerovnoměrné, případně nespojité, pak dochází k selhání predikčního algoritmu a ke generování neoptimálního prováděcího plánu. Pokud k tomu dojde, je jednou z možností rozbít dotaz do několika závislých dotazů a ty vyhodnocovat postupně. Následující dotazy použijí jako parametry již skutečné výsledky předchozích dotazů (nikoliv odhady), což vede k získání optimálních prováděcích plánů. Tom Lane navrhl jedno řešení (hodně diskutabilní, leč fungující), které umožňuje totéž ale v rámci jednoho dotazu. Využívá faktu, že se IMMUTABLE funkce vyhodnocují před vlastním generováním prováděcího plánu:
CREATE OR REPLACE FUNCTION getTypesLieuFromTheme(codeTheme text)
RETURNS text[] AS $f$
SELECT ARRAY(SELECT codetylieu::text FROM rubtylieu WHERE codeth = $1);
$f$ LANGUAGE SQL IMMUTABLE;
pak v dotazu
SELECT ...
WHERE ... AND gl.codetylieu = ANY(getTypesLieuFromTheme('RES'))
vsimnete si, v ze provadecim planu neni volani funkce a ani dotaz do tab. rubtylieu
-> Bitmap Heap Scan on genrelieu gl (cost=2.23..6.22 rows=88 width=4) (actual time=0.022..0.075 rows=88 loops=1)
Recheck Cond: ((codetylieu)::text = ANY ('{RES}'::text[]))
-> Bitmap Index Scan on ind_genrelieu2 (cost=0.00..2.21 rows=88 width=0) (actual time=0.016..0.016 rows=88 loops=1)
Index Cond: ((codetylieu)::text = ANY ('{RES}'::text[]))
Za normálních okolností nelze tento trik doporučit. Jako první krok s problémem planneru je vždy zvýšení počtu tříd v histogramu pro sloupec, se kterým má planner problém. Nicméně, pokud data jsou extrémně nevyrovnaná, může být tento trik jediným možným řešením.
Autonomní transakce v PostgreSQL
Autonomní transakce je šikovná, leč chybějící funkce v PostgreSQL. Díky untrusted procedurám lze ale do jisté míry autonomní transakce emulovat. Jedno možné řešení zaslal do konference Jon Roberts.
CREATE OR REPLACE FUNCTION fn_log_error(p_function varchar, p_location int, p_error varchar)
RETURNS void AS $$
DECLARE
v_sql varchar;
v_return varchar;
v_error varchar;
BEGIN
PERFORM dblink_connect('connection_name', 'dbname=...');
v_sql := INSERT INTO error_log (function_name, location, error_message, error_time) '
|| 'VALUES (''' || p_function_name || ''', '
|| p_location || ', ''' || p_error || ''', clock_timestamp())';
SELECT INTO v_return *
FROM dblink_exec('connection_name', v_sql, false);
--get the error message
SELECT INTO v_error *
FROM dblink_error_message('connection_name');
IF position('ERROR' in v_error) > 0 OR position('WARNING' in v_error) > 0 THEN
RAISE EXCEPTION '%', v_error;
END IF;
PERFORM dblink_disconnect('connection_name');
EXCEPTION
WHEN others THEN
PERFORM dblink_disconnect('connection_name');
RAISE EXCEPTION '(%)', SQLERRM;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
Optimalizijte opakované přiřazení varcharu funkcí array_to_string
plpgsql není vhodný jazyk pro intenzivní ne SQL operace. Je ideální jako lepidlo SQL příkazů, případně jako jazyk pro tvorbu prototypu. Pokud zjistíme, že úzké hrdlo dotazu je v plpgsql funkci, měli bychom tuto funkci přepsat. K dispozici je Perl, Python, C případně Java. Případně můžeme změnit řešení funkce. V plpgsql jsou nejpomalejší iterace, pokud se uvnitř iterace zvětšuje objem některé proměnné (řetězce nebo pole). Pokud tuto iteraci necháme na SQL, tak je funkce podstatně rychlejší.
CREATE OR REPLACE FUNCTION SlowList(int) -- pomala funkce pouzitelna pro N <= 100
RETURNS varchar AS $$
DECLARE s varchar = '';
BEGIN
FOR i IN 1..$1 LOOP
s := '<item>' || i || '</item>'; -- pomale je prirazeni s := s || ..
END LOOP;
RETURN s;
END; $$ LANGUAGE plpgsql IMMUTABLE;
CREATE OR REPLACE FUNCTON FastList(int) -- rychla verze funkce
RETURNS varchar AS $$
BEGIN
RETURN array_to_string(ARRAY(SELECT '<item>' || i || '</item>'
FROM generate_series(1, $1) g(i)),
'');
END; $$ LANGUAGE plpgsql IMMUTABLE;
Pro N < 100 proběhnou obě funkce v čase menším než 4ms (proto není důvod si pomáhat s poněkud nečitelným trikem). Pro větší N je v rychlosti provádění nezanedbatelný rozdíl (SlowList(10000) - 4000ms, FastList(10000) - 52ms). Pozn. autor - Pavel Stěhule.
Nepoužívejte COALESCE jako náhradu IS DISTINCT FROM
Sim Zacks nahlásil značné zpomalení jeho dotazů při přechodu na verzi 8.2.4. V této verzi PostgreSQL nedokáže správně predikovat výsledek funkce COALESCE(sloupec, false) = false, a díky tomu vybraný prováděcí plán není optimální. Po modifikaci dotazu na níže uvedenou formu Pg provede korektní odhad a zvolí optimální prováděcí plán:
SELECT ...
FROM some_tab
LEFT JOIN
some_tab2
ON sometab2.col IS DISTINCT FROM true; --> join false or NULL
COALESCE používejte pouze tehdy, když chcete získat konkrétní substituci případné hodnoty NULL.
Po týdnu se objevil podobný problém - opět při přechodu na 8.2.x - Dotaz obsahoval podmínku ve tvaru:
-- vysledkem je neoptimalni plan ((COALESCE((at_type)::integer, 1) = 1) AND (COALESCE(at_language, 0::numeric) = 0::numeric)) -- ekvivalent, ktery vede k optimalnimu provadecimu planu (at_type = 1 or at_type is null) AND (at_language = 0 or at_language is null)
Z hlediska optimálního výkonu se nedoporučuje používat v podmínce JOINu ani operátor IS DISTINCT FROM.
Portování z Oracle, problémy s funkcí to_date
Peter Eisentraut si všiml nekompatibility funkce to_date mezi PostgreSQL a Oraclem.
SQL> select to_date('31-DEC-200700:00:00', 'dd-mon-yyyy hh24:mi:ss') from
dual;
TO_DATE('
---------
31-DEC-07
On PostgreSQL:
select to_date('31-DEC-200700:00:00', 'dd-mon-yyyy hh24:mi:ss');
to_date
--------------
200700-12-31
Oracle tiše ignoruje mezery ve formátu, kdežto PostgreSQL jede exaktně podle formátu.
Toto rozdílné chování je možné pokrýt vlastní funkcí, která odstraní mezery. Funkci navrhl a do konference zaslal Jon Roberts.
CREATE OR REPLACE FUNCTION fn_to_date(p_date varchar, p_format varchar)
RETURNS timestamp AS $$
SELECT to_timestamp(replace($1, ' ', ''), replace($2, ' ', ''));
$$ LANGUAGE SQL STRICT IMMUTABLE;
# select fn_to_date('31-DEC-200700:00:00', 'dd-mon-yyyy hh24:mi:ss');
fn_to_date
---------------------
2007-12-31 00:00:00
(1 row)
Identifikace a oprava nevyhovujících znaků v dumpu
Mezi verzemi 8.1 a 8.2 došlo k zásadnímu zpřísnění kontroly přípustných znaků pro dané kódování. Důvodem byla potenciální možnost SQL injektáže prostřednictvím nevalidních UTF8 znaků. Tuto kontroly mohou způsobit odmítnutí načtení dumpu novějšími verzemi. Oprava je jednoduchá. V dumpu identifikujeme vadný znak a nahradíme jej:
$ iconv -f utf-8 -t utf-8 dump.sql > /dev/null iconv: illegal input sequence at position 1000 $ head -c 1010 dump.sql | tail -c 20 | od -c 0000000 h . B u t i s n 222 t i t 0000020 h i s ? 0000024
Zdroj: http://lca2007.linux.org.au/att_data/Miniconfs(2f)PostgreSQL/attachments/professional.pdf
Pozor na kombinaci NOT IN a NULL
Uživatelé SQL se delí na nullisty a nenulisty. Jedni doporučují používat NULL, druzí jej hlasitě zavrhují. Já patřím mezi ty prvé. Null se má používat, tam kde je třeba, a potom ušetří spoustu práce a pokud někam vlítne a tropí neplechu, pak je to jasná signalizace, že něco je špatně. Tyto arguenty nonulisti nikdy nepochopí a argumentují například následujícím příkladem, kdy pro ně dochází k anomálii:
postgres=# SELECT *
FROM (VALUES(10),(20),(30)) v(a)
WHERE a IN (10, 20, NULL); --> vse je v poradku, zadna anomalie
a
----
10
20
(2 rows)
postgres=# SELECT *
FROM (VALUES(10),(20),(30)) v(a)
WHERE a NOT IN (10, 20, NULL);
a
---
(0 rows) --> anomalie, melo se zobrazit 30
O žádnou anomálii se nerovná. V NOT IN je hodnota porovnaná se všemi prvky seznamu a vždy musí být jiná. Ovšem s NULL nelze porovnávat a tudíž žádná hodnota nemůže vyhovovat této podmínce.
Rychlé určení prvních písmen v názvech
Představte si, že máte adresář se 100 000 záznamy o zákaznících a rychle chcete získat všechna písmena, kterými začínají příjmení. Přesně tato úloha je tou, kdy je korelovaný dotaz rychlejší než jakákoliv jiná varianta. Zatímco sek. čtení a agregovaná funkce znamená, že se musí zpracovat 100 000 záznamů, tak korelovaný dotaz způsobí pouze 32 přístupů do funkcionálního indexu:
SELECT *
FROM (
SELECT substring('abcdefghijklmnopqrstuvwxyzěščřžýáíé'
FROM i For 1)
FROM generate_series(1,32) g(i)
) p(znak)
WHERE EXISTS(
SELECT 1
FROM jmena
WHERE substring(jmeno from 1 for 1) = p.znak
and prihlasen
);
Verze pro podporu českého znaku CH (a taky dalších: úůňľó):
SELECT *
FROM
(
SELECT substring('abcdefghijklmnopqrstuvwxyzěščřžýáíéúůňľó' FROM i For 1) FROM generate_series(1,40) g(i) UNION SELECT 'ch'
) p(onechar)
WHERE
EXISTS( SELECT 1 FROM a_osoba WHERE lower(substring(prijmeni from 1 for char_length(p.onechar))) = p.onechar );
Nejlépe placený zaměstnanec po n-té
Má oblíbená školní úloha: sestavte dotaz, který zobrazí zaměstnance s nejvyšší mzdou v rámci oddělení (předpokládá se, že oddělení je vícero). Obyčejně se řeší pomocí derivované tabulky nebo korelovaným poddotazem. V PostgreSQL lze použít nestandardní konstrukci DISTINCT ON:
postgres=# SELECT * FROM zamestnanci ;
jmeno | prijmeni | oddeleni | mzda
-----------+-----------+----------+-------
Pavel | Stehule | 1 | 10000
Zdenek | Stehule | 1 | 9000
Vladimira | Stehulova | 2 | 9000
(3 rows)
postgres=# SELECT DISTINCT ON (oddeleni) *
FROM zamestnanci
ORDER BY oddeleni, mzda DESC;
jmeno | prijmeni | oddeleni | mzda
-----------+-----------+----------+-------
Pavel | Stehule | 1 | 10000
Vladimira | Stehulova | 2 | 9000
(2 rows)
Pozor, DISTINCT ON není přenositelné. Nicméně, pokud to není problém, tak je to další varianta, která se může někdy hodit. Podstatnou nevýhodu je, že zobrazí pouze jeden řádek z každého oddělení, tj. pokud by dva a více zaměstnanců z jednoho oddělení měli nejvyšší plat, tak dostaneme neúplný výsledek.
Další variace na téma session proměnné
Session proměnné, tak jak je známe z jiných SQL serverů PostgreSQL nepodporuje. Pokud by se dalo hovořit o podpoře, tak pouze na straně klienta a ještě pouze v psql. Což znamená, že i kvůli relativně jednoduchým příkazům musíme psát uložené procedury, kde jsou promenné k dispozici. Slabou náhradou jsou proměnné modulů, které se definují dynamicky. Jedinou podmínku je povolení daného modulu v konfiguraci. Ukázku kódu zaslal do konference general Andreas Kretschmer:
- define in your postgresql.conf:
custom_variable_classes = 'myvar'
- use within psql:
test=# set myvar.benutzer = 'foo';
SET
test=*# select * from foo;
id | name
----+------
1 | foo
2 | bar
(2 rows)
test=*# select * from foo where name=current_setting('myvar.benutzer');
id | name
----+------
1 | foo
(1 row)
test=*# set myvar.benutzer = 'none';
SET
test=*# select * from foo where name=current_setting('myvar.benutzer');
id | name
----+------
(0 rows)
Indexování funkce xpath
8.3 má integrovanou funkci xpath. Podpora XML ovšem není dotažená do konce. Tato funkce vrací jako výsledek pole xml hodnot, což je typ, který zatím není podporovaný ani GIST nebo GIN indexem. Navíc Postgres nenabízí žádnou funkci, která by tuto konverzi provedla. Nic však nebrání napsat si vlastní funkci:
CREATE OR REPLACE FUNCTION xml_list_to_int(xml[])
RETURNS int[] AS $$
SELECT ARRAY(SELECT to_number(($1[i])::text,'999999.99')::int
FROM generate_series(1, array_upper($1,1)) g(i))
$$ LANGUAGE SQL IMMUTABLE;
CREATE CAST (xml[] AS int[]) WITH FUNCTION xml_list_to_int(xml[]);
-- celočíselné pole je GISTem podporováno
CREATE INDEX fx ON foo USING
GIN((xpath('//id/text()',objednavka_v_xml)::int[]));
psql jedině s less
psql obsahuje podporu stránkování, nicméně s možnostmi less vestavěná podpora srovnatelná není. Nic Vám ale nebrání použít less přímo v psql. Doporučuji. Autor: Merlin Moncure
#do profilu export PAGER=less export LESS="-iMSx4 -RSFX -e" #do .psqlrc \timing \pset pager always
Funkce pro určení průniku polí
Zase jednou mne pobavil David Fetter. Chytře využil již jednou použitého triku (univerzální třídění pole) a navrhl funkci, která určí průnik dvou polí. Jistě se mnou souhlasíte, že je radost se na takový kód dívat.
CREATE OR REPLACE FUNCTION array_intersect(ANYARRAY, ANYARRAY)
RETURNS ANYARRAY
LANGUAGE SQL
AS $$
SELECT ARRAY(
SELECT $1[i] AS "the_intersection"
FROM generate_series(
array_lower($1,1),
array_upper($1,1)
) AS i
INTERSECT
SELECT $2[j] AS "the_intersection"
FROM generate_series(
array_lower($2,1),
array_upper($2,1)
) AS j
);
$$;
VOID funkce v jazyce SQL
SQL funkce mohou obsahovat pouze SQL příkazy. Poslední SQL příkaz určuje návratovou hodnotu a typ, který musí být shodný s deklarovaným typem funkce. Jelikož VOID je fiktivní typ, tak žádný SELECT nemůže vracet žádnou takovou hodnotu. Jedinou výjimkou z tohoto pravidla je přetypovaná hodnota NULL na VOID.
CREATE OR REPLACE FUNCTION none() RETURNS VOID AS $$ SELECT NULL::VOID; $$ LANGUAGE SQL;
Rychlý výběr následujícího řádku
Při převodu starších aplikací (pozn. ISAM aplikace zpracovávají data po řádcích) se setkáváme s požadavkem rychlého dohledání následujícího řádku řádku určeného určitou kombinací hodnot (a1, b1, c1).
Tradiční řešení:
SELECT *
FROM data
WHERE (a > a1)
OR (a = a1 AND b > b1)
OR (a = a1 AND b = b1 AND c > c1)
ORDER BY a, b, c
LIMIT 1;
Merlin Moncure připomněl jednodušší zápis používající řádkové porovnání. Kromě toho, že tento zápis je čitelnější (zvlášť pro větší počet sloupců), dovoluje efektivnější použití vícesloupcového indexu např. Data(a,b,c).
SELECT * FROM data WHERE (a, b, c) > (a1, b1, c1) ORDER BY a, b, c LIMIT 1;
Samozřejmě, že iterací v tabulce pomocí těchto dotazů degradujeme výkon SQL serveru. SQL je efektivní, pokud používáme SQL na operace s množinami. To už je vhodnější použít kurzor. Pokud ale nemůžeme změnit charakter aplikace, nemáme na výběr. Napadá mne, že by se tato technika dala použít pro stránkování.
Pomalé dotazy v uložených procedurách
Jednou za čas si v konferenci někdo postěžuje, že dotaz v uložené proceduře trvá déle než když dotaz spustí přímo, a zda-li je to protože použil uložené procedury. Odpověď je relativně jednoduchá. Důvod je v před připravených dotazech (prepared statements), kdy se dotaz analyzuje ještě dříve, než dojde k nahrazení parametrů skutečnými hodnotami. Díky tomu je dotaz mnohem bezpečnější (nemůže dojít k SQL injektáži), ale nemusí být optimální - optimalizátor pracuje naslepo. Tento problém se vyskytuje výjimečně, řešením je použít dynamický dotaz. A s tím je spojeno větší úsilí s kontrolou parametrů. Při hledání problémů může pomoci klasický příkaz EXPLAIN ANALYZE, kterým dokážeme analyzovat provádění i před připravených dotazů.
t2=# PREPARE pp(integer) AS SELECT count(*) FROM foo WHERE a BETWEEN $1 + 100 AND $1 + 200;
t2=# EXPLAIN ANALYZE execute pp(100);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Aggregate (cost=438.60..438.61 rows=1 width=0) (actual time=16.149..16.150 rows=1 loops=1)
-> Bitmap Heap Scan on foo (cost=13.39..437.35 rows=500 width=0) (actual time=1.391..5.458 rows=1033 loops=1)
Recheck Cond: ((a >= ($1 + 100)) AND (a <= ($1 + 200)))
-> Bitmap Index Scan on fx (cost=0.00..13.26 rows=500 width=0) (actual time=1.131..1.131 rows=1033 loops=1)
Index Cond: ((a >= ($1 + 100)) AND (a <= ($1 + 200)))
Total runtime: 16.340 ms
(6 rows)
t2=# EXPLAIN ANALYZE SELECT count(*) FROM foo WHERE a BETWEEN 100 + 100 AND 100 + 200;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Aggregate (cost=432.21..432.22 rows=1 width=0) (actual time=15.930..15.932 rows=1 loops=1)
-> Bitmap Heap Scan on foo (cost=22.21..429.78 rows=971 width=0) (actual time=1.179..13.338 rows=1033 loops=1)
Recheck Cond: ((a >= 200) AND (a <= 300))
-> Bitmap Index Scan on fx (cost=0.00..21.97 rows=971 width=0) (actual time=0.917..0.917 rows=1033 loops=1)
Index Cond: ((a >= 200) AND (a <= 300))
Total runtime: 16.058 ms
(6 rows)
V příkladu se prováděcí plány téměř neliší, lze tedy v klidu použít před připravený příkaz.
Tento problém by měl být vyřešen v PostgreSQL 9.2.
Pomalý DISTINCT
Stávající operace DISTINCT je v PostgreSQL si vynucuje setřídění tabulku a eliminaci duplicit. Pokud pro eliminaci duplicit použijeme GROUP BY (pokud to je možné) zrychlíme několikanásobně dotaz. Důvodem je efektivnější implementace agregačních funkcí (HashAggregate).
postgres=# select count(*) from (select distinct i from g) a; count ------- 10001 (1 row) Time: 1563,109 ms postgres=# select count(*) from (select i from g group by i) a; count ------- 10001 (1 row) Time: 594,481 ms
Počínaje 8.4 DISTINCT, UNION, INTERSECT, EXCEPT používá hašování, takže výše zmíněný trik nepoužívejte.
Odvození kvartálu z intervalu
Aritmetika nad typem interval je docela alchymie. V typu interval se rozlišuje mezi dny, měsíci, roky. Vstupní hodnota se nenormalizuje, zůstává v zadaných jednotkách (tj. pokud zadám 200 dnů, tak vnitřně se tato hodnota uloží jako 0 let, 0 měsíců a 200 dnů). Na druhou stranu existují funkce, např. pro určení kvartálu, které operují pouze nad jednou položkou typu interval (pro určení kvartálu je podstatný počet měsíců). Tudíž, přestože 200 dnů by odpovídalo třetímu kvartálu, EXTRACT QUARTER Vám bude tvrdit, že se jedná o první kvartál. Normalizaci zadané hodnoty provádí funkce justify_interval (např. interval 200 dní se správně rozdělí na 0 let, 6 měsíců 20 dní). Z takto uložené hodnoty již lze korektně odečíst kvartál:
postgres=# SELECT interval '200 days', EXCTRACT(QUARTER FROM interval '300 days');
interval | date_part
----------+-----------
200 days | 1
(1 row)
postgres=# SELECT justify_interval(interval '200 days'),
EXTRACT(QUARTER FROM justify_interval('200 days'));
justify_interval | date_part
------------------+-----------
6 mons 20 days | 3
(1 row)
Autor Tom Lane v konferenci pgsql-bugs
Zobrazení náhodného řádku
Hubert Lubaczewski se ve svém blogu řeší otázku náhodného výběru z databáze. Při auditu databáze jednoho webshopu jsem narazil na poměrně obvyklé řešení ORDER BY random() LIMIT 1, které ovšem není vhodné pro větší tabulky - tuto funkci z principu nelze indexovat. Tudíž každý dotaz způsobí: a) sekvenční čtení tabulky (což samo o sobě až tak zlé není), b) řazení celé tabulky. Další metoda WHERE id >= (random()*C+1)::int LIMIT 1 je mnohem rychlejší, nicméně v případě děr v číselné řadě id preferuje první následující řádky po výpadku v posloupnosti časové řady (což v řadě případů nevadí). Mě se osvědčilo následující řešení, jehož jedinou nevýhodou je reálná (i když velice nízká) pravděpodobnost, že není vrácen žádný řádek, a příkaz je třeba opakovat.
SELECT id, ...
FROM data
WHERE id = ANY(ARRAY(
SELECT (random()*max_id)::int
FROM generate_series(1,20)))
LIMIT 1;
-- max_id nahradte cislem vetsim nez je maximalni id v tabulce + rezerva 20%
-- predpoklad, id je primarni klic tab. data