Из анализов здесь нужен простой общий анализ крови, чтобы понять, что подключились бактерии. А после этого в зависимости от клинической картины, опыта врача и стандартов лечения по данному диагнозу назначается антибиотик. А бакпосевы — это все же экстренные случаи не широкого профиля.
Мой опыт PostgreSQL подсказывает никогда не использовать конструкцию NOT IN, это самый медленный вариант из возможных — все жду, когда планировщик PostgreSQL научится с ним нормально работать. Обычно исключение строк реализуется или через EXCEPT, или через LEFT JOIN… WHERE id IS NULL. Но EXCEPT из этих вариантов самый производительный.
А вот теперь суть истории стала понятна и статья обрела смысл) Да, безусловно, использовать внутри рекурсивного запроса конструкцию with для вычисления следующего шага нельзя, это обычно фатально для производительности. Я тоже так стрелял себе в ногу…
Вообще вы подняли хорошую тему — надо бы собрать набор паттернов и антипаттернов в sql и написать большую статью, которая дополнялась бы по мере обсуждения в комментариях. Не хотите попробовать?;)
По факту вся статья сводится к простому правилу — минимизируйте количество строк и столбцов на каждом этапе запроса для увеличения производительности.
По поводу первой части с with… Проблема была не в cte, а в некорректно переписанном запросе, который не использовал условие where product_id = 1234. Ещё бы он не работал медленнее. Зачем вы смущаете юных падованов, связывая замедление с конструкцией with?)
Опять же, аргумент вида
Если айдишних получается, например, в ходе рекурсивного запроса, то в WITH такое условие не утащишь и идея с разделением запроса на куски будет безбожно тормозить
далёк от истины. Ничто не мешает положить результат рекурсивного запроса в виде единственного айдишника в cte, а потом написать
where product_id = (select id from recursive_with)
Касаемо красивой визуализации explain analyze — лично я предпочитаю построение в pgAdmin: толщина связей в отображении плана запроса очень чётко указывают на проблему производительности.
Сколь я помню у PosgresPro есть возможность при создании индекса помещать в его листья дополнительные поля, чтобы не обращаться к таблице. Параметр including, если память не изменяет. А вообще используйте вариант genew с поиском по индексу без обращения к таблице (кстати, вам на самом деле нужен только один составной индекс, а не целых три!). Этот вариант лучше массива залитого gin — в массиве вы теряете согласованность данных на внешних ключах. Кстати, во внешних ключах правильнее использовать on update cascade, чем no action — понятно, что первичные ключи не обновляют, но концептуально… А в запросе вместо distinct можно попробовать group by, он на прежних версиях меньше ошибался.
А вы прочитайте статью, где Николай объясняет причины своего ухода. Он как ведущий конструктор получал 300 долларов в месяц, а после работы клал плитку на стройке и унитазы чинил, чтобы с голоду не сдохнуть. И чудесное руководство, которое ему объясняло, что он никто и его легко заменит студент. По мне так это чистой воды вина руководства «Звезды», что мы потеряли данного специалиста (и кучу других, которые не смогли жить на 200-300 долларов в месяц и завязали с наукой).
Забавы ради попробовал решить, не подглядывая в ваш вариант (PostgreSQL).
Создаем таблицу с данными (данные неупорядоченные)
create table weather(time timestamp, clear boolean);
insert into weather(time, clear)
select generate_series(
'2000-01-01'::timestamp,
'2009-01-01'::timestamp,
random() * 7 * '1 day'::interval
) as time,
random() > 0.5 as clear order by random();
Находим интервалы солнечных дней (начало, конец и длина), упорядочиваем по длине и выбираем 20.
with ordered_weather as (select * from weather order by time),
nasty as (
select row_number() over() as gap_group, time, clear from ordered_weather where not clear
),
gaps as (
select row_number() over(), b.gap_group, a.time, a.clear from ordered_weather a
left join nasty b using (time, clear)
),
limited_gaps as (
select 0 as row_number, 0 as gap_group,
(select min(time) - '1 day'::interval from weather) as time, false as clear
union all
select * from gaps
union all
select (select max(row_number) + 1 from gaps), (select max(gap_group) + 1 from gaps),
null::timestamp, false
)
select c.time as clear_start, d.time as clear_stop, (d.time - c.time) as clear_interval
from limited_gaps a
left join (
select row_number, gap_group + 1 as gap_group from limited_gaps where gap_group is not null
) b using(gap_group)
join limited_gaps c on c.row_number = coalesce(b.row_number,0) + 1
join limited_gaps d on d.row_number = a.row_number - 1
where a.gap_group is not null and (a.row_number - coalesce(b.row_number,0) > 1)
order by (d.time - c.time) desc limit 20;
Я и правда не очень четко выразился. Речь о том, что создавать для каждого пользователя приложения своего пользователя в БД — очень плохая практика. При дальнейшем росте нагрузок (а они растут внезапно и незапланированно) это обернется невозможностью использовать пулы соединений к базе.
Вы получите согласованность на конец выполнения транзакции и (опционально) на любой момент внутри ее. Это позволяет упростить жизнь при вставке данных одной транзакцией в разные таблицы, где из-за внешних ключей нужно иначе было бы учитывать порядок вставки. Плюс это иногда удобно при импорте данных через insert.
Это, скорее, правило хорошего тона. Но теоретически, можно получить проблемы конфликта имен при установке расширений, которые используют схему public для своих функций. Маловероятно, но возможно.
Посмотрел на теги внизу и нашёл родственную душу — я всегда делаю такую же ошибку в написании слова deferrable ;) и только написав вспоминаю, что где-то был капкан.
Проблема в том, что использовать базу для аутентификации пользователей — плохая идея, этим должен заниматься сервер. Как я понял, вы решили схитрить и просто прокидывать пользователя к БД, а она пусть штатными средствами и разбирается кто есть кто и что ему можно. Это неправильная архитектура по причине отсутствия возможности для маштабируемости. Если у вас резко увеличится количество пользователей, база ляжет. И никакие балансировщики нагрузки БД вам не помогут, так как каждый пользователь будет со своим подключением, и их нельзя будет собрать в единый пул.
Вам не нужно поддерживать соответствие между пользователями в БД и на сервере. В простейшем случае в базе из пользователей будут владелец базы и пользователь для пула коннектов, под которым все ходят. У этого пользователя не будет прав на таблицы, только на выполнение функций с security definer. В каждой транзакции сервер должен выставлять
Перед выполнением каждой функции в ней должна быть проверка на то, что имя пользователя, содержащееся в глобальной переменной на уровне данной транзакции, разрешено для запрашиваемой функции. Это делается через
select current_setting('ваша_переменная_пользователя_из_pg_config') into username;
А дальше мы и смотрим, может ли данный username использовать вызванную функцию. Но эта проверка должна быть на уровне некой дополнительной функции, а не через пользователей PostgreSQL.
Базы данных держат сотни подключений, а не тысячи, как web сервера. И каждое простаивающее подключение съедает ресурсы. Именно поэтому никто взаимооднозначно не транслирует клиентов через сервер в базу, а заворачивают кучу пользователей в один пул. Иначе база не потянет. Поэтому штатными средствами acl базы вы сможете раздать права на выполнение функций только учеткам, которые используются в пуле. Не штатными — можно использовать GUC (в случае PostgreSQL), выставляя на сервере в каждой транзакции имя пользователя через set_config. А в функциях вытаскивать из глобальной переменной имя пользователя и делать проверку.
Вообще вы подняли хорошую тему — надо бы собрать набор паттернов и антипаттернов в sql и написать большую статью, которая дополнялась бы по мере обсуждения в комментариях. Не хотите попробовать?;)
По поводу первой части с with… Проблема была не в cte, а в некорректно переписанном запросе, который не использовал условие where product_id = 1234. Ещё бы он не работал медленнее. Зачем вы смущаете юных падованов, связывая замедление с конструкцией with?)
Опять же, аргумент вида
далёк от истины. Ничто не мешает положить результат рекурсивного запроса в виде единственного айдишника в cte, а потом написать
Касаемо красивой визуализации explain analyze — лично я предпочитаю построение в pgAdmin: толщина связей в отображении плана запроса очень чётко указывают на проблему производительности.
Создаем таблицу с данными (данные неупорядоченные)
Находим интервалы солнечных дней (начало, конец и длина), упорядочиваем по длине и выбираем 20.
А можно поподробнее про подводные камни на 100 ГБ? У меня БД на 60 ГБ, хочу узнать, к чему готовиться через пару лет.
Вам не нужно поддерживать соответствие между пользователями в БД и на сервере. В простейшем случае в базе из пользователей будут владелец базы и пользователь для пула коннектов, под которым все ходят. У этого пользователя не будет прав на таблицы, только на выполнение функций с security definer. В каждой транзакции сервер должен выставлять
Перед выполнением каждой функции в ней должна быть проверка на то, что имя пользователя, содержащееся в глобальной переменной на уровне данной транзакции, разрешено для запрашиваемой функции. Это делается через
А дальше мы и смотрим, может ли данный username использовать вызванную функцию. Но эта проверка должна быть на уровне некой дополнительной функции, а не через пользователей PostgreSQL.