PostgreSQL antipatternid: „Tuleb jääda vaid üks!”

SQL-is kirjeldad, mida sa soovid saada, mitte kuidas see peaks toimuma. Seetõttu on SQL-päringute arendamise probleem, mis on stiilis "kuidas see kõlab, nii see kirjutatakse", oma auväärsel kohal, koos SQL-iga seotud tingimuste arvutamise eripäradega.

Täna vaatame äärmiselt lihtsate näidete kaudu, millele see võib viia kontekstis, kus kasutatakse GROUP/DISTINCT ja LIMIT koos nendega.

Kui olete päringus kirjutanud "esmalt ühenda need tabelid ja seejärel eemalda kõik dubleerimised, peab jääma vaid üks näide iga võtme kohta" — just nii see ka töötab, isegi kui liitmine polnud üldse vajalik.

Ja mõnikord juhtub, et see "lihtsalt töötab", mõnikord avaldab see negatiivset mõju jõudlusele ja mõnikord annab täiesti ootamatuid efekte arendaja vaatepunktist.

Noh, võib-olla mitte nii muljetavaldavad, kuid…

"Magus paar": JOIN + DISTINCT

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

On arusaadav, et sooviti valida sellised X-kirjed, mille jaoks Y-s on seotud olemasoleva tingimusega. Kirjutati päring läbi JOIN — saadi mõned pk väärtused mitu korda (just nii palju, kui leidus sobivaid kirjeid Y-s). Kuidas eemaldada? Muidugi STDEV!

Eriti "rõõmustav" on see, kui iga X-kirje kohta leitakse sadu seotud Y-kirjeid ja siis kangelaslikult eemaldatakse dubleerimised…

Kuidas parandada? Esiteks tuleb mõista, et ülesannet saab muuta kuju "valida sellised X-kirjed, mille jaoks Y-s on KASVõi ÜKS seotud olemasoleva tingimusega" — sest kuskilt Y-kirjest me ei vaja midagi.

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 piisab, kui leida esimene leitud kirje, vanemad versioonid ei mõista. Seetõttu eelistan alati märkida LIMIT 1 sees 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;

See variant võimaldab vajadusel koheselt tagastada ka andmed leitud seotud Y-kirjest. Sarnast varianti käsitletakse artiklis "PostgreSQL Antipatterns: harva esinev kirje jõuab JOIN keskmesse".

"Miks maksta rohkem": DISTINCT [ON] + LIMIT 1

Lisaväärtuseks antud päringute muutmise puhul on võimalus hõlpsasti piirata kirjeid, kui vaja on ainult üks/mitu neist, nagu allpool näidatud:

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

Nüüd loeme päringut ja üritame aru saada, mida andmebaasipüstitus ette näeb:

• ühendame tabelid
• teeme unikaalseks X.pk järgi
• jätkuvatest kirjetest valime mingi ühe

Kuidas me seda saime? „Mingi üks kirje“ unikaalsetest — aga kui võtta see üks mitteunikaalsete seast, kas tulemus muutub? .. „Kui pole vahet, miks maksta rohkem?“

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- siia võib panna sobivaid tingimusi
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

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

„Ma võin lihtsalt küsida“: vaikne GROUP + LIMIT

Sellised asjad esinevad erinevate tühjuse kontrollide tabeli või CTE käigus päringu täitmisel:

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

Agregaatfunktsioonid (count/min/max/sum/...) täidetakse edukalt kogu komplektile, isegi ilma selge määramiseta on olemas, vaate määratlemine peab sisaldama. Ainult et LIMIT nad ei sobi hästi.

Arendaja võib mõelda „kui seal on kirjed, siis mul pole vaja rohkem LIMIT-i“. Kuid seda ei tohi teha! Sest andmebaasile see tähendab:

• loenda, mida soovitakse anna tagasi nii palju ridu, kui palutakse
• Olenevalt sihttingimustest on siin sobilik teha üks asendus:

(count + LIMIT 1) = 0

• NOT EXISTS(LIMIT 1) . Tundub, et (count + LIMIT 1) > 0
• EXISTS(LIMIT 1) . Tundub, et count >= N
• (SELECT count(*) FROM (... LIMIT N)) . Tundub, et „Kui palju grammidesse panna“: DISTINCT + LIMIT

SELECT DISTINCT pk FROM X LIMIT $1

Naivne arendaja võib siiralt arvata, et päringu täitmine lõpetatakse,

niipea, kui leiame $1 esimesest ettejuhtuvast erinevast väärtusest Kusagil tulevikus võib see tõesti nii toimida uue sõlme.

Index Skip Scan , mille rakendust töötatakse praegu välja, kuid praegu — ei.Praegu tõmmatakse kõigepealt kõik kirjed

, unikaalsed ja juba neist tagastatakse nii palju, kui on nõutud. Eriti kurb on see, kui soovisime midagi sellist , kuid tabelis on sadu tuhandeid…Et mitte asjatult kurvastada, kasutame rekuurse päringut $1 = 4„DISTINCT vaestele“ PostgreSQL Wiki-st

SQL-is kirjeldad, mida soovid saada, mitte kuidas seda täidetakse. 🥇PostgreSQL Antipatterns: „Pead jääma vaid üks!“ | ProHoster:

Allikas: habr.com