ඒ සියල්ල ආරම්භ වූ ආකාරය ඔබට මතකද. සෑම දෙයක්ම පළමු වතාවට සහ නැවත නැවතත් විය

මට PostgreSQL විමසුම ප්‍රශස්ත කළ යුතු ආකාරය සහ ඒ සියල්ලෙන් ලැබුණු දේ ගැන.
ඔබට සිදු වූයේ ඇයි? ඔව්, මොකද කලින් අවුරුදු 4 පුරාම හැම දෙයක්ම නිශ්ශබ්දව, සන්සුන්ව, ඔරලෝසුව ටික් කරක් මෙන් වැඩ කළා.
අභිලේඛනයක් ලෙස.

සැබෑ සිදුවීම් මත පදනම්ව.
සියලුම නම් වෙනස් කර ඇත, අහඹු සිදුවීම්.

ඔබ නිශ්චිත ප්‍රති result ලයක් අත්කර ගත් විට, එය ආරම්භය සඳහා පෙළඹවීම කුමක්ද, එය ආරම්භ වූ ස්ථානය කුමක්දැයි මතක තබා ගැනීම සැමවිටම සිත්ගන්නා කරුණකි.

එබැවින්, එහි ප්‍රතිඵලයක් ලෙස සිදුවූයේ කුමක්ද යන්න ලිපියේ කෙටියෙන් විස්තර කෙරේ.PostgreSQL කාර්ය සාධනය වැඩි දියුණු කිරීම සඳහා එක් ක්රමයක් ලෙස සංශ්ලේෂණය".

පෙර සිදුවීම් දාමය ප්‍රතිනිර්මාණය කිරීම බොහෝ විට සිත්ගන්නාසුළු වනු ඇත.
ඉතිහාසය විසින් නියම ආරම්භක දිනය සුරකින ලදී - 2018-09-10 18:02:48.
එසේම, කතාවේ ඒ සියල්ල ආරම්භ වූ ඉල්ලීමක් තිබේ:
ගැටළු ඉල්ලීමතෝරන්න
p.“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 ලෙස,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS වියදම් කළ_නම,
s.“SPENT_DATE” AS වියදම්_දිනය,
උපුටා ගැනීම ("SPENT_DATE" සිට වසර) වසරක් ලෙස,
උපුටා ගැනීම(මාසය "SPENT_DATE" සිට) මාසය ලෙස,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” පාරිභෝගික පාරම්_නම ලෙස
wdata w වෙතින්,
වියදම් කළා,
pmtr p,
වියදම් කළ_pd sp,
pd pd
කොහෙද 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” = (මැක්ස් තෝරන්න(s2.“SPENT_DATE”)
වැය කළ s2 සිට,
wdata w2
කොහෙද s2.“SPENT_ID” = w2.“SPENT_ID”
සහ w2.“LRM” = w.“LRM”);

ගැටලුවේ විස්තරය පුරෝකථනය කළ හැකි සම්මතය - "සියල්ල නරකයි. මට කියන්න මොකක්ද ප්‍රශ්නය කියලා."
මට එකපාරටම මතක් උනේ අඟල් 3 හමාරක් ඩ්‍රයිව් කරපු කාලෙක කතාවක්.

ලැමරය හැකර් වෙත පැමිණේ.
- මට කිසිම දෙයක් වැඩ කරන්නේ නැහැ, ගැටලුව කොහෙද කියලා මට කියන්න.
- DNA වල...

නමුත් ඇත්ත වශයෙන්ම, කාර්ය සාධන සිදුවීම් විසඳීමට මෙය මාර්ගය නොවේ. "එයාලට අපිව තේරෙන්නේ නැතුව ඇති" (සමග). අපි එය තේරුම් ගත යුතුයි.
හොඳයි, අපි හාරමු. සමහර විට ප්රතිඵලයක් ලෙස යමක් එකතු වනු ඇත.

විමර්ශන ආරම්භ විය

ඉතින්, පැහැදිලි කිරීමට පවා යොමු නොවී, පියවි ඇසින් වහාම දැකිය හැකි දේ.
1) JOIN භාවිතා නොකෙරේ. විශේෂයෙන්ම සම්බන්ධතා ගණන එකකට වඩා වැඩි නම් මෙය නරක ය.
2) නමුත් ඊටත් වඩා නරක දෙය නම් සහසම්බන්ධිත උප විමසුම්, එපමනක් නොව, එකතු කිරීම සමඟ ය. මෙය ඉතා නරකයි.
මෙය ඇත්තෙන්ම නරකයි. නමුත් මෙය එක් අතකින් පමණි. අනෙක් අතට, මෙය ඉතා හොඳයි, ගැටලුව පැහැදිලිවම විසඳුමක් සහ වැඩිදියුණු කළ හැකි ඉල්ලීමක් ඇති නිසා.
පේන කියන්නෙකු (C) වෙත නොයන්න.
විමසුම් සැලැස්ම එතරම් සංකීර්ණ නොවේ, නමුත් එය බෙහෙවින් ඇඟවුම් කරයි:
ක්රියාත්මක කිරීමේ සැලැස්ම

වඩාත් රසවත් හා ප්රයෝජනවත්, සුපුරුදු පරිදි, ආරම්භයේ සහ අවසානයේ වේ.
නෙස්ටඩ් ලූප් (පිරිවැය=935.84..479763226.18 පේළි=3322 පළල=135) (සැබෑ වේලාව=31.536..8220420.295 පේළි=8111656 ලූප=1)
සැලසුම් කාලය: 3.807 ms
ක්රියාත්මක කිරීමේ කාලය: 8222351.640 ms
සම්පූර්ණ කිරීමේ කාලය පැය 2 කට වඩා වැඩිය.

කාලය ගත වූ ව්‍යාජ උපකල්පන

උපකල්පනය 1 - ප්‍රශස්තකාරකය වැරදීමක් කර වැරදි සැලැස්මක් ගොඩනඟයි.

ක්රියාත්මක කිරීමේ සැලැස්ම දෘශ්යමාන කිරීම සඳහා, අපි වෙබ් අඩවිය භාවිතා කරන්නෙමු https://explain.depesz.com/. කෙසේ වෙතත්, වෙබ් අඩවිය රසවත් හෝ ප්රයෝජනවත් කිසිවක් පෙන්වූයේ නැත. මුලින්ම සහ දෙවන බැල්මට, ඇත්ත වශයෙන්ම උපකාර කළ හැකි කිසිවක් නැත. Full Scan එක අවම වෙන්න පුලුවන්ද. ඉදිරියට යන්න.

උපකල්පනය 2-ඔටෝවාකුම් පැත්තේ සිට පදනම මත බලපෑම, ඔබ තිරිංග ඉවත් කළ යුතුය.

නමුත් autovacuum demons හොඳින් හැසිරේ, දිගු එල්ලෙන ක්රියාවලීන් නොමැත. බරපතල බරක් නැත. අපි වෙන දෙයක් හොයන්න ඕන.

උපකල්පනය 3 - සංඛ්යාලේඛන යල්පැන ඇත, සියල්ල නැවත ගණනය කළ යුතුය

නැවතත්, එය නොවේ. සංඛ්‍යාලේඛන යාවත්කාලීනයි. ස්වයංක්‍රීය රික්තකයේ ගැටළු නොමැතිකම නිසා එය පුදුමයට කරුණක් නොවේ.

අපි ප්‍රශස්ත කිරීම ආරම්භ කරමු

ප්‍රධාන වගුව 'wdata' නිසැකවම කුඩා නොවේ, වාර්තා මිලියන 3කට ආසන්නය.
සම්පූර්ණ ස්කෑන් පහත දැක්වෙන්නේ මෙම වගුවයි.

Hash Cond: ((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 ms (පැය 2කට වඩා ටිකක් වැඩි)
එය බවට පත් විය: 6 985 431.575 ms (පැය 2 ක් පමණ)
සාමාන්යයෙන්, එකම ඇපල්, පැති දර්ශනය.
අපි සම්භාව්ය මතක තබා ගනිමු:
“ඔබට ඇත්තේ එකම එක, නමුත් පියාපත් නොමැතිද? සොයනු ඇත".

මූලධර්මය අනුව, මෙය හොඳ ප්රතිඵලය ලෙස හැඳින්විය හැක, හොඳයි, හොඳ නැත, නමුත් පිළිගත හැකි ය. අවම වශයෙන්, කොපමණ ප්‍රමාණයක් සිදු කර ඇත්ද සහ කළ දේ හොඳ වූයේ මන්දැයි විස්තර කරන විශාල වාර්තාවක් පාරිභෝගිකයාට ලබා දෙන්න.
නමුත් තවමත්, අවසාන තීරණය තවමත් බොහෝ දුරයි. ගොඩක් දුර.

දැන් වඩාත්ම සිත්ගන්නා කරුණ - අපි දිගටම ප්‍රශස්තිකරණය කරන්නෙමු, අපි ඉල්ලීම ඔප දමන්නෙමු

පළමු පියවර - JOIN භාවිතා කරන්න

නැවත ලිවූ ඉල්ලීම දැන් මේ වගේ (හොඳයි, අවම වශයෙන් වඩා ලස්සනයි):
JOIN භාවිතයෙන් විමසුමතෝරන්න
p.“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 ලෙස,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS වියදම් කළ_නම,
s.“SPENT_DATE” AS වියදම්_දිනය,
උපුටා ගැනීම ("SPENT_DATE" සිට වසර) වසරක් ලෙස,
උපුටා ගැනීම(මාසය "SPENT_DATE" සිට) මාසය ලෙස,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” පාරිභෝගික පාරම්_නම ලෙස
wdata w INNER JOIN s on w.“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”
WHERE
s.“SPENT_DATE” >= '2018-07-01' සහ s.“SPENT_DATE” <= '2018-09-30'AND
s.“SPENT_DATE” = (මැක්ස් තෝරන්න(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 කට වඩා වැඩි).
හොඳ ප්රතිඵලය. ප්‍රතිපත්තිමය වශයෙන්, නැවතත්, අපට එහි නතර විය හැකිය. නමුත් එය එතරම් උනන්දුවක් නොදක්වයි, ඔබට නතර කළ නොහැක.
සදහා

දෙවන පියවර - සහසම්බන්ධ උප විමසුමෙන් මිදෙන්න

වෙනස් කළ ඉල්ලීම් පෙළ:
සහසම්බන්ධ උප විමසුමකින් තොරවතෝරන්න
p.“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 ලෙස,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS වියදම් කළ_නම,
s.“SPENT_DATE” AS වියදම්_දිනය,
උපුටා ගැනීම ("SPENT_DATE" සිට වසර) වසරක් ලෙස,
උපුටා ගැනීම(මාසය "SPENT_DATE" සිට) මාසය ලෙස,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” පාරිභෝගික පාරම්_නම ලෙස
wdata w INNER JOIN s ON s.“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 INNER JOIN 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කට වඩා වැඩි).
මෙය දැනටමත් තරමක් හොඳයි.
කෙසේ වෙතත්, බ්රිතාන්යයන් පවසන පරිදි "නමුත්, සෑම විටම නමුත් පවතී" ඉතා හොඳ ප්‍රතිඵලයක් ස්වයංක්‍රීයව සැකයක් ඇති කළ යුතුය. මෙතන යම් දෙයක් වැරදිය.

සහසම්බන්ධ උප විමසුමෙන් මිදීම සඳහා විමසුම නිවැරදි කිරීම පිළිබඳ උපකල්පනය නිවැරදි ය. නමුත් අවසාන ප්‍රති result ලය නිවැරදි වීමට ඔබ එය ටිකක් වෙනස් කළ යුතුය.
ප්රතිඵලයක් වශයෙන්, පළමු අතරමැදි ප්රතිඵලය:
සහසම්බන්ධ උප විමසුමකින් තොරව සංස්කරණය කළ විමසුමතෝරන්න
p.“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 ලෙස,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS වියදම් කළ_නම,
s.“SPENT_DATE” AS වියදම්_දිනය,
උපුටා ගැනීම (වර්ෂ.“SPENT_DATE” සිට වසර) වසරක් ලෙස,
උපුටා ගැනීම (මාසය s.“SPENT_DATE” සිට) මාසය ලෙස,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” පාරිභෝගික පාරම්_නම ලෙස
wdata w INNER JOIN s ON s.“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 INNER JOIN 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

ඉතින්, අපි අවසන් කරන්නේ පළමු පිළිගත හැකි ප්‍රති result ලයයි, එය පාරිභෝගිකයාට පෙන්වීමට ලැජ්ජාවක් නොවේ:
ආරම්භ කළේ: 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