🥇PostgreSQL Antipatterns: "Deve rimanerne solo uno!"

In SQL, descrivi «cosa» desideri ottenere, non «come» deve essere eseguito. Pertanto, il problema nello sviluppo delle query SQL in uno stile «scritto come si sente» occupa un posto d'onore, accanto a particolarità del calcolo delle condizioni in SQL.

Oggi, attraverso esempi estremamente semplici, vediamo a cosa può portare nel contesto dell'uso di GROUP/DISTINCT e LIMIT insieme a loro.

Se hai scritto nella tua query «prima collega queste tabelle, poi elimina i duplicati, deve rimanere solo uno esemplare per ogni chiave» — funzionerà proprio così, anche se la join non fosse affatto necessaria.

E a volte va bene e «funziona semplicemente», a volte influisce negativamente sulle performance, e a volte produce effetti del tutto 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;

È abbastanza chiaro che si voleva selezionare tali registrazioni X, per le quali ci sono collegamenti in Y che soddisfano la condizione in corso. Hai scritto la query tramite JOIN — ho ricevuto alcuni valori pk più volte (esattamente quante registrazioni corrispondenti ci sono in Y). Come posso risolvere? Certamente DISTINCT!

È particolarmente frustrante quando per ogni registrazione X si trovano centinaia di registrazioni Y correlate e poi si eliminano eroicamente i duplicati...

Come posso correggere questo? Prima di tutto, bisogna riconoscere che la questione può essere modificata in «selezionare tali registrazioni X per cui in Y c'è ALMENO UN collegamento che soddisfa la condizione in corso» — perché non abbiamo bisogno di alcuna informazione dalla registrazione Y stessa.

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, e quelle più datate no. Perciò preferisco sempre indicare LIMIT 1 all'interno 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;

Questa stessa opzione permette, se necessario, di restituire direttamente alcuni dati dalla registrazione Y trovata. Una variante simile è stata discussa nell'articolo «Antipattern di PostgreSQL: una registrazione rara arriva 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 restituiti, se ne serve solo uno o più di uno, 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 si sta proponendo di fare al DBMS:

uniamo le tabelle
rendiamo unici per X.pk
tra i record rimanenti scegliamo uno qualsiasi

Cosa abbiamo ottenuto, quindi? "Un certo record" tra i record unici — e se prendessimo questo unico dai risultati non unici cambierebbe qualcosa? "E se non c'è differenza, perché pagare di più?"

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- qui si possono inserire condizioni adeguate
    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

Situazioni simili si riscontrano in vari controlli di non nullità 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/...) sono eseguite con successo su tutto il set, anche senza esplicita indicazione. GROUP BYSolo che con LIMIT non vanno molto d'accordo.

Lo sviluppatore potrebbe pensare «se ci sono registrazioni lì, non ho bisogno di più di LIMIT». Ma non è necessario! Perché per il database questo significa:

calcola cosa vogliono per tutte le registrazioni
restituisci tante righe quante richieste

A seconda delle condizioni obiettivo, è appropriato effettuare una delle seguenti sostituzioni:

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

«Quanto pesare in grammi»: DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Un sviluppatore naif potrebbe sinceramente ritenere che l'esecuzione della query si fermerà non appena troviamo i primi $1 valori diversi casuali.

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

Per ora, prima di tutto saranno estratte tutte le registrazioni, uniche, e solo da esse verrà restituito quanto richiesto. È particolarmente triste se vogliamo qualcosa del genere $1 = 4, e le registrazioni nella tabella sono centinaia di migliaia…

Per non rattristarsi inutilmente, utilizziamo una query ricorsiva «DISTINCT per poveri» dal PostgreSQL Wiki:

Fonte: habr.com