PostgreSQL Antipatterns: "Duhet të ketë vetëm një!"

Në SQL, ju përshkruani "çfarë" dëshironi të arrini, jo "si" duhet të ekzekutohet. Prandaj, problemi i zhvillimit të pyetjeve SQL në stilin "siç dëgjohet është si shkruhet" zë vendin e tij të nderit, së bashku me veçoritë e llogaritjes së kushteve në SQL.

Sot, duke përdorur shembuj jashtëzakonisht të thjeshtë, le të shohim se çfarë mund të çojë kjo në kontekstin e përdorimit GROUP/DISTINCT и LIMIT me ta.

Tani, nëse keni shkruar në kërkesë “Së pari lidhni këto shenja dhe më pas hidhni të gjitha dublikatat, duhet të mbetet vetëm një kopjoni për çdo çelës" - pikërisht kështu do të funksionojë, edhe nëse lidhja nuk ishte e nevojshme fare.

Dhe ndonjëherë ju jeni me fat dhe "thjesht funksionon", ndonjëherë ka një efekt të pakëndshëm në performancën, dhe nganjëherë jep efekte që janë krejtësisht të papritura nga këndvështrimi i zhvilluesit.

Epo, ndoshta jo aq spektakolare, por...

“Çifti i ëmbël”: BASHKOHU + DALLIM

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

Do të ishte e qartë se çfarë donin zgjidhni regjistrimet X për të cilat ka regjistrime në Y që lidhen me kushtin e përmbushur. Shkruani një kërkesë nëpërmjet JOIN - mori disa vlera pk disa herë (saktësisht sa hyrje të përshtatshme u shfaqën në Y). Si të hiqni? Sigurisht DISTINCT!

Është veçanërisht "kënaqëse" kur për çdo X-rekord ka disa qindra Y-rekord të lidhur, dhe më pas dublikatat hiqen heroikisht...

Si të rregulloni? Për të filluar, kuptoni se problemi mund të modifikohet "Zgjidhni regjistrimet X për të cilat në Y ka TË SAKTËN NJË të lidhur me kushtin e përmbushur" - në fund të fundit, ne nuk kemi nevojë për asgjë nga vetë rekordi Y.

I mbivendosur EKZISTON

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

Disa versione të PostgreSQL kuptojnë se në EXISTS mjafton të gjesh hyrjen e parë që del, të vjetrat jo. Prandaj preferoj të tregoj gjithmonë LIMIT 1 brenda EXISTS.

BASHKIMI ANËSOR

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;

I njëjti opsion lejon, nëse është e nevojshme, të kthehen menjëherë disa të dhëna nga rekordi Y i lidhur i gjetur. Një opsion i ngjashëm diskutohet në artikull "PostgreSQL Antipatterns: një rekord i rrallë do të arrijë në mes të një JOIN".

"Pse të paguani më shumë": DISTINCT [ON] + LIMIT 1

Një përfitim shtesë i transformimeve të tilla të pyetjeve është aftësia për të kufizuar lehtësisht kërkimin për regjistrime nëse nevojiten vetëm një ose disa prej tyre, si në rastin e mëposhtëm:

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

Tani ne lexojmë kërkesën dhe përpiqemi të kuptojmë se çfarë propozohet të bëjë DBMS:

• duke lidhur shenjat
• unike nga X.pk
• nga hyrjet e mbetura, zgjidhni një

Pra, çfarë keni marrë? "Vetëm një hyrje" nga ato unike - dhe nëse marrim këtë nga ato jo unike, a do të ndryshojë rezultati disi?.. "Dhe nëse nuk ka ndryshim, pse të paguajmë më shumë?"

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

Dhe saktësisht e njëjta temë me GROUP BY + LIMIT 1.

"Më duhet vetëm të pyes": i nënkuptuar GROUP + LIMIT

Gjëra të ngjashme ndodhin në të ndryshme kontrollet e moszbrazëtisë shenja ose CTE ndërsa kërkesa përparon:

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

Funksionet agregate (count/min/max/sum/...) ekzekutohen me sukses në të gjithë grupin, edhe pa udhëzime të qarta GROUP BY. Vetëm me LIMIT ata nuk janë shumë miqësorë.

Zhvilluesi mund të mendojë "Nëse ka regjistrime atje, atëherë nuk më duhet më shumë se LIMIT". Por mos e bëni këtë! Sepse për bazën është:

• numërojnë çfarë duan sipas të gjitha shënimeve
• jepni aq rreshta sa të kërkojnë

Në varësi të kushteve të synuara, është e përshtatshme të bëhet një nga zëvendësimet e mëposhtme:

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

"Sa duhet varur në gram": TË NDRYSHME + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Një zhvillues naiv mund të besojë sinqerisht se kërkesa do të ndalojë së ekzekutuari. sapo gjejmë 1$ nga vlerat e para të ndryshme që hasim.

Diku në të ardhmen kjo mund dhe do të funksionojë falë një nyje të re Skanimi i Kapërcimit të Indeksit, zbatimi i të cilit aktualisht është duke u përpunuar, por jo ende.

Tani për tani së pari të gjitha të dhënat do të merren, janë unike dhe vetëm prej tyre do të kthehet shuma e kërkuar. Është veçanërisht e trishtueshme nëse dëshirojmë diçka të tillë $ 1 = 4, dhe ka qindra mijëra rekorde në tabelë...

Për të mos u trishtuar më kot, le të përdorim një pyetje rekursive "DISTINCT është për të varfërit" nga PostgreSQL Wiki:

Burimi: www.habr.com