Antipattern të PostgreSQL: «Duhet të mbetet vetëm një!»

Në SQL përshkruani «çfarë» dëshironi të merrni, jo «si» duhet të ekzekutohet. Pra, problemi i zhvillimit të pyetjeve SQL në stilin «si shprehet, ashtu dhe shkruhet» zë vendin e tij të nderuar, përkrah veçorive të llogaritjes së kushteve në SQL.

Sot do të shohim shembuj shumë të thjeshtë për atë çfarë mund të sjellë në kontekstin e përdorimit të GROUP/DISTINCT dhe LIMIT ndërsa bashkë me ta.

Ja, nëse keni shkruar në pyetje «së pari bashkoni këto tabela, pastaj hiqni të gjitha kopjet, duhet të mbetet vetëm një ekzemplar për çdo çelës» — ashtu do të funksionojë, edhe nëse bashkimi nuk ka qenë i nevojshëm fare.

Dhe ndonjëherë ndodh që thjesht «punon», ndonjëherë — ndikon negativisht në performancë, e ndonjëherë sjell efekte krejtësisht të papritura nga pikëpamja e zhvilluesit.

Epo, ndoshta jo aq teatrale, por…

«Çifti i ëmbël»: JOIN + DISTINCT

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

Sa e qartë është se donit të zgjidhni këto regjistrime X, për të cilat në Y ka lidhje me kushtin që po ekzekutohet. E shkruajtët pyetjen përmes JOIN — morëm disa vlera pk disa herë (saktësisht sa shumë regjistra të përshtatshëm në Y rezultuan). Si ta eliminojmë? Sigurisht DISTINCT!

Sidomos ‘gëzon’ kur për çdo regjistër X gjenden disa qindra regjistra të lidhur Y, dhe më pas heroikisht hiqen dublikatet...

Si ta rregullojmë? Së pari, të kuptojmë se detyra mund të modifikohet në ‘të përzgjidhen regjistrat X, për të cilat në Y ka TË PAKTËN NJË regjistër të lidhur me kushtin e ekzekutimit’ — sepse nga regjistri Y vetë nuk na nevojitet asgjë.

Nënshkrimi EXISTS

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ë gjendet regjistri i parë që kapet, versionet më të vjetra — jo. Prandaj preferoj gjithmonë të tregoj LIMIT 1 brenda 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;

Ky variant gjithashtu lejon, nëse është e nevojshme, të kthehen menjëherë disa të dhëna nga regjistri i lidhur Y që u gjet. Një variant i ngjashëm është shqyrtuar në artikullin ‘PostgreSQL Antipatterns: një regjistër i rrallë arrin në mes të JOIN’.

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

Një avantazh shtesë i këtij lloji të transformimeve të pyetjeve është mundësia për të kufizuar lehtësisht ciklin e regjistrimeve, nëse nevojitet vetëm një/disa prej tyre, si në rastin në vijim:

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

Tani lexohet pyetja dhe përpiqemi të kuptojmë se çfarë propozohet të bëjë DBMS:

• kemi lidhur tabelat
• e bëjmë unike sipas X.pk
• nga regjistrimet e mbetura zgjedhim ndonjë një

Pra, çfarë kemi marrë? «Një regjistrim ndonjë» nga të unifikuar - po sikur ta marrim këtë një nga të paunifikuarit, rezultatet do të ndryshonin ndonjëherë?.. «Po nëse nuk ka ndonjë ndryshim, pse të paguajmë më shumë?»

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- këtu mund të futni kushte të përshtatshme
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Dhe i njëjti qëndrim me GROUP BY + LIMIT 1.

«Më mjafton të pyes»: GROUP i implicit + LIMIT

Këto gjëra hasen në raste të ndryshme kontrollesh për mosrënien bosh tabelash ose CTE gjatë ekzekutimit të pyetjes:

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

Funksionet agregate (count/min/max/sum/...) ekzekutohen me sukses në të gjithë grupin, madje edhe pa specifikimin e qartë GROUP BY. Por ata me LIMIT nuk shkojnë shumë mirë.

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

• llogarita se çfarë dëshirojnë për të gjitha regjistrimet
• kthe sa rreshta kërkohet

Varësisht nga kushtet e synuara, këtu është i përshtatshëm një nga zëvendësimet:

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

«Sa të peshojë në gramë»: DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Një zhvillues naiv mund të mendojë sinqerisht se ekzekutimi i pyetjes do të ndalet sapohesht të gjejmë $1 të parë të ndryshme vlera.

Ndoshta në të ardhmen kjo do të funksionojë falë një node të re Index Skip Scan, realizimi i së cilës aktualisht po punohet, por për momentin — jo.

Për momentin, së pari do të nxirren të gjitha regjistrimet, unikizohen, dhe vetëm nga ato do të kthehet sa kërkohet. Sidomos është e trishtueshme nëse ne dëshirojmë diçka si $1 = 4, ndërsa regjistrimet në tabelë janë qindra mijë...

Për të mos u ndjerë keq kot, le të përdorim një kërkesë rekurzive «DISTINCT për të varfrit» nga PostgreSQL Wiki:

Burimi: habr.com