PostgreSQL Antipatterns- အန္တရာယ်ရှိသော Joins များနှင့် OR များ

buffers များ သယ်ဆောင်လာမည့် လုပ်ဆောင်ချက်များကို သတိပြုပါ...
ဥပမာတစ်ခုအနေဖြင့် သေးငယ်သော query ကိုအသုံးပြု၍ PostgreSQL ရှိ queries များကို အကောင်းဆုံးဖြစ်အောင်ပြုလုပ်ရန် universal approach အချို့ကို ကြည့်ကြပါစို့။ ၎င်းတို့ကို သင်သုံးသည်ဖြစ်စေ မသုံးစွဲသည်ဖြစ်စေ သင့်အပေါ်တွင် မူတည်သော်လည်း ၎င်းတို့အကြောင်းကို သိထားသင့်သည်။

PG ၏နောက်ဆက်တွဲဗားရှင်းအချို့တွင် အချိန်ဇယားဆွဲသူသည် ပိုမိုစမတ်ကျလာသည်နှင့်အမျှ အခြေအနေပြောင်းလဲနိုင်သော်လည်း 9.4/9.6 အတွက် ၎င်းသည် ဤနမူနာများရှိသကဲ့သို့ ခန့်မှန်းခြေအားဖြင့် တူညီပါသည်။

အလွန်မှန်ကန်သော တောင်းဆိုချက်ကို ရယူကြပါစို့။

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;

ဇယားနှင့် အကွက်အမည်များအကြောင်းနယ်ပယ်များနှင့် ဇယားများ၏ "ရုရှား" အမည်များကို ကွဲပြားစွာ ဆက်ဆံနိုင်သော်လည်း ၎င်းသည် အရသာကိစ္စဖြစ်သည်။ အကြောင်းမှာ၊ Tensor မှာ ဒီမှာ နိုင်ငံခြား developer များမရှိပါ၊ နှင့် PostgreSQL သည်ကျွန်ုပ်တို့အား hieroglyphs များဖြင့်ပင်အမည်ပေးခွင့်ပြုသည်ဆိုလျှင်၊ ကိုးကားချက်များတွင် ထည့်သွင်းထားသည်။ထို့ကြောင့် ကျွန်ုပ်တို့သည် ကွဲလွဲမှုများမရှိစေရန် အရာဝတ္တုများကို ရှင်းလင်းပြတ်သားစွာ အမည်ပေးလိုပါသည်။
ရလဒ်အစီအစဉ်ကိုကြည့်ကြပါစို့။

[explain.tensor.ru တွင်ကြည့်ရှုရန်]

144ms နှင့် 53K ကြားခံများနီးပါး ဒေတာ 400MB ထက်ပိုပါတယ်။ ကျွန်ုပ်တို့တောင်းဆိုသည့်အချိန်၌ ၎င်းတို့အားလုံးသည် ကက်ရှ်တွင်ရှိနေပါက၊ သို့မဟုတ်ပါက ဒစ်ခ်မှဖတ်သည့်အခါ အဆများစွာကြာမည်ဖြစ်သည်။

algorithm သည် အရေးကြီးဆုံးဖြစ်သည်။

တောင်းဆိုမှုတိုင်းကို တစ်နည်းနည်းနဲ့ အကောင်းဆုံးဖြစ်အောင်လုပ်ဖို့၊ အဲဒါက ဘာလုပ်သင့်တယ်ဆိုတာ အရင်နားလည်ထားရမယ်။
ယခုအချိန်တွင်၊ ဤဆောင်းပါး၏ဘောင်အပြင်ဘက်တွင် ဒေတာဘေ့စ်တည်ဆောက်ပုံတည်ဆောက်ပုံကို ချန်ထားခဲ့ကာ ကျွန်ုပ်တို့သည် အတော်လေး “စျေးပေါသည်” ကို သဘောတူလိုက်ကြပါစို့။ တောင်းဆိုချက်ကို ပြန်ရေးပါ။ နှင့်/သို့မဟုတ် ကျွန်ုပ်တို့လိုအပ်သောအရာအချို့ကို အခြေခံပေါ်တွင် လူးပေးပါ။ ညွှန်းကိန်း.

ဒါကြောင့် တောင်းဆိုချက်
- အနည်းဆုံး စာရွက်စာတမ်းအချို့ရှိကြောင်း စစ်ဆေးပါ။
- ကျွန်ုပ်တို့လိုအပ်သောအခြေအနေတွင်၊ အမျိုးအစားတစ်ခုဖြစ်သည်။
- စာရေးသူ သို့မဟုတ် ဖျော်ဖြေသူသည် ကျွန်ုပ်တို့လိုအပ်သော ဝန်ထမ်းဖြစ်သည်။

Join + LIMIT ၁

ဇယားအများအပြားကို ဦးစွာထည့်သွင်းထားသည့် ဆော့ဖ်ဝဲရေးသားသူသည် မကြာခဏဆိုသလို စာရေးရန်ပိုမိုလွယ်ကူသည်၊ ထို့နောက် ဤအစုတစ်ခုလုံးမှ မှတ်တမ်းတစ်ခုသာကျန်တော့သည်။ ဒါပေမယ့် developer အတွက် ပိုလွယ်တာက database အတွက် ပိုထိရောက်တယ်လို့ မဆိုလိုပါဘူး။
ငါတို့ကိစ္စမှာ ဇယား ၃ ခုပဲရှိတယ်၊ ဘယ်လိုအကျိုးသက်ရောက်လဲ...

"စာရွက်စာတမ်းအမျိုးအစား" ဇယားနှင့်ချိတ်ဆက်မှုကို ဦးစွာဖယ်ရှားလိုက်ကြပါစို့၊ တစ်ချိန်တည်းတွင်ဒေတာဘေ့စ်ကိုပြောပြပါ။ ကျွန်ုပ်တို့၏ အမျိုးအစားမှတ်တမ်းသည် ထူးခြားသည်။ (ဒါကို ငါတို့သိပေမယ့် အစီအစဉ်ဆွဲသူက မသိသေးဘူး)။

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

ဟုတ်ကဲ့၊ ဇယား/CTE မှာ မှတ်တမ်းတစ်ခုရဲ့ ကွက်လပ်တစ်ခု ပါ၀င်တယ်ဆိုရင် PG မှာ ဒီအစား ဒီလိုမျိုး ရေးလို့တောင်ရပါတယ်။

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

PostgreSQL မေးခွန်းများတွင် ပျင်းရိအကဲဖြတ်ခြင်း။

BitmapOr နှင့် ယူနီယံ

အချို့ကိစ္စများတွင်၊ Bitmap Heap Scan သည် ကျွန်ုပ်တို့အတွက် များစွာကုန်ကျလိမ့်မည် - ဥပမာ၊ မှတ်တမ်းများစွာသည် လိုအပ်သောအခြေအနေနှင့် ကိုက်ညီသောအခါ ကျွန်ုပ်တို့၏အခြေအနေတွင်၊ ဆိုတော့ ရတယ်ပေါ့။ OR အခြေအနေသည် BitmapOr သို့ပြောင်းသွားသည်။- အစီအစဥ်တွင်လုပ်ဆောင်ခြင်း။
မူရင်းပြဿနာကို ပြန်ကြည့်ရအောင် - သက်ဆိုင်တဲ့ မှတ်တမ်းတစ်ခုကို ရှာရမယ်။ ဘယ်သူမဆို အခြေအနေများမှ - ဆိုလိုသည်မှာ၊ အခြေအနေနှစ်ခုလုံးအောက်တွင် 59K မှတ်တမ်းအားလုံးကိုရှာဖွေရန်မလိုအပ်ပါ။ အခြေအနေတစ်ခုတည်းကို အကောင်အထည်ဖော်ရန် နည်းလမ်းတစ်ခုရှိသည်။ ပထမတွင် ဘာမျှမတွေ့သောအခါမှ ဒုတိယသို့သွားပါ။. အောက်ဖော်ပြပါ ဒီဇိုင်းသည် ကျွန်ုပ်တို့ကို ကူညီပေးပါမည်-

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

"ပြင်ပ" ကန့်သတ်ချက် 1 သည် ပထမဆုံးမှတ်တမ်းကို တွေ့ရှိသောအခါ ရှာဖွေမှုပြီးဆုံးကြောင်း သေချာစေသည်။ ပထမဘလောက်တွင်တွေ့ရှိပြီးပါက၊ ဒုတိယဘလောက်ကိုလုပ်ဆောင်မည်မဟုတ်ပါ (ဘယ်တော့မှ မသတ်ဘူး။ လေးစားမှု)။

"CASE အောက်တွင်ခက်ခဲသောအခြေအနေများကိုဖုံးကွယ်ထားရန်"

မူရင်းမေးမြန်းမှုတွင် အလွန်အဆင်မပြေသည့်အခိုက်အတန့်တစ်ခုရှိသည် - ဆက်စပ်ဇယား “DocumentExtension” နှင့် ပတ်သက်သည့် အခြေအနေကို စစ်ဆေးခြင်း။ သမ္မာဝါစာ မခွဲခြားဘဲ အခြားအခြေအနေများ (ဥပမာ၊ ဃ။“ဖျက်ထားသည်” သည် မမှန်ပါ။) ဤချိတ်ဆက်မှုကို အမြဲတမ်းလုပ်ဆောင်ပြီး “အရင်းအမြစ်များကို ကုန်ကျစရိတ်များ” သည်။ ၎င်းတို့ကို များများ သို့မဟုတ် လျှော့သုံးမည် - ဤဇယား၏ အရွယ်အစားပေါ်တွင် မူတည်သည်။
သို့သော် သက်ဆိုင်ရာ မှတ်တမ်းကို ရှာဖွေခြင်းသည် အမှန်တကယ် လိုအပ်သည့်အခါမှသာ သင် မေးမြန်းချက်ကို ပြင်ဆင်နိုင်သည်-

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

ချိတ်ထားတဲ့ စားပွဲကနေ ပြီးတာနဲ့ ကျွန်တော်တို့ကို ပေးတယ်။ ရလဒ်အတွက် အကွက်တစ်ခုမျှ မလိုအပ်ပါ။ထို့နောက် ကျွန်ုပ်တို့သည် JOIN ကို မေးခွန်းခွဲတစ်ခုတွင် အခြေအနေတစ်ခုအဖြစ် ပြောင်းလဲရန် အခွင့်အရေးရှိသည်။
အညွှန်းပြုထားသောအကွက်များကို "Case ကွင်းပိတ်များအပြင်ဘက်" တွင်ထားခဲ့ကာ၊ မှတ်တမ်းမှရိုးရှင်းသောအခြေအနေများကိုပိတ်ဆို့သည့်အခါတွင်ထည့်ပါ - ယခုအချိန်တွင် "လေးလံသော" မေးခွန်းကိုထိုသို့ဖြတ်သန်းသည့်အခါမှသာလုပ်ဆောင်ပါသည်။

ကျွန်တော့်နာမည်က Total ပါ

ကျွန်ုပ်တို့သည် အထက်ဖော်ပြပါ စက်ပြင်အားလုံးဖြင့် ရရှိလာသောမေးခွန်းကို စုဆောင်းသည်-

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;

[သို့] အညွှန်းများကို ချိန်ညှိခြင်း။

UNION အကွက်ခွဲများအတွင်းရှိ အညွှန်းကိန်းအခြေအနေများသည် အနည်းငယ်ကွဲပြားကြောင်း လေ့ကျင့်သင်ကြားထားသောမျက်လုံးများက သတိပြုမိသည် - ၎င်းမှာ ကျွန်ုပ်တို့တွင် သင့်လျော်သောအညွှန်းများ ဇယားရှိပြီးသားဖြစ်သောကြောင့်ဖြစ်သည်။ ၎င်းတို့မရှိခဲ့ပါက၊ ဖန်တီးရကျိုးနပ်လိမ့်မည်- စာရွက်စာတမ်း (လူ ၃၊ စာရွက်စာတမ်းအမျိုးအစား) и စာရွက်စာတမ်း(စာရွက်စာတမ်းအမျိုးအစား၊ ဝန်ထမ်း).
ROW အခြေအနေများတွင် အကွက်များ၏ အစီအစဥ်အကြောင်းအစီအစဉ်ရေးဆွဲသူ၏အမြင်မှ၊ သေချာသည်မှာသင်ရေးနိုင်သည်။ (A၊ B) = (constA၊ constB)နှင့် (B၊ A) = (constB၊ constA). ဒါပေမယ့် မှတ်တမ်းတင်လိုက်တာ အညွှန်းကိန်းရှိ အကွက်များ၏ အစီအစဥ်အတိုင်းထိုသို့သော တောင်းဆိုချက်သည် နောက်ပိုင်းတွင် အမှားရှာရန် ပိုအဆင်ပြေပါသည်။
အစီအစဉ်ထဲမှာ ဘာပါလဲ။

[explain.tensor.ru တွင်ကြည့်ရှုရန်]

ကံမကောင်းစွာဖြင့်၊ ကျွန်ုပ်တို့သည် ကံမကောင်းခဲ့သဖြင့် ပထမ UNION ဘလောက်တွင် ဘာမျှမတွေ့ခဲ့သဖြင့် ဒုတိယတစ်ခုကို ကွပ်မျက်ဆဲဖြစ်သည်။ ဒါတောင် - သပ်သပ် 0.037ms နှင့် 11 ကြားခံများ!
ကျွန်ုပ်တို့သည် တောင်းဆိုချက်ကို အရှိန်မြှင့်ပြီး မှတ်ဉာဏ်ထဲတွင် ဒေတာစုပ်ယူမှုကို လျှော့ချလိုက်ပါသည်။ အကြိမ်ပေါင်း ထောင်ပေါင်းများစွာရိုးရှင်းသောနည်းစနစ်များကို အသုံးပြု၍ ကော်ပီကူးထည့်ခြင်းဖြင့် ရလဒ်ကောင်းတစ်ခု။ 🙂

source: www.habr.com