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 ले हामीलाई हाइरोग्लिफहरूमा पनि नामहरू दिन अनुमति दिन्छ, यदि तिनीहरू उद्धरणहरूमा संलग्न, त्यसपछि हामी वस्तुहरूको नाम स्पष्ट र स्पष्ट रूपमा राख्न रुचाउँछौं ताकि त्यहाँ कुनै विसंगतिहरू छैनन्।
नतिजा योजना हेरौं:

[explan.tensor.ru मा हेर्नुहोस्]

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

एल्गोरिथ्म सबैभन्दा महत्त्वपूर्ण छ!

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

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

JOIN + LIMIT १

धेरै पटक यो एक विकासकर्ता को लागी एक क्वेरी लेख्न को लागी सजिलो छ जहाँ तालिकाहरु को एक ठूलो संख्या पहिले जोडिएको छ, र त्यसपछि यो सम्पूर्ण सेटबाट केवल एक रेकर्ड रहन्छ। तर विकासकर्ताको लागि सजिलो भनेको डाटाबेसको लागि बढी प्रभावकारी होइन।
हाम्रो अवस्थामा त्यहाँ केवल 3 तालिकाहरू थिए - र प्रभाव के हो ...

पहिले "कागजात प्रकार" तालिकाको जडानबाट छुटकारा पाउनुहोस्, र एकै समयमा डाटाबेसलाई बताउनुहोस्। हाम्रो प्रकार रेकर्ड अद्वितीय छ (हामीलाई यो थाहा छ, तर अनुसूचकलाई अझै थाहा छैन):

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

हो, यदि तालिका/CTE मा एउटै रेकर्डको एउटै फिल्ड समावेश छ भने, त्यसपछि PG मा तपाईले यसरी लेख्न सक्नुहुन्छ, यसको सट्टा

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

PostgreSQL प्रश्नहरूमा अल्छी मूल्याङ्कन

BitmapOr बनाम UNION

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

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

"बाह्य" LIMIT 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)। तर रेकर्डिङ गर्दा अनुक्रमणिकामा क्षेत्रहरूको क्रममा, यस्तो अनुरोध पछि डिबग गर्न सजिलो छ।
के छ योजनामा ​​?

[explan.tensor.ru मा हेर्नुहोस्]

दुर्भाग्यवश, हामी दुर्भाग्यपूर्ण थियौं र पहिलो UNION ब्लकमा केहि पनि फेला परेन, त्यसैले दोस्रो अझै पनि कार्यान्वयन गरियो। तर पनि - मात्र 0.037ms र 11 बफरहरू!
हामीले अनुरोधको गति बढाएका छौं र मेमोरीमा डेटा पम्पिङ कम गरेका छौं धेरै हजार पटक, एकदम सरल प्रविधिहरू प्रयोग गरेर - थोरै प्रतिलिपि-पेस्टको साथ राम्रो परिणाम। 🙂

स्रोत: www.habr.com