Ты помнишь, как все начиналось. Все было впервые и вновь

[rinace_sysadmin]

О том, как пришлось заняться оптимизацией запроса PostgreSQL и что из всего этого получилось.
Почему пришлось? Да потому, что предыдущие 4 года все работало тихо, спокойно, как часики тикали.

В качестве эпиграфа.


Основано на реальных событиях.
Все имена изменены, совпадения случайны.

При достижении некоторого результата всегда интересно вспомнить, что-же послужило толчком к началу, с чего все началось.


Итак, что в результате получилось, кратко описано в статье « Синтез как один из методов улучшения производительности PostgreSQL».

Наверное, занятно будет воссоздать цепочку предшествующих событий.

История сохранила точную дату начала - 2018-09-10 18:02:48.

Также в истории есть запрос, с которого всё началось:
[Проблемный запрос]
SELECT
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)
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]
SELECT
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»
WHERE
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 минут).

Хороший результат. В принципе, опять, можно было бы на этом и остановиться. Но так неинтересно, нельзя останавливаться.

ИБО


Шаг второй - избавиться от коррелированного подзапроса

Измененный текст запроса:[Измененный текст запроса]
SELECT
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.«LRM»
) md on w.«LRM» = md.«LRM»
WHERE
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». Слишком хороший результат, должен автоматически вызвать подозрение. Что-то тут не так.

Гипотеза о исправлении запроса с целью избавления от коррелированного подзапроса - правильная. Но нужно чуть-чуть доработать, чтобы итоговый результат был верным.

В итоге, первый промежуточный результат:[Исправленный запрос без коррелированного подзапроса]
SELECT
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.«LRM»
) md ON md.«SPENT_DATE» = s.«SPENT_DATE» AND md.«LRM» = w.«LRM»
WHERE
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 680.326 ms (чуть больше 20 минут).
Итог(промежуточный): 208 014.134 ms (чуть больше 3-х минут).

Отличный результат.


Итог

На этом можно было бы и остановиться.

НО…

Аппетит приходит во время еды. Дорогу осилит, идущий. Любой результат- промежуточный. Остановился-умер. И т.д и т.п.

А давайте, продолжим оптимизацию.

Отличная идея. Особенно, учитывая то, что заказчик был очень даже не против. А даже сильно - за.

Итак, пришло время для изменения дизайна базы данных. Саму структуру запроса уже не оптимизировать (хотя, как потом выяснилось, есть вариант для того, чтобы все реально залетало). Но вот заняться оптимизацией и развитием дизайна базы данных, это уже очень перспективная идея. И главное интересная. Опять-таки, молодость вспомнить. Я ведь ни сразу стал DBA, из программистов вырос (бейсик, ассемблер, си, си дважды плюсанутый, оракл,plsql). Интересная конечно тема, для отдельных мемуаров ;-).

Впрочем, не будем отвлекаться.

Итак,


А может быть секционирование нам поможет?

Спойлер - «Да помогло, и в оптимизации быстродействия, в том числе.»

Но это уже совсем другая история…

Продолжение следует…

#postgresql #performance #optimization