PostgreSQLi antipatternid: „Jääb ainult üks!“

SQLis kirjeldad sa, „mida“ soovid saada, mitte „kuidas“ see peaks toimuma. Seetõttu on SQL-päringute koostamine stiilis „nagu kõlab, nii kirjutatakse” oma kohal. erinevuste arvutamine SQLis.

Täna vaatame üli lihtsate näidete kaudu, kuhu see võib viia seoses GROUP/DISTINCT ja LIMIT koos nendega.

Kui sa oled päringus kirjutanud „kõigepealt ühenda need tabelid ja seejärel eemalda kõik dubleed, peab alles jääma ainult üks eksemplar iga võtme kohta“ — just nii see töötabki, isegi kui liitmine polnud üldse vajalik.

Ja mõnikord on õnne ja see „lihtsalt töötab“, mõnikord — mõjutab see negatiivselt jõudlust, ja mõnikord annab see arendaja vaatenurgast täiesti ootamatuid efekte.

No, võib-olla mitte nii dramaatiliselt, aga…

„Magus paar”: JOIN + DISTINCT

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

Kuidas oleks mõistlik, et sooviti valida sellised kirjed X, mille puhul Y-st on seotud täidetava tingimusega. Kirjutasid päringu läbi JOIN — saadi mõned pk väärtused mitu korda (täpselt nii palju kui sobivaid kirjeid Y osutus). Kuidas eemaldada? Loomulikult. DISTINCT!

Eriti "rõõmustab", kui iga X-kirje kohta leidub mitusada seotud Y-kirjet, ja siis kangelaslikult eemaldatakse duplikaadid…

Kuidas parandada? Alustuseks tunnistada, et ülesannet saab muuta järgmistele. „valida sellised X-kirjed, mille Y-s on KUIDAGI ÜKS seotud tingimusega“ — sest Y-kirjast pole meile midagi vajalik.

Sisemine EXISTS

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

Mõned PostgreSQL versioonid mõistavad, et EXISTS-is piisab esimese leidmise kirjega, vanemad — mitte. Seetõttu eelistan alati näidata LIMIT 1 sisemine 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;

Sama variant võimaldab vajadusel kohe tagasi tuua ka mingisuguseid andmeid leitud seosest Y-kirjest. Sarnast varianti käsitleti artiklis „PostgreSQL Antipatterns: harva saavutab kirje JOIN-i keskpaika“.

„Miks maksta rohkem“: DISTINCT [ON] + LIMIT 1

Seda tüüpi päringu ülemineku täiendav eelis on võimalus hõlpsasti piirata salvestuste läbivaatamist, kui on vaja ainult ühte või mitut neist, nagu järgmisel juhul:

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

Nüüd loeme päringu ja püüame aru saada, mida andmebaasi haldussüsteem pakub:

• seome tabelid
• teeme X.pk alusel unikaalseks
• valime jääkidest mingi ühe

Nii et mida me saime? „Mingi üks salvestus” unikaalsetest — aga kui võtta see üks mitteunikaalsetest, kas tulemus muutub kuidagi?… „Aga kui vahet pole, miks maksma rohkem?”

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- siia saab sobivaid tingimusi lisada
    LIMIT 1 -- +1 Limiteerimine
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Ja täpselt sama teema GROUP BY + LIMIT 1.

„Mul on ainult küsida”: varjatud GROUP + LIMIT

Sarnaseid asju kohtab erinevate tühjuse kontrollide tabeli või CTE läbimisel päringu käigus:

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

Agregeerimisfunktsioonid (count/min/max/sum/...) töötavad edukalt kogu kogumi peal, isegi ilma selge viiteta GROUP BY. Ainult sellega on LIMIT nad päris hästi sõbrad.

Arendaja võib arvata «kui seal on kirjed, siis mul ei tohi olla rohkem kui LIMIT». Aga nii ei tohi teha! Sest andmebaasi jaoks on see:

• arvuta, mida nad soovivad kõigi rekordite kohta
• anna tagasi nii palju ridu, kui palutakse

Sõltuvalt sihttingimustest on siin kohane teha üks asendusi:

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

«Mitu grammi kaaluda»: DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Naivne arendaja võib siiralt arvata, et päringu täitmine peatub, niipea kui leiame $1 esimestest erinevatest väärtustest.

Kord tulevikus võib see tõesti toimida uue sõlme tõttu Index Skip Scan, mille rakendamine on praegu töös, aga praegu - ei ole.

Esialgu kõik kirjed on välja tõmmatud, unikaalsed, ja ainult neist tagastatakse, kui palju palutakse. Eriti kurb on, kui tahtsime midagi sarnast $1 = 4, aga tabelis on — sadu tuhandeid…

Et mitte asjatult kurvastada, kasutame rekursiivset päringut «DISTINCT vaesetele» PostgreSQL Wiki-st:

Allikas: habr.com