ProHoster >> Blog >> Administrimi >> Antipatterns të PostgreSQL: JOIN dhe OR të dëmshme

Antipatterns të PostgreSQL: JOIN dhe OR të dëmshme

Kujdesi për operacionet, bufferat që sjellin

Do tĂ« shqyrtojmĂ« disa qasje universale pĂ«r optimizimin e kĂ«rkesave nĂ« PostgreSQL pĂ«rmes njĂ« kĂ«rkese tĂ« vogĂ«l. TĂ« pĂ«rdoren ato apo jo — Ă«shtĂ« zgjedhja juaj, por ia vlen tĂ« jeni tĂ« informuar pĂ«r to.

Në ndonjë version të ardhshëm të PG, situata mund të ndryshojë me "inteligjencën" e planifikuesit, por për 9.4/9.6 ajo duket përafërsisht e njëjtë siç janë këtu shembujt.

Do të marrë një kërkesë mjaft reale:

SELECT
  TRUE
FROM
  "Dokument" d
INNER JOIN
  "DokumentRrëfyes" doc_ex
    USING("@Dokument")
INNER JOIN
  "TipiDokumentit" t_doc ON
    t_doc."@TipiDokumentit" = d."TipiDokumentit"
WHERE
  (d."Lënda3" = 19091 or d."Punonjësi" = 19091) AND
  d."$Kthimi" IS NULL AND
  d."Fshier" IS NOT TRUE AND
  doc_ex."Statusi"[1] IS TRUE AND
  t_doc."TipiDokumentit" = 'PlanPunes'
LIMIT 1;

për emrat e tabelave dhe fushaveNë lidhje me emrat "rusë" të fushave dhe tabelave, mund të keni mendime të ndryshme, por kjo është çështje shije. Pasi që në "Tensorin" tonë nuk kemi zhvillues të huaj, dhe PostgreSQL na lejon të jepni emra madje edhe me hieroglifë, për sa kohë që janë të rrethuara me thonjëza, ne preferojmë të emërojmë objektet në mënyrë të qartë dhe të kuptueshme, në mënyrë që të mos ketë keqkuptime.
Le të shohim planin e marrë:
Antipatterns të PostgreSQL: JOIN dhe OR të dëmshme
[shiko në explain.tensor.ru]

144ms dhe gati 53K buffer — do tĂ« thotĂ« mĂ« shumĂ« se 400MB tĂ« dhĂ«nash! Dhe do tĂ« kemi fat nĂ«se tĂ« gjitha ato do tĂ« ishin nĂ« cache nĂ« momentin e kĂ«rkesĂ«s tonĂ«, pĂ«rndryshe ajo do tĂ« zgjasĂ« disa herĂ« mĂ« gjatĂ« kur tĂ« lexojmĂ« nga disku.

Algoritmi është më i rëndësishëm se gjithçka!

Për të optimizuar ndonjë kërkesë, së pari duhet të kuptojmë çfarë duhet të bëjë ajo.
Të lëmë jashtë kësaj artikulli zhvillimin e strukturës së DB-së, dhe të pajtohemi që ne mund ta riformatojmë kërkesën dhe/ose të aplikojmë disa indekse.

Kështu, kërkesa:
— kontrollon ekzistencĂ«n e ndonjĂ« dokumenti
— nĂ« gjendjen e nevojshme dhe tĂ« njĂ« tipi tĂ« caktuar
— ku autori ose realizuesi Ă«shtĂ« punonjĂ«si ynĂ« i nevojshĂ«m

JOIN + LIMIT 1

ShpeshherĂ«, zhvilluesit e preferojnĂ« tĂ« shkruajnĂ« njĂ« kĂ«rkesĂ« ku fillimisht lidhen shumĂ« tabela, dhe pastaj nga gjithĂ« ato mbetet vetĂ«m njĂ« regjistrim. Por mĂ« e lehta pĂ«r zhvilluesin — nuk do tĂ« thotĂ« mĂ« efektive pĂ«r DB-nĂ«.
NĂ« rastin tonĂ« kishte vetĂ«m 3 tabela — dhe çfarĂ« efekti


Le të fillojmë duke eliminuar lidhjen me tabelën "TipDokumenti", ndërkohë që i tregojmë DB-së se regjistrimi i tipit tonë është unik (ne e dimë, por planifikuesi ende nuk e ka kuptuar):

ME T SI (
  ZGJIDHJA
    "@LlojiDokumentit"
  NGA
    "LlojiDokumentit"
  KU
    "LlojiDokumentit" = 'Plani i Punës'
  KUFIZO 1
)
...
KU
  d."LlojiDokumentit" = (TABELA T)
...

Po, nëse tabela/CTE përbëhet nga një fushë e vetme me një regjistër vetëm, mund ta shkruajmë edhe kështu në PG, në vend të

d."LlojiDokumentit" = (ZGJIDH "@LlojiDokumentit" NGA T KUFIZO 1)

Llogaritjet «lenjase» në kërkesat PostgreSQL

BitmapOr vs UNION

NĂ« disa raste, Bitmap Heap Scan do tĂ« na kushtojĂ« shumĂ« — pĂ«r shembull, nĂ« situatĂ«n tonĂ«, ku mjaft regjistra bien nĂ«n kushtin e kĂ«rkuar. E kemi marrĂ« kĂ«tĂ« pĂ«r shkak tĂ« kushtit OR, i transformuar nĂ« BitmapOr-operimin nĂ« plan.
Le tĂ« kthehemi te detyra fillestare — duhet tĂ« gjejmĂ« regjistrin qĂ« i pĂ«rgjigjet ndonjĂ«rit nga kushtet — do tĂ« thotĂ« nuk ka nevojĂ« tĂ« kĂ«rkojmĂ« tĂ« gjitha 59K regjistrat pĂ«r tĂ« dy kushtet. Ekziston njĂ« mĂ«nyrĂ« pĂ«r tĂ« pĂ«rfunduar njĂ« kusht, dhe pĂ«r tĂ« kaluar te tjetri vetĂ«m kur pĂ«r tĂ« parin nuk gjendet asgjĂ«. NjĂ« konstrukcion i tillĂ« do na ndihmojĂ«:

(
  ZGJIDHJA
    ...
  KUFIZO 1
)
UNION ALL
(
  ZGJIDHJA
    ...
  KUFIZO 1
)
KUFIZO 1

LIMIT 1 garanton që kërkimi do të mbyllet sapo të gjendet regjistri i parë. Dhe nëse ai gjendet në blokun e parë, ekzekutimi i dytë nuk do të kryhet (nuk ekzekutohet kurrë në plan).

«Fshehim nën CASE» kushtet e komplikuara

NjĂ« moment shumĂ« i pakĂ«ndshĂ«m nĂ« kĂ«rkesĂ«n origjinale Ă«shtĂ« kontrolli i gjendjes nĂ« tabelĂ«n e lidhur «DokumentZgjerimi». PavarĂ«sisht nga vĂ«rtetĂ«sia e kushteve tĂ« tjera nĂ« shprehje (pĂ«r shembull, d.«Fshirë» NUK ËSHTË E VERTETË), ky lidhje kryhet gjithmonĂ« dhe «kushton burime». MĂ« shumĂ« ose mĂ« pak do tĂ« shpenzohen - varet nga volumi i kĂ«saj tabele.
Por mund të modifikojmë kërkesën në mënyrë që kërkimi i regjistrit të lidhur të ndodhte vetëm kur kjo është në të vërtetë e nevojshme:

SELECT
  ...
FROM
  "Dokument" d
WHERE
  ... /*kusht index*/ DHE
  CASE
    KUR "$Draft" ËSHTË NULL DHE "FshirĂ«" NUK ËSHTË E VERTETË ATËHERË (
      SELECT
        "Gjendja"[1] ËSHTË E VERTETË
      FROM
        "DokumentZgjerimi"
      WHERE
        "@Dokument" = d."@Dokument"
    )
  FUND

Pasi nga tabela e lidhur nuk na duhen asnjë nga fushat, atëherë kemi mundësinë ta kthejmë JOIN në kusht sipas nënkërkese.
LĂ«rini fushat e indeksuara «jashtë» CASE, kushtet e thjeshta i pĂ«rfshijmĂ« nĂ« bllokun WHEN — dhe tani kĂ«rkesa «e rĂ«ndë» ekzekutohet vetĂ«m kur kalojmĂ« nĂ« THEN.

Mënyra ime «Të gjitha»

Krijojmë kërkesën përfundimtare me të gjitha mekanizmat e përshkruar më sipër:

ME T SI (
  ZGJIDH
    "@TipiDokumenti"
  NGA
    "TipiDokumentit"
  KU
    "TipiDokumentit" = 'Plani i Punës'
)
  (
    ZGJIDH
      E VERTETË
    NGA
      "Dokumenti" d
    KU
      ("Persona3", "TipiDokumentit") = (19091, (TABELA T)) DHE
      RASTI
        KUR "$Draft" ËSHTE NULL DHE "FshirĂ«" NUK ËSHTE E VERTETË atĂ«herĂ« (
          ZGJIDH
            "G状态"[1] ËSHTE E VERTETË
          NGA
            "DokumentiZgjatje"
          KU
            "@Dokumenti" = d."@Dokumenti"
        )
      FUND
    KUFIZO 1
  )
BASHKOHUNI TË GJITHA
  (
    ZGJIDH
      E VERTETË
    NGA
      "Dokumenti" d
    KU
      ("TipiDokumentit", "Punonjësi") = ((TABELA T), 19091) DHE
      RASTI
        KUR "$Draft" ËSHTE NULL DHE "FshirĂ«" NUK ËSHTE E VERTETË atĂ«herĂ« (
          ZGJIDH
            "G状态"[1] ËSHTE E VERTETË
          NGA
            "DokumentiZgjatje"
          KU
            "@Dokumenti" = d."@Dokumenti"
        )
      FUND
    KUFIZO 1
  )
KUFIZO 1;

Përshtatëm [për] indeksat

Syri i zakonshĂ«m vuri re se kushtet e indeksuara nĂ« nĂ«nblloqet UNION pak ndryshojnĂ« — kjo Ă«shtĂ« sepse tashmĂ« kemi indeksat e duhur nĂ« tabelĂ«. NĂ«se nuk do tĂ« kishte, atĂ«herĂ« do tĂ« ishte e nevojshme tĂ« krijohej: Dokumenti(Faqe3, LlojiDokumentit) dhe Dokumenti(LlojiDokumentit, PunonjĂ«si).
rreth rendit të fushave në kushtet ROWSipas planifikuesit, natyrisht, mund të shkruhet dhe (A, B) = (constA, constB), dhe (B, A) = (constB, constA). Por gjatë regjistrimit në rendin e fushave në indeks, një kërkesë e tillë është thjesht më e lehtë për debug.
ÇfarĂ« ka nĂ« plan?
Antipatterns të PostgreSQL: JOIN dhe OR të dëmshme
[shiko në explain.tensor.ru]

FatkeqĂ«sisht, nuk patĂ«m fat, dhe nĂ« bllokun e parĂ« UNION nuk u gjet asgjĂ«, prandaj i dyti ndoqi megjithatĂ« pĂ«r ekzekutim. Por edhe kĂ«shtu — vetĂ«m 0.037ms dhe 11 buffers!
Ne rritĂ«m shpejtĂ«sinĂ« e kĂ«rkesĂ«s dhe reduktuam "shkarkimin" e tĂ« dhĂ«nave nĂ« memorie nĂ« disa mijĂ«ra herĂ«, duke pĂ«rdorur metoda mjaft tĂ« thjeshta — njĂ« rezultat i shkĂ«lqyer pĂ«r njĂ« kopje tĂ« vogĂ«l. 🙂

Burimi: habr.com

Bli njĂ« hosting tĂ« besueshĂ«m pĂ«r faqet me mbrojtje DDoS, VPS VDS serverĂ« đŸ”„ Bli njĂ« hosting tĂ« besueshĂ«m pĂ«r faqet me mbrojtje DDoS, VPS VDS serverĂ« | ProHoster