PostgreSQL antipatterns: «Trebalo bi ostati samo jedan!»

U SQL-u opisujete "što" želite dobiti, a ne "kako" to treba izvršiti. Stoga problem razvoja SQL upita u stilu "kako se čuje, tako se piše" zauzima svoje počasno mjesto, uz osobitosti izračunavanja uvjeta u SQL-u.

Danas na iznimno jednostavnim primjerima ćemo vidjeti do čega to može dovesti u kontekstu korištenja GROUP/DISTINCT i LIMIT zajedno s njima.

Ako ste napisali u upitu "najprije spoji te tablice, a onda ukloni sve duplicirane, treba ostati samo jedan primer po svakom ključu" — upravo tako će raditi, čak i ako spajanje uopće nije bilo potrebno.

I ponekad imate sreće i to "jednostavno radi", ponekad to nepovoljno utječe na performanse, a ponekad daje potpuno neočekivane efekte iz perspektive programera.

Pa, možda ne toliko spektakularne, ali...

"Slatki par": JOIN + DISTINCT

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

Kako bi bilo jasno da ste htjeli izabrati takve zapise X za koje u Y postoje povezani s izvršenim uvjetom. Napisali ste upit kroz JOIN — dobili ste neke vrijednosti pk više puta (točno onoliko koliko je bilo odgovarajućih zapisa u Y). Kako ih ukloniti? Naravno DISTINCT!

Osobito "raduje" kada za svaki zapis X naiđe po nekoliko stotina povezanih Y zapisa, a onda se herojstveno uklanjaju duplicirani…

Kako ispraviti? Prvo shvatiti da se zadatak može modificirati do "izabrati takve zapise X za koje u Y postoji BAREM JEDAN povezan s izvršenim uvjetom" — jer nam iz same Y-zapisnice ništa nije potrebno.

Ugnježdeni EXISTS

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

Neke verzije PostgreSQL-a razumiju da je u EXISTS dovoljno pronaći prvi zapis, starije verzije — ne. Stoga radije uvijek navodim LIMIT 1 unutar EXISTS.

LATERAL JOIN

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;

Ova varijanta također omogućuje, po potrebi, odmah vratiti neke podatke iz pronađene povezane Y-zapisnice. Slična varijanta je razmatrana u članku "PostgreSQL Antipatterns: rijetka zapisnica dolazi do sredine JOIN".

"Zašto plaćati više": DISTINCT [ON] + LIMIT 1

Dodatna prednost takvih transformacija upita je mogućnost jednostavnog ograničavanja pretrage zapisa, ako su potrebni samo jedan/višestruki, kao u sljedećem slučaju:

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

Sada čitamo upit i pokušavamo razumjeti što se NDB želi učiniti:

• spajamo tablice
• jedinstveni po X.pk
• iz preostalih zapisa biramo neku jednu

Što smo dobili? »Neku jednu stavku« od jedinstvenih — a ako uzmemo ovu jednu iz nejedinstvenih, hoće li se rezultat promijeniti? .. »Ako nema razlike, zašto plaćati više?«

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- ovdje se mogu dodati odgovarajući uvjeti
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Isto je i sa GROUP BY + LIMIT 1.

»Samo trebam pitati«: implicitni GROUP + LIMIT

Slične stvari se javljaju kod raznih provjera praznine tablica ili CTE tijekom izvršavanja upita:

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

Agregatne funkcije (count/min/max/sum/...) uspješno se izvršavaju na cijelom skupu, čak i bez izričitog navođenja GROUP BY. Samo što se LIMIT oni ne slažu najbolje.

Programer može pomisliti »ako već postoje zapisi, ne treba mi više od LIMIT«. Ali ne treba tako! Jer za bazu to znači:

• izračunaj što je potrebno od svih zapisa
• vrati onoliko redaka koliko se traži

Ovisno o ciljnim uvjetima, ovdje je prikladno izvršiti jednu od zamjena:

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

»Koliko vaga u gramima«: DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Naivan programer može iskreno vjerovati da će izvršenje upita prestati, čim pronađemo $1 prvih različitih vrijednosti.

U budućnosti to bi moglo funkcionirati zahvaljujući novom čvoru Index Skip Scan, čija se implementacija trenutno razrađuje, ali zasad — ne.

Za sada će se prvo izvući svi zapisi, jedinstveniti, i tek iz njih vratiti koliko je zatraženo. Osobito je tužno ako smo željeli nešto poput $1 = 4, a zapisa u tablici — stotine tisuća...

Kako bismo izbjegli bespotrebno tugovanje, iskoristimo rekurzivni upit »DISTINCT za siromašne« iz PostgreSQL Wiki:

Izvor: habr.com