ProHoster > Blog > podávání > PostgreSQL Antipatterns: "Musí existovat pouze jeden!"

PostgreSQL Antipatterns: "Musí existovat pouze jeden!"

V SQL popisujete „co“ chcete dosáhnout, nikoli „jak“ by to mělo být provedeno. Proto problém vývoje SQL dotazů ve stylu „jak se slyší, tak se píše“ zaujímá své čestné místo spolu s vlastnosti výpočtu podmínek v SQL.

Dnes se na velmi jednoduchých příkladech podívejme, k čemu to může vést v kontextu použití GROUP/DISTINCT и LIMIT s nimi.

Nyní, pokud jste napsali v žádosti „Nejprve spojte tyto znaky a pak vyhoďte všechny duplikáty, měl by zůstat jen jeden kopie pro každý klíč" - přesně tak to bude fungovat, i když připojení nebylo vůbec potřeba.

A někdy máte štěstí a „prostě to funguje“, někdy to má nepříjemný vliv na výkon a někdy to dává efekty, které jsou z pohledu vývojáře zcela neočekávané.

PostgreSQL Antipatterns: "Musí existovat pouze jeden!"
No, možná ne tak velkolepé, ale...

„Sladký pár“: JOIN + DISTINCT

SELECT DISTINCT
  X.*
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
WHERE
  Y.bool_condition;

Bylo by jasné, co chtějí vyberte záznamy X, pro které jsou v Y záznamy související se splněnou podmínkou. Napsal žádost přes JOIN — několikrát získal nějaké hodnoty pk (přesně kolik vhodných položek se objevilo v Y). Jak odstranit? Rozhodně DISTINCT!

Je obzvláště „potěšující“, když pro každý záznam X existuje několik stovek souvisejících záznamů Y a pak jsou duplikáty hrdinně odstraněny...

PostgreSQL Antipatterns: "Musí existovat pouze jeden!"

Jak opravit? Pro začátek si uvědomte, že problém lze upravit na „vyberte záznamy X, pro které je v Y ALESPOŇ JEDEN přidružený ke splněné podmínce“ - koneckonců ze samotného Y-záznamu nic nepotřebujeme.

Vnořené EXISTUJE

SELECT
  *
FROM
  X
WHERE
  EXISTS(
    SELECT
      NULL
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  );

Některé verze PostgreSQL chápou, že v EXISTS stačí najít první položku, která se objeví, starší ne. Proto raději vždy uvedu LIMIT 1 uvnitř EXISTS.

BOČNÍ PŘIPOJENÍ

SELECT
  X.*
FROM
  X
, LATERAL (
    SELECT
      Y.*
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  ) Y
WHERE
  Y IS DISTINCT FROM NULL;

Stejná možnost umožňuje v případě potřeby okamžitě vrátit některá data z nalezeného přidruženého Y-záznamu. Podobná možnost je popsána v článku "PostgreSQL Antipatterns: vzácný záznam dosáhne středu JOIN".

„Proč platit více“: DISTINCT [ON] + LIMIT 1

Další výhodou takových transformací dotazů je možnost snadno omezit vyhledávání záznamů, pokud je potřeba pouze jeden nebo několik z nich, jako v následujícím případě:

SELECT DISTINCT ON(X.pk)
  *
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Nyní čteme žádost a snažíme se pochopit, co má DBMS dělat:

  • spojování značek
  • unikátní od X.pk
  • ze zbývajících položek vyberte jednu

Tak co jsi dostal? "Jen jeden záznam" od jedinečných - a když vezmeme tento z neunikátních, změní se nějak výsledek?.. "A když není rozdíl, proč platit víc?"

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- сюда можно подсунуть подходящих условий
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

A úplně stejné téma s GROUP BY + LIMIT 1.

„Jen se musím zeptat“: implicitní SKUPINA + LIMIT

Podobné věci se dějí u různých kontroly nevyprázdnění znaky nebo CTE, jak požadavek postupuje:

...
CASE
  WHEN (
    SELECT
      count(*)
    FROM
      X
    LIMIT 1
  ) = 0 THEN ...

Agregační funkce (count/min/max/sum/...) jsou úspěšně provedeny na celé sadě, a to i bez explicitních pokynů GROUP BY. Jen s LIMIT nejsou moc přátelští.

Vývojář může přemýšlet „pokud tam jsou záznamy, pak nepotřebuji více než LIMIT“. Ale nedělej to! Protože pro základ je:

  • počítat, co chtějí podle všech záznamů
  • dát tolik řádků, kolik žádají

V závislosti na cílových podmínkách je vhodné provést jednu z následujících substitucí:

  • (count + LIMIT 1) = 0 na NOT EXISTS(LIMIT 1)
  • (count + LIMIT 1) > 0 na EXISTS(LIMIT 1)
  • count >= N na (SELECT count(*) FROM (... LIMIT N))

„Kolik vážit v gramech“: DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Naivní vývojář může upřímně věřit, že se požadavek přestane vykonávat. jakmile najdeme 1 $ z prvních různých hodnot, se kterými se setkáme.

Někdy v budoucnu to může a bude fungovat díky novému uzlu Index Přeskočit skenování, na jehož realizaci se aktuálně pracuje, ale zatím ne.

Prozatím nejprve všechny záznamy budou načteny, jsou jedinečné a pouze z nich bude vrácena požadovaná částka. Je to obzvláště smutné, pokud bychom něco takového chtěli $ 1 = 4a v tabulce jsou stovky tisíc záznamů...

Abychom nebyli smutní nadarmo, použijme rekurzivní dotaz „DISTINCT je pro chudé“ z PostgreSQL Wiki:

PostgreSQL Antipatterns: "Musí existovat pouze jeden!"

Zdroj: www.habr.com

Přidat komentář