पोस्टग्रेएसक्यूएल एंटीपैटर्न: हानिकारक जॉइन और ओआरएस

बफ़र्स लाने वाले ऑपरेशनों से सावधान रहें...
उदाहरण के तौर पर एक छोटी क्वेरी का उपयोग करते हुए, आइए PostgreSQL में क्वेरी को अनुकूलित करने के लिए कुछ सार्वभौमिक दृष्टिकोण देखें। आप उनका उपयोग करते हैं या नहीं यह आप पर निर्भर है, लेकिन उनके बारे में जानना जरूरी है।

पीजी के कुछ बाद के संस्करणों में स्थिति बदल सकती है क्योंकि शेड्यूलर अधिक स्मार्ट हो जाता है, लेकिन 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 हमें चित्रलिपि में भी नाम देने की अनुमति देता है, यदि वे उद्धरणों में संलग्न, तो हम वस्तुओं को स्पष्ट और स्पष्ट रूप से नाम देना पसंद करते हैं ताकि कोई विसंगतियां न हों।
आइए परिणामी योजना पर नजर डालें:

[देखें व्याख्या.टेंसर.आरयू]

144ms और लगभग 53K बफ़र्स - यानी 400MB से ज्यादा डेटा! और हम भाग्यशाली होंगे यदि हमारे अनुरोध के समय तक वे सभी कैश में हैं, अन्यथा डिस्क से पढ़ने पर कई गुना अधिक समय लगेगा।

एल्गोरिदम सबसे महत्वपूर्ण है!

किसी भी तरह से किसी भी अनुरोध को अनुकूलित करने के लिए, आपको पहले यह समझना होगा कि उसे क्या करना चाहिए।
आइए अभी के लिए डेटाबेस संरचना के विकास को इस लेख के दायरे से बाहर छोड़ दें, और सहमत हों कि हम अपेक्षाकृत "सस्ते में" कर सकते हैं अनुरोध पुनः लिखें और/या हमारी ज़रूरत की कुछ चीज़ों को आधार पर रोल करें अनुक्रमित.

तो अनुरोध:
- कम से कम कुछ दस्तावेज़ के अस्तित्व की जाँच करता है
- उस स्थिति में जिसकी हमें आवश्यकता है और एक निश्चित प्रकार की
- जहां लेखक या कलाकार वह कर्मचारी है जिसकी हमें आवश्यकता है

सम्मिलित हों + सीमा 1

अक्सर किसी डेवलपर के लिए एक क्वेरी लिखना आसान होता है जहां पहले बड़ी संख्या में तालिकाओं को जोड़ा जाता है, और फिर इस पूरे सेट से केवल एक रिकॉर्ड बचता है। लेकिन डेवलपर के लिए आसान का मतलब डेटाबेस के लिए अधिक कुशल होना नहीं है।
हमारे मामले में केवल 3 टेबलें थीं - और इसका प्रभाव क्या है...

आइए सबसे पहले "दस्तावेज़ प्रकार" तालिका से कनेक्शन से छुटकारा पाएं, और साथ ही डेटाबेस को बताएं कि हमारा प्रकार का रिकॉर्ड अद्वितीय है (हम यह जानते हैं, लेकिन अनुसूचक को अभी तक कोई पता नहीं है):

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

हां, यदि तालिका/सीटीई में एक ही रिकॉर्ड का एक ही क्षेत्र शामिल है, तो पीजी में आप इसके बजाय इस तरह भी लिख सकते हैं

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

PostgreSQL प्रश्नों में आलसी मूल्यांकन

बिटमैपऑर बनाम यूनियन

कुछ मामलों में, बिटमैप हीप स्कैन हमें बहुत महंगा पड़ेगा - उदाहरण के लिए, हमारी स्थिति में, जब बहुत सारे रिकॉर्ड आवश्यक शर्त को पूरा करते हैं। हमें यह मिल गया क्योंकि OR स्थिति BitmapOr में बदल गई-योजना में संचालन.
आइए मूल समस्या पर लौटते हैं - हमें संबंधित रिकॉर्ड ढूंढने की आवश्यकता है किसी को भी शर्तों से - यानी, दोनों स्थितियों के तहत सभी 59K रिकॉर्ड की खोज करने की कोई आवश्यकता नहीं है। एक शर्त को पूरा करने का एक तरीका है, और दूसरे पर तभी जाएं जब पहले में कुछ न मिले. निम्नलिखित डिज़ाइन हमारी सहायता करेगा:

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

"बाहरी" LIMIT 1 यह सुनिश्चित करता है कि पहला रिकॉर्ड मिलने पर खोज समाप्त हो जाए। और यदि यह पहले ब्लॉक में पहले से ही पाया जाता है, तो दूसरे ब्लॉक को निष्पादित नहीं किया जाएगा (कभी निष्पादित नहीं किया गया के संबंध में)।

"CASE के अंतर्गत कठिन परिस्थितियों को छिपाना"

मूल क्वेरी में एक अत्यंत असुविधाजनक क्षण है - संबंधित तालिका "डॉक्यूमेंट एक्सटेंशन" के विरुद्ध स्थिति की जाँच करना। अभिव्यक्ति में अन्य स्थितियों की सच्चाई के बावजूद (उदाहरण के लिए, घ. "हटाया गया" सत्य नहीं है), यह कनेक्शन हमेशा निष्पादित होता है और "संसाधनों की लागत" होती है। उनमें से अधिक या कम खर्च किया जाएगा - यह इस तालिका के आकार पर निर्भर करता है।
लेकिन आप क्वेरी को संशोधित कर सकते हैं ताकि संबंधित रिकॉर्ड की खोज तभी हो जब यह वास्तव में आवश्यक हो:

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;

अनुक्रमणिका को [से] समायोजित करना

एक प्रशिक्षित आंख ने देखा कि यूनियन सबब्लॉक में अनुक्रमित स्थितियां थोड़ी भिन्न हैं - ऐसा इसलिए है क्योंकि हमारे पास पहले से ही तालिका में उपयुक्त अनुक्रमित हैं। और यदि वे अस्तित्व में नहीं थे, तो यह बनाने लायक होगा: दस्तावेज़(व्यक्ति3, दस्तावेज़ प्रकार) и दस्तावेज़(दस्तावेज़ प्रकार, कर्मचारी).
ROW स्थितियों में फ़ील्ड के क्रम के बारे मेंयोजनाकार के दृष्टिकोण से, निःसंदेह, आप लिख सकते हैं (ए, बी) = (constA, constB)और (बी, ए) = (constB, constA). लेकिन रिकॉर्डिंग करते समय सूचकांक में फ़ील्ड के क्रम में, ऐसा अनुरोध बाद में डीबग करना अधिक सुविधाजनक है।
योजना में क्या है?

[देखें व्याख्या.टेंसर.आरयू]

दुर्भाग्य से, हम बदकिस्मत थे और पहले यूनियन ब्लॉक में कुछ भी नहीं मिला, इसलिए दूसरा अभी भी निष्पादित किया गया था। लेकिन फिर भी - केवल 0.037ms और 11 बफ़र्स!
हमने अनुरोध की गति बढ़ा दी है और मेमोरी में डेटा पंपिंग कम कर दी है कई हजार बार, काफी सरल तकनीकों का उपयोग करते हुए - थोड़े से कॉपी-पेस्ट के साथ एक अच्छा परिणाम। 🙂

स्रोत: www.habr.com