🥇PostgreSQL Antipatterns: I-JOIN enobungozi kunye OKANYE

Lumkela ukusebenza okuzisa izithinteli...
Sisebenzisa umbuzo omncinci njengomzekelo, makhe sijonge ezinye iindlela zehlabathi zokuphucula imibuzo kwiPostgreSQL. Ukuba uyazisebenzisa okanye awuzisebenzisi kukuwe, kodwa kufanelekile ukwazi ngazo.

Kwezinye iinguqulelo ezilandelayo ze-PG imeko inokutshintsha njengoko umcwangcisi esiba krelekrele, kodwa ku-9.4/9.6 ijongeka ngokufana ngokufanayo, njengakwimizekelo elapha.

Masithathe isicelo sokwenyani:

SELECT
  TRUE
FROM
  "Документ" d
INNER JOIN
  "ДокументРасширение" doc_ex
    USING("@Документ")
INNER JOIN
  "ТипДокумента" t_doc ON
    t_doc."@ТипДокумента" = d."ТипДокумента"
WHERE
  (d."Лицо3" = 19091 or d."Сотрудник" = 19091) AND
  d."$Черновик" IS NULL AND
  d."Удален" IS NOT TRUE AND
  doc_ex."Состояние"[1] IS TRUE AND
  t_doc."ТипДокумента" = 'ПланРабот'
LIMIT 1;

malunga netafile kunye namagama entsimiAmagama "esiRashiya" amasimi kunye neetafile angaphathwa ngendlela eyahlukileyo, kodwa lo mbandela wokunambitha. Ngenxa yokuba apha eTensor akukho baphuhlisi bangaphandle, kwaye i-PostgreSQL isivumela ukuba sinike amagama nakwi-hieroglyphs, ukuba kunjalo efakwe kwizicatshulwa, emva koko sikhetha ukubiza izinto ngokucacileyo nangokucacileyo ukuze kungabikho zintlukwano.
Makhe sijonge isicwangciso sesiphumo:
I-PostgreSQL Antipatterns: I-JOIN enobungozi kunye ne-ORs
[Jonga apha explain.tensor.ru]

144ms kwaye phantse i-53K buffers - oko kukuthi, ngaphezu kwe-400MB yedatha! Kwaye siya kuba nethamsanqa ukuba zonke zikwi-cache ngexesha lesicelo sethu, kungenjalo kuya kuthatha amaxesha amaninzi xa kufundwa kwidiski.

I-algorithm ibaluleke kakhulu!

Ukuze ngandlela ithile wenze nasiphi na isicelo, kufuneka uqale uqonde ukuba masikwenze ntoni na.
Masilushiye uphuhliso lwesiseko sedatha ngokwayo ngaphandle kobubanzi beli nqaku okwangoku, kwaye sivume ukuba sinakho "ngexabiso eliphantsi" bhala kwakhona isicelo kunye/okanye siqengqeleke kwisiseko ezinye zezinto esizidingayo indices.

Ngoko isicelo:
— ijonga ubukho boxwebhu oluthile noko
-kwimeko esiyifunayo kunye nohlobo oluthile
- apho umbhali okanye umenzi engumsebenzi esimdingayo

JOIN + UMDA 1

Rhoqo kubalula kumphuhlisi ukuba abhale umbuzo apho inani elikhulu leetafile zidityaniswa kuqala, kwaye emva koko irekhodi enye kuphela eseleyo kule seti iyonke. Kodwa kulula kumphuhlisi akuthethi ukusebenza kakuhle kwisiseko sedatha.
Kwimeko yethu kwakukho iitafile ezi-3 kuphela - kwaye yintoni umphumo ...

Masiqale silahle unxibelelwano kunye netheyibhile "yohlobo loXwebhu", kwaye kwangaxeshanye sixelele isiseko sedatha ukuba uhlobo lwethu irekhodi unique (siyayazi le nto, kodwa umcwangcisi akanalo nofifi okwangoku):

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
  LIMIT 1
)
...
WHERE
  d."ТипДокумента" = (TABLE T)
...

Ewe, ukuba itafile/CTE iqulathe umhlaba omnye werekhodi enye, ngoko kwiPG ungabhala ngolu hlobo, endaweni yokuba

d."ТипДокумента" = (SELECT "@ТипДокумента" FROM T LIMIT 1)

Uvavanyo lobuvila kwimibuzo yePostgreSQL

BitmapOr vs UNION

Kwezinye iimeko, i-Bitmap Heap Scan iya kusixabisa kakhulu-umzekelo, kwimeko yethu, xa iirekhodi ezininzi zidibana nemeko efunekayo. Siyifumene ngenxa yokuba OKANYE imeko iguqulwe yaba yiBitmapOr- ukusebenza kwisicwangciso.
Masibuyele kwingxaki yokuqala - kufuneka sifumane irekhodi elihambelanayo nabani na ukusuka kwiimeko - oko kukuthi, akukho mfuneko yokukhangela zonke iirekhodi ze-59K phantsi kweemeko zombini. Kukho indlela yokwenza imeko enye, kwaye yiya kowesibini kuphela xa kungafunyanwanga nto kweyokuqala. Uyilo olulandelayo luya kusinceda:

(
  SELECT
    ...
  LIMIT 1
)
UNION ALL
(
  SELECT
    ...
  LIMIT 1
)
LIMIT 1

"Ngaphandle" UMDA we-1 uqinisekisa ukuba uphendlo luphela xa irekhodi yokuqala ifunyenwe. Kwaye ukuba sele ifunyenwe kwibhloko yokuqala, eyesibini ibhloko ayisayi kuphunyezwa.zange abulawe Ukuthobela u).

"Ukufihla iimeko ezinzima phantsi kwe-CASE"

Kukho umzuzu ongathandekiyo kakhulu kumbuzo wokuqala - ukujonga imeko ngokuchasene netheyibhile enxulumeneyo "Ukwandiswa koXwebhu". Nokuba yinyani yezinye iimeko kwintetho (umzekelo, d.“Icinyiwe” AYIYONYANISO), olu xhulumaniso luhlala luqhutywa kwaye "izibonelelo zeendleko". Okuninzi okanye ngaphantsi kwazo kuya kuchithwa - kuxhomekeke kubukhulu bale tafile.
Kodwa ungawuguqula umbuzo ukuze ukhangelo lwerekhodi enxulumeneyo lwenzekile kuphela xa luyimfuneko ngokwenene:

SELECT
  ...
FROM
  "Документ" d
WHERE
  ... /*index cond*/ AND
  CASE
    WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
      SELECT
        "Состояние"[1] IS TRUE
      FROM
        "ДокументРасширение"
      WHERE
        "@Документ" = d."@Документ"
    )
  END

Kanye ukusuka kwitafile edityanisiweyo kuthi Akukho nanye imimandla efunekayo kwisiphumo, ke sinethuba lokujika JOIN ibe yimeko kwi-subquery.
Masishiye amasimi ane-indexed "ngaphandle kweebakaki ze-CASE", yongeza iimeko ezilula ukusuka kwirekhodi ukuya kwi-WHEN ibhloko - kwaye ngoku umbuzo "onzima" uphunyezwa kuphela xa udlula kwi-THEN.

Ifani yam ngu "Total"

Siqokelela umbuzo onesiphumo nabo bonke oomatshini abachazwe ngasentla:

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
)
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("Лицо3", "ТипДокумента") = (19091, (TABLE T)) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
UNION ALL
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("ТипДокумента", "Сотрудник") = ((TABLE T), 19091) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
LIMIT 1;

Ukulungelelanisa [kwi] izalathi

Iliso eliqeqeshiweyo laqaphela ukuba iimeko ezifakwe kwi-indexed subblocks ze-UNION zihluke kancinane - oku kungenxa yokuba sele sinezalathisi ezifanelekileyo kwitafile. Kwaye ukuba bezingekho, kuya kuba kufanelekile ukudala: Uxwebhu(uMntu3, uhlobo loxwebhu) и Uxwebhu(Uhlobo loxwebhu, uMsebenzi).
malunga nomyalelo wemihlaba kwiimeko zeROWUkusuka kwindawo yokujonga umcwangcisi, ngokuqinisekileyo, ungabhala (A, B) = (constA, constB)kwaye (B, A) = (constB, constA). Kodwa xa urekhoda ngolandelelwano lwemihlaba kwisalathiso, isicelo esinjalo silunge ngakumbi ukulungisa ingxaki kamva.
Yintoni ekwisicwangciso?
I-PostgreSQL Antipatterns: I-JOIN enobungozi kunye ne-ORs
[Jonga apha explain.tensor.ru]

Ngelishwa, sasinethamsanqa kwaye akukho nto ifunyenwe kwibhloko yokuqala ye-UNION, ngoko ke eyesibini yayisabulawa. Kodwa nangona kunjalo - kuphela 0.037ms kunye ne-11 buffers!
Siye sakhawuleza isicelo kwaye sanciphisa ukupompa idatha kwimemori amawaka aliqela amaxesha, usebenzisa iindlela ezilula ngokufanelekileyo-isiphumo esihle kunye nekopi encinci-uncamathisele. 🙂

umthombo: www.habr.com