Zobrazení zdroje stránky Moderní SQL

<i>Autor: Pavel Stěhule 2016</i>

[[Category:Články]]

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.

<h2>Testovací data</h2>
Všechny příklady testuji v databázi obce.
<pre>
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   │    
</pre>

<h2>Dotaz s korelovaným poddotazem s klauzulí <code>LIMIT</code></h2>

Využití korelovaného dotazu s klauzulí <code>LIMIT</code> 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:
<pre>
SELECT *
   FROM obce ext
  WHERE pocet_obyvatel = (SELECT max(pocet_obyvatel)
                             FROM obce o
                            WHERE o.okres_id = ext.okres_id);
</pre>
případně:
<pre>
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);
</pre>
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.
<pre>
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                                                                                                                               │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
</pre>
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:
<pre>
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                                                                                                                                       │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
</pre>
versus
<pre>
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                                                                                                                                      │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
</pre>

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 <code>LATERAL</code> join. Omezení, které mne nyní
tlačí, je omezení na tvar výsledku korelovaného poddotazů - výsledkem může být pouze jedna hodnota:
<pre>
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...
                  ^
</pre>
<p>V PostgreSQL mohu toto omezení obejít pomocí pole [https://www.postgresql.org/docs/9.5/static/rowtypes.html 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:
<pre>
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)"}                                           │
</pre>
Nicméně výsledek je ve tvaru, který uživatelé nedocení. S použitím funkce [https://www.postgresql.org/docs/current/static/functions-array.html <code>unnest</code>] 
a trochou magie (přístup k atributu kompozitní hodnoty) se dostanu k výsledku:
<pre>
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                                                                                                                             │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
</pre>
Dotaz je fantasticky rychlý, ale cesta k němu je relativně obtížná. Vyžaduje trochu odvahy a znalostí tmavých zákoutí Postgresu.

<h2>Dotaz s window funkcí</h2>

I když jsou [https://www.postgresql.org/docs/9.5/static/sql-select.html 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 <code>HAVING</code>.

Mou oblíbenou window funkcí je funkce [https://blog.jooq.org/2014/08/12/the-difference-between-row_number-rank-and-dense_rank/ <code>rank()</code>], která vrací pořadí hodnoty v množině:
<pre>
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 │
</pre>
Pro filtrování musím použít derivovaný poddotaz - tj <code>SELECT FROM SELECT</code>
<pre>
SELECT *
   FROM (SELECT *, rank() OVER (PARTITION BY okres_id ORDER BY pocet_obyvatel DESC) 
            FROM obce) s
  WHERE rank &lt;= 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 &lt;= 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 &lt;= 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                                                                                                                         │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
</pre>
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>.

<h2>Common Table Expression</h2>

Podporou [https://www.postgresql.org/docs/9.5/static/queries-with.html 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 <code>ORDER BY</code> a <code>LIMIT</code> - 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):
<pre>
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 &lt;&gt; ALL(x.ids)
               AND cardinality(x.ids) &lt; 3
               AND o.pocet_obyvatel = (SELECT max(pocet_obyvatel)
                                          FROM obce o2
                                         WHERE o2.okres_id = x.okres_id
                                           AND o2.id &lt;&gt; ALL(x.ids))
   )
  SELECT * FROM x;
</pre>  
K mému překvapení je tento dotaz docela rychlý:
<pre>
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                                     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) &lt; 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 &lt;&gt; 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 &lt;&gt; 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 &lt;&gt; ALL (x_1.ids))                                                                                                           │
│                                             Rows Removed by Filter: 1                                                                                                               │
│ Planning time: 2.883 ms                                                                                                                                                             │
│ Execution time: 20.430 ms                                                                                                                                                           │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
</pre>
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.

<h2>Dotaz s <code>LATERAL</code> joinem</h2>

<code>LATERAL</code> 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 <code>LATERAL</code> 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í.
<pre>
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 │
</pre>
Dotaz je velice rychlý:
<pre>
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                                                                                                                     │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
</pre>

Navíc zápis je velmi expresivní, jednoduchý (téměř zde nelze udělat chybu).

<h2>Závěr</h2>

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 <code>LATERAL</code> join.