- И тогда самец забирается на самое высокое дерево и оттуда планирует.
- Простите, что планирует?
@ к/ф «День радио»
Когда говорят "мы ускорили выполнение нашего запроса в N раз" это значит, что сначала сделали плохо а потом начал�� думать как улучшить.
Так я думал раньше.
Мне досталась вполне распространенная прикладная задача - найти в базе по ИНН или названию нужные компании и из нескольких таблиц собрать по ним статистику - количество
email
телефонов
размещенных вакансий
назначенных исполнительных производств
Структура данных в базе
Без индексов конечно никуда, поэтому для всех полей таблиц, содержащих ИНН создал соответствующие индексы:
для полей типа 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
Результат планировщика:
время выполнения запроса - 0.13ms Ускорение более 100 раз по отношению к последнему варианту!
Теперь обернем запрос в 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
Результатом остался доволен.
Мораль - сразу хорошо получается только у гениев. Для всех остальных есть планировщик запросов и документация.
