Comments 29
Первый запрос неправильный. Так с one to many не работают - у вас телефоны умножатся на мыло, потом на fssp, а потом на вакансии, и вы потом будете думать, откуда там столько fssp и вакансий?
Дальнейшие запросы - это не оптимизация первого запроса, они совершенно от него отличаются - вы в них работу с one to many прячете в подзапросы, тем самым не давая данным разъезжаться, при этом второй запрос пердец какой безграмотный. Так же вы так и не избавились от дубликатов при работе с мылом и телефоном, просто вы их стыдливо спрятали под ковер дистинктом.
Да спасибо. С точки зрения вычислений запросы работают корректно
Что касается конструкции финального запроса - наверное вы правы. Если подскажете как еще можно избавиться от дубликатов кроме дистинкта будет здорово
Если подскажете как еще можно избавиться от дубликатов кроме дистинкта
Есть шикарный метод избавления от дубликатов - просто не плодить их.
Сразу говорю, это вредный совет.
Предисловие: Любе и ли вы Firebird, такую СУБД, где легитимный индексированный join может просто безосновательно (почти) выполнятся дольше вложенного select?
Как вариант, избежать лишних сущностей можно агрегацией телефонов и мыл в лист
Select
*,
(select list('; ', phone) from phones where.. Group by. )
***Или наоборот, сначала сплющить её полностью:
Company join (select list *** From phones group by***)
Сделайте группировку до Джойна. Интересно даже какой будет тайминг в первом запросе, но правильном (СТЕ или подзапрос)
а то что в принципе один емейл тут может принадлежать нескольким компаниям, вас не смущает??? Это в реальности вообще как??? И с телефонами так же. Тут похоже гуманитарий всю модель данных рисовал. SQL запросы это тут уже следствие
Некий бух ведет десяток ИПшек, как думаете этот бух будет заводить 10 телефонов и 10 емейлов для связи с поставщиками/клиентами ИПшек или будет один общий? Да и сам чел может иметь ооошку и ипшку(продукты ведем по одной кассе, алкоголь по другой, например). В жизни и не такое случается
У одного собственника может быть несколько компаний и при этом нет ничего, что мешало бы сделать им общий e-mail, например, личный e-mail собственника.
>один емейл тут может принадлежать нескольким компаниям
Например, организация, а при ней военное представительство. Письмо получают на общую "внешнюю" почту, а потом закидывают во внутреннюю.
Так же делаете два подзапроса с группировкой по компании - один вернёт почту, другой телефоны. С группировками можно даже 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 компании, но вы сами должны понимать, что вы
наплодили кучу ненужных данных в виде декартова произведения четырех коллекций
Заставили СУБД их обрабатывать
Извините за глупый вопрос (не приходилось работать с БД), не могло ли кэширование ОС повлиять на результаты? Повторное обращение или обращение к другим данным сильно влияет ?
Насчет кеширования на уровне ОС сказать не могу, не знаю как postgres взаимодействует с ОС. Функция pgsql в который оборачивал запрос судя по документации при первом вызове формирует и кешируеет план запроса. Что касается изменения входных параметров (инн или названия компании) скрость выполнения зависела только от количества и вывода найденных строк. это по моим экспериментам
А точно все запросы выдают ровно тоже самое? А то как-то подозрительно выглядит захардкоженные ИНН и прочие.
Пожалуйста, дополните фотографию со схемой внешних связей таблиц собственно структурами таблиц (их CREATE TABLE). Вернее, наоборот, структуры (с внешними ключами) - это основа, а картинка - так, дополнение для наглядности. Ну и, конечно, лишние для содержания статьи поля можно опустить, или хотя бы закомментировать.
И когда пишете запрос, источником данных которого является более чем одна таблица - обязательно для абсолютно каждого поля исходной таблицы указывайте алиас этой таблицы. Иначе совершенно непонятно, что откуда берётся. Нет, у себя в коде можете творить всё, что угодно - но в статье подобное совершенно недопустимо.
Для таких запросов удобно использовать каскадные CTE. Я бы пробовал именно так. Возможно будет чуть медленнее, но зато понятнее, чем мешанина из запросов и подзапросов. Потом понадобится скорректировать и долго будете вникать что же здесь написано.
У товарища Постгресс, да ещё и не факт что самый свежий. Так что CTE запросто могут кэшироваться на диске, что скажется на производительности отнюдь не лучшим образом.
А вот агрегирующие подзапросы и последующее связывание их с основной таблицей - это, скорее всего, решение. Конечно, условие отбора по INN должно быть как в подзапросах, так и в основном запросе. А если отбор идёт всегда строго по одному ИНН (т.е. выходных записей немного) - так и вообще самым разумным может быть использование агрегирующих коррелированных подзапросов в списке вывода.
Что же до понятности и отсутствия мешанины - так это всего лишь вопрос вменяемого форматирования и комментирования, по крайней мере в случае одиночного запроса.
Честно говоря, проблемы изначально в структуре. Если вы ходите связывать по полю - зачем его держать внутри 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. Тоже явно выбираете что вам надо
Насчет структуры - согласен полностью. В полной мере прочувствовал что ее улучшение - отдельная задача. Что касается оконных функций - пробовал такой вариант - скорость выполнения не очень была. Спасибо за отзыв
Если в этой системе inn является уникальным ключом, то зачем в phone/email хранятся какие-то jsonb-объекты? Простого массива inn'ов разве недостаточно? Тогда они и искались бы эффективнее исходно.
У всякой проблемы всегда есть решение — простое, удобное и, конечно, ошибочное. Генри Луис Менкен (100+)
При сравнении производительности запросов неплохо бы приводить планы, иначе может оказаться, что все "тормоза" первичного варианта вызваны исключительно стартовой незакэшированностью данных (shared read).
Как пишут SQL-запросы гуманитарии