PostgreSQL Antipatterns: "Payenera kukhala imodzi yokha!"

Mu SQL, mumafotokoza "zomwe" mukufuna kukwaniritsa, osati "momwe" ziyenera kuchitidwa. Chifukwa chake, vuto lopanga mafunso a SQL mumayendedwe a "monga momwe zimamvekera momwe zidalembedwera" limatenga malo ake aulemu, limodzi ndi mawonekedwe a kuwerengera zinthu mu SQL.

Masiku ano, pogwiritsa ntchito zitsanzo zosavuta kwambiri, tiyeni tiwone zomwe izi zingatsogolere pakugwiritsa ntchito GROUP/DISTINCT и LIMIT ndi iwo.

Tsopano, ngati inu munalemba mu pempho "Choyamba, gwirizanitsani zizindikiro izi, kenako ndikutaya zobwereza zonse, pakhale mmodzi yekha wotsala kope pa kiyi iliyonse" - izi ndi momwe zimagwirira ntchito, ngakhale kulumikizana sikunali kofunikira konse.

Ndipo nthawi zina mumakhala ndi mwayi ndipo "zimangogwira ntchito", nthawi zina zimakhala ndi zotsatira zosasangalatsa pa ntchito, ndipo nthawi zina zimapereka zotsatira zomwe sizimayembekezereka kuchokera kwa wopanga.

Chabwino, mwina osati zochititsa chidwi, koma ...

"Okondedwa": JOIN + DISTINCT

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

Zikadadziwika zomwe akufuna sankhani zolemba X zomwe muli zolembedwa mu Y zomwe zikugwirizana ndi kukwaniritsidwa kwake. Adalemba pempho kudzera JOIN - ali ndi ma pk angapo kangapo (ndendende zingati zomwe zidawonekera mu Y). Kodi kuchotsa? Ndithudi DISTINCT!

"Ndizosangalatsa" makamaka ngati pa X-rekodi iliyonse pali mazana angapo okhudzana ndi ma Y, ndiyeno zobwereza zimachotsedwa mwaulemu ...

Kodi kukonza bwanji? Choyamba, dziwani kuti vutoli likhoza kusinthidwa "sankhani zolemba za X zomwe mu Y muli ZOSAWIRITSA NTCHITO imodzi yokhudzana ndi kukwaniritsidwa" - pambuyo pake, sitifuna kalikonse kuchokera ku Y-rekodi yokha.

Zosungidwa EXISTS

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

Mabaibulo ena a PostgreSQL amamvetsetsa kuti mu EXISTS ndikokwanira kupeza cholowa choyamba chomwe chimabwera, okalamba samatero. Chifukwa chake ndimakonda kuwonetsa nthawi zonse LIMIT 1 mkati 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;

Njira yomweyi imalola, ngati kuli kofunikira, kubweza nthawi yomweyo zina kuchokera ku Y-rekodi yomwe idapezeka. Njira yofananayi ikufotokozedwa m'nkhaniyo "PostgreSQL Antipatterns: mbiri yosowa idzafika pakati pa JOIN".

"Chifukwa chiyani mumalipira zambiri": DIISTINCT [ON] + LIMIT 1

Phindu linanso lakusintha kwamafunso koteroko ndikutha kuchepetsa kusakira ma rekodi ngati imodzi yokha kapena zingapo zikufunika, monga momwe zilili:

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

Tsopano tikuwerenga pempholo ndikuyesera kumvetsetsa zomwe DBMS ikufuna kuchita:

• kugwirizana zizindikiro
• wapadera ndi X.pk
• kuchokera pazotsalira zotsalira, sankhani chimodzi

Ndiye mwapeza chiyani? "Kungolowa kamodzi" kuchokera kwapadera - ndipo ngati titenga iyi mwa ena omwe si apadera, kodi zotsatira zake zidzasintha mwanjira ina?.. "Ndipo ngati palibe kusiyana, bwanji kulipira zambiri?"

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

Ndipo ndendende mutu womwewo ndi GROUP BY + LIMIT 1.

"Ndingofunsa": GROUP + LIMIT

Zinthu zofanana zimachitika mosiyanasiyana macheke opanda pake zizindikiro kapena CTEs pamene pempho likupita patsogolo:

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

Ntchito zophatikiza (count/min/max/sum/...) amachitidwa bwino pagulu lonse, ngakhale popanda malangizo omveka bwino GROUP BY. Ndi chete LIMIT sali ochezeka kwambiri.

Wokonza akhoza kuganiza "ngati pali zolembedwa pamenepo, ndiye kuti sindikufunika kuposa LIMIT". Koma musachite zimenezo! Chifukwa pa maziko ndi:

• werengera zomwe akufuna molingana ndi zolemba zonse
• perekani mizere yambiri momwe akufunsa

Kutengera zomwe mukufuna, ndikofunikira kusintha chimodzi mwazinthu izi:

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

"Kodi mungapachike ma gramu zingati": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Wopanga wosadziwa akhoza kukhulupirira moona mtima kuti pempholo lisiya kugwira ntchito. tikangopeza $ 1 pazoyambira zosiyanasiyana zomwe zimabwera.

Nthawi zina mtsogolo izi zitha kugwira ntchito chifukwa cha node yatsopano Index Skip Scan, kukhazikitsidwa kwake komwe kukuchitika, koma sikunachitikebe.

Kwa tsopano choyamba zolemba zonse zidzachotsedwa, ndi apadera, ndipo ndalama zimene mwapemphazo zidzabwezedwa kuchokera kwa iwo okha. Ndizomvetsa chisoni kwambiri ngati tikufuna chinachake chonga $ 1 = 4, ndipo pali mazana masauzande a zolemba patebulo ...

Kuti tisakhale achisoni pachabe, tiyeni tigwiritse ntchito funso lobwerezabwereza "DISTINCT ndi ya osauka" kuchokera ku PostgreSQL Wiki:

Source: www.habr.com