Ти пам'ятаєш, як усе починалося. Все було вперше та знову

Про те, як довелося зайнятися оптимізацією запиту 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 from «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,
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)
Планування часу: 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 мс (трохи більше 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 from «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 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");
Планування часу: 2.486 ms
Execution time: 1223680.326 ms

Отже, перший результат.
було: 6 ms (майже 985 години).
стало: 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 from «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 s 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.
) md on w. "LRM" = md. "LRM"
ДЕ
s.«SPENT_DATE» >= '2018-07-01' AND s.«SPENT_DATE» <= '2018-09-30';
Планування часу: 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 s 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.
) 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';
Планування часу: 3.192 ms
Execution time: 208014.134 ms

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

Відмінний результат.

Підсумок

На цьому можна було б зупинитися.
АЛЕ…
Апетит приходить під час їжі. Дорогу здолає той, хто йде. Будь-який результат-проміжний. Зупинився-помер. І Т. Д. і Т. П.
А давайте, продовжимо оптимізацію.
Чудова ідея. Особливо з огляду на те, що замовник був дуже навіть не проти. А навіть сильно за.

Отже, настав час зміни дизайну бази даних. Саму структуру запиту вже не оптимізувати (хоча, як потім з'ясувалося, є варіант для того, щоб усе реально залітали). Але зайнятися оптимізацією та розвитком дизайну бази даних, це вже дуже перспективна ідея. І головне — цікава. Знову ж таки, молодість згадати. Я не відразу став DBA, з програмістів виріс (бейсик, асемблер, сі, сі двічі плюсанутий, оракл, plsql). Цікава звичайно тема для окремих мемуарів ;-).
Втім, не відволікатимемося.

Отже,

А може, секціонування нам допоможе?
Спойлер - "Так допомогло, і в оптимізації швидкодії, в тому числі."

Але це вже зовсім інша історія.

Далі буде…

Джерело: habr.com