Комментарии 24
По первому же примеру: ман не главное, ведь даже при чтении функции очевидно, что она проводит тупо выборку нужного формата и возвращает её, это логически приводит к тому, что все остальные условия будут работать уже с полученной выборкой.
Взяли результат из сложной функции -> фильтранули его -> "ай-яй-яй, всё сломалось, но мы же ничего не меняли в логике!". Бинго! Вы вынесли одно из условий в следующий этап обработки.
ИМХО, тут скорее на лицо проблема построения логических цепочек. Т.к. даже для меня, человека весьма далёкого от подобных сложных запросов и не знающего о функциях и их работе в SQL, очевидно, что тут получается сначала огромный результат по всем продуктам(!) и только потом он изменяется.
Об этом и хотелось сказать. Что в случае 2 табличек и 3 условий, всё довольно очевидно. А когда объектов десяток или больше и если не знать кто с кем как связан, то легко можно вынести такой кусок куда подальше и быть уверенным, что всё будет хорошо. По личному ощущению, после 100 строк sql-запроса становится довольно сложно в нём ориентироваться. Плюс, рискну огрести, но всё-таки: по рассказу постфактум всё может быть довольно понятно, но когда в ходе разработки на очередном этапе всё поломалось, то найти причину не так просто. Конечно потом ударишь себя ладонью в лоб, и скажешь как же мы могли этого не заметить, но это будет позже.
Согласен, но всё-таки, если параметр WHERE стоит за пределами выражения, это должно наводить на мысль..)
Таки да. Но если в WHERE ещё десятка полтора условий и значимых для конкретной части только 1-2шт, то они там и теряются. Всё познаётся в сравнении. В sql нужно быть безумно внимательным, иначе неизменно получается хрень.
Хм, а дробить это сразу а функции можно или это сильно будет сказываться на производительности? Просто мне всё-таки хочется понять, почему Вы сказали, что практики "чистого кода" не сильно применимы к SQL(ну, да, это вообще не ЯП, но всё-таки).
Скажем так, имею в виду, что к чистому sql-запросу практики для императивного программирования очень редко применимы (крайний пример: огромный метод в классе на том же Ruby — это великое зло и все будут жаждать разбить его, а огромный запрос, который выбирает за милисекунды нужные данные — это необходимое зло, с которым мало кто что-то делает, максимум подтащат красивое соглашение об оформлении кода). Если бы это был PL/SQL, то там другой коленкор, но всё равно скорость превалирует над красотой. В вышеописанном случае: есть небольшая портянка на 150 строк. Любые попытки разбить запрос на отдельные части ведут к проседанию производительности. Может быть можно его иначе написать и будет более читаемо, но вряд ли трудозатраты окупятся. Как раз об этом и хочется поговорить ещё с кем-либо.
Вам, может быть, и очевидно что запрос сначала делает полную выборку, а уже потом ее фильтрует… а вот хорошие оптимизаторы запросов так не считают.
Специально проверил в MS SQL:
select t1.id as x, t2.id as y, t3.id as z
from c as t1, c as t2, c as t3
where t1.id < 10 and t2.id < 10 and t3.id < 10;
with t as (
select t1.id as x, t2.id as y, t3.id as z
from c as t1, c as t2, c as t3)
select * from t
where x < 10 and y < 10 and z < 10;Оба запроса имеют одинаковые планы.
да, но в Вашем примере не хватает одной детали. В "облагороженном" куске кода было такое условие: WHERE section_item_history.product_id IS NOT NULL, — а потом за пределами WITH должно было стоять ещё одно, но уже с конкретным параметром product_id. Так что, для корректности пример должен быть в духе:
select t1.id as x, t2.id as y, t3.id as z
from c as t1, c as t2, c as t3
where t1.id < 10 and t2.id < 10 and t3.id < 10;
with t as (
select t1.id as x, t2.id as y, t3.id as z
from c as t1, c as t2, c as t3
where x is not null and y is not null and z is not null)
select * from t
where x < 10 and y < 10 and z < 10;Как я уже писал выше, с SQL знаком весьма поверхностно, но два разных условия выборки на лицо.
Дополнительное простое условие WHERE ну никак не может замедлить выборку.
В любом случае, добавление нового условия и оборачивание в WITH — это независимые изменения.
Вот рискну не согласиться с вами по поводу того, что WHERE никак не может замедлить выборку. Банальный пример: таблица с полями id и text. В text кладём что-нибудь поувесистей на пару метров или пару гигов. Сначала ищем по id, он же pk и на нём же, по умолчанию, висит индекс. Получаем данные моментально. Потом берём LIKE по текстовой колонке без индексов и ждём второго пришествия. Можно хитрее придумать. Например, у нас партиционирование и очередной WHERE заставит лезть в старые партиции, хранящиеся чуть ли не на магнитных лентах глубоко в архивах (или просто на медленном хранилище, так как потребности в них особо нет). Конечно нужно сильно подумать о том, как бы такую архитектуру с партициями сделать, но всё же. Посему считаю высказывание "WHERE не может замедлить запрос" — ложным.
При обучении это сильно зависит от предыдущего опыта. Грубо говоря, если человек много пишет на С, он по-умолчанию подумает, что здесь избыточные операции. Если же человек пишет на Хаскеле, где вся ненужная работа выкидывается и чтобы посчитать 2*2 легко можно создать бесконечный возрастающий массив, умножить его на себя же и взять второй элемент; то человек легко может подумать, что и здесь всё оптимизируется, и не будет же база данных заниматься такой фигнё, как строить в памяти квадрат со сторонами в 4к элементов!
Немного уйду от темы, но всё же. Такое случается в начале, когда 1-2 языка программирования в ассортименте и опыта не шибко много. Потом уже начинаешь думать а какой из известных подходов более всего актуален для поставленной задачи и для заданных условий. Там же учишься не смешивать контексты разных языков, чтобы, например, не писать в Си-шном стиле на Ruby. После этого оказывается, что есть сразу несколько вариантов решения, среди которых все имеют плюсы и минус. Вот тут-то и начинается самое интересное. Кстати, это интереснейшая тема для обсуждения!
LEFT JOIN specifications_history AS specification_history
ON specification_history.id = specification_detail.entity_history_id
AND specification_history.specification_id = ANY(specification_parts.ids)
но, ANY(specification_parts.ids) — это ересь. При соединениях сравнения выполняются построчно, потому внутри ANY(specification_parts.ids) все-равно всегда будет ровно одна строка. А значит, условие полностью равносильно с… AND specification_history.specification_id = specification_parts.ids
Да и статья — это, как и билет на экзамене
Выучишь — а попадётся другое.
По поводу первой части с with… Проблема была не в cte, а в некорректно переписанном запросе, который не использовал условие where product_id = 1234. Ещё бы он не работал медленнее. Зачем вы смущаете юных падованов, связывая замедление с конструкцией with?)
Опять же, аргумент вида
Если айдишних получается, например, в ходе рекурсивного запроса, то в WITH такое условие не утащишь и идея с разделением запроса на куски будет безбожно тормозить
далёк от истины. Ничто не мешает положить результат рекурсивного запроса в виде единственного айдишника в cte, а потом написать
where product_id = (select id from recursive_with)
Касаемо красивой визуализации explain analyze — лично я предпочитаю построение в pgAdmin: толщина связей в отображении плана запроса очень чётко указывают на проблему производительности.
Таки да, вы правы абсолютно, вся статья о минимизации. И о том, что нужно понимать структуру данных, чтобы эффективно минимизировать.
Касаемо WITH вместе с рекурсией. Либо я неправильно выразился, либо вы меня плохо поняли. Имелось в виду, когда во второй части WITH RECURSIVE (которая после UNION) используется блок из отдельного WITH. То есть он нужен для вычисления следующего шага рекурсии на основании результатов текущего. Здесь мы не можем (либо я просто не знаю как) прокинуть свежевычисленный id внутрь отдельного WITH, чтобы урезать выборку в нём. И, кажись, в этом месте я накосячил в статье, так как плохо описал эту ситуацию.
Вообще вы подняли хорошую тему — надо бы собрать набор паттернов и антипаттернов в sql и написать большую статью, которая дополнялась бы по мере обсуждения в комментариях. Не хотите попробовать?;)
Вот, пытаюсь по мере сил. Проблема лишь в том, что SQL в работе не так часто случается. А без конкретных примеров из практики получается уныло и не интересно. Подборка по прострелу конечности в Ruby/Rails у меня гораздо больше, но руки не дойдут опубликовать. А в целом только за. Если как-нибудь организовать наброс материала теми, у кого он есть, то готов заняться оформлением, проверкой и рассказом в доступной форме.
Как писать кривые запросы с неоптимальным планом и заставить задуматься СУБД