Záleží vždy na Equi-Joinech?

Občas narazím na nějaká tvrzení o tom, jak databáze fungují a jak se mají dotazovat. A rád taková doporučení čtu. Zvláště pokud jsou doplněna teoretickým vysvětlením. Ale ještě více rád čtu takové komentáře, pokud jsou doplněny testy, které dokazují, co teorie předvídala. Nejlepší případ je vždy, když jsou skripty publikovány a lze je reprodukovat.

Často se ale stává, že taková tvrzení o výkonu jsou činěna obecně bez jakéhokoli důkazu: vždy platí, že pattern x funguje lépe než y. A to může platit, pokud máte více znalostí o obsahu svých dat, než databáze může mít. Nebo pokud existuje matematické vysvětlení takového chování.

Podle mého názoru by v mnoha případech ohledně indexování nebo strategií joinů měla být tvrzení omezena na databázové technologie a verze. Také tvrzení bez reprodukovatelných testů by neměla být brána jako samozřejmost, ale testována.

Pokud něco testuji na SQL Serveru 2008 a porovnávám to s SQL Serverem 2019, obvykle dostanu úplně jiné chování, protože SQL Server se masivně vyvinul. Pokud porovnám Postgres se Snowflake a Exasolem, dostanu jiné chování, protože ty pozdější byly vytvořeny s analytickými dotazy na mysli.

To je důvod, proč chci zpochybnit tvrzení:

„Equi-Joins (inner joins) jsou vždy lepší než left joiny"

Proč na tom záleží

Pokud se rada používat vždy určitý pattern dodržuje bez testování, může to vést ke špatnému výkonu. Jelikož jsme s naším nástrojem Datavault Builder v automatizačním byznysu, výběr špatného patternu se násobí stovkami nebo dokonce tisíci.

Proč mohou být left joiny lepší

Za prvé: za určitých podmínek jsou inner joiny lepší. Například umožňují optimalizátoru dotazů začít dotazy na kterémkoli konci řetězce joinů, a pokud to umožní masivně omezit mezivýsledek, hodně to pomůže. Existuje mnoho dalších scénářů.

To ale neplatí vždy:

Představte si 2 scénáře:

máte 10 tabulek. Jedna má 15 milionů záznamů a máte 9 dalších tabulek se 45 miliony záznamů. Všechny linky vedou z vaší 15m tabulky přímo do okolních tabulek. Všech 15m záznamů má odpovídající záznam v 9 ostatních tabulkách.
máte 10 tabulek. Jedna má 30 milionů záznamů a máte 9 dalších tabulek se 45 miliony záznamů. Všechny linky vedou z vaší 30m tabulky přímo do okolních tabulek. 15m záznamů má odpovídající záznam v 9 ostatních tabulkách. 15m nemá.

Testovací nastavení: tabulka s 30M záznamy propojená s 9 satelitními tabulkami se 45M záznamy — equi-join benchmark na Snowflake

To je scénář, který jsem provedl na Snowflake 26. srpna 2021 (to je relevantní, protože Snowflake se neustále zlepšuje).

Můj předpoklad byl: left joiny fungují lépe pro tento typ dotazu. Proč? Stejné testy jsem dělal pro SQL Server (2017) a Oracle (12c) před 2 lety a uzavřel, že LEFT JOINY fungují lépe v tomto scénáři.

Teorie zde: alespoň ve druhém případě může být tabulka 30m filtrována před spojením, takže by měla fungovat lépe. Pro případ, kdy vše odpovídá, bych očekával stejný výkon nebo možná o něco horší (možná o pár procent).

Uvidíme, že má očekávání souhlasí s mými měřeními. V případě použití left joinu mohu také ukládat méně dat, protože mohu použít NULL hodnotu k označení, že neexistuje odpovídající záznam.

Porovnání místa na disku: příprava tabulky LEFT JOIN vs INNER JOIN s přístupy surrogate key 1 sloupec a 2 sloupce

Testování

Vypnul jsem cache výsledků. Ale po každém dotazu jsem nevyčistil všechny cache dat. Všechny testy byly provedeny alespoň 3krát. Výsledky byly přijaty, pokud byla odchylka mezi běhy menší než 10%.

Výsledky

Provedl jsem 2 druhy testů: použít 2 sloupce pro spojení mezi tabulkou 15m/30m a 9 ostatními, nebo pouze 1 sloupec (pomocí surrogate key pro kombinaci prvních dvou polí).

Ve všech případech s mým objemem dat na Snowflake dnes verze s left joiny fungovala přibližně stejně NEBO lépe.

Benchmark běh 1: časy provedení cross-satellite joinu porovnávající LEFT JOIN a INNER JOIN na Snowflake

Výsledek cross-joinu zobrazující distribuci odpovídajících a neodpovídajících záznamů

Benchmark běh 2: časy provedení cross-satellite joinu — LEFT JOIN vs INNER JOIN

Benchmark běh 3: časy provedení cross-satellite joinu potvrzující výhodu LEFT JOINu

Aby to bylo fér: INNER JOIN na jeden sloupec skenuje přibližně o 20-25% méně dat (protože nemusí filtrovat). Ale jelikož je to lokální skenování, neuškodí to a urychluje to JOIN část.

Také vidíme, že join na 1 sloupec je, jak se očekávalo, mnohem efektivnější než join na 2 sloupce.

Porovnání výkonu na 3 satelitech: surrogate key na 1 sloupec překonává join na 2 sloupce — warehouse Snowflake S

Shrnutí

V určitých případech mají INNER JOINY lepší výkon než left joiny. Ale ne ve všech. Ukázal jsem reprodukovatelnými testy, že za určitých podmínek LEFT JOINY nefungují hůře, nebo dokonce mnohem lépe než INNER JOINY.

Nechci dělat univerzální tvrzení o svém scénáři. Jen říkám: na určitých databázích v určitých verzích pro určité dotazy mohou být LEFT JOINY lepší volbou.

Také jsme viděli, že na Snowflake je join s 1 sloupcem mnohem lepší než s 2 sloupci.

Vyzkoušejte si to sami

Jelikož je možné, že jsem něco minul. Definoval jsem některé klíče špatně? Vyčistil jsem cache špatně nebo napsal SQL ne optimálně? Neváhejte si výsledky reprodukovat sami.

Testovací skript lze stáhnout zde:

AUTOMATED_PIT_TEST