PostgreSQL Antipatterns: убираем медленные и ненужные сортировки

[nanshakov]

А через что вы смотрите планы выполнения запросов ?

[Kilor]

Через наш сервис визуализации explain.tensor.ru, вот о нем:
habr.com/ru/company/tensor/blog/505348

[mayorovp]

Нет, потому что чтение из вложенного запроса сохраняет порядок записей и так.

А оно точно сохраняет порядок? Всегда-всегда сохраняет? А если вложенных запросов несколько? А если там соединение вложенного запроса с таблицей?

[Kilor]

Соединение вложенного запроса с таблицей само по себе генерирует неопределенный порядок, потому что заранее неизвестно, что это будет — Nested Loop, Hash Join, Merge Join.
Но вот если уже это соединение «обернуть» внешним запросом, то вот он — сохранит порядок. Правда, все тот же неопределенный.

[mayorovp]

Всё перечисленное вами не является частью семантики SQL.

Кстати, а если в условии WHERE написать подзапрос — сохранение порядка всё ещё гарантируется или уже нет?

[Kilor]

Частью SQL-стандарта — не является, частью конкретной реализации в PostgreSQL — вполне.

WHERE добавляет к Subquery Scan/CTE Scan/Function Scan/Values Scan-узлу Filter-атрибут, поэтому порядок сохраняется. По крайней мере, пока не будет реализовано что-то маловероятное типа Parallel CTE Scan.

[mayorovp]

Ну нет, WHERE совершенно не обязательно добавляет что-то из перечисленного вами.

Вот запрос где получился Hash Join: https://explain.tensor.ru/archive/explain/504794ad1f08415afa2531efdb13ce50:0:2020-10-08#explain

А вот запрос где получился Nested Loop:
https://explain.tensor.ru/archive/explain/2477bb926f5102ef7cf9d38386975e09:0:2020-10-08

Кстати, на этих запросах видно, что порядок действительно сохранился, но вот построенный план… Чем больше я изучаю PostgreSQL — тем меньше я понимаю как его использовать.

[Kilor]

Я имел в виду, что конкретно к _этим_ узлам WHERE добавляет только Filter.

Безусловно, если у нас узлы вообще какие-то другие в плане, то на них это утверждение не распространяется — например, как ситуация с Seq Scan в примере выше.

[VaalKIA]

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

[Kilor]

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

[alexeyrom]

> Мы-то понимаем, что «внутренняя» сортировка ни на что не влияет (в большинстве случаев), но СУБД — нет.

А почему, собственно? Вроде бы оптимизация достаточно очевидная. Просто не дошли пока? Или есть какие-то краевые случаи, когда она некорректна, и их сложно отловить?

[Kilor]

А если внутри запроса вызывается некоторая функция, результат которой зависит от порядка, например?.. Слишком много вариантов, когда самовольное устранение явно вписанной сортировки может навредить.

[alexeyrom]

Я имел в виду именно когда в плане получается sort(sort({query}, order1), order2), как в этом примере, можно превратить в sort({query}, merge(order1, order2)) для какого-то понятия merge. Т.е. к моменту внутренней сортировки все функции в query уже отработали (с логической точки зрения).

[Kilor]

Иногда так превратить можно, но вычислить такую возможность — сложно.
Представим, что внутри query сидит агрегатная функция вроде sum(n × Vn), зависящая от порядка подаваемых на вход значений.