25.03.2024 08:21
osp2003 27 21000 Источник

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

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

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

Когда говорят "мы ускорили выполнение нашего запроса в 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
    Результатом остался доволен.

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

Комментарии (27)


  1. RedWolf
    25.03.2024 09:35
    #26650313
    +8

    Первый запрос неправильный. Так с one to many не работают - у вас телефоны умножатся на мыло, потом на fssp, а потом на вакансии, и вы потом будете думать, откуда там столько fssp и вакансий?

    Дальнейшие запросы - это не оптимизация первого запроса, они совершенно от него отличаются - вы в них работу с one to many прячете в подзапросы, тем самым не давая данным разъезжаться, при этом второй запрос пердец какой безграмотный. Так же вы так и не избавились от дубликатов при работе с мылом и телефоном, просто вы их стыдливо спрятали под ковер дистинктом.


    1. osp2003 Автор
      25.03.2024 09:35
      #26650413
      +1

      Да спасибо. С точки зрения вычислений запросы работают корректно

      Что касается конструкции финального запроса - наверное вы правы. Если подскажете как еще можно избавиться от дубликатов кроме дистинкта будет здорово


      1. Akina
        25.03.2024 09:35
        #26650745
        +3

        Если подскажете как еще можно избавиться от дубликатов кроме дистинкта

        Есть шикарный метод избавления от дубликатов - просто не плодить их.


        1. AgentFire
          25.03.2024 09:35
          #26652189
          +2

          Сразу с козырей ходите :)


          1. Akina
            25.03.2024 09:35
            #26653275

            Не, ну а чё? Вот реально - товарищ сперва создаёт себе проблемы, а потом их мужественно преодолевает. Иначе я никак не могу интерпретировать его горячее желание сначала всё связать, получив очевидный JOIN multiplying, а потом посчитать количество уникальных значений.


      1. Rijen
        25.03.2024 09:35
        #26656939

        Сразу говорю, это вредный совет.

        Предисловие: Любе и ли вы Firebird, такую СУБД, где легитимный индексированный join может просто безосновательно (почти) выполнятся дольше вложенного select?

        Как вариант, избежать лишних сущностей можно агрегацией телефонов и мыл в лист

        Select
*, 
(select list('; ', phone) from phones where.. Group by. )
***

        Или наоборот, сначала сплющить её полностью:

        Company join (select list  ***  From phones group by***)


    1. Ivan22
      25.03.2024 09:35
      #26651957
      +1

      а то что в принципе один емейл тут может принадлежать нескольким компаниям, вас не смущает??? Это в реальности вообще как??? И с телефонами так же. Тут похоже гуманитарий всю модель данных рисовал. SQL запросы это тут уже следствие


      1. Zhuck
        25.03.2024 09:35
        #26653105
        +2

        Некий бух ведет десяток ИПшек, как думаете этот бух будет заводить 10 телефонов и 10 емейлов для связи с поставщиками/клиентами ИПшек или будет один общий? Да и сам чел может иметь ооошку и ипшку(продукты ведем по одной кассе, алкоголь по другой, например). В жизни и не такое случается


      1. Serg7777
        25.03.2024 09:35
        #26653419
        +2

        У одного собственника может быть несколько компаний и при этом нет ничего, что мешало бы сделать им общий e-mail, например, личный e-mail собственника.


        1. Ivan22
          25.03.2024 09:35
          #26654285
          +1

          даже в таком примере, модель отстойная. Нормальный вариант - M:M - 10 строк в таблице Company_Email c 10 id-шками компаний и 10 одинаковыми мейлами.


  1. RedWolf
    25.03.2024 09:35
    #26650437
    +4

    Так же делаете два подзапроса с группировкой по компании - один вернёт почту, другой телефоны. С группировками можно даже inner join-ами обойтись, поскольку записи всегда будут.

    Первый запрос работает некорректно. Если у вас два телефона и три почты у компании, то это даст шесть записей. Даже если будет по одному делопроизводству и вакансии, то в итоге их будет по шесть в count-ах

    Грубо говоря,

    Телефоны

    (c1, t1), (c1, t2)

    Почта

    (c1, m1), (c1, m2), (c1, m3)

    Делопроизводства

    (c1, d1)

    Вакансии

    (c1, v1), (c2, v2)

    Где c1 - некая компания

    Тогда (это не настоящий sql), company join phone join mail join fssp join vacancy where company.inn = 'c1' дам нам аж двенадцать записей. Дистинкт по телефонам и почте нам поможет, а для fssp и вакансий его нет. Он бы помог посчитать уникальные вакансии и fssp компании, но вы сами должны понимать, что вы

    1. наплодили кучу ненужных данных в виде декартова произведения четырех коллекций

    2. Заставили СУБД их обрабатывать


  1. vk6677
    25.03.2024 09:35
    #26650443

    Извините за глупый вопрос (не приходилось работать с БД), не могло ли кэширование ОС повлиять на результаты? Повторное обращение или обращение к другим данным сильно влияет ?


    1. osp2003 Автор
      25.03.2024 09:35
      #26650539

      Насчет кеширования на уровне ОС сказать не могу, не знаю как postgres взаимодействует с ОС. Функция pgsql в который оборачивал запрос судя по документации при первом вызове формирует и кешируеет план запроса. Что касается изменения входных параметров (инн или названия компании) скрость выполнения зависела только от количества и вывода найденных строк. это по моим экспериментам


  1. domix32
    25.03.2024 09:35
    #26650753

    А точно все запросы выдают ровно тоже самое? А то как-то подозрительно выглядит захардкоженные ИНН и прочие.


    1. osp2003 Автор
      25.03.2024 09:35
      #26650781
      +1

      В тексте код запроса с конкретными параметрами для примера. При тестировании скорости запросы оборачивались в функции.

      select * from company_stat_by_inn('0054803450334')

      select * from company_stat_by_name('ржд')


  1. Akina
    25.03.2024 09:35
    #26650767
    +1

    Пожалуйста, дополните фотографию со схемой внешних связей таблиц собственно структурами таблиц (их CREATE TABLE). Вернее, наоборот, структуры (с внешними ключами) - это основа, а картинка - так, дополнение для наглядности. Ну и, конечно, лишние для содержания статьи поля можно опустить, или хотя бы закомментировать.

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


  1. ALexKud
    25.03.2024 09:35
    #26650947
    +3

    Для таких запросов удобно использовать каскадные CTE. Я бы пробовал именно так. Возможно будет чуть медленнее, но зато понятнее, чем мешанина из запросов и подзапросов. Потом понадобится скорректировать и долго будете вникать что же здесь написано.


    1. Akina
      25.03.2024 09:35
      #26653261
      +1

      У товарища Постгресс, да ещё и не факт что самый свежий. Так что CTE запросто могут кэшироваться на диске, что скажется на производительности отнюдь не лучшим образом.

      А вот агрегирующие подзапросы и последующее связывание их с основной таблицей - это, скорее всего, решение. Конечно, условие отбора по INN должно быть как в подзапросах, так и в основном запросе. А если отбор идёт всегда строго по одному ИНН (т.е. выходных записей немного) - так и вообще самым разумным может быть использование агрегирующих коррелированных подзапросов в списке вывода.

      Что же до понятности и отсутствия мешанины - так это всего лишь вопрос вменяемого форматирования и комментирования, по крайней мере в случае одиночного запроса.


      1. ALexKud
        25.03.2024 09:35
        #26655463

        Может быть вы правы, я забыл что у постгресса нет tempdb...


  1. Batalmv
    25.03.2024 09:35
    #26651011
    +3

    Честно говоря, проблемы изначально в структуре. Если вы ходите связывать по полю - зачем его держать внутри JSON? Место под данные стоит намного дешевле, чем все остальное. Часто ключ к успеху - не героическое "преодоление", а оптимизированная структура, даже иногда ценой избыточности данных.

    Как писать запросы - да такое. Можете вообще попробовать писать что-то вроде select inn, (select count(*) from ... where inn = inn), (select count(*) from ... where inn = inn), (select count(*) from ... where inn = inn) from ... where

    Условно, вы можете не пытаться собрать в одном "декартовом произведении" все что вам нужно, чтобы потом вырезать и группиировать из него, а пользоваться подзапросами. Оптимизатор обычно все равно это "раздупляет" и делает как надо.

    Вам надо количество email - так выберите его явно :)

    Да, и еще - можно использовать аналитические запросы вроде count over partition by. Тоже явно выбираете что вам надо


    1. osp2003 Автор
      25.03.2024 09:35
      #26651051

      Насчет структуры - согласен полностью. В полной мере прочувствовал что ее улучшение - отдельная задача. Что касается оконных функций - пробовал такой вариант - скорость выполнения не очень была. Спасибо за отзыв


      1. Kilor
        25.03.2024 09:35
        #26651997
        +1

        Если в этой системе inn является уникальным ключом, то зачем в phone/email хранятся какие-то jsonb-объекты? Простого массива inn'ов разве недостаточно? Тогда они и искались бы эффективнее исходно.


        1. osp2003 Автор
          25.03.2024 09:35
          #26652161

          Не совсем так. Уникальна пара инн+Огрн

          А одна почта может быть привязана к нескольким компаниям отсюда и jsonb


          1. Kilor
            25.03.2024 09:35
            #26652259

            На схеме нарисовано, что ИНН - это PK в company. Где тут про ОГРН?

            Ну, и в первом варианте jsonb-объект с единственным ключом inn ищется в phone/email в массиве... очевидно, состоящем из объектов такой же структуры?


            1. osp2003 Автор
              25.03.2024 09:35
              #26652293

              Да схему нужно было поразвернутей приводить. Писали уже выше. Учту спасибо


  1. kozlov_de
    25.03.2024 09:35
    #26651205
    +2

    У всякой проблемы всегда есть решение — простое, удобное и, конечно, ошибочное. Генри Луис Менкен (100+)


  1. Kilor
    25.03.2024 09:35
    #26651531
    +4

    При сравнении производительности запросов неплохо бы приводить планы, иначе может оказаться, что все "тормоза" первичного варианта вызваны исключительно стартовой незакэшированностью данных (shared read).