ProHoster > Blog > Administrácia > PostgreSQL Antipatterns: "Musí existovať len jeden!"

PostgreSQL Antipatterns: "Musí existovať len jeden!"

V SQL popisujete „čo“ chcete získať, nie „ako“ by sa to malo robiť. Preto problém vývoja SQL dotazov v štýle „ako sa počúva, tak sa píše“ zaujíma svoje čestné miesto spolu s zvláštnosti vyhodnocovania podmienok v SQL.

Dnes sa na veľmi jednoduchých príkladoch pozrime, k čomu to môže viesť v kontexte používania GROUP/DISTINCT и LIMIT s nimi.

Teda ak si napísal v žiadosti „Najprv pripojte tieto tablety a potom vyhoďte všetky duplikáty, mal by byť len jeden príklad pre každý kľúč" - presne takto to bude fungovať, aj keď spojenie nebolo vôbec potrebné.

A niekedy máte šťastie a „jednoducho to funguje“, niekedy to má nepríjemný vplyv na výkon a niekedy to prináša efekty, ktoré sú z pohľadu vývojára absolútne neočakávané.

PostgreSQL Antipatterns: "Musí existovať len jeden!"
No, možno nie také veľkolepé, ale…

"Sladký pár": PRIDAJ SA + ODLIŠNI

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

Ako by bolo jasné, čo chceli vyberte také záznamy X, ku ktorým je v Y priradená splnená podmienka. Žiadosť bola odoslaná cez JOIN - niekoľkokrát dostal nejaké hodnoty pk (presne koľko vhodných záznamov sa ukázalo byť v Y). Ako odstrániť? určite DISTINCT!

Je obzvlášť „príjemné“, keď pre každý záznam X existuje niekoľko stoviek súvisiacich záznamov Y a potom sa duplikáty hrdinsky odstránia ...

PostgreSQL Antipatterns: "Musí existovať len jeden!"

Ako opraviť? Na začiatok si uvedomte, že úlohu možno upraviť na "vyberte tie záznamy X, pre ktoré existuje MINIMÁLNE JEDEN v Y spojený so splnenou podmienkou" - koniec koncov, nepotrebujeme nič zo samotného záznamu Y.

Vnorené EXISTUJE

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

Niektoré verzie PostgreSQL chápu, že v EXISTS stačí nájsť prvý záznam, ktorý narazí, staršie nie. Preto radšej vždy uvádzam LIMIT 1 vnútri EXISTS.

BOČNÉ PRIPOJENIE

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;

Rovnaká možnosť umožňuje v prípade potreby okamžite vrátiť niektoré údaje z nájdeného pridruženého Y-záznamu súčasne. Podobná možnosť je diskutovaná v článku "PostgreSQL Antipatterns: zriedkavý záznam sa dostane do stredu JOIN".

„Prečo platiť viac“: DISTINCT [ON] + LIMIT 1

Ďalšou výhodou takýchto transformácií dotazov je možnosť jednoducho obmedziť enumeráciu záznamov, ak je potrebný iba jeden/niekoľko z nich, ako v nasledujúcom prípade:

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

Teraz si prečítame žiadosť a pokúsime sa pochopiť, čo má DBMS robiť:

  • spojíme pláty
  • unikát od X.pk
  • vyberte jeden zo zostávajúcich záznamov

Tak čo ste dostali? "nejaký jeden záznam" od jedinečných - a ak si vezmete tento z nejedinečných, zmení sa výsledok nejako? .. "A ak nie je rozdiel, prečo platiť viac?"

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

A presne tá istá téma s GROUP BY + LIMIT 1.

"Musím sa len opýtať": implicitná SKUPINA + LIMIT

Podobné veci sa vyskytujú v rôznych kontroly prázdnoty štítky alebo CTE počas postupu požiadavky:

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

Súhrnné funkcie (count/min/max/sum/...) sa úspešne vykonajú na celej množine, a to aj bez explicitného určenia GROUP BY. Len tu s LIMIT nie sú veľmi priateľskí.

Developer môže rozmýšľať „Ak sú tam záznamy, potom nepotrebujem viac ako LIMIT“. Ale to nemusíte! Pretože pre základ je:

  • počítať, čo chcú na všetkých záznamoch
  • dať toľko riadkov, koľko žiadajú

V závislosti od cieľových podmienok je vhodné vykonať jednu z nasledujúcich substitúcií:

  • (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))

"Koľko zavesiť v gramoch": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Naivný vývojár môže úprimne veriť, že vykonávanie požiadavky sa zastaví, hneď ako nájdeme prvé 1 dolár rôzne hodnoty, ktoré narazíme.

Niekedy v budúcnosti to môže a bude fungovať vďaka novému uzlu Index Preskočiť skenovanie, na realizácii ktorého sa momentálne pracuje, no zatiaľ nie.

Zatiaľ prvý všetky záznamy budú obnovené, sú jedinečné a vráti sa len toľko z nich, koľko sa požaduje. Je to obzvlášť smutné, ak by sme niečo také chceli $ 1 = 4a v tabuľke sú státisíce záznamov...

Aby sme neboli smutní nadarmo, použijeme rekurzívny dotaz "DISTINCT for the Poor" z PostgreSQL Wiki:

PostgreSQL Antipatterns: "Musí existovať len jeden!"

Zdroj: hab.com

Pridať komentár