Как пишут SQL-запросы гуманитарии
— И тогда самец забирается на самое высокое дерево и оттуда планирует.
— Простите, что планирует?
@ к/ф «День радио»
Когда говорят «мы ускорили выполнение нашего запроса в N раз» это значит, что сначала сделали плохо, а потом начали думать как улучшить.
Так я думал раньше.
Мне досталась вполне распространенная прикладная задача — найти в базе по ИНН или названию нужные компании и из нескольких таблиц собрать по ним статистику — количество
Структура данных в базе
Без индексов конечно никуда, поэтому для всех полей таблиц, содержащих ИНН создал соответствующие индексы:
для полей типа varchar — BTREE-индексы,
для полей jsonb[] -GIN-индексы
В качестве точки отсчета написал такой запрос
explain (FORMAT JSON, ANALYZE)
select
inn, ogrn, name,
count(distinct email) as email_count,
count(distinct phone) as phone_count,
count(debtor_inn) as proverka_count,
count(company_inn) as vacancy_count
from company
left join email on jsonb_build_object('inn', inn) <@ any (email.company)
left join phone on jsonb_build_object('inn', inn) <@ any (phone.company)
left join fssp on debtor_inn = inn
left join vacancy on company_inn = inn
where
inn = '7708503727'
group by (inn, ogrn, name)
Результаты плана
время выполнения 5555ms
GIN-индексы для jsonb[] полей при планировании запроса не используются
85% времени выполнения запроса занимает соединение данных вложенным циклом (Nested Loop Left Join). количество обрабатываемых строк в цикле — 239 тыс.
Прежде всего нужно подключить в поиске GIN-индекс.
Одна из конструкций с которой работает GIN — (jsonb @@ jsonpath). Поэтому применил такой лайфхак — создал новое поле jsonb и перенес в него значения jsonb-массива, обернув его в jsonb.
{
'companies': jsonb[]
}
и создал GIN-индексы на новое поле
CREATE INDEX IF NOT EXISTS company2_idx
ON tablename USING gin
(company2 jsonb_path_ops)
WHERE company2 IS NOT NULL;
Далее переписал условия поиска ИНН используя конструкцию (jsonb @@ jsonpath)
...
left join view.phone on phone.company2 @@ ('exists($.companies[*].inn ? (@=="' || '7708503727' || '"))')::jsonpath
left join view.email on email.company2 @@ ('exists($.companies[*].inn ? (@=="' || '7708503727' || '"))')::jsonpath
...
После этого планировщик подключил созданный индекс
В результате
время запроса сократилось в 5 раз — 960ms
планировщик начал использовать GIN-индексы
количество строк во вложенном цикле сократилось в 2 раза (- до 123 тыс), но по прежнему более 80% времени выполнения запроса приходилось на соединение строк во вложенном цикле
Пришло время оптимизации запроса. Гибкость языка SQL позволяет написать его как минимум тремя разными способами (именно столько получилось у меня). Самым удачным вариантом с точки зрения времени выполнения стал такой
explain (FORMAT JSON, ANALYZE)
select
b.*,
count(company_inn) as vacancy_count
from
(select
a.*,
count(debtor_inn) as fssp_count
from
(select
inn, ogrn, name,
count(distinct email) as email_count,
count(distinct phone) as phone_count
from company
left join phone on phone.company2 @@ ('exists($.companies[*].inn ? (@=="' || '7708503727' || '"))')::jsonpath
left join email on email.company2 @@ ('exists($.companies[*].inn ? (@=="' || '7708503727' || '"))')::jsonpath
where inn = '7708503727'
group by (inn, ogrn, name)) as a
left join fssp on debtor_inn = inn
group by (inn, full_name, email_count, phone_count)) as b
left join vacancy on company_inn = inn
group by (inn, full_name, email_count, phone_count, fssp_count)
Результаты планировщика:
время запроса — 176ms
95% времени выполнения приходится на операцию сортировки данных перед последней группировкой.
Вроде и не плохой результат — получилось ускорить запрос еще более чем 5 раз. Смущала длительная сортировка. Большое количество строк не позволяло выполнить быструю сортировку в памяти, данные сохранялись на диск, отсюда и такой результат. Победить эту проблему путем перестройки структуры запроса у меня не получилось. Увеличение параметра work_mem и отключение enable_sort дало 3-х кратный прирост производительности, но я посчитал это «нечистым» подходом. Поэтому полез в документацию и «придумал» таки 4-й вариант запроса.
explain (FORMAT JSON, ANALYZE)
select
b.*, vacancy_count, fssp_count
from
(select
inn, ogrn, name,
count(distinct email) as email_count,
count(distinct phone) as phone_count
from company
left join phone on phone.company2 @@ ('exists($.companies[*].inn ? (@=="' || '616106158873' || '"))')::jsonpath
left join email on email.company2 @@ ('exists($.companies[*].inn ? (@=="' || '616106158873' || '"))')::jsonpath
where inn = '616106158873'
group by (inn, ogrn, name)
) as b
left join (
select debtor_inn, count(debtor_inn) as fssp_count
from fssp where debtor_inn = '616106158873' group by debtor_inn
) as c on debtor_inn = inn
left join (
select company_inn, count(company_inn) as vacancy_count
from vacancy where company_inn = '616106158873' group by company_inn
) as a on company_inn = inn
Результат планировщика:
Теперь обернем запрос в sql-функцию и проверим скорость выполнения.
select * from company_stat('7708503727')
Время выполнения запроса — 1.7ms
Вторая функция для поиска по названию компании проектировалась аналогично. Нужно было только добавить дополнительный запрос на поиск ИНН по названию компании, конечно учитывая ее не уникальность.
Для этой цели конечно использовал FTS (наверное самый мой любимый инструмент в postgresql):
в таблице company создал дополнительное поле типа ts_vector
создал для поля GIN-индекс Окончательный вариант функции, в которую обернул запрос выглядит так
create or replace function company_stat_by_name(X text)
returns table(inn_ character varying(20), orgn character varying(25), company_name text, email_num bigint, phone_num bigint, fssp_num bigint, vacancy_num bigint)
as $$
DECLARE
r record;
BEGIN
FOR r IN
(select inn from company
where fts_company_name @@ websearch_to_tsquery('config', X))
LOOP
RETURN QUERY select
b.*, vacancy_count, fssp_count
from
(select
inn, ogrn, full_name,
count(distinct email) as email_count,
count(distinct phone) as phone_count
from company
left join phone on phone.company2 @@ ('exists($.companies[*].inn ? (@=="' || r.inn || '"))')::jsonpath
left join email on email.company2 @@ ('exists($.companies[*].inn ? (@=="' || r.inn || '"))')::jsonpath
where inn = r.inn
group by (inn, ogrn, full_name)
) as b
left join (
select debtor_inn, count(debtor_inn) as fssp_count
from fssp where debtor_inn = r.inn group by debtor_inn
) as c on debtor_inn = r.inn
left join (
select company_inn, count(company_inn) as vacancy_count
from vacancy where company_inn = r.inn group by company_inn
) as a on company_inn = r.inn;
END LOOP;
END;
$$ language plpgsql
Поскольку может быть найдено несколько компаний совпадающих по названию, оценил время выполнения для крайних случаев:
3–4 строки в результате выполнения функции — время выполнения 2.4ms
> 200 строк в результате выполнения функции — время выполнения 34ms
Результатом остался доволен.
Мораль — сразу хорошо получается только у гениев. Для всех остальных есть планировщик запросов и документация.
