Как пишут SQL-запросы гуманитарии

— И тогда самец забирается на самое высокое дерево и оттуда планирует.

— Простите, что планирует?

@ к/ф «День радио»

Когда говорят «мы ускорили выполнение нашего запроса в N раз» это значит, что сначала сделали плохо, а потом начали думать как улучшить.

Так я думал раньше.

Мне досталась вполне распространенная прикладная задача — найти в базе по ИНН или названию нужные компании и из нескольких таблиц собрать по ним статистику — количество

Структура данных в базе

7d87f9fad2a40f5c48237099933af1f0.png

Без индексов конечно никуда, поэтому для всех полей таблиц, содержащих ИНН создал соответствующие индексы:

  • для полей типа 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
    Результатом остался доволен.

Мораль — сразу хорошо получается только у гениев. Для всех остальных есть планировщик запросов и документация.

© Habrahabr.ru