Հիշու՞մ եք, թե ինչպես սկսվեց ամեն ինչ։ Ամեն ինչ առաջին անգամ էր ու նորից

Այն մասին, թե ինչպես պետք է օպտիմալացնեինք PostgreSQL հարցումը և ինչ ստացվեց այդ ամենից:
Ինչու՞ ստիպված էիր: Այո, քանի որ նախորդ 4 տարիներին ամեն ինչ աշխատում էր հանգիստ, հանգիստ, ինչպես ժամացույցը:
Որպես էպիգրաֆ.

Իրական իրադարձությունների հիման վրա:
Բոլոր անունները փոխված են, զուգադիպությունները պատահական են։

Երբ հասնում ես որոշակի արդյունքի, միշտ հետաքրքիր է հիշել, թե որն էր սկզբի խթանը, որտեղից սկսվեց ամեն ինչ:

Այսպիսով, այն, ինչ տեղի ունեցավ արդյունքում, համառոտ նկարագրված է հոդվածում «Սինթեզը՝ որպես PostgreSQL-ի կատարողականությունը բարելավելու մեթոդներից մեկը.

Հավանաբար հետաքրքիր կլինի վերստեղծել նախորդ իրադարձությունների շղթան։
Պատմությունը պահպանեց ճշգրիտ մեկնարկի ամսաթիվը - 2018-09-10 18:02:48:
Նաև պատմության մեջ կա խնդրանք, որից ամեն ինչ սկսվեց.
Խնդրի հարցումSELECT
p.«PARAMETER_ID» որպես parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. «LRM» AS LRM,
w. «LOTID» AS lotid,
w.«RTD_VALUE» AS RTD_value,
w.«LOWER_SPEC_LIMIT» AS ստորին_spec_limit,
w.«UPPER_SPEC_LIMIT» AS վերին_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS ծախսված_անուն,
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-ից,
ծախսած ս,
pmtr p,
spent_pd sp,
pd pd
WHERE s.«SPENT_ID» = w.«SPENT_ID»
AND p."PARAMETER_ID" = w."PARAMETER_ID"
AND s.«SPENT_ID» = sp.«SPENT_ID»
AND pd."PD_ID" = sp."PD_ID"
AND s.«SPENT_DATE» >= '2018-07-01' AND s.«SPENT_DATE» <= '2018-09-30'
և s.«SPENT_DATE» = (SELECT MAX(s2.«SPENT_DATE»)
Ծախսած s2-ից,
wdata w2
WHERE s2.«SPENT_ID» = w2.«SPENT_ID»
ԵՎ w2.«LRM» = w.«LRM»);

Խնդրի նկարագրությունը կանխատեսելիորեն ստանդարտ է. «Ամեն ինչ վատ է: Ասա ինձ, թե որն է խնդիրը»:
Միանգամից հիշեցի մի անեկդոտ 3 ու կես դյույմ քշումների ժամանակներից.

Լամերը գալիս է հաքերի մոտ։
-Ինձ մոտ ոչինչ չի ստացվում, ասա, թե որտեղ է խնդիրը:
-ԴՆԹ-ում...

Բայց, իհարկե, այս կերպ չի կարելի լուծել ներկայացման միջադեպերը։ «Նրանք կարող են մեզ չհասկանալ« (Հետ): Մենք պետք է դա պարզենք:
Դե, արի փորենք։ Միգուցե արդյունքում ինչ-որ բան կուտակվի։

Հետաքննություն է սկսվել

Այսպիսով, ինչ կարելի է անմիջապես տեսնել անզեն աչքով, առանց նույնիսկ ԲԱՑԱՏՐԵԼՈՒԹՅԱՆ դիմելու։
1) JOIN-ները չեն օգտագործվում: Սա վատ է, հատկապես, եթե միացումների թիվը մեկից ավելի է:
2) Բայց ավելի վատն այն է, որ փոխկապակցված ենթահղումները, ընդ որում, ագրեգացման հետ: Սա շատ վատ է։
Սա իհարկե վատ է: Բայց սա միայն մի կողմից. Մյուս կողմից, սա շատ լավ է, քանի որ խնդիրն ակնհայտորեն ունի լուծում և խնդրանք, որը կարելի է բարելավել։
Մի գնա գուշակի մոտ (C):
Հարցման պլանն այնքան էլ բարդ չէ, բայց բավականին ցուցիչ է.
Կատարման պլան

Ամենահետաքրքիրն ու օգտակարը, ինչպես միշտ, սկզբում և վերջում է:
Nested Loop (ծախս=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-Ավտովակուումային կողմից ազդել բազայի վրա, պետք է ազատվել արգելակներից։

Բայց ավտովակուումային դևերը լավ են վարվում, երկար կախված գործընթացներ չկան: Ոչ մի լուրջ բեռ: Մենք պետք է այլ բան փնտրենք:

Վարկած 3 – Վիճակագրությունը հնացել է, ամեն ինչ վերահաշվարկի կարիք ունի

Կրկին, ոչ դա: Վիճակագրությունը արդիական է։ Ինչը, հաշվի առնելով ավտովակուումի հետ կապված խնդիրների բացակայությունը, զարմանալի չէ:

Եկեք սկսենք օպտիմալացնել

Հիմնական աղյուսակը «wdata»-ն, իհարկե, փոքր չէ, գրեթե 3 միլիոն գրառում:
Եվ այս աղյուսակն է, որին հետևում է Full Scan-ը:

Hash Cond. ((w."SPENT_ID" = s."SPENT_ID") AND ((Subplan 1) = s."SPENT_DATE"))
-> Հետագա սկանավորում wdata-ի վրա (արժեք=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-ի միջոցովSELECT
p.«PARAMETER_ID» որպես parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. «LRM» AS LRM,
w. «LOTID» AS lotid,
w.«RTD_VALUE» AS RTD_value,
w.«LOWER_SPEC_LIMIT» AS ստորին_spec_limit,
w.«UPPER_SPEC_LIMIT» AS վերին_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS ծախսված_անուն,
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
WROM wdata w INNER JOIN ծախսվել է ON w.“SPENT_ID”=s.”“SPENT_ID”
ՆԵՐՔԻՆ ՄԻԱՑՈՒՄ pmtr p ON p.«PARAMETER_ID» = w.«PARAMETER_ID»
ՆԵՐՔԻՆ ՄԻԱՑՈՒՄ spent_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» = (SELECT MAX (s2. «SPENT_DATE»)
WDATA-ից w2 ՆԵՐՔԻՆ ՄԻԱՑՈՒՄ ծախսվել է s2 ON w2-ի վրա.“SPENT_ID”=s2.“SPENT_ID”
ՆԵՐՔԻՆ ՄԻԱՑՈՒՄ wdata w
ON w2.«LRM» = w.«LRM» );
Պլանավորման ժամանակը` 2.486 ms
Կատարման ժամանակը: 1223680.326 ms

Այսպիսով, առաջին արդյունքը.
էր. 6 ms (գրեթե 985 ժամ):
Դարձավ՝ 1 223 680.326 ms (20 րոպեից մի փոքր ավելի):
Լավ արդյունք. Սկզբունքորեն, կրկին, մենք կարող էինք կանգ առնել այնտեղ: Բայց դա այնքան անհետաքրքիր է, դուք չեք կարող կանգ առնել:
ՀԱՄԱՐ

Քայլ երկրորդ՝ ազատվել փոխկապակցված ենթահարկից

Հարցման տեքստը փոխվել է՝
Առանց փոխկապակցված ենթահղումներիSELECT
p.«PARAMETER_ID» որպես parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. «LRM» AS LRM,
w. «LOTID» AS lotid,
w.«RTD_VALUE» AS RTD_value,
w.«LOWER_SPEC_LIMIT» AS ստորին_spec_limit,
w.«UPPER_SPEC_LIMIT» AS վերին_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS ծախսված_անուն,
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
WROM wdata w INNER JOIN ծախսվել է ON s.“SPENT_ID” = w.“SPENT_ID”
ՆԵՐՔԻՆ ՄԻԱՑՈՒՄ pmtr p ON p.«PARAMETER_ID» = w.«PARAMETER_ID»
ՆԵՐՔԻՆ ՄԻԱՑՈՒՄ spent_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»)
FROM ծախսած s2-ից ՆԵՐՔԻՆ ՄԻԱՑԵՔ wdata w2 ON s2.“SPENT_ID” = w2.“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 րոպեից մի փոքր ավելի):
Սա արդեն բավականին լավ է։
Այնուամենայնիվ, ինչպես ասում են բրիտանացիները.Բայց, միշտ կա, բայց« Չափազանց լավ արդյունքը պետք է ինքնաբերաբար կասկած հարուցի։ Այստեղ ինչ-որ բան այն չէ:

Հարցումը շտկելու վարկածը՝ փոխկապակցված ենթհարցումից ազատվելու համար ճիշտ է։ Բայց դուք պետք է մի փոքր շտկեք այն, որպեսզի վերջնական արդյունքը ճիշտ լինի:
Արդյունքում, առաջին միջանկյալ արդյունքը.
Խմբագրված հարցումն առանց փոխկապակցված ենթհարցմանSELECT
p.«PARAMETER_ID» որպես parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. «LRM» AS LRM,
w. «LOTID» AS lotid,
w.«RTD_VALUE» AS RTD_value,
w.«LOWER_SPEC_LIMIT» AS ստորին_spec_limit,
w.«UPPER_SPEC_LIMIT» AS վերին_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS ծախսված_անուն,
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
WROM wdata w INNER JOIN ծախսվել է ON s.“SPENT_ID” = w.“SPENT_ID”
ՆԵՐՔԻՆ ՄԻԱՑՈՒՄ pmtr p ON p.«PARAMETER_ID» = w.«PARAMETER_ID»
ՆԵՐՔԻՆ ՄԻԱՑՈՒՄ spent_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
ՆԵՐՔԻՆ ՄԻԱՑՆԵԼ ( SELECT w2. «LRM», MAX (s2. «SPENT_DATE») ՈՐՊԵՍ «SPENT_DATE»
FROM ծախսած s2-ից ՆԵՐՔԻՆ ՄԻԱՑԵՔ wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
ԽՈՒՄԲ W2.«LRM»
) md ON md.“SPENT_DATE” = s.“SPENT_DATE” AND 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): Հետաքրքիր թեմա, իհարկե, առանձին հուշագրության համար ;-):
Այնուամենայնիվ, եկեք չշեղվենք:

Այնպես որ,

Կամ գուցե բաժանումը մեզ կօգնի:
Սփոյլեր - «Այո, դա օգնեց, ներառյալ կատարողականի օպտիմալացումը»:

Բայց դա բոլորովին այլ պատմություն է...

Շարունակելի…

Source: www.habr.com