můj úvod do databází a PostgreSQL byl určen pro vývoj webových aplikací a statistickou analýzu. Naučil jsem se jen tolik SQL, abych získal dotazy, abych vrátil správné odpovědi. Díky své práci s PostGIS (a FOSS4G) jsem se spřátelil s Paulem Ramseym. Nyní jsme spolupracovníci v Crunchy Data a on mi pomáhá s mým SQL-fu. Jednou z prvních lekcí, které mě naučil, bylo “ zkuste použít spíše spojení než poddotazy.“

dnešní příspěvek bude fungovat prostřednictvím této rady, protože Paul a já pracujeme přes některé SQL.

na co se snažíme odpovědět

snažím se vytvořit nějaké nové výukové a mluvící materiály o používání SQL v datové vědě a pracoval jsem na nějaké manipulaci s daty před analýzou. Měl jsem tabulku fire_weather, což je podmnožina tabulky počasí, a chci najít všechny položky v počasí, které nejsou v fire_weather. Mým počátečním instinktem bylo napsat poddotaz, ale zdálo se to jako přímý a snadný dotaz, jak se řídit Paulovou radou“ použít spojení“.

špatně připojit dotaz

začal jsem s tímto připojit dotaz:

 select count(weather.*) from weather, fire_weather where weather.id != fire_weather.id;

a nefungovalo to (jinak bych tento blogový příspěvek nepsal). Ukazuje se, že se jedná o křížové spojení, kde skončíme se všemi párovými kombinacemi všech řádků v obou tabulkách.

správný dotaz na spojení

takže v tomto okamžiku se uvolním (na rozdíl od zvonění na telefonu) Paul a začneme diskutovat o tom, jak udělat správné spojení. Ukázalo se, že správná syntaxe je:

select count(weather.*)from weather left join fire_weather on weather.id = fire_weather.idwhere fire_weather.id is null;

v podstatě uděláte levý vnější spoj, který vám poskytne všechny řádky z tabulky počasí a pouze položky fire_weather, které odpovídají. Poté odfiltrujete všechny záznamy, kde jsou shody pro fire_weather. Docela jednoduché pochopit, ale ne příliš set like, jako při použití teorie množin (která je základem vztahů v relačních databázových systémech). Pak znovu, nyní máme pracovní dotaz na připojení.

analyzujte toto

jako součást mé cesty k lepšímu porozumění SQL v PostgreSQL jsem se stal velkým fanouškem EXPLAIN ANALYZE for pro časování a při pohledu na plán dotazu. Jen ze zvědavosti jsem se rozhodl podívat se na načasování a dotaz plán pro připojit dotaz. Zde je výstup a trvalo to asi 7 milisekund s poněkud komplikovaným plánem dotazu:

Aggregate (cost=1358.58..1358.59 rows=1 width=8) (actual time=6.780..6.781 rows=1 loops=1)
-> Hash Anti Join (cost=1168.76..1358.58 rows=1 width=64) (actual time=1.648..6.218 rows=6802 loops=1)
Hash Cond: (weather.id = fire_weather.id)
-> Seq Scan on weather (cost=0.00..159.05 rows=8205 width=68) (actual time=0.008..3.157 rows=8205 loops=1)
-> Hash (cost=785.56..785.56 rows=30656 width=4) (actual time=1.609..1.609 rows=1403 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 306kB
-> Seq Scan on fire_weather (cost=0.00..785.56 rows=30656 width=4) (actual time=0.008..1.399 rows=1403 loops=1)
Planning Time: 0.110 ms
Execution Time: 6.807 ms

nyní poddotaz

a teď jsem chtěl vidět, jak by můj původní nápad pro poddotaz fungoval. Zde je způsob, jak odpovědět na stejnou otázku:

select count(weather.*) from weather where id not in (select id from fire_weather);

další analýza

měli byste vidět, proč se mi tento dotaz líbil, je to velmi nastavené a velmi jednoduché psát. Když se podívám na tento dotaz s vysvětlením analýzy, dostanu:

Aggregate (cost=1052.02..1052.03 rows=1 width=8) (actual time=7.458..7.459 rows=1 loops=1)
-> Seq Scan on weather (cost=862.20..1041.76 rows=4102 width=64) (actual time=1.139..6.727 rows=6802 loops=1)
Filter: (NOT (hashed SubPlan 1))
Rows Removed by Filter: 1403
SubPlan 1
-> Seq Scan on fire_weather (cost=0.00..785.56 rows=30656 width=4) (actual time=0.004..0.643 rows=1403 loops=1)
Planning Time: 0.068 ms
Execution Time: 7.497 ms

porovnání dotazů

takže skončíme s velmi jednoduchým plánem a načasováním, které jsou přibližně stejné jako spojení. Paul a já jsme diskutovali, proč by časování mohlo být tak podobné, a přišli jsme s nejméně dvěma důvody:

  1. datová sada má velmi málo řádků (8k), takže výkon poddotazu se může zhoršit s větší sadou dat.
  2. můj stroj má diskové jednotky NVMe, které poskytují sekvenční přístup ještě větší rozdíl ve výkonu.

další poddotaz založený na sadě

konečně Paul, přišel s dalším dotazem založeným na sadě, aby odpověděl na stejnou otázku:

select * from weather exceptselect weather.* from weather join fire_weather on weather.id = fire_weather.id

Tento používá novou klauzuli SQL, kromě toho, která je součástí kombinátorů dotazů set operation. Jedním velkým omezením těchto dotazů je to, že dotazy na každé straně klauzule s výjimkou musí vrátit stejné sloupce a datové typy. To vysvětluje, proč tento dotaz nemůže vrátit celkový počet řádků. Ukázalo se však, že tento dotaz má horší výkon a mnohem složitější plán dotazů:

HashSetOp Except (cost=0.00..1984.13 rows=8205 width=74) (actual time=10.382..11.171 rows=6802 loops=1)
-> Append (cost=0.00..1532.86 rows=16410 width=74) (actual time=0.021..5.867 rows=9608 loops=1)
" -> Subquery Scan on ""*SELECT* 1"" (cost=0.00..241.10 rows=8205 width=44) (actual time=0.021..2.300 rows=8205 loops=1)"
-> Seq Scan on weather (cost=0.00..159.05 rows=8205 width=40) (actual time=0.006..0.537 rows=8205 loops=1)
" -> Subquery Scan on ""*SELECT* 2"" (cost=261.61..1209.71 rows=8205 width=44) (actual time=1.796..2.788 rows=1403 loops=1)"
-> Hash Join (cost=261.61..1127.66 rows=8205 width=40) (actual time=1.795..2.583 rows=1403 loops=1)
Hash Cond: (fire_weather.id = weather_1.id)
-> Seq Scan on fire_weather (cost=0.00..785.56 rows=30656 width=4) (actual time=0.008..0.413 rows=1403 loops=1)
-> Hash (cost=159.05..159.05 rows=8205 width=40) (actual time=1.768..1.768 rows=8205 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 765kB
-> Seq Scan on weather weather_1 (cost=0.00..159.05 rows=8205 width=40) (actual time=0.002..0.518 rows=8205 loops=1)
Planning Time: 0.127 ms
Execution Time: 11.975 ms

překvapivý zvrat

pak jsem přemýšlel o dotazu, který Paul navrhl, a uvědomil jsem si, že opravdu nepotřebujeme spojení na pravé straně kromě klauzule. Protože fire_weather obsahuje všechny stejné sloupce jako počasí, můžeme použít sloupce, které chceme, a získat odpověď, kterou jsme očekávali.

select id from weather exceptselect id from fire_weather;

nyní to má pěknou sadu syntaxe, takže je opravdu snadné pochopit. Pokud bychom chtěli skutečně získat počet jako v ostatních dotazech, můžeme náš dotaz zabalit do CTE.

with count_me as (select id from weather exceptselect id from fire_weather)select count(*) from count_me;

s tímto zlatým lístkem získáme 6 ms časy dotazu a plány dotazu, které jsou čistší, ale ne nejjednodušší. Měl bych poznamenat, že čistota a jednoduchost nejsou klíčovými faktory při hodnocení plánu dotazů.

HashSetOp Except (cost=0.00..1624.68 rows=8205 width=8) (actual time=4.834..5.313 rows=6802 loops=1)
-> Append (cost=0.00..1527.52 rows=38861 width=8) (actual time=0.004..3.248 rows=9608 loops=1)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..241.10 rows=8205 width=8) (actual time=0.004..1.903 rows=8205 loops=1)
-> Seq Scan on weather (cost=0.00..159.05 rows=8205 width=4) (actual time=0.003..1.001 rows=8205 loops=1)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..1092.12 rows=30656 width=8) (actual time=0.003..0.554 rows=1403 loops=1)
-> Seq Scan on fire_weather (cost=0.00..785.56 rows=30656 width=4) (actual time=0.002..0.409 rows=1403 loops=1)
Planning Time: 0.246 ms
Execution Time: 5.897 ms

moje poslední jídla s sebou

zde jsou poslední lekce, které bych vám chtěl nechat z tohoto malého cvičení.

  1. nikdy oční bulvy dotaz krát-to byly všechny stejné rychlosti do mého oka. Vysvětlete analýzu je váš přítel.
  2. napište dotaz způsobem, který dává největší smysl, a poté proveďte časování. Pokud to není dobré, pak se podívejte na alternativu (pravděpodobně se připojí)
  3. existuje několik způsobů, jak dospět ke stejné odpovědi v SQL – „správná“ odpověď bude velmi situační závislá. Několik věcí, které ovlivní výsledek:
    1. vaše velikost dat-dotaz může přestat být „ok“, jak vaše velikost dat roste
    2. indexy
    3. rychlost vašeho disku
    4. velikost paměti
    5. rychlost procesoru
    6. jak často plánujete provést dotaz
  4. konečně, čas strávený zlepšováním znalostí a dovedností SQL se vyplatí. I když nepíšete nejúčinnější dotazy, jsou stále obvykle rychlejší než psaní mnoha procedurálních kódů. Jak jsem se dozvědět více a více SQL vzory čím více ohromen jsem vůbec kód mohu nahradit několika řádky SQL (a já obvykle získat obrovské zvýšení výkonu).

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna.