PostgreSQL Antipatterns: CTE x CTE

गतिविधि के प्रकार से, किसी को परिस्थितियों से निपटना पड़ता है जब एक डेवलपर अनुरोध लिखता है और सोचता है कि " आधार स्मार्ट है, यह सब कुछ संभाल सकता है! "

कुछ मामलों में (आंशिक रूप से डेटाबेस क्षमताओं की अज्ञानता से, आंशिक रूप से समय से पहले अनुकूलन से), यह दृष्टिकोण "फ्रेंकस्टीन" की उपस्थिति की ओर जाता है।

पहले मैं इस तरह के एक प्रश्न का उदाहरण दूंगा:

--         WITH RECURSIVE cte_bind AS ( SELECT DISTINCT ON (key_a, key_b) key_a a , key_b b , fld1 bind_fld1 , fld2 bind_fld2 FROM tbl ) --  min/max      , cte_max AS ( SELECT a , max(bind_fld1) bind_fld1 , min(bind_fld2) bind_fld2 FROM cte_bind GROUP BY a ) --        min/max- , cte_a_bind AS ( SELECT cte_bind.a , cte_bind.b , cte_max.bind_fld1 , cte_max.bind_fld2 FROM cte_bind INNER JOIN cte_max ON cte_max.a = cte_bind.a ) SELECT * FROM cte_a_bind; 

अनुरोध की गुणवत्ता का निष्पक्ष मूल्यांकन करने के लिए, आइए कुछ मनमाना डेटा सेट बनाएं:

 CREATE TABLE tbl AS SELECT (random() * 1000)::integer key_a , (random() * 1000)::integer key_b , (random() * 10000)::integer fld1 , (random() * 10000)::integer fld2 FROM generate_series(1, 10000); CREATE INDEX ON tbl(key_a, key_b); 

यह पता चला है कि डेटा को पढ़ना कुल क्वेरी निष्पादन समय के एक चौथाई से भी कम समय में लिया गया है :

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

हड्डियों से जुदा

हम अनुरोध को करीब से देखेंगे, और हम हैरान हो जाएंगे:

1. यहाँ पुनरावर्ती के साथ क्यों है, अगर कोई पुनरावर्ती CTE नहीं हैं?
2. एक अलग सीटीई में समूह मिनट / अधिकतम मान क्यों होता है यदि वे अभी भी मूल नमूने से जुड़े हुए हैं?
   + 25% समय
3. अंत में बिना शर्त 'सेलेक्ट * FROM' के माध्यम से पिछले CTE से री-रीडिंग का उपयोग क्यों करें?
   + 14% समय

इस मामले में, हम बहुत भाग्यशाली थे कि हैश जॉइन कनेक्शन के लिए चुना गया था, और नेस्टेड लूप नहीं, क्योंकि तब हमें एक भी सीटीई स्कैन पास नहीं मिलेगा, लेकिन 10K!


वास्तव में, आप परिणाम के रूप में क्या प्राप्त करना चाहते थे? हाँ, यह आमतौर पर इस तरह का सवाल है कि वह "तीन-कहानी" अनुरोधों के विश्लेषण के 5 वें मिनट में कहीं जाता है।

हम प्रत्येक अद्वितीय कुंजी जोड़ी के लिए समूह से key / a से मिनट / अधिकतम निकालना चाहते थे।
इसलिए हम इसके लिए विंडो फ़ंक्शंस का उपयोग करेंगे:

 SELECT DISTINCT ON(key_a, key_b) key_a a , key_b b , max(fld1) OVER(w) bind_fld1 , min(fld2) OVER(w) bind_fld2 FROM tbl WINDOW w AS (PARTITION BY key_a); 

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

चूंकि दोनों संस्करणों में डेटा पढ़ने में लगभग 4-5ms समान रूप से लगते हैं, हमारा -32% का संपूर्ण लाभ शुद्ध सीपीयू बेस से हटा दिया जाता है , अगर ऐसा अनुरोध अक्सर किया जाता है।

सामान्य तौर पर, आपको आधार को "राउंड - वियर, स्क्वायर - रोल" के लिए मजबूर नहीं करना चाहिए।