I-PostgreSQL Antipatterns: "Kufanele kusele eyodwa kuphela!"

Ku-SQL, uchaza ukuthi "yini" ofuna ukuyifinyelela, hhayi "kanjani" okufanele yenziwe. Ngakho-ke, inkinga yokuthuthukisa imibuzo ye-SQL ngesitayela sokuthi "njengoba kuzwakala ukuthi ibhalwe kanjani" ithatha indawo yayo yokuhlonipha, kanye izici zokubala izimo ku-SQL.

Namuhla, sisebenzisa izibonelo ezilula kakhulu, ake sibone ukuthi lokhu kungaholela kuphi kumongo wokusetshenziswa GROUP/DISTINCT и LIMIT nabo.

Manje, uma ubhale esicelweni “qala uxhume lezi zimpawu, bese ulahla zonke izimpinda, kufanele kusele oyedwa kuphela kopisha ukhiye ngamunye" - yiyona ndlela ezosebenza ngayo, noma ngabe uxhumano lwalungadingeki nhlobo.

Futhi ngezinye izikhathi unenhlanhla futhi "isebenza nje", ngezinye izikhathi inomphumela ongemuhle ekusebenzeni, futhi ngezinye izikhathi inikeza imiphumela engalindelekile ngokuphelele ekubukeni komthuthukisi.

Hhayi-ke, mhlawumbe akuyona into emangalisayo, kodwa ...

“Izithandani ezinhle”: JOIN + DIISTINCT

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

Bekuzocaca ukuthi bafunani khetha amarekhodi X okukhona kuwo amarekhodi kokuthi Y ahlobene nesimo esigcwalisekile. Ubhale isicelo nge JOIN - uthole amanani we-pk izikhathi eziningana (ngisho ukuthi mangaki okufakiwe afanelekile ku-Y). Indlela ukususa? Impela DISTINCT!

Kuyajabulisa kakhulu uma kurekhodi lika-X ngalinye kunamakhulu amaningana ahlobene namarekhodi ka-Y, bese okuphindiwe kususwa ngobuqhawe...

Ungalungisa kanjani? Okokuqala, qaphela ukuthi inkinga ingalungiseka "khetha amarekhodi X okuthi ngo-Y kube okungenani ELILODWA elihlotshaniswa nesimo esigcwalisekile" - emva kwakho konke, asidingi lutho oluvela ku-Y-rekhodi ngokwayo.

Isidleke EXISTS

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

Ezinye izinguqulo ze-PostgreSQL ziyaqonda ukuthi ku-EXISTS kwanele ukuthola ukufakwa kokuqala okuvela, abadala abakwenzi. Ngakho-ke ngikhetha ukukhombisa njalo LIMIT 1 ngaphakathi EXISTS.

I-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;

Inketho efanayo ivumela, uma kunesidingo, ukubuyisela ngokushesha idatha ethile kurekhodi elihlotshaniswa ne-Y elitholiwe. Inketho efanayo kuxoxwa ngayo esihlokweni "I-PostgreSQL Antipatterns: irekhodi eliyivelakancane lizofinyelela phakathi kwe-JOIN".

“Kungani ukhokha kakhulu”: DISTINCT [ON] + LIMIT 1

Inzuzo eyengeziwe yokuguqulwa kwemibuzo enjalo yikhono lokukhawulela kalula ukusesha amarekhodi uma kudingeka elilodwa noma ambalwa kuphela, njengalokhu okulandelayo:

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

Manje sifunda isicelo futhi sizame ukuqonda ukuthi i-DBMS ihlongozwa ukwenzani:

• ukuhlanganisa izimpawu
• ihlukile nge-X.pk
• kokufakiwe okusele, khetha okukodwa

Pho utholeni? "Kunye nje ukungena" kwabahlukile - futhi uma sithatha lena kwezingezona ezehlukile, ingabe umphumela uzoshintsha ngandlela thile?.. "Futhi uma ungekho umehluko, kungani ukhokha ngaphezulu?"

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

Futhi isihloko esifanayo ne GROUP BY + LIMIT 1.

"Kuzomele ngibuze": I-GROUP ecacile + LIMIT

Izinto ezifanayo zenzeka ngezindlela ezihlukahlukene amasheke angenalutho izimpawu noma ama-CTE njengoba isicelo siqhubeka:

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

Hlanganisa imisebenzi (count/min/max/sum/...) asetshenziswe ngempumelelo kulo lonke isethi, ngisho nangaphandle kweziqondiso ezicacile GROUP BY. Kuphela nge LIMIT abanabungane kakhulu.

Umthuthukisi angacabanga "uma kukhona amarekhodi lapho, angidingi ngaphezu kwe-LIMIT". Kodwa ungakwenzi lokho! Ngoba ngokwesisekelo kuba:

• bala lokho abakufunayo ngokwawo wonke amarekhodi
• banikeze imigqa eminingi njengoba becela

Ngokuya ngezimo ezihlosiwe, kufanelekile ukwenza okukodwa kwalokhu okulandelayo:

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

“Imalini okumele ilenge ngamagremu”: DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Unjiniyela ongenalwazi angase akholelwe ngobuqotho ukuthi isicelo sizoyeka ukusebenza. ngokushesha nje lapho sithola u-$1 wamanani okuqala ahlukene afikayo.

Ngesinye isikhathi esikhathini esizayo lokhu kungase futhi kuzosebenza ngenxa ye-node entsha Inkomba Yeqa Iskena, ukuqaliswa okusalungiswa okwamanje, kodwa okwamanje.

Okwamanje kuqala wonke amarekhodi azolandwa, zihlukile, futhi kuzobuyiswa kubo kuphela inani eliceliwe. Kuyadabukisa ikakhulukazi uma sifuna into enjalo $ 1 = 4, futhi kunamakhulu ezinkulungwane zamarekhodi etafuleni...

Ukuze singadabuki ngeze, masisebenzise umbuzo ophindaphindayo "I-DISTINCT eyabampofu" isuka ku-PostgreSQL Wiki:

Source: www.habr.com