PostgreSQL अँटीपॅटर्न: हानिकारक सामील होणे आणि 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;

टेबल आणि फील्ड नावांबद्दलफील्ड आणि टेबल्सची "रशियन" नावे वेगळ्या पद्धतीने हाताळली जाऊ शकतात, परंतु ही चवची बाब आहे. कारण द टेन्सर येथे तेथे कोणतेही परदेशी विकसक नाहीत आणि PostgreSQL आम्हाला चित्रलिपीमध्ये देखील नावे देण्याची परवानगी देते, जर ते कोट मध्ये बंद, मग आम्ही वस्तूंना अस्पष्टपणे आणि स्पष्टपणे नाव देण्यास प्राधान्य देतो जेणेकरून कोणतीही विसंगती नाही.
चला परिणामी योजना पाहू:

[explain.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 vs UNION

काही प्रकरणांमध्ये, बिटमॅप हीप स्कॅनसाठी आम्हाला खूप खर्च येईल - उदाहरणार्थ, आमच्या परिस्थितीत, जेव्हा बरेच रेकॉर्ड आवश्यक अटी पूर्ण करतात. आम्हाला ते मिळाले कारण OR स्थिती BitmapOr मध्ये बदलली- योजना मध्ये ऑपरेशन.
चला मूळ समस्येकडे परत जाऊया - आम्हाला संबंधित रेकॉर्ड शोधण्याची आवश्यकता आहे कोणीही अटींमधून - म्हणजे, दोन्ही परिस्थितींमध्ये सर्व 59K रेकॉर्ड शोधण्याची गरज नाही. एक अट बाहेर काम एक मार्ग आहे, आणि पहिल्यामध्ये काहीही सापडले नाही तेव्हाच दुसऱ्याकडे जा. खालील डिझाइन आम्हाला मदत करेल:

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

"बाह्य" मर्यादा 1 हे सुनिश्चित करते की प्रथम रेकॉर्ड सापडल्यावर शोध समाप्त होईल. आणि जर ते पहिल्या ब्लॉकमध्ये आधीच सापडले असेल तर, दुसरा ब्लॉक कार्यान्वित केला जाणार नाही (कधीही अंमलात आणले नाही च्या संदर्भात).

"केस अंतर्गत कठीण परिस्थिती लपवत आहे"

मूळ क्वेरीमध्ये एक अत्यंत गैरसोयीचा क्षण आहे - संबंधित सारणी "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 ला स्थितीत बदलण्याची संधी आहे.
चला अनुक्रमित फील्ड "केस ब्रॅकेटच्या बाहेर" सोडू, रेकॉर्डमधून 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