Antipattern PostgreSQL: "Deve essercene solo uno!"

In SQL, descrivi "cosa" vuoi ottenere, non "come" dovrebbe essere eseguito. Pertanto, il problema di sviluppare query SQL nello stile “come si sente è come si scrive” occupa il posto d’onore, insieme a caratteristiche del calcolo delle condizioni in SQL.

Oggi, utilizzando esempi estremamente semplici, vediamo a cosa può portare questo nel contesto di utilizzo GROUP/DISTINCT и LIMIT con loro.

Ora, se scrivessi nella richiesta “prima collega questi segni e poi butta via tutti i duplicati, dovrebbe essercene solo uno copia per ogni chiave" - funzionerà esattamente così, anche se la connessione non fosse affatto necessaria.

E a volte sei fortunato e "funziona e basta", a volte ha un effetto spiacevole sulle prestazioni, a volte dà effetti completamente inaspettati dal punto di vista dello sviluppatore.

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

“Dolce coppia”: UNISCITI + DISTINTO

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

Sarebbe chiaro cosa volevano selezionare i record X per i quali sono presenti record in Y correlati alla condizione soddisfatta. Ha scritto una richiesta tramite JOIN - ha ottenuto più volte alcuni valori pk (esattamente quante voci adatte sono apparse in Y). Come rimuovere? Certamente DISTINCT!

È particolarmente “gratificante” quando per ogni record X ci sono diverse centinaia di record Y correlati, e poi i duplicati vengono eroicamente rimossi...

Come risolvere? Per cominciare, renditi conto che il problema può essere modificato in “seleziona i record X per i quali in Y esiste ALMENO UNO associato alla condizione soddisfatta” - dopo tutto, non abbiamo bisogno di nulla dal record Y stesso.

Nidificato ESISTE

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 la prima voce che appare, quelle più vecchie no. Quindi preferisco indicare sempre LIMIT 1 interno EXISTS.

GIUNZIONE 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;

La stessa opzione consente, se necessario, di restituire immediatamente alcuni dati dal record Y associato trovato. Un'opzione simile è discussa nell'articolo "Antipattern PostgreSQL: un record raro raggiungerà la metà di un JOIN".

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

Un ulteriore vantaggio di tali trasformazioni di query è la possibilità di limitare facilmente la ricerca dei record se ne sono necessari solo uno o alcuni, come nel caso seguente:

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

Adesso leggiamo la richiesta e cerchiamo di capire cosa si propone di fare il DBMS:

• collegando i segni
• unico di X.pk
• dalle voci rimanenti, selezionarne una

Allora cosa hai ottenuto? "Una sola voce" da quelli unici - e se prendiamo questo dei non unici, il risultato cambierà in qualche modo?.. “E se non c'è differenza, perché pagare di più?”

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

Ed esattamente lo stesso argomento con GROUP BY + LIMIT 1.

“Devo solo chiedere”: GRUPPO implicito + LIMITE

Cose simili si verificano in modi diversi controlli di non vuoto segni o CTE man mano che la richiesta avanza:

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

Funzioni aggregate (count/min/max/sum/...) vengono eseguiti con successo sull'intero set, anche senza istruzioni esplicite GROUP BY. Solo con LIMIT non sono molto amichevoli.

Lo sviluppatore può pensare "se ci sono record lì, non ho bisogno di più di LIMIT". Ma non farlo! Perché per la base è:

• contare quello che vogliono secondo tutti i documenti
• fornire tutte le righe richieste

A seconda delle condizioni target, è opportuno effettuare una delle seguenti sostituzioni:

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

“Quanto pesare in grammi”: DISTINTO + LIMITE

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Uno sviluppatore ingenuo potrebbe credere sinceramente che l'esecuzione della richiesta verrà interrotta. non appena troviamo $1 tra i primi valori diversi che incontriamo.

In futuro questo potrebbe funzionare e funzionerà grazie a un nuovo nodo Indice Salta scansione, la cui attuazione è attualmente in fase di elaborazione, ma non ancora.

Per ora prima tutti i record verranno recuperati, sono unici, e solo da essi verrà restituito l'importo richiesto. È particolarmente triste se volessimo qualcosa del genere $ 1 = 4e nella tabella sono presenti centinaia di migliaia di record...

Per non essere tristi invano, utilizziamo una query ricorsiva "DISTINCT è per i poveri" da PostgreSQL Wiki:

Fonte: habr.com