Moderní SQL
Autor: Pavel Stěhule 2016
SQL dnes už umí více než spojování tabulek a poddotazy. Prostřednictvím analytických funkcí a rekurzivních dotazů se SQL dostává za rámec relačního modelu. To je schopnost, která některým teoretikům vadila, nám ale může často pomoci s efektivním řešením relativně běžných problémů.
Novější (cca 10 let staré) techniky se pokusím demonstrovat na mém oblíbeném příkladu - nalezení N největších prvků z M skupin. Například - tři nejlépe hodnocené produkty z každé kategorie, tři největší obce z každého okresu, .. Tato úloha je zajímavá v tom, že čistě relačním aparátem není jednoduše řešitelná (složitost je relativní - v každém případě není efektivně řešitelná). Jedná se ale o relativně častou úlohu, na které se lze mnohé naučit.
Testovací data
Všechny příklady testuji v databázi obce.
postgres=# \d obce
View "public.obce"
┌────────────────┬───────────────────────┬───────────┐
│ Column │ Type │ Modifiers │
╞════════════════╪═══════════════════════╪═══════════╡
│ id │ integer │ │
│ okres_id │ character varying(6) │ │
│ nazev │ character varying(40) │ │
│ pocet_obyvatel │ integer │ │
└────────────────┴───────────────────────┴───────────┘
postgres=# \sv obce
CREATE OR REPLACE VIEW public.obce AS
SELECT obce2.id,
obce2.okres_id,
obce2.nazev,
obce2.pocet_muzu + obce2.pocet_zen AS pocet_obyvatel
FROM obce2
postgres=# \d obce2
Table "public.obce2"
┌────────────┬───────────────────────┬───────────────────────────────────────────────────┐
│ Column │ Type │ Modifiers │
╞════════════╪═══════════════════════╪═══════════════════════════════════════════════════╡
│ id │ integer │ not null default nextval('obce_id_seq'::regclass) │
│ okres_id │ character varying(6) │ │
│ nazev │ character varying(40) │ │
│ pocet_muzu │ integer │ │
│ pocet_zen │ integer │ │
│ vek_muzu │ numeric(3,1) │ │
│ vek_zen │ numeric(3,1) │ │
└────────────┴───────────────────────┴───────────────────────────────────────────────────┘
Indexes:
"_obce_pkey" PRIMARY KEY, btree (id)
"obce_okres_id_expr_idx" btree (okres_id, (pocet_muzu + pocet_zen) DESC)
Foreign-key constraints:
"obce_okres_id_fk" FOREIGN KEY (okres_id) REFERENCES okresy(id)
postgres=# SELECT * FROM obce;
┌──────┬──────────┬───────────────────────────────────────┬────────────────┐
│ id │ okres_id │ nazev │ pocet_obyvatel │
╞══════╪══════════╪═══════════════════════════════════════╪════════════════╡
│ 1 │ CZ0100 │ Praha │ 1249026 │
│ 2 │ CZ0201 │ Benešov │ 16382 │
│ 3 │ CZ0201 │ Bernartice │ 223 │
│ 4 │ CZ0201 │ Bílkovice │ 182 │
│ 5 │ CZ0201 │ Blažejovice │ 100 │
│ 6 │ CZ0201 │ Borovnice │ 76 │
│ 7 │ CZ0201 │ Bukovany │ 736 │
│ 8 │ CZ0201 │ Bystřice │ 4220 │
postgres=# \d okresy
Table "public.okresy"
┌────────┬──────┬───────────┐
│ Column │ Type │ Modifiers │
╞════════╪══════╪═══════════╡
│ id │ text │ not null │
│ nazev │ text │ │
└────────┴──────┴───────────┘
Indexes:
"okresy_pkey" PRIMARY KEY, btree (id)
"okresy_nuts_idx" UNIQUE, btree (id)
Referenced by:
TABLE "obce2" CONSTRAINT "obce_okres_id_fk" FOREIGN KEY (okres_id) REFERENCES okresy(id)
postgres=# SELECT * FROM okresy;
┌────────┬─────────────────────┐
│ id │ nazev │
╞════════╪═════════════════════╡
│ CZ0201 │ Benešov │
│ CZ0202 │ Beroun │
│ CZ0203 │ Kladno │
│ CZ0204 │ Kolín │
│ CZ0205 │ Kutná Hora │
│ CZ0206 │ Mělník │
│ CZ0207 │ Mladá Boleslav │
│ CZ0208 │ Nymburk │
│ CZ020B │ Příbram │
│ CZ020C │ Rakovník │
│ CZ0311 │ České Budějovice │
│ CZ0312 │ Český Krumlov │
│ CZ0313 │ Jindřichův Hradec │
Dotaz s korelovaným poddotazem s klauzulí LIMIT
Využití korelovaného dotazu s klauzulí LIMIT je asi nejklasičtější řešení úlohy. Vazba mezi vnějším dotazem a
poddotazem umožňuje zafixovat jeden nebo více atributů v poddotazu a na takto omezené množině spočítat
agregační funkci nebo vzít prvních N hodnot:
SELECT *
FROM obce ext
WHERE pocet_obyvatel = (SELECT max(pocet_obyvatel)
FROM obce o
WHERE o.okres_id = ext.okres_id);
případně:
SELECT *
FROM obce ext
WHERE pocet_obyvatel IN (SELECT pocet_obyvatel
FROM obce o
WHERE o.okres_id = ext.okres_id
ORDER BY pocet_obyvatel DESC
LIMIT 3);
Tyto dotazy PostgreSQL řeší pomocí nested loopu (cyklu), kdy pro každý řádek vnějšího dotazu se provede vnitřní dotaz. V tomto případě to znamená 6200 dotazů do indexu.
postgres=# EXPLAIN ANALYZE SELECT *
FROM obce ext
WHERE (okres_id,pocet_obyvatel) IN (SELECT o.okres_id, pocet_obyvatel
FROM obce o
WHERE o.okres_id = ext.okres_id
ORDER BY pocet_obyvatel DESC
LIMIT 3);
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Seq Scan on obce2 (cost=0.00..12977.53 rows=3125 width=25) (actual time=0.091..111.277 rows=227 loops=1) │
│ Filter: (SubPlan 1) │
│ Rows Removed by Filter: 6023 │
│ SubPlan 1 │
│ -> Limit (cost=0.28..3.81 rows=3 width=11) (actual time=0.013..0.015 rows=3 loops=6250) │
│ -> Index Scan using obce_okres_id_expr_idx on obce2 obce2_1 (cost=0.28..95.43 rows=81 width=11) (actual time=0.013..0.014 rows=3 loops=6250) │
│ Index Cond: ((okres_id)::text = (obce2.okres_id)::text) │
│ Planning time: 0.646 ms │
│ Execution time: 111.387 ms │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
V tomto případě to není kdoví jak tragické, ale jsou tu i další (a lepší nebo horší možnosti).
Při hledání maxim ze skupiny můžeme použít více hodnotové porovnání a vyhnout se tak korelovanému poddotazu:
SELECT *
FROM obce
WHERE (okres_id, pocet_obyvatel) IN (SELECT okres_id, max(pocet_obyvatel)
FROM obce
GROUP BY okres_id);
postgres=# EXPLAIN ANALYZE SELECT *
FROM obce
WHERE (okres_id, pocet_obyvatel) IN (SELECT okres_id, max(pocet_obyvatel)
FROM obce
GROUP BY okres_id);
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Hash Semi Join (cost=171.07..329.88 rows=234 width=25) (actual time=8.336..13.275 rows=77 loops=1) │
│ Hash Cond: (((obce2.okres_id)::text = (obce2_1.okres_id)::text) AND ((obce2.pocet_muzu + obce2.pocet_zen) = (max((obce2_1.pocet_muzu + obce2_1.pocet_zen))))) │
│ -> Seq Scan on obce2 (cost=0.00..121.50 rows=6250 width=29) (actual time=0.020..1.421 rows=6250 loops=1) │
│ -> Hash (cost=169.92..169.92 rows=77 width=11) (actual time=8.291..8.291 rows=77 loops=1) │
│ Buckets: 1024 Batches: 1 Memory Usage: 12kB │
│ -> HashAggregate (cost=168.38..169.15 rows=77 width=11) (actual time=8.202..8.246 rows=77 loops=1) │
│ Group Key: obce2_1.okres_id │
│ -> Seq Scan on obce2 obce2_1 (cost=0.00..121.50 rows=6250 width=15) (actual time=0.005..1.617 rows=6250 loops=1) │
│ Planning time: 1.265 ms │
│ Execution time: 13.412 ms │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
versus
postgres=# EXPLAIN ANALYZE SELECT *
FROM obce ext
WHERE pocet_obyvatel = (SELECT max(pocet_obyvatel)
FROM obce o
WHERE o.okres_id = ext.okres_id);
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Seq Scan on obce2 (cost=0.00..9338.10 rows=31 width=25) (actual time=0.094..113.746 rows=77 loops=1) │
│ Filter: ((pocet_muzu + pocet_zen) = (SubPlan 2)) │
│ Rows Removed by Filter: 6173 │
│ SubPlan 2 │
│ -> Result (cost=1.46..1.47 rows=1 width=4) (actual time=0.017..0.017 rows=1 loops=6250) │
│ InitPlan 1 (returns $1) │
│ -> Limit (cost=0.28..1.46 rows=1 width=4) (actual time=0.016..0.016 rows=1 loops=6250) │
│ -> Index Scan using obce_okres_id_expr_idx on obce2 obce2_1 (cost=0.28..95.63 rows=81 width=4) (actual time=0.015..0.015 rows=1 loops=6250) │
│ Index Cond: (((okres_id)::text = (obce2.okres_id)::text) AND ((pocet_muzu + pocet_zen) IS NOT NULL)) │
│ Planning time: 0.837 ms │
│ Execution time: 113.834 ms │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Je mnohem efektivnější jednou spočítat agregaci, a pak join, než 6200x hledat maximum.
Bohužel pro hledání N prvků ze skupiny nic podobného nemáme (pokud neznáme window funkce, o kterých se zmíním později). Aby výpočet byl efektivní, tak bychom potřebovali
iterovat přes okresy, jelikož okresy mají cca 100x méně řádků. Starší SQL nám ale nic takového nedovolí. V novějším SQL máme LATERAL join. Omezení, které mne nyní
tlačí, je omezení na tvar výsledku korelovaného poddotazů - výsledkem může být pouze jedna hodnota:
postgres=# SELECT *, (SELECT * FROM obce WHERE okresy.id = okres_id ORDER BY pocet_obyvatel DESC LIMIT 3) FROM okresy;
ERROR: subquery must return only one column
LINE 1: SELECT *, (SELECT * FROM obce WHERE okresy.id = okres_id ORD...
^
V PostgreSQL mohu toto omezení obejít pomocí pole kompozitních hodnot. Poznámka - V Postgresu můžete použít sloupec stejného jména jako má tabulka ve smyslu kompozitní hodnoty složené ze všech sloupců tabulky. Následující dotaz projde bez chyby:
SELECT *, ARRAY(SELECT obce
FROM obce
WHERE okresy.id = okres_id
ORDER BY pocet_obyvatel DESC
LIMIT 3)
FROM okresy;
┌────────┬─────────────────────┬──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ id │ nazev │ array │
╞════════╪═════════════════════╪══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ CZ0201 │ Benešov │ {"(2,CZ0201,Benešov,16382)","(104,CZ0201,Vlašim,12024)","(101,CZ0201,\"Týnec nad Sázavou\",5463)"} │
│ CZ0202 │ Beroun │ {"(117,CZ0202,Beroun,18616)","(145,CZ0202,\"Králův Dvůr\",6816)","(128,CZ0202,Hořovice,6751)"} │
│ CZ0203 │ Kladno │ {"(234,CZ0203,Kladno,69938)","(270,CZ0203,Slaný,15091)","(274,CZ0203,Stochov,5847)"} │
│ CZ0204 │ Kolín │ {"(326,CZ0204,Kolín,30935)","(310,CZ0204,\"Český Brod\",6842)","(348,CZ0204,Pečky,4501)"} │
│ CZ0205 │ Kutná Hora │ {"(423,CZ0205,\"Kutná Hora\",21425)","(399,CZ0205,Čáslav,10120)","(475,CZ0205,\"Zruč nad Sázavou\",4962)"} │
│ CZ0206 │ Mělník │ {"(515,CZ0206,Mělník,19173)","(504,CZ0206,\"Kralupy nad Vltavou\",17635)","(520,CZ0206,Neratovice,16494)"} │
│ CZ0207 │ Mladá Boleslav │ {"(613,CZ0207,\"Mladá Boleslav\",44750)","(614,CZ0207,\"Mnichovo Hradiště\",8507)","(550,CZ0207,\"Benátky nad Jizerou\",7362)"} │
│ CZ0208 │ Nymburk │ {"(716,CZ0208,Nymburk,14568)","(727,CZ0208,Poděbrady,13838)","(713,CZ0208,Milovice,9145)"} │
Nicméně výsledek je ve tvaru, který uživatelé nedocení. S použitím funkce unnest
a trochou magie (přístup k atributu kompozitní hodnoty) se dostanu k výsledku:
SELECT nazev, (o).nazev, (o).pocet_obyvatel
FROM (SELECT nazev, unnest(ARRAY(SELECT obce
FROM obce
WHERE okresy.id = okres_id
ORDER BY pocet_obyvatel DESC
LIMIT 3)) AS o
FROM okresy) s;
postgres=# EXPLAIN ANALYZE SELECT nazev, (o).nazev, (o).pocet_obyvatel
FROM (SELECT nazev, unnest(ARRAY(SELECT obce
FROM obce
WHERE okresy.id = okres_id
ORDER BY pocet_obyvatel DESC
LIMIT 3)) AS o
FROM okresy) s;
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Subquery Scan on s (cost=0.00..410.75 rows=7700 width=112) (actual time=0.132..3.649 rows=227 loops=1) │
│ -> Seq Scan on okresy (cost=0.00..333.75 rows=7700 width=42) (actual time=0.125..3.424 rows=227 loops=1) │
│ SubPlan 1 │
│ -> Limit (cost=0.28..3.81 rows=3 width=36) (actual time=0.027..0.033 rows=3 loops=77) │
│ -> Index Scan using obce_okres_id_expr_idx on obce2 (cost=0.28..95.63 rows=81 width=36) (actual time=0.025..0.030 rows=3 loops=77) │
│ Index Cond: (okresy.id = (okres_id)::text) │
│ Planning time: 0.671 ms │
│ Execution time: 3.779 ms │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Dotaz je fantasticky rychlý, ale cesta k němu je relativně obtížná. Vyžaduje trochu odvahy a znalostí tmavých zákoutí Postgresu.
Dotaz s window funkcí
I když jsou window funkce relativně dlouho ve standardu, a už pár let i v Postgresu (PostgreSQL 8.4 - rok 2009), tak nepatří mezi často používané. Což je škoda, protože díky
nim mohou být dotazy velmi názorné a většinou i docela rychlé. Window funkce jsou funkce, které se počítají nad množinou (podobně jako agregační
funkce), ale na rozdíl od agregačních funkcí může být hodnota window funkce pro každý prvek množiny jiná (u agregačních funkcí je výsledek společný
pro všechny prvky množiny). Je tu několik paralel s agregačními funkcemi, ale možná ještě více rozdílů. Window funkce neredukují počet řádků, a také
pro window funkce nelze použít klauzuli HAVING.
Mou oblíbenou window funkcí je funkce rank(), která vrací pořadí hodnoty v množině:
postgres=# SELECT *, rank() OVER (PARTITION BY okres_id ORDER BY pocet_obyvatel DESC) FROM obce; ┌──────┬──────────┬───────────────────────────────────────┬────────────────┬──────┐ │ id │ okres_id │ nazev │ pocet_obyvatel │ rank │ ╞══════╪══════════╪═══════════════════════════════════════╪════════════════╪══════╡ │ 1 │ CZ0100 │ Praha │ 1249026 │ 1 │ │ 918 │ CZ020A │ Roztoky │ 7538 │ 1 │ │ 882 │ CZ020A │ Hostivice │ 7275 │ 2 │ │ 890 │ CZ020A │ Jesenice │ 6284 │ 3 │ │ 869 │ CZ020A │ Černošice │ 6179 │ 4 │ │ 919 │ CZ020A │ Rudná │ 4494 │ 5 │ │ 906 │ CZ020A │ Mníšek pod Brdy │ 4442 │ 6 │
Pro filtrování musím použít derivovaný poddotaz - tj SELECT FROM SELECT
SELECT *
FROM (SELECT *, rank() OVER (PARTITION BY okres_id ORDER BY pocet_obyvatel DESC)
FROM obce) s
WHERE rank <= 3;
┌──────┬──────────┬──────────────────────────────────┬────────────────┬──────┐
│ id │ okres_id │ nazev │ pocet_obyvatel │ rank │
╞══════╪══════════╪══════════════════════════════════╪════════════════╪══════╡
│ 1 │ CZ0100 │ Praha │ 1249026 │ 1 │
│ 918 │ CZ020A │ Roztoky │ 7538 │ 1 │
│ 882 │ CZ020A │ Hostivice │ 7275 │ 2 │
│ 890 │ CZ020A │ Jesenice │ 6284 │ 3 │
│ 1025 │ CZ020B │ Příbram │ 34217 │ 1 │
│ 958 │ CZ020B │ Dobříš │ 8464 │ 2 │
│ 1033 │ CZ020B │ Sedlčany │ 7643 │ 3 │
│ 1120 │ CZ020C │ Rakovník │ 16503 │ 1 │
│ 1106 │ CZ020C │ Nové Strašecí │ 5198 │ 2 │
│ 1096 │ CZ020C │ Lužná │ 1802 │ 3 │
│ 2 │ CZ0201 │ Benešov │ 16382 │ 1 │
│ 104 │ CZ0201 │ Vlašim │ 12024 │ 2 │
│ 101 │ CZ0201 │ Týnec nad Sázavou │ 5463 │ 3 │
│ 117 │ CZ0202 │ Beroun │ 18616 │ 1 │
postgres=# EXPLAIN ANALYZE SELECT *
FROM (SELECT *, rank() OVER (PARTITION BY okres_id ORDER BY pocet_obyvatel DESC)
FROM obce) s
WHERE rank <= 3;
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Subquery Scan on s (cost=0.28..556.29 rows=2083 width=33) (actual time=0.038..18.265 rows=227 loops=1) │
│ Filter: (s.rank <= 3) │
│ Rows Removed by Filter: 6023 │
│ -> WindowAgg (cost=0.28..478.17 rows=6250 width=33) (actual time=0.035..16.824 rows=6250 loops=1) │
│ -> Index Scan using obce_okres_id_expr_idx on obce2 (cost=0.28..353.17 rows=6250 width=25) (actual time=0.027..6.304 rows=6250 loops=1) │
│ Planning time: 0.242 ms │
│ Execution time: 18.342 ms │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Výsledek je docela rychlý (i když nikoliv maximálně rychlý, PostgreSQL nedokáže včas zaříznout výpočet window funkce) na to, jak jednoduše jsem se k němu dostal. S window funkcemi si lze hrát dlouho - můžeme počítat klouzavé agregační funkce, můžeme počítat kumulativní funkce. Zájemnce o podrobnější studium odkáži na <a href="https://www.postgresql.org/docs/9.5/static/tutorial-window.html">dokumentaci</a>.
Common Table Expression
Podporou rekurzivních dotazů SQL vybočuje z relačního modelu (v PostgreSQL 8.4). Nicméně jakmile máme rekurzi v datech (organizační struktura, kusovník), tak se samozřejmě hodí. Začíná se výpočtem nulté množiny. Vypočtu spojení této množiny a množiny nad kterou rekurzivní dotaz počítám. Pokud výsledek není prázdný, tak jej použiji znovu pro spojení s primární množinou. Výsledkem je sjednocení všech výsledků.
Chvíli mi trvalo, než jsem přišel na způsob, jak CTE vnutit do této úlohy (také odladění dotazu trvalo nejdéle)- pro výpočet N největších hodnot z M skupin se CTE vůbec nehodí. Tato úloha je ale perfektní pro procvičení a naučení CTE:
Nultou množinou budou největší obce z okresů. V CTE nelze použít klauzule ORDER BY a LIMIT - je proto nutné spočítat následující
menší obci jiným způsobem (v poli si udržuji seznam největších obcí, které pak vylučuji z výpočtu agregace):
WITH RECURSIVE x AS
(-- hledám největší obce z okresů
SELECT *, ARRAY[id] AS ids
FROM obce
WHERE (okres_id, pocet_obyvatel) IN (SELECT okres_id, max(pocet_obyvatel)
FROM obce
GROUP BY okres_id)
UNION ALL
-- hledám následující menší obce z okresů
SELECT o.*, o.id || ids
FROM obce o
JOIN x
ON x.okres_id = o.okres_id
AND o.id <> ALL(x.ids)
AND cardinality(x.ids) < 3
AND o.pocet_obyvatel = (SELECT max(pocet_obyvatel)
FROM obce o2
WHERE o2.okres_id = x.okres_id
AND o2.id <> ALL(x.ids))
)
SELECT * FROM x;
K mému překvapení je tento dotaz docela rychlý:
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ╞═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡ │ CTE Scan on x (cost=18418.28..18425.36 rows=354 width=166) (actual time=8.111..20.104 rows=227 loops=1) │ │ CTE x │ │ -> Recursive Union (cost=171.07..18418.28 rows=354 width=57) (actual time=8.106..19.753 rows=227 loops=1) │ │ -> Hash Semi Join (cost=171.07..329.88 rows=234 width=57) (actual time=8.103..12.652 rows=77 loops=1) │ │ Hash Cond: (((obce2_1.okres_id)::text = (obce2_2.okres_id)::text) AND ((obce2_1.pocet_muzu + obce2_1.pocet_zen) = (max((obce2_2.pocet_muzu + obce2_2.pocet_zen))))) │ │ -> Seq Scan on obce2 obce2_1 (cost=0.00..121.50 rows=6250 width=29) (actual time=0.019..1.289 rows=6250 loops=1) │ │ -> Hash (cost=169.92..169.92 rows=77 width=11) (actual time=8.055..8.055 rows=77 loops=1) │ │ Buckets: 1024 Batches: 1 Memory Usage: 12kB │ │ -> HashAggregate (cost=168.38..169.15 rows=77 width=11) (actual time=7.977..8.019 rows=77 loops=1) │ │ Group Key: obce2_2.okres_id │ │ -> Seq Scan on obce2 obce2_2 (cost=0.00..121.50 rows=6250 width=15) (actual time=0.005..1.529 rows=6250 loops=1) │ │ -> Nested Loop (cost=1.76..1808.13 rows=12 width=57) (actual time=0.081..2.262 rows=50 loops=3) │ │ -> WorkTable Scan on x x_1 (cost=0.00..58.50 rows=780 width=60) (actual time=0.011..0.068 rows=51 loops=3) │ │ Filter: (cardinality(ids) < 3) │ │ Rows Removed by Filter: 25 │ │ -> Index Scan using obce_expr_okres_id_idx on obce2 obce2_3 (cost=1.76..2.23 rows=1 width=29) (actual time=0.006..0.007 rows=1 loops=152) │ │ Index Cond: (((pocet_muzu + pocet_zen) = (SubPlan 2)) AND ((okres_id)::text = (x_1.okres_id)::text)) │ │ Filter: (id <> ALL (x_1.ids)) │ │ SubPlan 2 │ │ -> Result (cost=1.47..1.48 rows=1 width=4) (actual time=0.028..0.029 rows=1 loops=152) │ │ InitPlan 1 (returns $3) │ │ -> Limit (cost=0.28..1.47 rows=1 width=4) (actual time=0.026..0.027 rows=1 loops=152) │ │ -> Index Scan using obce_okres_id_expr_idx on obce2 (cost=0.28..96.64 rows=81 width=4) (actual time=0.025..0.025 rows=1 loops=152) │ │ Index Cond: (((okres_id)::text = (x_1.okres_id)::text) AND ((pocet_muzu + pocet_zen) IS NOT NULL)) │ │ Filter: (id <> ALL (x_1.ids)) │ │ Rows Removed by Filter: 1 │ │ SubPlan 2 │ │ -> Result (cost=1.47..1.48 rows=1 width=4) (actual time=0.028..0.029 rows=1 loops=152) │ │ InitPlan 1 (returns $3) │ │ -> Limit (cost=0.28..1.47 rows=1 width=4) (actual time=0.026..0.027 rows=1 loops=152) │ │ -> Index Scan using obce_okres_id_expr_idx on obce2 (cost=0.28..96.64 rows=81 width=4) (actual time=0.025..0.025 rows=1 loops=152) │ │ Index Cond: (((okres_id)::text = (x_1.okres_id)::text) AND ((pocet_muzu + pocet_zen) IS NOT NULL)) │ │ Filter: (id <> ALL (x_1.ids)) │ │ Rows Removed by Filter: 1 │ │ Planning time: 2.883 ms │ │ Execution time: 20.430 ms │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Ovšem snadno se zde udělá chyba - docela obtížně se tyto dotazy ladí, a stačí trochu jinak referencovat a při stejném správném výsledku mít 10x pomalejší dotaz.
Dotaz s LATERAL joinem
LATERAL join je v PostgreSQL relativně horkou novinkou (PostgreSQL 9.3 - rok 2013). Umožňuje elegantně (a standardní cestou) vyřešit problémy, které jsem měl při psaní
dotazu s korelovaným poddotazem, když jsem chtěl iterovat přes okresy. Při LATERAL spojení se pro každý řádek levé relace provede
poddotaz, jehož výsledek se páruje s řádkem, pro který se poddotaz počítal. Je to korelované (vázané) spojení relací.
SELECT *
FROM okresy,
LATERAL (SELECT *
FROM obce
WHERE okresy.id = obce.okres_id
ORDER BY pocet_obyvatel DESC
LIMIT 3) s;
┌────────┬─────────────────────┬──────┬──────────┬──────────────────────────────────┬────────────────┐
│ id │ nazev │ id │ okres_id │ nazev │ pocet_obyvatel │
╞════════╪═════════════════════╪══════╪══════════╪══════════════════════════════════╪════════════════╡
│ CZ0201 │ Benešov │ 2 │ CZ0201 │ Benešov │ 16382 │
│ CZ0201 │ Benešov │ 104 │ CZ0201 │ Vlašim │ 12024 │
│ CZ0201 │ Benešov │ 101 │ CZ0201 │ Týnec nad Sázavou │ 5463 │
│ CZ0202 │ Beroun │ 117 │ CZ0202 │ Beroun │ 18616 │
│ CZ0202 │ Beroun │ 145 │ CZ0202 │ Králův Dvůr │ 6816 │
│ CZ0202 │ Beroun │ 128 │ CZ0202 │ Hořovice │ 6751 │
│ CZ0203 │ Kladno │ 234 │ CZ0203 │ Kladno │ 69938 │
│ CZ0203 │ Kladno │ 270 │ CZ0203 │ Slaný │ 15091 │
│ CZ0203 │ Kladno │ 274 │ CZ0203 │ Stochov │ 5847 │
│ CZ0204 │ Kolín │ 326 │ CZ0204 │ Kolín │ 30935 │
│ CZ0204 │ Kolín │ 310 │ CZ0204 │ Český Brod │ 6842 │
Dotaz je velice rychlý:
postgres=# EXPLAIN ANALYZE SELECT *
FROM okresy,
LATERAL (SELECT *
FROM obce
WHERE okresy.id = obce.okres_id
ORDER BY pocet_obyvatel DESC
LIMIT 3) s;
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Nested Loop (cost=0.28..299.49 rows=231 width=42) (actual time=0.086..2.346 rows=227 loops=1) │
│ -> Seq Scan on okresy (cost=0.00..1.77 rows=77 width=17) (actual time=0.019..0.047 rows=77 loops=1) │
│ -> Limit (cost=0.28..3.81 rows=3 width=25) (actual time=0.023..0.027 rows=3 loops=77) │
│ -> Index Scan using obce_okres_id_expr_idx on obce2 (cost=0.28..95.43 rows=81 width=25) (actual time=0.022..0.025 rows=3 loops=77) │
│ Index Cond: (okresy.id = (okres_id)::text) │
│ Planning time: 0.659 ms │
│ Execution time: 2.459 ms │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Navíc zápis je velmi expresivní, jednoduchý (téměř zde nelze udělat chybu).
Závěr
Na pár příkladech jsem se snažil demonstrovat výhody používání novějších funkcí v SQL (novějších než ANSI SQL 92).
Pokud by mi šlo o názornost a jednoduchost, tak bych patrně silně preferoval použití window funkcí. Pokud
bych musel řešit výkon, nebo kladl důraz na výkon, pak v řadě případů mohu použít LATERAL join.