இது எப்படி தொடங்கியது என்று உங்களுக்கு நினைவிருக்கிறதா. எல்லாமே முதல் முறை மற்றும் மீண்டும்

PostgreSQL வினவலை எவ்வாறு மேம்படுத்த வேண்டும் மற்றும் அதிலிருந்து என்ன வந்தது என்பது பற்றி.
நீங்கள் ஏன் செய்ய வேண்டியிருந்தது? ஆம், ஏனென்றால் முந்தைய 4 ஆண்டுகளாக எல்லாம் அமைதியாக, அமைதியாக, கடிகாரம் டிக்டிங் போல வேலை செய்தது.
கல்வெட்டாக.

உண்மை சம்பவங்களை அடிப்படையாகக் கொண்டது.
அனைத்து பெயர்களும் மாற்றப்பட்டுள்ளன, தற்செயல் நிகழ்வுகள் சீரற்றவை.

நீங்கள் ஒரு குறிப்பிட்ட முடிவை அடையும்போது, ​​​​ஆரம்பத்திற்கான உத்வேகம் என்ன என்பதை நினைவில் கொள்வது எப்போதும் சுவாரஸ்யமானது, அது எங்கு தொடங்கியது.

எனவே, இதன் விளைவாக என்ன நடந்தது என்பது கட்டுரையில் சுருக்கமாக விவரிக்கப்பட்டுள்ளது "PostgreSQL செயல்திறனை மேம்படுத்துவதற்கான முறைகளில் ஒன்று தொகுப்பு".

முந்தைய நிகழ்வுகளின் சங்கிலியை மீண்டும் உருவாக்குவது சுவாரஸ்யமாக இருக்கும்.
வரலாறு சரியான தொடக்கத் தேதியைச் சேமித்தது - 2018-09-10 18:02:48.
மேலும், கதையில் ஒரு கோரிக்கை உள்ளது, அதில் இருந்து இது தொடங்கியது:
பிரச்சனை கோரிக்கைதேர்வு
ப.“PARAMETER_ID” parameter_id ஆக,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" வாடிக்கையாளர்_பார்ட்னர் எண்,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.“RTD_VALUE” என RTD_value,
w.“LOWER_SPEC_LIMIT” AS low_spec_limit,
w.“UPPER_SPEC_LIMIT” மேல்_spec_limit,
ப."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS செலவிட்ட_பெயர்,
s.“SPENT_DATE” AS செலவழித்த_தேதி,
பிரித்தெடுத்தல்("SPENT_DATE" இலிருந்து ஆண்டு) AS ஆண்டு,
பிரித்தெடுத்தல்(மாதம் "SPENT_DATE") மாதமாக,
s."REPORT_NAME" அறிக்கை_பெயர்,
p."STPM_NAME" AS stpm_name,
ப.“CUSTOMERPARAM_NAME” வாடிக்கையாளர்பரம்_பெயர்
Wdata w இலிருந்து,
செலவழித்த கள்,
pmtr p,
செலவிடப்பட்ட_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
எங்கே s2.“SPENT_ID” = w2.“SPENT_ID”
மற்றும் w2.“LRM” = w.“LRM”);

சிக்கலின் விளக்கம் கணிக்கக்கூடிய நிலையானது - “எல்லாம் மோசமாக உள்ளது. என்ன பிரச்சனை என்று சொல்லுங்கள்.”
எனக்கு உடனடியாக மூன்றரை அங்குல டிரைவ்களின் காலத்திலிருந்து ஒரு கதை நினைவுக்கு வந்தது:

லேமர் ஹேக்கரிடம் வருகிறது.
-எனக்கு எதுவும் வேலை செய்யவில்லை, பிரச்சனை எங்கே என்று சொல்லுங்கள்.
டிஎன்ஏவில்...

ஆனால் நிச்சயமாக, செயல்திறன் சம்பவங்களைத் தீர்ப்பதற்கான வழி இதுவல்ல. "அவர்கள் நம்மைப் புரிந்து கொள்ளாமல் இருக்கலாம்" (உடன்). நாம் அதை கண்டுபிடிக்க வேண்டும்.
சரி, தோண்டலாம். ஒருவேளை இதன் விளைவாக ஏதாவது குவிந்துவிடும்.

விசாரணை தொடங்கியது

எனவே, EXPLAIN கூட நாடாமல், நிர்வாணக் கண்ணால் உடனடியாக என்ன பார்க்க முடியும்.
1) JOINகள் பயன்படுத்தப்படவில்லை. இது மோசமானது, குறிப்பாக இணைப்புகளின் எண்ணிக்கை ஒன்றுக்கு மேற்பட்டதாக இருந்தால்.
2) ஆனால் அதைவிட மோசமானது ஒன்றுக்கொன்று தொடர்புடைய துணை வினவல்கள் ஆகும். இது மிகவும் மோசமானது.
இது நிச்சயமாக மோசமானது. ஆனால் இது ஒருபுறம் மட்டுமே. மறுபுறம், இது மிகவும் நல்லது, ஏனென்றால் பிரச்சனைக்கு ஒரு தீர்வு மற்றும் மேம்படுத்தக்கூடிய கோரிக்கை உள்ளது.
ஜோசியக்காரரிடம் (சி) செல்ல வேண்டாம்.
வினவல் திட்டம் மிகவும் சிக்கலானது அல்ல, ஆனால் இது மிகவும் சுட்டிக்காட்டுகிறது:
செயல்படுத்தும் திட்டம்

மிகவும் சுவாரஸ்யமான மற்றும் பயனுள்ள, வழக்கம் போல், ஆரம்பத்திலும் முடிவிலும் உள்ளது.
உள்ளமைக்கப்பட்ட வளையம் (செலவு=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) = கள்."SPENT_DATE"))
-> Seq ஸ்கேன் wdata w இல் (செலவு=0.00..574151.49 வரிசைகள்=26886249 அகலம்=46) (உண்மையான நேரம்=0.005..8153.565 வரிசைகள்=26873950 சுழல்கள்=1)
நாங்கள் நிலையான காரியத்தைச் செய்கிறோம்: "வாருங்கள், ஒரு குறியீட்டை உருவாக்குவோம், எல்லாம் பறக்கும்."
"SPENT_ID" புலத்தில் ஒரு அட்டவணையை உருவாக்கியது
அதன் விளைவாக:
குறியீட்டைப் பயன்படுத்தி செயல்படுத்தும் திட்டத்தை வினவவும்

சரி, அது உதவி செய்ததா?
இருந்தது: 8 222 351.640 ms (2 மணிநேரத்திற்கு சற்று அதிகமாக)
ஆனது: 6 985 431.575 ms (கிட்டத்தட்ட 2 மணிநேரம்)
பொதுவாக, அதே ஆப்பிள்கள், பக்க பார்வை.
கிளாசிக்ஸை நினைவில் கொள்வோம்:
"உங்களிடம் அதே ஒன்று இருக்கிறதா, ஆனால் இறக்கைகள் இல்லாமல் இருக்கிறதா? தேடுவேன்".

கொள்கையளவில், இது ஒரு நல்ல முடிவு என்று அழைக்கப்படலாம், நல்லது, நல்லது அல்ல, ஆனால் ஏற்றுக்கொள்ளத்தக்கது. குறைந்த பட்சம், வாடிக்கையாளருக்கு எவ்வளவு செய்யப்பட்டுள்ளது மற்றும் ஏன் செய்தது நல்லது என்பதை விவரிக்கும் ஒரு பெரிய அறிக்கையை வழங்கவும்.
ஆனால் இன்னும், இறுதி முடிவு இன்னும் வெகு தொலைவில் உள்ளது. மிக மிக.

இப்போது மிகவும் சுவாரஸ்யமான விஷயம் - நாங்கள் தொடர்ந்து மேம்படுத்துகிறோம், கோரிக்கையை மெருகூட்டுவோம்

படி ஒன்று - JOIN ஐப் பயன்படுத்தவும்

மீண்டும் எழுதப்பட்ட கோரிக்கை இப்போது இது போல் தெரிகிறது (குறைந்தது இன்னும் அழகான):
JOIN ஐப் பயன்படுத்தி வினவல்தேர்வு
ப.“PARAMETER_ID” parameter_id ஆக,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" வாடிக்கையாளர்_பார்ட்னர் எண்,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.“RTD_VALUE” என RTD_value,
w.“LOWER_SPEC_LIMIT” AS low_spec_limit,
w.“UPPER_SPEC_LIMIT” மேல்_spec_limit,
ப."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS செலவிட்ட_பெயர்,
s.“SPENT_DATE” AS செலவழித்த_தேதி,
பிரித்தெடுத்தல்("SPENT_DATE" இலிருந்து ஆண்டு) AS ஆண்டு,
பிரித்தெடுத்தல்(மாதம் "SPENT_DATE") மாதமாக,
s."REPORT_NAME" அறிக்கை_பெயர்,
p."STPM_NAME" AS stpm_name,
ப.“CUSTOMERPARAM_NAME” வாடிக்கையாளர்பரம்_பெயர்
wdata w INNER JOIN இல் செலவழித்த வி.“SPENT_ID”=s.”“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN paid_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_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”
INNER JOIN wdata w
ON w2.“LRM” = w.“LRM” );
திட்டமிடல் நேரம்: 2.486 ms
செயல்படுத்தும் நேரம்: 1223680.326 ms

எனவே, முதல் முடிவு.
இருந்தது: 6 ms (கிட்டத்தட்ட 985 மணிநேரம்).
ஆனது: 1 223 680.326 ms (20 நிமிடங்களுக்கு மேல்).
நல்ல முடிவு. கொள்கையளவில், மீண்டும், நாம் அங்கேயே நிறுத்தலாம். ஆனால் இது மிகவும் ஆர்வமற்றது, நீங்கள் நிறுத்த முடியாது.
FOR

படி இரண்டு - தொடர்புடைய துணை வினவலில் இருந்து விடுபடவும்

மாற்றப்பட்ட கோரிக்கை உரை:
தொடர்புள்ள துணை வினவல் இல்லாமல்தேர்வு
ப.“PARAMETER_ID” parameter_id ஆக,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" வாடிக்கையாளர்_பார்ட்னர் எண்,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.“RTD_VALUE” என RTD_value,
w.“LOWER_SPEC_LIMIT” AS low_spec_limit,
w.“UPPER_SPEC_LIMIT” மேல்_spec_limit,
ப."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS செலவிட்ட_பெயர்,
s.“SPENT_DATE” AS செலவழித்த_தேதி,
பிரித்தெடுத்தல்("SPENT_DATE" இலிருந்து ஆண்டு) AS ஆண்டு,
பிரித்தெடுத்தல்(மாதம் "SPENT_DATE") மாதமாக,
s."REPORT_NAME" அறிக்கை_பெயர்,
p."STPM_NAME" AS stpm_name,
ப.“CUSTOMERPARAM_NAME” வாடிக்கையாளர்பரம்_பெயர்
Wdata w INNER JOIN இல் செலவழித்த வினாடிகள்.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN paid_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
உள் சேர் (தேர்வு w2.“LRM”, MAX(s2.“SPENT_DATE”)
s2 இல் செலவழித்த s2 INNER Wdata w2 ON s2.“SPENT_ID” = wXNUMX.“SPENT_ID”
w2 மூலம் குழு.“LRM”
) md on w.“LRM” = md.“LRM”
எங்கே
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 நிமிடங்களுக்கு மேல்).
இது ஏற்கனவே நன்றாக உள்ளது.
இருப்பினும், ஆங்கிலேயர்கள் சொல்வது போல் "ஆனால், எப்போதும் ஒரு ஆனால் உள்ளது" மிகவும் நல்ல முடிவு தானாகவே சந்தேகத்தை எழுப்ப வேண்டும். இங்கே ஏதோ தவறு உள்ளது.

தொடர்புள்ள துணை வினவலில் இருந்து விடுபட வினவலை சரிசெய்வது பற்றிய கருதுகோள் சரியானது. ஆனால் இறுதி முடிவு சரியாக இருக்க, நீங்கள் அதை சிறிது மாற்ற வேண்டும்.
இதன் விளைவாக, முதல் இடைநிலை முடிவு:
தொடர்புள்ள துணை வினவல் இல்லாமல் திருத்தப்பட்ட வினவல்தேர்வு
ப.“PARAMETER_ID” parameter_id ஆக,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" வாடிக்கையாளர்_பார்ட்னர் எண்,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.“RTD_VALUE” என RTD_value,
w.“LOWER_SPEC_LIMIT” AS low_spec_limit,
w.“UPPER_SPEC_LIMIT” மேல்_spec_limit,
ப."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS செலவிட்ட_பெயர்,
s.“SPENT_DATE” AS செலவழித்த_தேதி,
பிரித்தெடுத்தல்(வருடம் s.“SPENT_DATE”) AS ஆண்டு,
பிரித்தெடுத்தல்(மாதம் இலிருந்து s.“SPENT_DATE”) மாதமாக,
s."REPORT_NAME" அறிக்கை_பெயர்,
p."STPM_NAME" AS stpm_name,
ப.“CUSTOMERPARAM_NAME” வாடிக்கையாளர்பரம்_பெயர்
Wdata w INNER JOIN இல் செலவழித்த வினாடிகள்.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN paid_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
உள் இணை ( w2.“LRM”, MAX(s2.“SPENT_DATE”) என “SPENT_DATE” ஐத் தேர்ந்தெடுக்கவும்
s2 இல் செலவழித்த s2 INNER Wdata w2 ON s2.“SPENT_ID” = wXNUMX.“SPENT_ID”
w2 மூலம் குழு.“LRM”
) md ON md.“SPENT_DATE” = s.“SPENT_DATE” மற்றும் md.“LRM” = w.“LRM”
எங்கே
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 நிமிடங்களுக்கு மேல்).

சிறப்பான முடிவு.

இதன் விளைவாக

அங்கேயே நிறுத்தியிருக்கலாம்.
ஆனாலும்…
சாப்பிட்டால் பசி வரும். நடப்பவர் சாலையை மாஸ்டர் செய்வார். எந்த முடிவும் இடைநிலை. நின்று இறந்தார். முதலியன
தேர்வுமுறையைத் தொடரலாம்.
சிறந்த யோசனை. குறிப்பாக வாடிக்கையாளர் கூட கவலைப்படவில்லை என்று கருதுகின்றனர். மேலும் அதற்கு வலுவாகவும்.

எனவே, தரவுத்தள மறுவடிவமைப்புக்கான நேரம் இது. வினவல் கட்டமைப்பை இனி மேம்படுத்த முடியாது (இருப்பினும், அது பின்னர் மாறியது போல், எல்லாம் உண்மையில் தோல்வியடைவதை உறுதிப்படுத்த ஒரு விருப்பம் உள்ளது). ஆனால் தரவுத்தள வடிவமைப்பை மேம்படுத்துவது மற்றும் மேம்படுத்துவது ஏற்கனவே மிகவும் நம்பிக்கைக்குரிய யோசனையாகும். மற்றும் மிக முக்கியமாக சுவாரஸ்யமானது. மீண்டும், உங்கள் இளமையை நினைவில் கொள்ளுங்கள். நான் உடனடியாக DBA ஆகவில்லை, நான் ஒரு புரோகிராமராக வளர்ந்தேன் (BASIC, assembler, C, double-plus C, Oracle, plsql). ஒரு சுவாரஸ்யமான தலைப்பு, நிச்சயமாக, ஒரு தனி நினைவுக் குறிப்புக்கு ;-).
இருப்பினும், நாம் திசைதிருப்ப வேண்டாம்.

எனவே

அல்லது பகிர்வு நமக்கு உதவுமா?
ஸ்பாய்லர் - "ஆம், செயல்திறனை மேம்படுத்துவது உட்பட இது உதவியது."

ஆனால் இது முற்றிலும் மாறுபட்ட கதை ...

தொடரும்…

ஆதாரம்: www.habr.com