darthunix
0
Для версионирования или сравнения схем использую Pyrseas. Очень хорошая штука, кстати.
darthunix
+2
Хорошо, спрошу максимально прямо — о чем эта статья и что из нееследует вынести после прочтения?
darthunix
+2
Понимаете, просто само название статьи и начала абзацев вроде
Я бы хотел рассмотреть почему же SQL Server так долго строит план запроса.
Давайте поговорим о порядке соединения таблиц подробнее

настраивают на то, что на примере запроса из начала статьи будет разобран алгоритм построения плана и дана общая теория по специфике работы планировщика MS SQL. А закончится статья тем, что автор победил проблему с учётом вышеописанных знаний. По итогу же имеем «Вот запрос, нагенерированный 1С на 20 таблиц с повторами. Он строится иногда долго. Почему? Много таблиц в соединении плохо, так как эн-факториал. Вывод — не делайте много таблиц и уважайте планировщик». Ну такому уровню статей не место на Хабре, простите.
darthunix
+1
Я так и не понял из статьи, как именно MS SQL находит баланс между стоимостью плана и лимитом по времени перебора. N! вариантов — это хорошо для объяснения на пальцах, но вряд ли именно так все работает. У того же PostgreSQL помимо стандартного планировщика запросов есть ещё модуль генетической оптимизации. На последнем pgconf показывали модуль адаптивной оптимизации, который учитывал предыдущий опыт построения планов по схожим запросам. Наверняка в MS SQL все не менее наворечено и можно тоже что-то понастраивать с вычислением стоимости планов на большом количестве соединений. А просто сказать, что когда много таблиц, то планировщик начинает работать не очень… не информативно.
darthunix
+2
Хотелось бы для PostgreSQL больше поддержки нативных объектов. Сейчас нет ни оберток над внешними данными (внешние таблицы, схемы и сервера), ни типов, ни расширений, ни объектов полнотекстового поиска (словари, парсеры, шаблоны), ни правил. К сожалению, как бы я ни желал перейти с PgAdmin на DataGrip, пока функционал последнего не позволяет это сделать…
darthunix
0
Мой опыт PostgreSQL подсказывает никогда не использовать конструкцию NOT IN, это самый медленный вариант из возможных — все жду, когда планировщик PostgreSQL научится с ним нормально работать. Обычно исключение строк реализуется или через EXCEPT, или через LEFT JOIN… WHERE id IS NULL. Но EXCEPT из этих вариантов самый производительный.
darthunix
+1
А вот теперь суть истории стала понятна и статья обрела смысл) Да, безусловно, использовать внутри рекурсивного запроса конструкцию with для вычисления следующего шага нельзя, это обычно фатально для производительности. Я тоже так стрелял себе в ногу…
Вообще вы подняли хорошую тему — надо бы собрать набор паттернов и антипаттернов в sql и написать большую статью, которая дополнялась бы по мере обсуждения в комментариях. Не хотите попробовать?;)
darthunix
0
По факту вся статья сводится к простому правилу — минимизируйте количество строк и столбцов на каждом этапе запроса для увеличения производительности.

По поводу первой части с with… Проблема была не в cte, а в некорректно переписанном запросе, который не использовал условие where product_id = 1234. Ещё бы он не работал медленнее. Зачем вы смущаете юных падованов, связывая замедление с конструкцией with?)
Опять же, аргумент вида
Если айдишних получается, например, в ходе рекурсивного запроса, то в WITH такое условие не утащишь и идея с разделением запроса на куски будет безбожно тормозить

далёк от истины. Ничто не мешает положить результат рекурсивного запроса в виде единственного айдишника в cte, а потом написать
where product_id = (select id from recursive_with)


Касаемо красивой визуализации explain analyze — лично я предпочитаю построение в pgAdmin: толщина связей в отображении плана запроса очень чётко указывают на проблему производительности.
darthunix
0
Сколь я помню у PosgresPro есть возможность при создании индекса помещать в его листья дополнительные поля, чтобы не обращаться к таблице. Параметр including, если память не изменяет. А вообще используйте вариант genew с поиском по индексу без обращения к таблице (кстати, вам на самом деле нужен только один составной индекс, а не целых три!). Этот вариант лучше массива залитого gin — в массиве вы теряете согласованность данных на внешних ключах. Кстати, во внешних ключах правильнее использовать on update cascade, чем no action — понятно, что первичные ключи не обновляют, но концептуально… А в запросе вместо distinct можно попробовать group by, он на прежних версиях меньше ошибался.
darthunix
0
Курсоры — это медленно и очень плохая практика в sql. Ну и да, вы смотрели план выполнения запроса в этом именованном алгоритме?
darthunix
+1
Забавы ради попробовал решить, не подглядывая в ваш вариант (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;
darthunix
0
Я внес замечания в статью по поводу with, deferrable и set autocommit off. Спасибо большое за замечания и советы.
darthunix
0
Поправил, это и правда было написано плохим стилем.
darthunix
0
Кроме того создание инкрементального бекапа при помощи braman это отдельная боль (если у вас БД больше 100 гигов)

А можно поподробнее про подводные камни на 100 ГБ? У меня БД на 60 ГБ, хочу узнать, к чему готовиться через пару лет.
darthunix
0
Если количество клиентов гарантированно останется небольшим (десяток-другой) и увеличиваться никогда не будет, то да. Главное, не ошибиться.
darthunix
0
Я и правда не очень четко выразился. Речь о том, что создавать для каждого пользователя приложения своего пользователя в БД — очень плохая практика. При дальнейшем росте нагрузок (а они растут внезапно и незапланированно) это обернется невозможностью использовать пулы соединений к базе.
darthunix
0
Вы получите согласованность на конец выполнения транзакции и (опционально) на любой момент внутри ее. Это позволяет упростить жизнь при вставке данных одной транзакцией в разные таблицы, где из-за внешних ключей нужно иначе было бы учитывать порядок вставки. Плюс это иногда удобно при импорте данных через insert.
darthunix
+2
Это, скорее, правило хорошего тона. Но теоретически, можно получить проблемы конфликта имен при установке расширений, которые используют схему public для своих функций. Маловероятно, но возможно.
darthunix
+3
И не просто наступал, но и героически преодолевал последствия. До сих пор вздрагиваю.
darthunix
0
Посмотрел на теги внизу и нашёл родственную душу — я всегда делаю такую же ошибку в написании слова deferrable ;) и только написав вспоминаю, что где-то был капкан.
darthunix
0
Проблема в том, что использовать базу для аутентификации пользователей — плохая идея, этим должен заниматься сервер. Как я понял, вы решили схитрить и просто прокидывать пользователя к БД, а она пусть штатными средствами и разбирается кто есть кто и что ему можно. Это неправильная архитектура по причине отсутствия возможности для маштабируемости. Если у вас резко увеличится количество пользователей, база ляжет. И никакие балансировщики нагрузки БД вам не помогут, так как каждый пользователь будет со своим подключением, и их нельзя будет собрать в единый пул.
Вам не нужно поддерживать соответствие между пользователями в БД и на сервере. В простейшем случае в базе из пользователей будут владелец базы и пользователь для пула коннектов, под которым все ходят. У этого пользователя не будет прав на таблицы, только на выполнение функций с security definer. В каждой транзакции сервер должен выставлять
begin;
select set_config('ваша_переменная_пользователя_из_pg_config', 'пользователь_на_сервере', false);
select func(...);
commit;

Перед выполнением каждой функции в ней должна быть проверка на то, что имя пользователя, содержащееся в глобальной переменной на уровне данной транзакции, разрешено для запрашиваемой функции. Это делается через
select current_setting('ваша_переменная_пользователя_из_pg_config') into username;

А дальше мы и смотрим, может ли данный username использовать вызванную функцию. Но эта проверка должна быть на уровне некой дополнительной функции, а не через пользователей PostgreSQL.
darthunix
0
Базы данных держат сотни подключений, а не тысячи, как web сервера. И каждое простаивающее подключение съедает ресурсы. Именно поэтому никто взаимооднозначно не транслирует клиентов через сервер в базу, а заворачивают кучу пользователей в один пул. Иначе база не потянет. Поэтому штатными средствами acl базы вы сможете раздать права на выполнение функций только учеткам, которые используются в пуле. Не штатными — можно использовать GUC (в случае PostgreSQL), выставляя на сервере в каждой транзакции имя пользователя через set_config. А в функциях вытаскивать из глобальной переменной имя пользователя и делать проверку.