PostgreSQL-i antimustrid: "Peab olema ainult üks!"

SQL-is kirjeldate "mida" soovite saavutada, mitte "kuidas" seda tuleks täita. Seetõttu võtab aukohal probleem SQL-päringute arendamisel stiilis "nagu kuuldakse, kuidas kirjutatakse" koos SQL-i tingimuste arvutamise omadused.

Täna, kasutades äärmiselt lihtsaid näiteid, vaatame, milleni see võib kasutamise kontekstis kaasa tuua GROUP/DISTINCT и LIMIT nendega.

Nüüd, kui sa kirjutasid taotlusesse "Kõigepealt ühendage need märgid ja siis visake kõik duplikaadid välja, ainult üks peaks alles jääma koopia iga võtme jaoks" - täpselt nii see töötab, isegi kui ühendust poleks üldse vaja.

Ja mõnikord veab ja see "lihtsalt töötab", mõnikord mõjub see jõudlusele ebameeldivalt ja mõnikord annab see arendaja seisukohast täiesti ootamatuid efekte.

Noh, võib-olla mitte nii suurejooneline, aga...

“Armas paar”: LIITU + ERISTA

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

Oleks selge, mida nad tahtsid valige kirjed X, mille jaoks on Y-s kirjed, mis on seotud täidetud tingimusega. Kirjutas päringu kaudu JOIN - sai mitu pk-väärtust (täpselt mitu sobivat kirjet Y-s ilmus). Kuidas eemaldada? Kindlasti DISTINCT!

See on eriti "rõõmustav", kui iga X-kirje kohta on mitusada seotud Y-kirjet ja seejärel eemaldatakse duplikaadid kangelaslikult ...

Kuidas parandada? Alustuseks mõistke, et probleemi saab muuta "valige kirjed X, mille puhul Y on täidetud tingimusega seotud VÄHEMALT ÜKS" - lõppude lõpuks ei vaja me Y-kirjest endast midagi.

Pesastatud OLEMAS

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

Mõned PostgreSQL-i versioonid mõistavad, et EXISTS-is piisab esimese ettetuleva kirje leidmisest, vanemad versioonid mitte. Seetõttu eelistan alati näidata LIMIT 1 jooksul EXISTS.

KÜLGNE LIITUMINE

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 valik võimaldab vajadusel kohe tagastada leitud seotud Y-kirjest mõned andmed. Sarnast võimalust käsitletakse artiklis "PostgreSQL-i antimustrid: haruldane kirje jõuab JOIN-i keskele".

„Miks maksta rohkem”: ERINEV [SEES] + LIIT 1

Selliste päringuteisenduste lisaeelis on võimalus hõlpsalt piirata kirjete otsimist, kui vaja on ainult ühte või mõnda neist, nagu järgmisel juhul:

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

Nüüd loeme taotlust ja proovime mõista, mida DBMS-ile tehakse:

• märkide ühendamine
• ainulaadne X.pk
• ülejäänud kirjete hulgast valige üks

Mida sa siis said? "Ainult üks sissekanne" unikaalsetest - ja kui võtta see mitteunikaalsetest, kas tulemus muutub kuidagi?.. “Ja kui vahet pole, miks siis rohkem maksta?”

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

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

"Ma lihtsalt pean küsima": kaudne GROUP + LIMIT

Sarnased asjad esinevad erinevatel juhtudel mittetühjuse kontrollid märgib või CTE-d taotluse edenedes:

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

Koondfunktsioonid (count/min/max/sum/...) käivitatakse edukalt kogu komplektis, isegi ilma selgesõnaliste juhisteta GROUP BY. Ainult koos LIMIT nad ei ole väga sõbralikud.

Arendaja võib mõelda "Kui seal on kirjeid, siis ma ei vaja rohkem kui LIMIT". Aga ära tee seda! Sest aluse jaoks on see:

• loe, mida nad tahavad kõigi kirjete järgi
• andke nii palju ridu, kui nad küsivad

Olenevalt sihttingimustest on asjakohane teha üks järgmistest asendustest:

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

"Kui palju kaaluda grammides": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Naiivne arendaja võib siiralt uskuda, et päringu täitmine lakkab. niipea, kui leiame 1 dollari esimestest erinevatest väärtustest, mis kokku puutuvad.

Kunagi tulevikus võib ja töötab see tänu uuele sõlmele Indeks Skani vahelejätmine, mille elluviimine on praegu väljatöötamisel, kuid veel mitte.

Esialgu esmalt kõik kirjed otsitakse alla, on ainulaadsed ja ainult neilt tagastatakse nõutud summa. See on eriti kurb, kui me midagi sellist tahtsime $ 1 = 4, ja tabelis on sadu tuhandeid kirjeid...

Et mitte asjata kurvastada, kasutame rekursiivset päringut "DISTINCT on vaestele" PostgreSQL Wikist:

Allikas: www.habr.com