PostgreSQL Antipatterns: හානිකර JOINs සහ ORs

බෆර ගෙන එන මෙහෙයුම් වලින් පරිස්සම් වන්න...
උදාහරණයක් ලෙස කුඩා විමසුමක් භාවිතා කරමින්, PostgreSQL හි විමසුම් ප්‍රශස්ත කිරීම සඳහා විශ්වීය ප්‍රවේශයන් කිහිපයක් බලමු. ඔබ ඒවා භාවිතා කරනවාද නැද්ද යන්න ඔබට භාරයි, නමුත් ඒවා ගැන දැන ගැනීම වටී.

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 එකේ විදේශීය සංවර්ධකයින් නොමැත, සහ PostgreSQL අපට හයිරොග්ලිෆ් වලින් පවා නම් ලබා දීමට ඉඩ දෙයි. උපුටා දැක්වීම් වල අමුණා ඇත, එවිට අපි නොගැලපීම් ඇති නොවන පරිදි නිසැකව හා පැහැදිලිව වස්තූන් නම් කිරීමට කැමැත්තෙමු.
ප්රතිඵල සැලැස්ම දෙස බලමු:

[විස්තර කරන්න.tensor.ru බලන්න]

144ms සහ 53K පමණ බෆර - එනම්, 400MB වඩා වැඩි දත්ත! තවද අපගේ ඉල්ලීමේ වේලාව වන විට ඒවා සියල්ලම හැඹිලියේ තිබේ නම් අපි වාසනාවන්ත වනු ඇත, එසේ නොමැතිනම් තැටියෙන් කියවන විට එය බොහෝ වාරයක් ගතවනු ඇත.

ඇල්ගොරිතම වඩාත් වැදගත් වේ!

ඕනෑම ඉල්ලීමක් කෙසේ හෝ ප්‍රශස්ත කිරීම සඳහා, එය කළ යුත්තේ කුමක්දැයි ඔබ මුලින්ම තේරුම් ගත යුතුය.
දැනට, අපි මෙම ලිපියේ විෂය පථයෙන් පිටත දත්ත සමුදා ව්‍යුහය සංවර්ධනය කිරීම අත්හැර, අපට සාපේක්ෂව "ලාභ" හැකි බවට එකඟ වෙමු. ඉල්ලීම නැවත ලියන්න සහ/හෝ අපට අවශ්‍ය සමහර දේවල් පාදක කර ගන්න දර්ශක.

එබැවින් ඉල්ලීම:
- අවම වශයෙන් යම් ලියවිල්ලක පැවැත්ම පරීක්ෂා කරයි
- අපට අවශ්‍ය සහ යම් ආකාරයක තත්වයක
- එහිදී කතුවරයා හෝ රංගන ශිල්පියා අපට අවශ්‍ය සේවකයා වේ

එක්වන්න + සීමාව 1

බොහෝ විට සංවර්ධකයෙකුට ප්‍රථමයෙන් වගු විශාල සංඛ්‍යාවක් සම්බන්ධ වන විමසුමක් ලිවීමට පහසු වන අතර පසුව මෙම සම්පූර්ණ කට්ටලයෙන් ඉතිරිව ඇත්තේ එක් වාර්තාවක් පමණි. නමුත් සංවර්ධකයාට පහසු යන්නෙන් අදහස් කරන්නේ දත්ත සමුදාය සඳහා වඩා කාර්යක්ෂම නොවේ.
අපගේ නඩුවේ තිබුණේ වගු 3 ක් පමණි - සහ එහි බලපෑම කුමක්ද ...

අපි මුලින්ම "ලේඛන වර්ගය" වගුව සමඟ ඇති සම්බන්ධතාවය ඉවත් කරමු, ඒ සමඟම දත්ත සමුදායට කියන්න අපගේ වර්ගයේ වාර්තාව අද්විතීයයි (අපි මෙය දනිමු, නමුත් උපලේඛකයාට තවමත් අදහසක් නැත):

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

ඔව්, වගුව/CTE සමන්විත වන්නේ තනි වාර්තාවක තනි ක්ෂේත්‍රයකින් නම්, PG හි ඔබට මේ ආකාරයෙන් ලිවිය හැකිය,

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

PostgreSQL විමසුම්වල අලස ඇගයීම

BitmapOr එදිරිව UNION

සමහර අවස්ථාවලදී, Bitmap Heap Scan අපට විශාල මුදලක් වැය වනු ඇත - උදාහරණයක් ලෙස, අපගේ තත්වය තුළ, බොහෝ වාර්තා අවශ්ය කොන්දේසි සපුරාලන විට. අපිට ඒක ලැබුන නිසා OR තත්ත්වය BitmapOr බවට පත් විය- සැලැස්ම තුළ ක්රියාත්මක කිරීම.
අපි මුල් ගැටලුව වෙත ආපසු යමු - අපට අනුරූප වාර්තාවක් සොයාගත යුතුය ඕනෑම කෙනෙක් කොන්දේසි වලින් - එනම්, කොන්දේසි දෙකම යටතේ සියලුම 59K වාර්තා සෙවීමට අවශ්‍ය නොවේ. එක් කොන්දේසියක් වැඩ කිරීමට ක්රමයක් ඇත, සහ දෙවැන්නට යන්න, පළමු කිසිවක් සොයාගත නොහැකි වූ විට පමණි. පහත සැලසුම අපට උපකාරී වනු ඇත:

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

"බාහිර" සීමාව 1 පළමු වාර්තාව සොයාගත් විට සෙවුම අවසන් වන බව සහතික කරයි. තවද එය දැනටමත් පළමු කොටසේ තිබේ නම්, දෙවන කොටස ක්‍රියාත්මක නොවේ (කිසිදා ක්‍රියාත්මක නොවේ සම්බන්ධයෙන්).

"CASE යටතේ දුෂ්කර කොන්දේසි සැඟවීම"

මුල් විමසුමේ අතිශයින්ම අපහසු මොහොතක් ඇත - අදාළ වගුවේ "DocumentExtension" ට එරෙහිව තත්ත්වය පරීක්ෂා කිරීම. ප්‍රකාශනයේ ඇති වෙනත් කොන්දේසිවල සත්‍යතාව කුමක් වුවත් (උදාහරණයක් ලෙස, d.“මකන ලද” සත්‍ය නොවේ), මෙම සම්බන්ධතාවය සැමවිටම ක්රියාත්මක වන අතර "සම්පත් වියදම්". ඔවුන්ගෙන් වැඩි හෝ අඩු මුදලක් වැය වනු ඇත - මෙම වගුවේ ප්රමාණය මත රඳා පවතී.
නමුත් ඔබට විමසුම වෙනස් කළ හැකි අතර එමඟින් අදාළ වාර්තාවක් සෙවීම සිදුවන්නේ එය සැබවින්ම අවශ්‍ය වූ විට පමණි:

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 වරහන් වලින් පිටත” තබමු, වාර්තාවේ සිට WHEN බ්ලොක් එකට සරල කොන්දේසි එකතු කරමු - දැන් “බර” විමසුම ක්‍රියාත්මක වන්නේ THEN වෙත යන විට පමණි.

මගේ අවසාන නම "මුළු"

ඉහත විස්තර කර ඇති සියලුම යාන්ත්‍ර විද්‍යාව සමඟ අපි ප්‍රතිඵල විමසුම රැස් කරමු:

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 උප බ්ලොක් වල සුචිගත කළ කොන්දේසි තරමක් වෙනස් බව පුහුණු ඇසක් දුටුවේය - මෙයට හේතුව අප දැනටමත් මේසය මත සුදුසු දර්ශක ඇති බැවිනි. ඒවා නොතිබුනේ නම්, එය නිර්මාණය කිරීම වටී: ලේඛනය (පුද්ගලයා 3, ලේඛන වර්ගය) и ලේඛනය (ලේඛන වර්ගය, සේවකයා).
ROW කොන්දේසි වල ක්ෂේත්‍ර අනුපිළිවෙල ගැනසැලසුම්කරුගේ දෘෂ්ටි කෝණයෙන්, ඇත්ත වශයෙන්ම, ඔබට ලිවිය හැකිය (A, B) = (constA, constB)හා (B, A) = (constB, constA). නමුත් පටිගත කිරීමේදී දර්ශකයේ ඇති ක්ෂේත්රවල අනුපිළිවෙල අනුව, එවැනි ඉල්ලීමක් පසුව නිදොස් කිරීම සඳහා වඩාත් පහසු වේ.
සැලැස්මේ ඇත්තේ කුමක්ද?

[විස්තර කරන්න.tensor.ru බලන්න]

අවාසනාවන්ත ලෙස, අපි අවාසනාවන්ත වූ අතර පළමු UNION කොටසෙහි කිසිවක් සොයා නොගත් නිසා දෙවැන්න තවමත් ක්‍රියාත්මක විය. නමුත් එසේ වුවද - පමණි 0.037ms සහ බෆර 11ක්!
අපි ඉල්ලීම වේගවත් කර මතකයේ දත්ත පොම්ප කිරීම අඩු කර ඇත දහස් වාරයක්, තරමක් සරල තාක්ෂණික ක්රම භාවිතා කිරීම - කුඩා පිටපත්-පේස්ට් සමග හොඳ ප්රතිඵලය. 🙂

මූලාශ්රය: www.habr.com