PostgreSQL Antipatterns: हानिकारक जॉइन और ओआरएस

भय संचालन बफ़र्स लाने ...
उदाहरण के रूप में एक छोटी सी क्वेरी का उपयोग करना, 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; 

आइए परिणामी योजना देखें:

[स्पष्टीकरण पर देखें ।ensor.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 प्रश्नों में आलसी कंप्यूटिंग

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

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

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

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

"मुश्किल के तहत छिपा" कठिन परिस्थितियों

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

 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 ब्लॉक में जोड़ते हैं - और अब "भारी" क्वेरी को केवल तब स्विच करते समय निष्पादित किया जाता है।

मेरा अंतिम नाम "कुल" है

हम उपरोक्त वर्णित सभी यांत्रिकी के साथ परिणामी क्वेरी एकत्र करते हैं:

 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, दस्तावेज़ प्रकार) और दस्तावेज़ (दस्तावेज़ प्रकार, कर्मचारी) ।

क्या है प्लान?

[स्पष्टीकरण पर देखें ।ensor.ru]

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