हे सगळं कसं सुरू झालं ते आठवतंय. सर्व काही प्रथमच आणि पुन्हा होते

आम्हाला PostgreSQL क्वेरी कशी ऑप्टिमाइझ करायची होती आणि यातून काय बाहेर आले याबद्दल.
तुम्हाला का करावे लागले? होय, कारण मागील 4 वर्षांपासून सर्वकाही शांतपणे, शांतपणे, घड्याळाच्या टिकल्याप्रमाणे काम करत होते.
एक एपिग्राफ म्हणून.

वास्तविक घटनांवर आधारित.
सर्व नावे बदलली आहेत, योगायोग यादृच्छिक आहेत.

जेव्हा आपण एक विशिष्ट परिणाम प्राप्त करता, तेव्हा हे लक्षात ठेवणे नेहमीच मनोरंजक असते की सुरुवातीची प्रेरणा काय होती, हे सर्व कोठून सुरू झाले.

तर, परिणामी काय घडले ते लेखात थोडक्यात वर्णन केले आहे “PostgreSQL कार्यप्रदर्शन सुधारण्याच्या पद्धतींपैकी एक म्हणून संश्लेषण».

मागील कार्यक्रमांची साखळी पुन्हा तयार करणे कदाचित मनोरंजक असेल.
इतिहासाने अचूक प्रारंभ तारीख जतन केली - 2018-09-10 18:02:48.
तसेच, कथेमध्ये एक विनंती आहे जिथून हे सर्व सुरू झाले:
समस्या विनंतीनिवडा
p. “PARAMETER_ID” parameter_id म्हणून,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" as customer_partnumber,
w. "LRM" AS LRM,
w. "LOTID" प्रमाणे लोटिड,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” as low_spec_limit,
w.“UPPER_SPEC_LIMIT” as upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s. “SPENT_DATE” AS spent_date,
अर्क ("SPENT_DATE" पासून वर्ष) AS वर्ष,
महिना म्हणून काढा("SPENT_DATE" पासून महिना),
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p. “CUSTOMERPARAM_NAME” as customerparam_name
wdata w पासून,
खर्च केला,
pmtr p,
spent_pd sp,
pd pd
WHERE s.“SPENT_ID” = w.“SPENT_ID”
आणि p."PARAMETER_ID" = w."PARAMETER_ID"
आणि s.“SPENT_ID” = sp.“SPENT_ID”
आणि pd."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
WHERE s2.“SPENT_ID” = w2.“SPENT_ID”
आणि w2.“LRM” = w.“LRM”);

समस्येचे वर्णन अंदाजे मानक आहे - “सर्व काही वाईट आहे. काय प्रॉब्लेम आहे ते सांग."
मला लगेच साडेतीन इंचाच्या ड्राईव्हच्या काळातील एक किस्सा आठवला:

लंगडा हॅकरकडे येतो.
- माझ्यासाठी काहीही काम करत नाही, मला सांगा समस्या कुठे आहे.
- डीएनए मध्ये...

परंतु अर्थातच, कामगिरीच्या घटनांचे निराकरण करण्याचा हा मार्ग नाही. "ते आम्हाला समजू शकत नाहीत" (सह). आपण ते बाहेर काढणे आवश्यक आहे.
बरं, खणून काढूया. कदाचित परिणामी काहीतरी जमा होईल.

चौकशी सुरू झाली

तर, स्पष्टीकरणाचा अवलंब न करता उघड्या डोळ्यांनी काय ताबडतोब पाहिले जाऊ शकते.
1) JOIN वापरले जात नाहीत. हे वाईट आहे, विशेषतः जर कनेक्शनची संख्या एकापेक्षा जास्त असेल.
२) पण त्याहूनही वाईट म्हणजे सहसंबंधित सबक्वेरी, शिवाय, एकत्रीकरणासह. हे खूप वाईट आहे.
हे अर्थातच वाईट आहे. पण हे फक्त एकीकडे आहे. दुसरीकडे, हे खूप चांगले आहे, कारण समस्येचे स्पष्टपणे समाधान आणि विनंती आहे जी सुधारली जाऊ शकते.
भविष्य सांगणाऱ्या (सी) कडे जाऊ नका.
क्वेरी योजना इतकी क्लिष्ट नाही, परंतु ती खूपच सूचक आहे:
अंमलबजावणी योजना

सर्वात मनोरंजक आणि उपयुक्त, नेहमीप्रमाणे, सुरूवातीस आणि शेवटी आहे.
नेस्टेड लूप (किंमत=935.84..479763226.18 पंक्ती=3322 रुंदी=135) (वास्तविक वेळ=31.536..8220420.295 पंक्ती=8111656 लूप=1)
नियोजन वेळ: 3.807 ms
अंमलबजावणीची वेळ: 8222351.640 ms
पूर्ण होण्याची वेळ 2 तासांपेक्षा जास्त आहे.

खोट्या गृहितकांना वेळ लागला

गृहीतक 1 - ऑप्टिमायझर चूक करतो आणि चुकीची योजना तयार करतो.

अंमलबजावणी योजनेची कल्पना करण्यासाठी, आम्ही साइट वापरू https://explain.depesz.com/. तथापि, साइटने मनोरंजक किंवा उपयुक्त काहीही दर्शवले नाही. पहिल्या आणि दुसऱ्या दृष्टीक्षेपात, खरोखर मदत करू शकेल असे काहीही नाही. हे शक्य आहे की पूर्ण स्कॅन किमान आहे. पुढे जा.

हायपोथेसिस 2-ऑटोव्हॅक्यूम बाजूच्या बेसवर प्रभाव, आपल्याला ब्रेकपासून मुक्त होणे आवश्यक आहे.

परंतु ऑटोव्हॅक्यूम डिमन चांगले वागतात, लांब-लटकणारी प्रक्रिया नाहीत. गंभीर भार नाही. आपल्याला आणखी काहीतरी शोधण्याची गरज आहे.

गृहीतक 3 - आकडेवारी जुनी आहे, प्रत्येक गोष्टीची पुनर्गणना करणे आवश्यक आहे

पुन्हा, ते नाही. आकडेवारी अद्ययावत आहे. जे, ऑटोव्हॅक्यूममध्ये समस्या नसल्यामुळे, आश्चर्यकारक नाही.

चला ऑप्टिमाइझ करणे सुरू करूया

मुख्य सारणी 'wdata' नक्कीच लहान नाही, जवळजवळ 3 दशलक्ष रेकॉर्ड आहेत.
आणि हे टेबल पूर्ण स्कॅनचे अनुसरण करते.

हॅश कॉन्ड: ((w."SPENT_ID" = s."SPENT_ID") आणि ((उपयोजना 1) = s."SPENT_DATE"))
-> Seq स्कॅन wdata w वर (cost=0.00..574151.49 पंक्ती=26886249 रुंदी=46) (वास्तविक वेळ=0.005..8153.565 पंक्ती=26873950 loops=1)
आम्ही मानक गोष्ट करतो: "चला, एक निर्देशांक बनवू आणि सर्वकाही उडेल."
“SPENT_ID” फील्डवर एक अनुक्रमणिका तयार केली
परिणामी:
अनुक्रमणिका वापरून अंमलबजावणी योजना क्वेरी करा

बरं, मदत केली का?
होते: 8 222 351.640ms (2 तासांपेक्षा थोडे जास्त)
झाले: 6 985 431.575 ms (जवळजवळ 2 तास)
सर्वसाधारणपणे, समान सफरचंद, बाजूचे दृश्य.
चला क्लासिक्स लक्षात ठेवूया:
“तुमच्याकडे तेच आहे, पण पंख नसलेले? शोधेल".

तत्वतः, याला एक चांगला परिणाम म्हटले जाऊ शकते, चांगले, चांगले नाही, परंतु स्वीकार्य. कमीतकमी, किती केले गेले आणि काय केले गेले ते चांगले का होते याचे वर्णन करणारा एक मोठा अहवाल ग्राहकांना द्या.
मात्र, अंतिम निर्णय अद्याप दूर आहे. खूप दूर.

आणि आता सर्वात मनोरंजक गोष्ट - आम्ही ऑप्टिमाइझ करणे सुरू ठेवतो, आम्ही विनंती पॉलिश करू

पहिली पायरी - जॉइन वापरा

पुन्हा लिहिलेली विनंती आता यासारखी दिसते (किमान अधिक सुंदर):
JOIN वापरून क्वेरी करानिवडा
p. “PARAMETER_ID” parameter_id म्हणून,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" as customer_partnumber,
w. "LRM" AS LRM,
w. "LOTID" प्रमाणे लोटिड,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” as low_spec_limit,
w.“UPPER_SPEC_LIMIT” as upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s. “SPENT_DATE” AS spent_date,
अर्क ("SPENT_DATE" पासून वर्ष) AS वर्ष,
महिना म्हणून काढा("SPENT_DATE" पासून महिना),
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p. “CUSTOMERPARAM_NAME” as customerparam_name
"SPENT_ID"=s.""SPENT_ID" वर आतील सामील होण्यासाठी खर्च केलेल्या wdata वरून
आतील सामील व्हा pmtr p ON p. “PARAMETER_ID” = w. “PARAMETER_ID”
INNER Join spent_pd sp ON s. “SPENT_ID” = sp. “SPENT_ID”
आतील सहभागी pd pd ऑन pd. “PD_ID” = sp. “PD_ID”
WHERE
s. “SPENT_DATE” >= '2018-07-01' आणि s. “SPENT_DATE” <= '2018-09-30'आणि
s.“SPENT_DATE” = (MAX निवडा(s2.“SPENT_DATE”)
w2 वर w2 अंतर्गत सामील होण्यासाठी s2 खर्च केला wdata वरून.“SPENT_ID”=s2.“SPENT_ID”
इनर जॉइन wdata w
ऑन w2.“LRM” = w.“LRM”);
नियोजन वेळ: 2.486 ms
अंमलबजावणीची वेळ: 1223680.326 ms

तर, पहिला निकाल.
होते: 6 ms (जवळजवळ 985 तास).
झाले: 1 223 680.326 ms (फक्त 20 मिनिटांपेक्षा जास्त).
चांगला परिणाम. तत्वतः, पुन्हा, आम्ही तेथे थांबू शकतो. परंतु हे इतके रसहीन आहे, आपण थांबवू शकत नाही.
च्या साठी

पायरी दोन - सहसंबंधित सबक्वेरीपासून मुक्त व्हा

विनंती मजकूर बदलला:
सहसंबंधित सबक्वेरीशिवायनिवडा
p. “PARAMETER_ID” parameter_id म्हणून,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" as customer_partnumber,
w. "LRM" AS LRM,
w. "LOTID" प्रमाणे लोटिड,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” as low_spec_limit,
w.“UPPER_SPEC_LIMIT” as upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s. “SPENT_DATE” AS spent_date,
अर्क ("SPENT_DATE" पासून वर्ष) AS वर्ष,
महिना म्हणून काढा("SPENT_DATE" पासून महिना),
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p. “CUSTOMERPARAM_NAME” as customerparam_name
wdata वरून INNER Join वर s खर्च केला.“SPENT_ID” = w.“SPENT_ID”
आतील सामील व्हा pmtr p ON p. “PARAMETER_ID” = w. “PARAMETER_ID”
INNER Join spent_pd sp ON s. “SPENT_ID” = sp. “SPENT_ID”
आतील सहभागी pd pd ऑन pd. “PD_ID” = sp. “PD_ID”
अंतर्गत सामील व्हा (w2 निवडा.“LRM”, MAX(s2.“SPENT_DATE”)
खर्च केलेल्या s2 पासून INNER जॉइन wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
w2 द्वारे गट.“LRM”
) md on w.“LRM” = md.“LRM”
WHERE
s."SPENT_DATE" >= '2018-07-01' आणि s."SPENT_DATE" <= '2018-09-30';
नियोजन वेळ: 2.291 ms
अंमलबजावणीची वेळ: 165021.870 ms

होते: 1 223 680.326 ms (फक्त 20 मिनिटांपेक्षा जास्त).
झाले: 165 021.870 ms (फक्त 2 मिनिटांपेक्षा जास्त).
हे आधीच चांगले आहे.
तथापि, ब्रिटिश म्हणतात त्याप्रमाणे "पण, नेहमी एक पण आहे" जो परिणाम खूप चांगला आहे तो आपोआप संशय निर्माण करतो. येथे काहीतरी चूक आहे.

सहसंबंधित सबक्वेरीपासून मुक्त होण्यासाठी क्वेरी दुरुस्त करण्याबद्दलचे गृहितक योग्य आहे. परंतु अंतिम परिणाम योग्य होण्यासाठी तुम्हाला त्यात थोडासा बदल करावा लागेल.
परिणामी, प्रथम मध्यवर्ती परिणाम:
सहसंबंधित सबक्वेरीशिवाय संपादित क्वेरीनिवडा
p. “PARAMETER_ID” parameter_id म्हणून,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" as customer_partnumber,
w. "LRM" AS LRM,
w. "LOTID" प्रमाणे लोटिड,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” as low_spec_limit,
w.“UPPER_SPEC_LIMIT” as upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s. “SPENT_DATE” AS spent_date,
अर्क (s. “SPENT_DATE” पासून वर्ष) AS वर्ष,
काढा(s. “SPENT_DATE” पासून महिना) महिना म्हणून,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p. “CUSTOMERPARAM_NAME” as customerparam_name
wdata वरून INNER Join वर s खर्च केला.“SPENT_ID” = w.“SPENT_ID”
आतील सामील व्हा pmtr p ON p. “PARAMETER_ID” = w. “PARAMETER_ID”
INNER Join spent_pd sp ON s. “SPENT_ID” = sp. “SPENT_ID”
आतील सहभागी pd pd ऑन pd. “PD_ID” = sp. “PD_ID”
आतील सामील व्हा (w2 निवडा.“LRM”, MAX(s2.“SPENT_DATE”) “SPENT_DATE” म्हणून
खर्च केलेल्या s2 पासून INNER जॉइन wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
w2 द्वारे गट.“LRM”
) md ON md. “SPENT_DATE” = s. “SPENT_DATE” आणि md. “LRM” = w. “LRM”
WHERE
s."SPENT_DATE" >= '2018-07-01' आणि s."SPENT_DATE" <= '2018-09-30';
नियोजन वेळ: 3.192 ms
अंमलबजावणीची वेळ: 208014.134 ms

तर, आम्ही ज्याचा शेवट करतो तो पहिला स्वीकारार्ह परिणाम आहे, जो ग्राहकाला दाखवण्यास लाज वाटत नाही:
सुरुवात केली: 8 222 351.640 ms (2 तासांपेक्षा जास्त)
आम्ही साध्य करण्यात व्यवस्थापित केले: 1 ms (223 मिनिटांपेक्षा थोडे जास्त).
निकाल (अंतरिम): 208 014.134 ms (फक्त 3 मिनिटांपेक्षा जास्त).

उत्कृष्ट परिणाम.

परिणाम

आम्ही तिथे थांबू शकलो असतो.
परंतु…
खाण्याने भूक लागते. जो चालतो तो रस्ता पार पाडतो. कोणताही परिणाम मध्यवर्ती आहे. थांबला आणि मेला. इ.
चला ऑप्टिमायझेशन सुरू ठेवूया.
उत्तम कल्पना. विशेषत: ग्राहकाचीही हरकत नाही हे लक्षात घेऊन. आणि त्यासाठी जोरदार.

तर, डेटाबेस रीडिझाइन करण्याची वेळ आली आहे. क्वेरी स्ट्रक्चर स्वतःच यापुढे ऑप्टिमाइझ केले जाऊ शकत नाही (जरी, हे नंतर दिसून आले की, सर्वकाही प्रत्यक्षात अपयशी ठरते याची खात्री करण्यासाठी एक पर्याय आहे). परंतु डेटाबेस डिझाइन ऑप्टिमाइझ करणे आणि विकसित करणे ही आधीपासूनच एक अतिशय आशादायक कल्पना आहे. आणि सर्वात महत्वाचे म्हणजे मनोरंजक. पुन्हा, आपले तारुण्य लक्षात ठेवा. मी लगेच डीबीए झालो नाही, मी प्रोग्रामर (बेसिक, असेंबलर, सी, डबल-प्लस सी, ओरॅकल, plsql) म्हणून मोठा झालो. एक मनोरंजक विषय, अर्थातच, वेगळ्या संस्मरणासाठी ;-).
तथापि, आपण विचलित होऊ नका.

त्यामुळे

किंवा कदाचित विभाजन आम्हाला मदत करेल?
स्पॉयलर - "होय, हे कार्यप्रदर्शन ऑप्टिमाइझ करण्यास मदत करते."

पण ती पूर्णपणे वेगळी कथा आहे...

पुढे चालू…

स्त्रोत: www.habr.com