🥇PostgreSQL Antipatterns: «Deve rimanere solo uno!»

In SQL descrivi "cosa" vuoi ottenere, e non "come" questo deve essere eseguito. Pertanto, il problema dello sviluppo di query SQL in stile "come si sente, così si scrive" occupa un posto d'onore, accanto a caratteristiche del calcolo delle condizioni in SQL.

Oggi, con esempi estremamente semplici, vediamo a cosa può portare tutto ciò nel contesto dell'utilizzo di GROUP/DISTINCT e LIMIT insieme ad essi.

Ecco, se hai scritto nella query "prima unisci queste tabelle, poi scarta tutti i duplicati, deve rimanere solo una copia per ciascuna chiave" — funzionerà esattamente in questo modo, anche se l'unione non era affatto necessaria.

E a volte si ha fortuna e questo "funziona semplicemente", talvolta — influisce negativamente sulle prestazioni, e a volte produce effetti assolutamente inaspettati dal punto di vista dello sviluppatore.

Beh, forse, non così spettacolari, ma...

"Coppia dolce": JOIN + DISTINCT

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

È piuttosto chiaro cosa si voleva selezionare quelle righe di X per cui in Y ci sono record correlati alla condizione in corso. È stata scritta una query tramite JOIN — si sono ottenuti alcuni valori pk più volte (esattamente quanti erano i record corrispondenti in Y). Come rimuovere? Certo DISTINCT!

È particolarmente "stimolante" quando per ogni record X si trovano diverse centinaia di record Y correlati, e poi si rimuovono eroicamente i duplicati...

Come risolvere? Per cominciare, rendersi conto che il compito può essere modificato in "selezionare quelli di X per cui in Y c'è ALMENO UN record correlato alla condizione in corso" — poiché da ciascun record Y non abbiamo bisogno di nulla.

E EXISTS annidato

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

Alcune versioni di PostgreSQL capiscono che in EXISTS è sufficiente trovare il primo record disponibile, le versioni più vecchie no. Pertanto, preferisco sempre indicare LIMIT 1 all'interno ESISTE.

JOIN LATERAL

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;

Questa stessa opzione consente, se necessario, di restituire immediatamente alcuni dati dal record Y trovato. Una variante simile è stata considerata nell'articolo "PostgreSQL Antipatterns: un raro record arriverà a metà JOIN".

"Perché pagare di più": DISTINCT [ON] + LIMIT 1

Un ulteriore vantaggio di tali trasformazioni delle query è la possibilità di limitare facilmente il numero di record, se ne serve solo uno/alcuni, come nel seguente caso:

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

Ora leggiamo la query e cerchiamo di capire cosa viene proposto al DBMS:

uniamo le tabelle
uniamo per X.pk
dai record rimanenti ne scegliamo uno

Quindi, cosa abbiamo ottenuto? «Qualche record» tra quelli unici — e se prendiamo questo unico tra quelli non unici, il risultato cambierà forse in qualche modo?.. «E se non c'è differenza, perché pagare di più?»

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- qui si possono inserire condizioni appropriate
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

E lo stesso vale per GROUP BY + LIMIT 1.

«Posso solo chiedere»: GROUP implicito + LIMIT

Questi casi si presentano in diverse verifiche di non vuoto tabelle o CTE durante l'esecuzione della query:

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

Le funzioni aggregate (count/min/max/sum/...) vengono eseguite con successo su tutto il set, anche senza indicazione esplicita. GROUP BY.Ma con LIMIT non vanno molto d'accordo.

Lo sviluppatore può pensare «se ci sono record là, allora non devono superare il LIMIT». Ma non è così! Perché per il database è:

calcola cosa vogliono su tutti i record
restituisci tante righe quante ne vengono richieste

A seconda delle condizioni obiettivo, è opportuno effettuare una delle sostituzioni:

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

«Quanto pesare in grammi»: DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Uno sviluppatore ingenuo può sinceramente pensare che l'esecuzione della query si fermerà, non appena troveremo i $1 primi valori diversi trovati.

In un futuro potrebbe funzionare così grazie al nuovo nodo Index Skip Scan, la cui implementazione è attualmente in fase di sviluppo, ma per ora — no.

Fino ad allora, prima saranno estratti tutti i record, unici, e solo da essi torneranno quelli richiesti. È particolarmente triste se volevamo qualcosa del genere $1 = 4, e i record nella tabella sono centinaia di migliaia…

Per non rimanere delusi inutilmente, utilizziamo una query ricorsiva «DISTINCT per i poveri» da PostgreSQL Wiki:

Fonte: habr.com