PostgreSQL ആന്റിപാറ്റേണുകൾ: ഹാനികരമായ ജോയിനുകളും OR-കളും

ബഫറുകൾ കൊണ്ടുവരുന്ന പ്രവർത്തനങ്ങൾ സൂക്ഷിക്കുക...
ഒരു ചെറിയ ചോദ്യം ഉദാഹരണമായി ഉപയോഗിച്ച്, 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;

പട്ടികയെയും ഫീൽഡ് നാമങ്ങളെയും കുറിച്ച്ഫീൽഡുകളുടെയും ടേബിളുകളുടെയും "റഷ്യൻ" പേരുകൾ വ്യത്യസ്തമായി പരിഗണിക്കാം, പക്ഷേ ഇത് രുചിയുടെ കാര്യമാണ്. എന്തുകൊണ്ടെന്നാല് ഇവിടെ ടെൻസറിൽ വിദേശ ഡെവലപ്പർമാരില്ല, കൂടാതെ ഹൈറോഗ്ലിഫുകളിൽ പോലും പേരുകൾ നൽകാൻ PostgreSQL ഞങ്ങളെ അനുവദിക്കുന്നു. ഉദ്ധരണികളിൽ ഉൾപ്പെടുത്തിയിട്ടുണ്ട്, പിന്നെ പൊരുത്തക്കേടുകൾ ഉണ്ടാകാതിരിക്കാൻ വ്യക്തവും വ്യക്തവുമായ ഒബ്‌ജക്‌റ്റുകൾക്ക് പേരിടാൻ ഞങ്ങൾ താൽപ്പര്യപ്പെടുന്നു.
ഫലമായുണ്ടാകുന്ന പ്ലാൻ നോക്കാം:

[explain.tensor.ru-ൽ കാണുക]

144ms, ഏകദേശം 53K ബഫറുകൾ - അതായത്, 400MB-യിൽ കൂടുതൽ ഡാറ്റ! ഞങ്ങളുടെ അഭ്യർത്ഥന സമയത്ത് അവയെല്ലാം കാഷെയിലാണെങ്കിൽ ഞങ്ങൾ ഭാഗ്യവാന്മാരായിരിക്കും, അല്ലാത്തപക്ഷം ഡിസ്കിൽ നിന്ന് വായിക്കുമ്പോൾ അത് പല മടങ്ങ് സമയമെടുക്കും.

അൽഗോരിതം ഏറ്റവും പ്രധാനമാണ്!

ഏതെങ്കിലും അഭ്യർത്ഥന എങ്ങനെയെങ്കിലും ഒപ്റ്റിമൈസ് ചെയ്യുന്നതിന്, അത് എന്താണ് ചെയ്യേണ്ടതെന്ന് നിങ്ങൾ ആദ്യം മനസ്സിലാക്കണം.
ഡാറ്റാബേസ് ഘടനയുടെ വികസനം ഇപ്പോൾ ഈ ലേഖനത്തിന്റെ പരിധിക്ക് പുറത്ത് വിടാം, നമുക്ക് താരതമ്യേന "വിലകുറഞ്ഞത്" കഴിയുമെന്ന് സമ്മതിക്കുക. അഭ്യർത്ഥന മാറ്റിയെഴുതുക കൂടാതെ/അല്ലെങ്കിൽ നമുക്ക് ആവശ്യമുള്ള ചില കാര്യങ്ങൾ അടിസ്ഥാനത്തിലേക്ക് ഉരുട്ടുക സൂചികകൾ.

അതിനാൽ അഭ്യർത്ഥന:
- കുറഞ്ഞത് ചില പ്രമാണങ്ങളുടെ നിലനിൽപ്പ് പരിശോധിക്കുന്നു
- നമുക്ക് ആവശ്യമുള്ളതും ഒരു പ്രത്യേക തരത്തിലുള്ളതുമായ അവസ്ഥയിൽ
- എവിടെ രചയിതാവ് അല്ലെങ്കിൽ അവതാരകൻ നമുക്ക് ആവശ്യമുള്ള ജീവനക്കാരനാണ്

ചേരുക + പരിധി 1

ഒരു വലിയ എണ്ണം പട്ടികകൾ ആദ്യം ചേരുന്നിടത്ത് ഒരു ചോദ്യം എഴുതുന്നത് പലപ്പോഴും ഒരു ഡവലപ്പർക്ക് എളുപ്പമാണ്, തുടർന്ന് ഈ മുഴുവൻ സെറ്റിൽ നിന്നും ഒരു റെക്കോർഡ് മാത്രമേ അവശേഷിക്കുന്നുള്ളൂ. എന്നാൽ ഡെവലപ്പർക്ക് എളുപ്പം എന്നതിനർത്ഥം ഡാറ്റാബേസിന് കൂടുതൽ കാര്യക്ഷമതയുള്ളത് എന്നല്ല.
ഞങ്ങളുടെ കാര്യത്തിൽ 3 പട്ടികകൾ മാത്രമേ ഉണ്ടായിരുന്നുള്ളൂ - അതിന്റെ ഫലം എന്താണ് ...

നമുക്ക് ആദ്യം "ഡോക്യുമെന്റ് തരം" പട്ടികയുമായുള്ള കണക്ഷൻ ഒഴിവാക്കാം, അതേ സമയം ഡാറ്റാബേസിനോട് പറയുക ഞങ്ങളുടെ തരം റെക്കോർഡ് അദ്വിതീയമാണ് (ഞങ്ങൾക്ക് ഇത് അറിയാം, പക്ഷേ ഷെഡ്യൂളർക്ക് ഇതുവരെ ഒരു ആശയവുമില്ല):

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

അതെ, പട്ടിക/സിടിഇ ഒരൊറ്റ റെക്കോർഡിന്റെ ഒരൊറ്റ ഫീൽഡ് ഉൾക്കൊള്ളുന്നുവെങ്കിൽ, പിജിയിൽ നിങ്ങൾക്ക് ഇതുപോലെ എഴുതാം.

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

PostgreSQL ചോദ്യങ്ങളിൽ അലസമായ വിലയിരുത്തൽ

BitmapOr vs UNION

ചില സന്ദർഭങ്ങളിൽ, ബിറ്റ്മാപ്പ് ഹീപ്പ് സ്കാൻ ഞങ്ങൾക്ക് വളരെയധികം ചിലവാകും - ഉദാഹരണത്തിന്, ഞങ്ങളുടെ സാഹചര്യത്തിൽ, ധാരാളം റെക്കോർഡുകൾ ആവശ്യമായ അവസ്ഥ പാലിക്കുമ്പോൾ. കാരണം ഞങ്ങൾക്ക് അത് ലഭിച്ചു അല്ലെങ്കിൽ അവസ്ഥ BitmapOr ആയി മാറി- പദ്ധതിയിൽ പ്രവർത്തനം.
നമുക്ക് യഥാർത്ഥ പ്രശ്നത്തിലേക്ക് മടങ്ങാം - അതിനനുസൃതമായ ഒരു റെക്കോർഡ് നമുക്ക് കണ്ടെത്തേണ്ടതുണ്ട് ആർക്കും വ്യവസ്ഥകളിൽ നിന്ന് - അതായത്, രണ്ട് വ്യവസ്ഥകളിലും എല്ലാ 59K റെക്കോർഡുകളും തിരയേണ്ട ആവശ്യമില്ല. ഒരു വ്യവസ്ഥ പ്രവർത്തിക്കാൻ ഒരു വഴിയുണ്ട്, കൂടാതെ ആദ്യത്തേതിൽ ഒന്നും കണ്ടെത്താത്തപ്പോൾ മാത്രം രണ്ടാമത്തേതിലേക്ക് പോകുക. ഇനിപ്പറയുന്ന ഡിസൈൻ ഞങ്ങളെ സഹായിക്കും:

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

"ബാഹ്യ" പരിധി 1 ആദ്യ റെക്കോർഡ് കണ്ടെത്തുമ്പോൾ തിരയൽ അവസാനിക്കുന്നുവെന്ന് ഉറപ്പാക്കുന്നു. ആദ്യ ബ്ലോക്കിൽ ഇത് ഇതിനകം കണ്ടെത്തിയാൽ, രണ്ടാമത്തെ ബ്ലോക്ക് എക്സിക്യൂട്ട് ചെയ്യില്ല (ഒരിക്കലും നടപ്പിലാക്കിയിട്ടില്ല സംബന്ധിച്ച്).

"കേസ് പ്രകാരം ബുദ്ധിമുട്ടുള്ള സാഹചര്യങ്ങൾ മറയ്ക്കുന്നു"

യഥാർത്ഥ അന്വേഷണത്തിൽ അങ്ങേയറ്റം അസൗകര്യമുള്ള ഒരു നിമിഷമുണ്ട് - "ഡോക്യുമെന്റ് എക്സ്റ്റൻഷൻ" എന്ന അനുബന്ധ പട്ടികയ്‌ക്കെതിരായ നില പരിശോധിക്കുന്നു. പദപ്രയോഗത്തിലെ മറ്റ് വ്യവസ്ഥകളുടെ സത്യം പരിഗണിക്കാതെ തന്നെ (ഉദാഹരണത്തിന്, 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). എന്നാൽ റെക്കോർഡ് ചെയ്യുമ്പോൾ സൂചികയിലെ ഫീൽഡുകളുടെ ക്രമത്തിൽ, അത്തരമൊരു അഭ്യർത്ഥന പിന്നീട് ഡീബഗ് ചെയ്യാൻ കൂടുതൽ സൗകര്യപ്രദമാണ്.
എന്താണ് പ്ലാനിൽ ഉള്ളത്?

[explain.tensor.ru-ൽ കാണുക]

നിർഭാഗ്യവശാൽ, ഞങ്ങൾ നിർഭാഗ്യവാന്മാരായിരുന്നു, ആദ്യത്തെ UNION ബ്ലോക്കിൽ ഒന്നും കണ്ടെത്താനായില്ല, അതിനാൽ രണ്ടാമത്തേത് അപ്പോഴും നിർവ്വഹിച്ചു. എന്നാൽ അങ്ങനെയാണെങ്കിലും - മാത്രം 0.037ms ഉം 11 ബഫറുകളും!
ഞങ്ങൾ അഭ്യർത്ഥന വേഗത്തിലാക്കുകയും മെമ്മറിയിലെ ഡാറ്റ പമ്പിംഗ് കുറയ്ക്കുകയും ചെയ്തു ആയിരക്കണക്കിന് തവണ, വളരെ ലളിതമായ ടെക്നിക്കുകൾ ഉപയോഗിച്ച് - ഒരു ചെറിയ കോപ്പി-പേസ്റ്റ് ഉപയോഗിച്ച് ഒരു നല്ല ഫലം. 🙂

അവലംബം: www.habr.com