Ты памятаеш, як усё пачыналася. Усё было ўпершыню і зноў

Аб тым, як прыйшлося заняцца аптымізацыяй запыту PostgreSQL і што з усяго гэтага атрымалася.
Чаму прыйшлося? Ды таму, што папярэднія 4 гады ўсё працавала ціха, спакойна, як гадзінічкі цікалі.
У якасці эпіграфа.

Заснавана на рэальных падзеях.
Усе імёны зменены, супадзенні выпадковыя.

Пры дасягненні некаторага выніку заўсёды цікава ўспомніць, што-ж паслужыла штуршком да пачатку, з чаго ўсё пачалося.

Такім чынам, што ў выніку атрымалася, коратка апісана ў артыкуле «Сінтэз як адзін з метадаў паляпшэння прадукцыйнасці PostgreSQL.

Мусіць, забаўна будзе ўзнавіць ланцужок папярэдніх падзей.
Гісторыя захавала дакладную дату пачатку - 2018-09-10 18:02:48.
Таксама ў гісторыі ёсць запыт, з якога ўсё пачалося:
Праблемны запытВЫБАР
p."PARAMETER_ID" as 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 lower_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,
extract(year from "SPENT_DATE") AS year,
extract(month ад "SPENT_DATE") як месяц,
s.«REPORT_NAME» AS report_name,
p.«STPM_NAME» AS stpm_name,
p.«CUSTOMERPARAM_NAME» AS customerparam_name
FROM wdata w,
spent s,
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'
and s."SPENT_DATE" = (SELECT MAX(s2."SPENT_DATE")
FROM spent s2,
wdata w2
WHERE s2.SPENT_ID = w2.SPENT_ID
AND w2.«LRM» = w.«LRM»);

Апісанне праблемы, прадказальна стандартна – “Усё дрэнна. Падкажыце ў чым праблема”.
Адразу ж прыгадаўся анекдот часоў дыскаводаў на 3 з паловай цалі:

Прыходзіць ламер да хакера.
-У мяне нічога не працуе, падкажы, дзе праблема.
-У ДНК…

Але так рашаць інцыдэнты прадукцыйнасці, вядома, нельга. “Нас могуць не зразумець” (с). Трэба разбірацца.
Што ж, будзем капаць. Можа, што і назапасіцца ў выніку.

Investigation started

Такім чынам, што відаць адразу няўзброеным поглядам, нават не звяртаючыся да дапамогі EXPLAIN.
1) Ці не выкарыстоўваюцца JOIN. Гэта дрэнна, асабліва калі колькасць злучэнняў большая за адну.
2) Але што яшчэ горш - карэлявалі подзапросов, да таго ж, з агрэгацыяй. Гэта вельмі дрэнна.
Гэта дрэнна вядома. Але гэта толькі, з аднаго боку. З іншага боку, гэта вельмі добра, таму што задача адназначна мае рашэнне і запыт можна палепшыць.
Да варажбіткі не хадзі(З).
План запыту не такі ўжо складаны, аднак паказальны:
План выканання

Самае цікавае і карыснае, як звычайна, у пачатку і канцы.
Nested Loop (cost=935.84..479763226.18 rows=3322 width=135) (actual time=31.536..8220420.295 rows=8111656 loops=1)
Planning time: 3.807 ms
Execution time: 8222351.640 ms
Час выканання больш за 2-х гадзін.

Ілжывыя гіпотэзы, якія занялі час

Гіпотэза 1- Аптымізатар памыляецца, будуе няправільны план.

Для візуалізацыі плана выканання скарыстаемся сайтам https://explain.depesz.com/. Зрэшты, нічога цікавага ці карыснага сайт не паказаў. На першы і другі погляд - нічога, што магло б рэальна дапамагчы. Хіба, што Full Scan мінімальны. Ідзем, далей.

Гіпотэза 2-Імпакт на базу з боку autovacuum, трэба пазбавіцца ад тармазоў.

Але, дэманы autovacuum паводзяць сябе добра, доўга віслых працэсаў няма. Калі-небудзь сур'ёзнай нагрузкі - не. Трэба шукаць нешта яшчэ.

Гіпотэза 3-Статыстыка састарэлая, трэба пералічыць усё залятае

Ізноў, не тое. Статыстыка актуальная. Што, улічваючы адсутнасць праблем з autovacuum, нядзіўна.

Пачынаем аптымізаваць

Галоўная табліца 'wdata' вядома ж не маленькая, амаль 3 мільёны запісаў.
І менавіта па гэтай табліцы ідзе Full Scan.

Hash Cond: ((w.SPENT_ID = s.SPENT_ID) AND ((SubPlan 1) = s.SPENT_DATE)))
-> Seq Scan on wdata w (cost=0.00..574151.49 rows=26886249 width=46) (actual time=0.005..8153.565 rows=26873950 loops=1)
Паступаем стандартна: а давай, зробім азначнік і ўсё залятае .
Зрабілі індэкс па полі "SPENT_ID"
У выніку:
План выканання запыту з выкарыстаннем індэкса

Ну што, памагло?
было: 8 222 351.640 ms (крыху больш за 2 гадзіны)
стала: 6 985 431.575 ms (амаль 2 гадзіны)
Увогуле, тыя ж яблыкі, выгляд збоку.
Успамінаем класіку:
«А ў вас ёсць такі ж, але без крылаў? Будзем шукаць».

У прынцыпе, гэта можна было б назваць добрым вынікам, ну ня добрым, але прымальным. Прынамсі, даць вялікую справаздачу заказчыку з апісаннем таго, колькі шмат усяго было зроблена і чаму тое, што зроблена тое і добра.
Але ўсё ж да канчатковага рашэння яшчэ далёка. Вельмі далёка.

А вось зараз самае цікавае - працягваем аптымізаваць, будзем паліраваць запыт

Крок першы - выкарыстоўваць JOIN

Перапісаны запыт, зараз выглядае так (ну як мінімум прыгажэй):
Запыт з выкарыстаннем JOINВЫБАР
p."PARAMETER_ID" as 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 lower_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,
extract(year from "SPENT_DATE") AS year,
extract(month ад "SPENT_DATE") як месяц,
s.«REPORT_NAME» AS report_name,
p.«STPM_NAME» AS stpm_name,
p.«CUSTOMERPARAM_NAME» AS customerparam_name
FROM wdata w INNER JOIN spent s ON w.«SPENT_ID»=s.«SPENT_ID»
INNER JOIN pmtr p ON p.PARAMETER_ID = w.PARAMETER_ID
INNER JOIN 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' AND s.«SPENT_DATE» <= '2018-09-30'AND
s.SPENT_DATE = (SELECT MAX(s2.SPENT_DATE))
FROM wdata w2 INNER JOIN spent s2 ON w2. "SPENT_ID" = s2. "SPENT_ID"
INNER JOIN wdata w
ON w2.«LRM» = w.«LRM» );
Planning time: 2.486 ms
Execution time: 1223680.326 ms

Такім чынам, першы вынік.
было: 6 985 431.575 ms (амаль 2 гадзіны).
стала: 1 223 680.326 ms (крыху больш за 20 хвілін).
Добры вынік. У прынцыпе, зноў, можна было б на гэтым і спыніцца. Але так нецікава, нельга спыняцца.
Бо

Крок другой - пазбавіцца ад карэляванага подзапросов

Зменены тэкст запыту:
Без карэляванага подзапросовВЫБАР
p."PARAMETER_ID" as 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 lower_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,
extract(year from "SPENT_DATE") AS year,
extract(month ад "SPENT_DATE") як месяц,
s.«REPORT_NAME» AS report_name,
p.«STPM_NAME» AS stpm_name,
p.«CUSTOMERPARAM_NAME» AS customerparam_name
FROM wdata w INNER JOIN spent ON S. "SPENT_ID" = w. "SPENT_ID"
INNER JOIN pmtr p ON p.PARAMETER_ID = w.PARAMETER_ID
INNER JOIN spent_pd sp ON s.SPENT_ID = sp.SPENT_ID
INNER JOIN pd pd ON pd. "PD_ID" = sp. "PD_ID"
INNER JOIN (SELECT w2. "LRM", MAX (s2. "SPENT_DATE")
FROM spent s2 INNER JOIN wdata w2 ON s2. "SPENT_ID" = w2. "SPENT_ID"
GROUP BY w2. "LRM"
) md on w."LRM" = md."LRM"
ДЗЕ
s.«SPENT_DATE» >= '2018-07-01' AND s.«SPENT_DATE» <= '2018-09-30';
Planning time: 2.291 ms
Execution time: 165021.870 ms

было: 1 223 680.326 ms (крыху больш за 20 хвілін).
стала: 165 021.870 ms (крыху больш за 2 хвіліны).
Вось гэта ўжо зусім добра.
Аднак, як кажуць ангельцыBut, there is always a but». Занадта добры вынік павінен аўтаматычна выклікаць падазрэнне. Нешта тут не так.

Гіпотэза аб выпраўленні запыту з мэтай збавення ад карэляванага подзапросов - правільная. Але трэба крыху дапрацаваць, каб выніковы вынік быў правільны.
У выніку, першы прамежкавы вынік:
Адрэдагаваны запыт без карэляванага подзапросовВЫБАР
p."PARAMETER_ID" as 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 lower_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,
extract(year from s.SPENT_DATE)) AS year,
extract(month from s.SPENT_DATE)) as month,
s.«REPORT_NAME» AS report_name,
p.«STPM_NAME» AS stpm_name,
p.«CUSTOMERPARAM_NAME» AS customerparam_name
FROM wdata w INNER JOIN spent ON S. "SPENT_ID" = w. "SPENT_ID"
INNER JOIN pmtr p ON p.PARAMETER_ID = w.PARAMETER_ID
INNER JOIN spent_pd sp ON s.SPENT_ID = sp.SPENT_ID
INNER JOIN pd pd ON pd. "PD_ID" = sp. "PD_ID"
INNER JOIN ( SELECT w2. "LRM", MAX (s2. "SPENT_DATE") AS "SPENT_DATE"
FROM spent s2 INNER JOIN wdata w2 ON s2. "SPENT_ID" = w2. "SPENT_ID"
GROUP BY w2. "LRM"
) md ON md.SPENT_DATE = s.SPENT_DATE AND md.LRM = w.LRM
ДЗЕ
s.«SPENT_DATE» >= '2018-07-01' AND s.«SPENT_DATE» <= '2018-09-30';
Planning time: 3.192 ms
Execution time: 208014.134 ms

Такім чынам, што маем у выніку - першы прымальны вынік, які не сорамна паказаць заказчыку:
Пачалося з: 8 222 351.640 ms (больш за 2 гадзіны)
Атрымалася дамагчыся: 1 223 ms (крыху больш за 680.326 хвілін).
Вынік(прамежкавы): 208 014.134 ms (крыху больш за 3 хвіліны).

Выдатны вынік.

Вынік

На гэтым можна было б і спыніцца.
АЛЕ…
Апетыт прыходзіць падчас ежы. Дарогу здужае, ідучы. Любы вынік-прамежкавы. Спыніўся-памёр. І г.д і да т.п.
А давайце, працягнем аптымізацыю.
Выдатная ідэя. Асабліва, улічваючы тое, што замоўца быў вельмі нават не супраць. А нават моцна - за.

Такім чынам, надышоў час для змены дызайну базы дадзеных. Саму структуру запыту ўжо не аптымізаваць (хоць, як потым высветлілася, ёсць варыянт для таго, каб усё рэальна залятала). Але вось заняцца аптымізацыяй і развіццём дызайну базы даных, гэта ўжо вельмі перспектыўная ідэя. І галоўнае цікавая. Зноў-такі, маладосць успомніць. Я бо ні адразу стаў DBA, з праграмістаў вырас (бейсік, асэмблер, сі, сі двойчы плюсануты, аракл, plsql). Цікавая вядома тэма, для асобных мемуараў ;-).
Зрэшты, не будзем адцягвацца.

Такім чынам,

А можа, секцыянаванне нам дапаможа?
Спойлер – «Ды дапамагло, і ў аптымізацыі хуткадзейнасці, у тым ліку.»

Але гэта ўжо зусім іншая гісторыя…

Працяг будзе…

Крыніца: habr.com