Дар хотир доред, ки ҳамааш чӣ гуна оғоз ёфт. Ҳама чиз бори аввал буд ва боз

Дар бораи он, ки чӣ гуна мо бояд дархости PostgreSQL-ро оптимизатсия кунем ва аз он чӣ натиҷа дод.
Чаро шумо маҷбур шудед? Бале, зеро 4 соли пеш хама чиз ором, оромона, мисли тик-такти соат кор мекард.
Ҳамчун эпиграф.

Дар асоси рӯйдодҳои воқеӣ.
Ҳама номҳо иваз карда шудаанд, тасодуфҳо тасодуфӣ мебошанд.

Вақте ки шумо ба натиҷаи муайян ноил мешавед, ҳамеша ҷолиб аст, ки ба ёд оред, ки чӣ гуна такони ибтидоӣ буд ва ҳамааш аз куҷо оғоз ёфт.

Пас, он чизе, ки дар натиҷа рӯй дод, дар мақолаи мухтасар тавсиф карда шудааст "Синтез ҳамчун яке аз усулҳои баланд бардоштани самаранокии PostgreSQL".

Эҳтимол аз нав сохтани занҷири рӯйдодҳои қаблӣ ҷолиб хоҳад буд.
Таърих таърихи дақиқи оғозро захира кард - 2018-09-10 18:02:48.
Инчунин, дар достон дархосте ҳаст, ки ҳамааш аз он оғоз шудааст:
Дархости мушкилотSELECT
саҳ.“PARAMETER_ID” ҳамчун parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS рақами муштарӣ,
в. "LRM" AS LRM,
в. "ЛОТИД" AS лотид,
w.“RTD_VALUE” AS RTD_value,
w."LOWER_STEC_LIMIT" AS лимити поёнии
w."БОҲИДИ_МАХСУС" AS лимити_боли,
саҳ."TYPE_CALCUL" AS type_calcul,
с."SPENT_NAME" AS_name_name,
s."SPENT_DATE" ҲАМчун санаи сарфшуда,
иқтибос (сол аз "SPENT_DATE") AS сол,
иқтибос (моҳ аз "SPENT_DATE") ҳамчун моҳ,
с."REPORT_NAME" AS гузориш_ном,
саҳ."STPM_NAME" AS stpm_name,
саҳ."МУШТОРПАРАМ_НОМ" ҲАМЧУНИН Параметри_номи муштарӣ
АЗ wdata w,
сарф с,
pmtr p,
harcanan_pd sp,
пд пд
КУҶО с.“SPENT_ID” = в.“SPENT_ID”
ВА саҳ."PARAMETER_ID" = w."PARAMETER_ID"
ВА с.“SPENT_ID” = сп.“SPENT_ID”
ВА pd."PD_ID" = sp."PD_ID"
ВА с.“САНАИ ИСТИФОДА” >= '2018-07-01' ВА с.“САНАИ ХАРФОН” <= '2018-09-30'
ва с."SPENT_DATE" = (интихоби MAX(s2."SPENT_DATE")
АЗ s2 сарфшуда,
wdata w2
КУҶО s2.“SPENT_ID” = w2.“SPENT_ID”
ВА w2.“LRM” = w.“LRM”);

Тавсифи мушкилот пешгӯӣ стандарт аст - «Ҳама чиз бад аст. Ба ман бигӯед, ки мушкил дар чист».
Дарҳол як латифае аз замони ронандагони 3 ва ним дюйм ба ёдам омад:

Ламер ба назди хакер меояд.
-Ҳеҷ коре ба ман намерасад, бигӯед, ки мушкил дар куҷост.
-Дар ДНК...

Аммо, албатта, ин роҳи ҳалли ҳодисаҳои иҷроиш нест. "Шояд онҳо моро нафаҳманд" (Бо). Мо бояд онро муайян кунем.
Хуб, биёед кобед. Шояд дар натиҷа чизе ҷамъ шавад.

Тафтишот оғоз шуд

Пас, он чизеро, ки дарҳол бо чашми бараҳна дидан мумкин аст, ҳатто бидуни муроҷиат ба ШАХСОН.
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/. Аммо дар сайт ягон чизи ҷолиб ва муфид нишон дода нашудааст. Дар назари аввал ва дуюм чизе нест, ки воқеан кӯмак карда метавонад. Оё имкон дорад, ки пурра сканкунӣ ҳадди аққал бошад. Ба пеш.

Гипотезаи 2-Таъсир ба пойгоҳ аз ҷониби автовакуум, шумо бояд аз тормозҳо халос шавед.

Аммо демонатхои автовакуумй нагз рафтор мекунанд, процессхои дуру дароз овехта намешаванд. Бори ҷиддӣ нест. Мо бояд чизи дигареро ҷустуҷӯ кунем.

Гипотезаи 3 - Омор кӯҳна шудааст, ҳама чизро аз нав ҳисоб кардан лозим аст

Боз, на ин. Статистика то ба имрӯз аст. Ки бо назардошти набудани мушкилот бо autovacuum, тааҷҷубовар нест.

Биёед ба оптимизатсия шурӯъ кунем

Ҷадвали асосии 'wdata' албатта хурд нест, қариб 3 миллион сабт.
Ва маҳз ҳамин ҷадвал аст, ки Full Scan пайравӣ мекунад.

Ҳаш Cond: ((в."SPENT_ID" = с."SPENT_ID") ВА ((Зерплани 1) = с."SPENT_DATE"))
-> Seq Scan дар бораи 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

Дархости аз нав навишташуда ҳоло чунин менамояд (хуб, ҳадди аққал зеботар):
Бо истифода аз JOIN дархост кунедSELECT
саҳ.“PARAMETER_ID” ҳамчун parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS рақами муштарӣ,
в. "LRM" AS LRM,
в. "ЛОТИД" AS лотид,
w.“RTD_VALUE” AS RTD_value,
w."LOWER_STEC_LIMIT" AS лимити поёнии
w."БОҲИДИ_МАХСУС" AS лимити_боли,
саҳ."TYPE_CALCUL" AS type_calcul,
с."SPENT_NAME" AS_name_name,
s."SPENT_DATE" ҲАМчун санаи сарфшуда,
иқтибос (сол аз "SPENT_DATE") AS сол,
иқтибос (моҳ аз "SPENT_DATE") ҳамчун моҳ,
с."REPORT_NAME" AS гузориш_ном,
саҳ."STPM_NAME" AS stpm_name,
саҳ."МУШТОРПАРАМ_НОМ" ҲАМЧУНИН Параметри_номи муштарӣ
АЗ wdata w INNER JOIN сарфшуда s ON w.“SPENT_ID”=s.”“SPENT_ID”
ИННЕР JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
ҲАМРОҲИ ДОХИЛИИ SPENT_pd ON s.“SPENT_ID” = сп.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
НОҲИЯИ ПАНҶ
с.“САНАИ ХАРФОН” >= '2018-07-01' ВА с.“САНАИ ХАРФОНА” <= '2018-09-30'ва
с."SPENT_DATE" = (интихоби MAX(s2."SPENT_DATE")
АЗ wdata w2 INNER JOIN сарф s2 ON w2.“SPENT_ID”=s2.“SPENT_ID”
WDAta W
ДАР БОРАИ w2.“LRM” = w.“LRM” );
Вақти банақшагирӣ: 2.486 ms
Вақти иҷро: 1223680.326 ms

Ҳамин тавр, натиҷаи аввал.
буд: 6 мс (қариб 985 соат).
шуд: 1 223 680.326 мс (ҳамагӣ бештар аз 20 дақиқа).
Натиҷаи хуб. Дар асл, мо боз дар он ҷо истода метавонистем. Аммо ин хеле ҷолиб аст, шумо наметавонед бас кунед.
БАРОИ

Қадами дуюм - аз зерпурсишҳои алоқаманд халос шавед

Матни дархости ивазшуда:
Бе зерпурсишҳои алоқамандSELECT
саҳ.“PARAMETER_ID” ҳамчун parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS рақами муштарӣ,
в. "LRM" AS LRM,
в. "ЛОТИД" AS лотид,
w.“RTD_VALUE” AS RTD_value,
w."LOWER_STEC_LIMIT" AS лимити поёнии
w."БОҲИДИ_МАХСУС" AS лимити_боли,
саҳ."TYPE_CALCUL" AS type_calcul,
с."SPENT_NAME" AS_name_name,
s."SPENT_DATE" ҲАМчун санаи сарфшуда,
иқтибос (сол аз "SPENT_DATE") AS сол,
иқтибос (моҳ аз "SPENT_DATE") ҳамчун моҳ,
с."REPORT_NAME" AS гузориш_ном,
саҳ."STPM_NAME" AS stpm_name,
саҳ."МУШТОРПАРАМ_НОМ" ҲАМЧУНИН Параметри_номи муштарӣ
АЗ wdata w INNER JOIN s ON s сарф шудааст.“SPENT_ID” = w.“SPENT_ID”
ИННЕР JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
ҲАМРОҲИ ДОХИЛИИ SPENT_pd ON s.“SPENT_ID” = сп.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
ҲАМРОҲИИ ДОХИЛӢ (Интихоб w2.“LRM”, MAX(s2.“STENT_DATE”)
АЗ s2 сарфшуда INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
ГУРУХ АЗ РУИ w2.“LRM”
) md оид ба w.“LRM” = md.“LRM”
НОҲИЯИ ПАНҶ
с."SPENT_DATE" >= '2018-07-01' ВА с."САНАИ SPENT_STA" <= '2018-09-30';
Вақти банақшагирӣ: 2.291 ms
Вақти иҷро: 165021.870 ms

буд: 1 223 680.326 мс (ҳамагӣ бештар аз 20 дақиқа).
шуд: 165 021.870 мс (ҳамагӣ бештар аз 2 дақиқа).
Ин аллакай хеле хуб аст.
Аммо, чунон ки бритониёҳо мегӯянд, "Аммо, ҳамеша як аммо вуҷуд дорад" Натиҷаи хеле хуб бояд ба таври худкор шубҳаро ба вуҷуд орад. Чизе ин ҷо хатост.

Гипотеза дар бораи ислоҳи дархост барои халос шудан аз зерпурсишҳои алоқаманд дуруст аст. Аммо шумо бояд онро каме тағир диҳед, то натиҷаи ниҳоӣ дуруст бошад.
Дар натиҷа, натиҷаи аввалини мобайнӣ:
Дархости таҳриршуда бидуни зерпурсити мутақобилаSELECT
саҳ.“PARAMETER_ID” ҳамчун parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS рақами муштарӣ,
в. "LRM" AS LRM,
в. "ЛОТИД" AS лотид,
w.“RTD_VALUE” AS RTD_value,
w."LOWER_STEC_LIMIT" AS лимити поёнии
w."БОҲИДИ_МАХСУС" AS лимити_боли,
саҳ."TYPE_CALCUL" AS type_calcul,
с."SPENT_NAME" AS_name_name,
s."SPENT_DATE" ҲАМчун санаи сарфшуда,
иқтибос(сол аз с.“SPENT_DATE”) AS сол,
иқтибос (моҳ аз с.“SPENT_DATE”) ҳамчун моҳ,
с."REPORT_NAME" AS гузориш_ном,
саҳ."STPM_NAME" AS stpm_name,
саҳ."МУШТОРПАРАМ_НОМ" ҲАМЧУНИН Параметри_номи муштарӣ
АЗ wdata w INNER JOIN s ON s сарф шудааст.“SPENT_ID” = w.“SPENT_ID”
ИННЕР JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
ҲАМРОҲИ ДОХИЛИИ SPENT_pd ON s.“SPENT_ID” = сп.“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” = с.“SPENT_DATE” ВА md.“LRM” = w.“LRM”
НОҲИЯИ ПАНҶ
с."SPENT_DATE" >= '2018-07-01' ВА с."САНАИ SPENT_STA" <= '2018-09-30';
Вақти банақшагирӣ: 3.192 ms
Вақти иҷро: 208014.134 ms

Ҳамин тавр, он чизе ки мо ба даст меорем, аввалин натиҷаи қобили қабул аст, ки ба муштарӣ нишон додан шарм нест:
Бо оғоз: 8 222 351.640 мс (зиёда аз 2 соат)
Мо тавонистем ба даст орем: 1 мс (каме бештар аз 223 дақиқа).
Натиҷа (мавқеъ): 208 014.134 мс (ҳамагӣ бештар аз 3 дақиқа).

Натиҷаи аъло.

Натиҷа

Мо метавонистем дар он ҷо таваққуф кунем.
АММО ...
Иштаҳо бо хӯрок меояд. Касе, ки роҳ меравад, роҳро азхуд мекунад. Ҳар як натиҷа миёна аст. Истод ва мурд. Ва гайра.
Биёед оптимизатсияро идома диҳем.
Идеяи олӣ. Хусусан бо назардошти он, ки муштарӣ ҳатто зид нест. Ва ҳатто барои он сахт.

Пас, вақти азнавсозии пойгоҳи додаҳо расидааст. Худи сохтори пурсиш дигар наметавонад оптимизатсия карда шавад (гарчанде ки баъдтар маълум шуд, имкони кафолат додани он, ки ҳама чиз воқеан ноком мешавад). Аммо оғоз кардани оптимизатсия ва таҳияи тарҳи пойгоҳи додаҳо аллакай як идеяи хеле умедбахш аст. Ва муҳимтар аз ҳама ҷолиб. Боз ба ёд оред ҷавонии худ. Ман фавран DBA нашудам, ман ҳамчун барномасоз калон шудам (BASIC, assembler, C, double-plus C, Oracle, plsql). Мавзӯи ҷолиб, албатта, барои як ёддошти алоҳида ;-).
Бо вуҷуди ин, биёед парешон нашавем.

Ва ҳамин тавр,

Ё шояд тақсимкунӣ ба мо кӯмак кунад?
Спойлер - "Бале, ин кӯмак кард, аз ҷумла дар оптимизатсияи кор."

Аммо ин як ҳикояи тамоман дигар аст...

Давом дорад…

Манбаъ: will.com