क्या आपको याद है कि यह सब कैसे शुरू हुआ? सब कुछ पहली बार और बार-बार था

इस बारे में कि हमें PostgreSQL क्वेरी को कैसे अनुकूलित करना था और इससे क्या निकला।
आपको ऐसा क्यों करना पड़ा? हां, क्योंकि पिछले 4 वर्षों में सब कुछ चुपचाप, शांति से, टिक-टिक करती घड़ी की तरह चलता रहा।
एक पुरालेख के रूप में.

वास्तविक घटनाओं पर आधारित.
बदल गए हैं सारे नाम, संयोग हैं बेतरतीब।

जब आप एक निश्चित परिणाम प्राप्त करते हैं, तो यह याद रखना हमेशा दिलचस्प होता है कि शुरुआत के लिए प्रेरणा क्या थी, यह सब कहाँ से शुरू हुआ।

तो, परिणामस्वरूप क्या हुआ, इसका संक्षेप में लेख में वर्णन किया गया है "PostgreSQL प्रदर्शन को बेहतर बनाने के तरीकों में से एक के रूप में संश्लेषण'.

पिछली घटनाओं की श्रृंखला को फिर से बनाना संभवतः दिलचस्प होगा।
इतिहास ने सटीक प्रारंभ तिथि सहेजी - 2018-09-10 18:02:48।
साथ ही, कहानी में एक अनुरोध भी है जिससे यह सब शुरू हुआ:
समस्या अनुरोधचुनते हैं
p.“PARAMETER_ID” को पैरामीटर_आईडी के रूप में,
पीडी.''पीडी_नाम'' एएस पीडी_नाम,
पीडी "ग्राहक_पार्टनंबर" ग्राहक_पार्टनंबर के रूप में,
डब्ल्यू. "एलआरएम" एएस एलआरएम,
डब्ल्यू. "लोटिड" एएस लोटिड,
w.“RTD_VALUE” RTD_मान के रूप में,
w.“LOWER_SPEC_LIMIT” निचली_स्पेक_सीमा के रूप में,
w.“UPPER_SPEC_LIMIT” ऊपरी_स्पेक_सीमा के रूप में,
p"TYPE_CALCUL" AS type_calcul,
s.'SPENT_NAME' AS स्पेंट_नाम,
s.“SPENT_DATE” जैसा व्यतीत_दिनांक,
उद्धरण(वर्ष "SPENT_DATE" से) वर्ष के रूप में,
("SPENT_DATE" से महीना) महीने के रूप में निकालें,
s."REPORT_NAME" AS रिपोर्ट_नाम,
p.''STPM_NAME'' AS stpm_name,
p.“CUSTOMERPARAM_NAME” ग्राहकपरम_नाम के रूप में
wdata w से,
खर्च किया,
पीएमटीआर पी,
व्यतीत_पीडी एसपी,
पीडी पीडी
जहां s.“SPENT_ID” = w.“SPENT_ID”
और p.'PARAMETER_ID' = w.'PARAMETER_ID'
और एस.“SPENT_ID” = sp.“SPENT_ID”
और पीडी.''PD_ID'' = sp.''PD_ID''
और s.“SPENT_DATE” >= '2018-07-01' और s.“SPENT_DATE” <= '2018-09-30'
और s.“SPENT_DATE” = (MAX(s2.“SPENT_DATE” चुनें)
खर्च किए गए s2 से,
wdata w2
जहां s2.“SPENT_ID” = w2.“SPENT_ID”
और w2.“LRM” = w.“LRM”);

समस्या का विवरण अनुमानित रूप से मानक है - "सबकुछ खराब है।" मुझे बताओ समस्या क्या है।"
मुझे तुरंत साढ़े तीन इंच ड्राइव के समय का एक किस्सा याद आया:

लैमर हैकर के पास आता है।
-मेरे लिए कुछ भी काम नहीं करता, मुझे बताओ समस्या कहां है।
-डीएनए में...

लेकिन निःसंदेह, प्रदर्शन संबंधी घटनाओं को हल करने का यह तरीका नहीं है। “हो सकता है वे हमें न समझें" (साथ)। हमें इसका पता लगाने की जरूरत है.
अच्छा, चलो खोदो। हो सकता है परिणाम स्वरूप कुछ जमा हो जाये।

जांच शुरू हुई

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

सबसे दिलचस्प और उपयोगी, हमेशा की तरह, शुरुआत और अंत में है।
नेस्टेड लूप (लागत=935.84..479763226.18 पंक्तियाँ=3322 चौड़ाई=135) (वास्तविक समय=31.536..8220420.295 पंक्तियाँ=8111656 लूप=1)
योजना समय: 3.807 मि.से
निष्पादन समय: 8222351.640 एमएस
समापन का समय 2 घंटे से अधिक है।

झूठी परिकल्पनाएँ जिनमें समय लगा

परिकल्पना 1 - अनुकूलक गलती करता है और गलत योजना बनाता है।

निष्पादन योजना की कल्पना करने के लिए, हम साइट का उपयोग करेंगे https://explain.depesz.com/. हालाँकि, साइट ने कुछ भी दिलचस्प या उपयोगी नहीं दिखाया। पहली और दूसरी नज़र में, ऐसा कुछ भी नहीं है जो वास्तव में मदद कर सके। क्या यह संभव है कि पूर्ण स्कैन न्यूनतम हो. आगे बढ़ो।

परिकल्पना 2-ऑटोवैक्यूम की ओर से आधार पर प्रभाव, आपको ब्रेक से छुटकारा पाने की आवश्यकता है।

लेकिन ऑटोवैक्यूम डेमॉन अच्छा व्यवहार करते हैं, लंबे समय तक चलने वाली कोई प्रक्रिया नहीं होती है। कोई गंभीर भार नहीं. हमें कुछ और तलाशना होगा.

परिकल्पना 3 - आँकड़े पुराने हो गए हैं, हर चीज़ की पुनर्गणना करने की आवश्यकता है

फिर, ऐसा नहीं. आँकड़े अद्यतन हैं। जो, ऑटोवैक्यूम के साथ समस्याओं की कमी को देखते हुए, आश्चर्य की बात नहीं है।

आइए अनुकूलन शुरू करें

मुख्य तालिका 'wdata' निश्चित रूप से छोटी नहीं है, लगभग 3 मिलियन रिकॉर्ड।
और यह वह तालिका है जिसका पूर्ण स्कैन अनुसरण करता है।

हैश शर्त: ((w'SPENT_ID' = s'SPENT_ID') और ((उपयोजना 1) = s'SPENT_DATE'))
-> Seq स्कैन wdata पर w (लागत=0.00..574151.49 पंक्तियाँ=26886249 चौड़ाई=46) (वास्तविक समय=0.005..8153.565 पंक्तियाँ=26873950 लूप=1)
हम मानक कार्य करते हैं: "आओ, एक सूचकांक बनाएं और सब कुछ उड़ जाएगा।"
"SPENT_ID" फ़ील्ड पर एक इंडेक्स बनाया गया
रिजल्ट:
इंडेक्स का उपयोग करके क्वेरी निष्पादन योजना

अच्छा, क्या इससे मदद मिली?
यह था: 8 222 351.640 एमएस (2 घंटे से थोड़ा अधिक)
यह बन गया: 6 985 431.575 एमएस (लगभग 2 घंटे)
सामान्य तौर पर, वही सेब, पार्श्व दृश्य।
आइए क्लासिक्स को याद करें:
“क्या आपके पास भी वही है, लेकिन बिना पंखों के? हम ढूँढेंगे"।

सिद्धांत रूप में, इसे एक अच्छा परिणाम कहा जा सकता है, ठीक है, अच्छा नहीं, लेकिन स्वीकार्य है। कम से कम, ग्राहक को एक बड़ी रिपोर्ट प्रदान करें जिसमें बताया गया हो कि कितना किया गया है और जो किया गया वह अच्छा क्यों था।
लेकिन अभी भी अंतिम फैसला दूर है. बहुत दूर।

और अब सबसे दिलचस्प बात - हम अनुकूलन करना जारी रखेंगे, हम अनुरोध को बेहतर बनाएंगे

चरण एक - जॉइन का उपयोग करें

दोबारा लिखा गया अनुरोध अब इस तरह दिखता है (अच्छा, कम से कम अधिक सुंदर):
JOIN का उपयोग करके क्वेरी करेंचुनते हैं
p.“PARAMETER_ID” को पैरामीटर_आईडी के रूप में,
पीडी.''पीडी_नाम'' एएस पीडी_नाम,
पीडी "ग्राहक_पार्टनंबर" ग्राहक_पार्टनंबर के रूप में,
डब्ल्यू. "एलआरएम" एएस एलआरएम,
डब्ल्यू. "लोटिड" एएस लोटिड,
w.“RTD_VALUE” RTD_मान के रूप में,
w.“LOWER_SPEC_LIMIT” निचली_स्पेक_सीमा के रूप में,
w.“UPPER_SPEC_LIMIT” ऊपरी_स्पेक_सीमा के रूप में,
p"TYPE_CALCUL" AS type_calcul,
s.'SPENT_NAME' AS स्पेंट_नाम,
s.“SPENT_DATE” जैसा व्यतीत_दिनांक,
उद्धरण(वर्ष "SPENT_DATE" से) वर्ष के रूप में,
("SPENT_DATE" से महीना) महीने के रूप में निकालें,
s."REPORT_NAME" AS रिपोर्ट_नाम,
p.''STPM_NAME'' AS stpm_name,
p.“CUSTOMERPARAM_NAME” ग्राहकपरम_नाम के रूप में
Wdata w से INNER JOIN खर्च किया गया s पर w.“SPENT_ID”=s.”“SPENT_ID”
इनर जॉइन pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
इनर जॉइन खर्च_पीडी एसपी एस पर। "एसपीईएनटी_आईडी" = एसपी। "एसपीईएनटी_आईडी"
इनर जॉइन पीडी पीडी ऑन पीडी.“पीडी_आईडी” = एसपी.“पीडी_आईडी”
कहां
s.“SPENT_DATE” >= '2018-07-01' और s.“SPENT_DATE” <= '2018-09-30'AND
s.“SPENT_DATE” = (MAX(s2.“SPENT_DATE” चुनें)
Wdata से w2 INNER JOIN ने w2 पर खर्च किया s2.“SPENT_ID”=s2.“SPENT_ID”
इनर जॉइन wdata w
ON w2.“LRM” = w.“LRM”);
योजना समय: 2.486 मि.से
निष्पादन समय: 1223680.326 एमएस

तो, पहला परिणाम.
यह था: 6 एमएस (लगभग 985 घंटे)।
यह बन गया: 1 223 680.326 एमएस (सिर्फ 20 मिनट से अधिक)।
अच्छा परिणाम। सिद्धांत रूप में, फिर से, हम वहीं रुक सकते हैं। लेकिन यह इतना अरुचिकर है कि आप रुक नहीं सकते।
क्योंकि

चरण दो - सहसंबद्ध सबक्वेरी से छुटकारा पाएं

परिवर्तित अनुरोध पाठ:
सहसंबंधित सबक्वेरी के बिनाचुनते हैं
p.“PARAMETER_ID” को पैरामीटर_आईडी के रूप में,
पीडी.''पीडी_नाम'' एएस पीडी_नाम,
पीडी "ग्राहक_पार्टनंबर" ग्राहक_पार्टनंबर के रूप में,
डब्ल्यू. "एलआरएम" एएस एलआरएम,
डब्ल्यू. "लोटिड" एएस लोटिड,
w.“RTD_VALUE” RTD_मान के रूप में,
w.“LOWER_SPEC_LIMIT” निचली_स्पेक_सीमा के रूप में,
w.“UPPER_SPEC_LIMIT” ऊपरी_स्पेक_सीमा के रूप में,
p"TYPE_CALCUL" AS type_calcul,
s.'SPENT_NAME' AS स्पेंट_नाम,
s.“SPENT_DATE” जैसा व्यतीत_दिनांक,
उद्धरण(वर्ष "SPENT_DATE" से) वर्ष के रूप में,
("SPENT_DATE" से महीना) महीने के रूप में निकालें,
s."REPORT_NAME" AS रिपोर्ट_नाम,
p.''STPM_NAME'' AS stpm_name,
p.“CUSTOMERPARAM_NAME” ग्राहकपरम_नाम के रूप में
डब्ल्यूडेटा से इनर जॉइन खर्च किया गया एस पर एस। "एसपीईएनटी_आईडी" = डब्ल्यू। "एसपीईएनटी_आईडी"
इनर जॉइन pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
इनर जॉइन खर्च_पीडी एसपी एस पर। "एसपीईएनटी_आईडी" = एसपी। "एसपीईएनटी_आईडी"
इनर जॉइन पीडी पीडी ऑन पीडी.“पीडी_आईडी” = एसपी.“पीडी_आईडी”
इनर जॉइन (w2.“LRM”, MAX(s2.“SPENT_DATE” चुनें)
खर्च किए गए s2 से आंतरिक रूप से s2 पर wdata w2 से जुड़ें।“SPENT_ID” = w2.“SPENT_ID”
w2.“LRM” द्वारा समूह
) एमडी ऑन डब्ल्यू.“एलआरएम” = एमडी.“एलआरएम”
कहां
s."SPENT_DATE" >= '2018-07-01' और s."SPENT_DATE" <= '2018-09-30';
योजना समय: 2.291 मि.से
निष्पादन समय: 165021.870 एमएस

यह था: 1 223 680.326 एमएस (सिर्फ 20 मिनट से अधिक)।
यह बन गया: 165 021.870 एमएस (सिर्फ 2 मिनट से अधिक)।
यह पहले से ही काफी अच्छा है.
हालाँकि, जैसा कि अंग्रेज कहते हैं "लेकिन, हमेशा एक परंतु होता है" जो परिणाम बहुत अच्छा है, उससे स्वतः ही संदेह पैदा हो जाना चाहिए। यहां कुछ गड़बड़ है.

सहसंबंधित सबक्वेरी से छुटकारा पाने के लिए क्वेरी को सही करने की परिकल्पना सही है। लेकिन अंतिम परिणाम सही होने के लिए आपको इसमें थोड़ा बदलाव करना होगा।
परिणामस्वरूप, पहला मध्यवर्ती परिणाम:
सहसंबंधित सबक्वेरी के बिना संपादित क्वेरीचुनते हैं
p.“PARAMETER_ID” को पैरामीटर_आईडी के रूप में,
पीडी.''पीडी_नाम'' एएस पीडी_नाम,
पीडी "ग्राहक_पार्टनंबर" ग्राहक_पार्टनंबर के रूप में,
डब्ल्यू. "एलआरएम" एएस एलआरएम,
डब्ल्यू. "लोटिड" एएस लोटिड,
w.“RTD_VALUE” RTD_मान के रूप में,
w.“LOWER_SPEC_LIMIT” निचली_स्पेक_सीमा के रूप में,
w.“UPPER_SPEC_LIMIT” ऊपरी_स्पेक_सीमा के रूप में,
p"TYPE_CALCUL" AS type_calcul,
s.'SPENT_NAME' AS स्पेंट_नाम,
s.“SPENT_DATE” जैसा व्यतीत_दिनांक,
उद्धरण (वर्ष से "SPENT_DATE") वर्ष के रूप में,
(माह से "SPENT_DATE") महीने के रूप में निकालें,
s."REPORT_NAME" AS रिपोर्ट_नाम,
p.''STPM_NAME'' AS stpm_name,
p.“CUSTOMERPARAM_NAME” ग्राहकपरम_नाम के रूप में
डब्ल्यूडेटा से इनर जॉइन खर्च किया गया एस पर एस। "एसपीईएनटी_आईडी" = डब्ल्यू। "एसपीईएनटी_आईडी"
इनर जॉइन pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
इनर जॉइन खर्च_पीडी एसपी एस पर। "एसपीईएनटी_आईडी" = एसपी। "एसपीईएनटी_आईडी"
इनर जॉइन पीडी पीडी ऑन पीडी.“पीडी_आईडी” = एसपी.“पीडी_आईडी”
इनर जॉइन (w2.“LRM”, MAX(s2.“SPENT_DATE”) को “SPENT_DATE” के रूप में चुनें
खर्च किए गए s2 से आंतरिक रूप से s2 पर wdata w2 से जुड़ें।“SPENT_ID” = w2.“SPENT_ID”
w2.“LRM” द्वारा समूह
) एमडी पर एमडी.“SPENT_DATE” = s.“SPENT_DATE” और एमडी.“LRM” = w.“LRM”
कहां
s."SPENT_DATE" >= '2018-07-01' और s."SPENT_DATE" <= '2018-09-30';
योजना समय: 3.192 मि.से
निष्पादन समय: 208014.134 एमएस

तो, अंत में हमें पहला स्वीकार्य परिणाम मिलता है, जिसे ग्राहक को दिखाना कोई शर्म की बात नहीं है:
से शुरू हुआ: 8 222 351.640 एमएस (2 घंटे से अधिक)
हम हासिल करने में कामयाब रहे: 1 एमएस (223 मिनट से थोड़ा अधिक)।
परिणाम (अंतरिम): 208 014.134 एमएस (सिर्फ 3 मिनट से अधिक)।

Отличный результат।

संपूर्ण

हम वहां रुक सकते थे.
लेकिन ...
भूख खाने से आती है. जो चलेगा वही मार्ग पर निपुण होगा। कोई भी परिणाम मध्यवर्ती होता है. रुका और मर गया. वगैरह।
आइए अनुकूलन जारी रखें।
महान विचार। खासकर यह देखते हुए कि ग्राहक को कोई आपत्ति भी नहीं हुई। और इसके लिए दृढ़ता से भी.

तो, अब डेटाबेस को फिर से डिज़ाइन करने का समय आ गया है। क्वेरी संरचना को अब अनुकूलित नहीं किया जा सकता है (हालाँकि, जैसा कि बाद में पता चला, यह सुनिश्चित करने का एक विकल्प है कि सब कुछ वास्तव में विफल हो जाता है)। लेकिन डेटाबेस डिज़ाइन का अनुकूलन और विकास शुरू करना पहले से ही एक बहुत ही आशाजनक विचार है। और सबसे महत्वपूर्ण दिलचस्प. फिर से, अपनी जवानी को याद करो। मैं तुरंत डीबीए नहीं बन गया, मैं एक प्रोग्रामर (बेसिक, असेंबलर, सी, डबल-प्लस सी, ओरेकल, पीएलएसक्यूएल) के रूप में बड़ा हुआ। निस्संदेह, एक अलग संस्मरण के लिए एक दिलचस्प विषय ;-)।
हालाँकि, आइए विचलित न हों।

इस प्रकार,

या शायद विभाजन से हमें मदद मिलेगी?
स्पॉइलर - "हां, इससे मदद मिली, जिसमें प्रदर्शन को अनुकूलित करना भी शामिल है।"

लेकिन यह बिल्कुल अलग कहानी है ...

करने के लिए जारी…

स्रोत: www.habr.com