PostgreSQL Antipatterns: "Ci deve esse solu unu!"

In SQL, descrivi "ciò chì" vulete ottene, micca "cumu" deve esse eseguitu. Dunque, u prublema di sviluppà e dumande SQL in u stilu di "cum'è hè intesu hè cumu hè scrittu" piglia u so postu d'onore, cù funziunalità di calculà e cundizioni in SQL.

Oghje, usendu esempi estremamente simplici, vedemu ciò chì questu pò purtà in u cuntestu di usu GROUP/DISTINCT и LIMIT cun elli.

Avà, se avete scrittu in a dumanda "Prima cunnetta sti segni, è poi scaccià tutti i duplicati, ci deve esse solu unu copia per ogni chjave " - questu hè esattamente cumu si travaglià, ancu s'ellu ùn era micca necessariu a cunnessione.

È qualchì volta site furtunatu è "funziona solu", qualchì volta hà un effettu dispiacevule nantu à u rendiment, è qualchì volta dà effetti chì sò completamente inespettati da u puntu di vista di u sviluppatore.

Ebbè, forse micca cusì spettaculare, ma...

"Coppiu dolce": JOIN + DISTINCT

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

Saria chjaru ciò chì vulianu selezziunà i registri X per i quali ci sò registri in Y chì sò ligati à a cundizione cumpleta. Scrivu una dumanda via JOIN - avè alcuni valori pk parechje volte (esattamente quante entrate adattate apparsu in Y). Cumu caccià? Di sicuru DISTINCT!

Hè soprattuttu "gratificante" quandu per ogni X-record ci sò parechje centinaie di Y-records rilativi, è dopu i duplicati sò eroicamente eliminati...

Cumu riparà? Per principià, capisce chì u prublema pò esse mudificatu "selezziunà i registri X per quale in Y ci hè ALMENU UNU assuciatu cù a cundizione cumprita" - dopu à tuttu, ùn avemu micca bisognu di nunda di u Y-record stessu.

Nidificati ESISTE

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

Certi versioni di PostgreSQL capiscenu chì in EXISTS hè abbastanza per truvà a prima entrata chì vene, i vechji ùn anu micca. Dunque, preferiscu sempre indicà LIMIT 1 ind'u EXISTS.

JOIN LATERALE

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;

A listessa opzione permette, se ne necessariu, di rinvià immediatamente qualchi dati da u Y-record associatu trovu. Una opzione simili hè discutata in l'articulu "Antipatterns PostgreSQL: un registru raru ghjunghje à a mità di un JOIN".

"Perchè pagà più": DISTINCT [ON] + LIMIT 1

Un benefiziu supplementu di tali trasfurmazioni di dumanda hè a capacità di limità facilmente a ricerca di registri se solu unu o uni pochi di elli sò necessarii, cum'è in u casu seguente:

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

Avà leghje a dumanda è pruvate à capisce ciò chì u DBMS hè prupostu di fà:

• culligamentu di i segni
• unicu da X.pk
• da e voci restanti, selezziunate unu

Allora chì avete ricevutu? "Solu una entrata" da i unichi - è s'è no pigliemu questu unu di i micca unichi, u risultatu cambierà in qualchì manera? .. "E s'ellu ùn ci hè micca differenza, perchè pagà più?"

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

È esattamente u listessu tema cun GROUP BY + LIMIT 1.

"Aghju solu dumandà": implicit GROUP + LIMIT

E cose simili si verificanu in diverse cuntrolli senza vacuità segni o CTE mentre a dumanda avanza:

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

funzioni aggregate (count/min/max/sum/...) sò eseguiti cù successu in tuttu u settore, ancu senza istruzioni esplicite GROUP BY. Solu cù LIMIT ùn sò micca assai amichevuli.

U sviluppatore pò pensà "Se ci sò registri, allora ùn aghju micca bisognu di più di LIMIT". Ma ùn fate micca cusì ! Perchè per a basa hè:

• conta ciò chì volenu sicondu tutti i registri
• dà tante linee quant'elli dumandanu

Sicondu e cundizioni di destinazione, hè adattatu per fà una di e seguenti sustituzzioni:

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

"Quantu pisà in grammi": ​​DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Un sviluppatore ingenu pò crede sinceramente chì a dumanda cesserà di eseguisce. appena truvamu $ 1 di i primi valori diffirenti chì venenu.

Qualchì volta in u futuru questu puderà è travaglià grazia à un novu node Index Skip Scan, l'implementazione di quale hè attualmente in travagliu, ma micca ancu.

Per avà prima tutti i registri seranu recuperati, sò unichi, è solu da elli a quantità dumandata serà restituita. Hè soprattuttu tristu s'è no vulemu qualcosa cum'è $ 1 = 4, è ci sò centinaie di millaie di registri in a tavula ...

Per ùn esse tristi in vain, usemu una dumanda recursiva "DISTINCT hè per i poveri" da PostgreSQL Wiki:

Source: www.habr.com