Важные изменения в работе CTE в PostgreSQL 12

    WITH w AS  NOT MATERIALIZED (
        SELECT * 
        FROM very_very_big_table
    )
    SELECT * 
    FROM w AS w1 
        JOIN w AS w2 
            ON w1.key = w2.ref
    WHERE w2.key = 123;

    Сегодня в репозиторий PostgreSQL упал комит, позволяющий управлять поведением обработки подзапросов CTE, а именно: теперь можно явно указывать, будет ли подзапрос материализовываться отдельно или же выполняться как часть одного большого запроса.


    Это войдет в PostgreSQL 12, и это big deal. Давайте рассмотрим, почему


    Программисты любят CTE, потому что это позволяет существенно улучшить читаемость кода. Ну действительно, некоторые аналитические запросы могут работать с десятками таблиц и различными группировками и фильтрами. Писать всё это одним большим запросом — гарантированно получится что-то нечитаемое. Поэтому с помощью оператора WITH мы последовательно, небольшими подзапросами (которым задается человекочитаемое имя) описываем логику работы, а потом выдаем результат. Очень удобно.


    Точнее, было бы очень удобно, если бы не одно но: текущий PostgreSQL выполняет эти подзапросы отдельно друг от друга, материализовывает их (записывает результат во временную таблицу). Это может привести к существенному замедлению по сравнению с одним большим нечитабельным монстром. Особенно, если CTE-подзапросы возвращают миллионы строк.


    Впрочем, бывают ситуации, когда такое отдельное выполнение работает во благо: есть такой оптимизационный трюк, когда часть сложного запроса лучше выполнить отдельно, а постгрес самостоятельно этого не понимает. Тогда мы выносим эту часть в CTE-подзапрос.


    В общем, ситуации бывают разные, именно поэтому в Postgres 12 был сделан комит, добавляющий ключевые слова MATERIALIZED и NOT MATERIALIZED, которые указывают соответственно материализовывать ли запрос или инлайнить.


    Более того, изменилось дефолтное поведение. Теперь CTE-подзапрос по умолчанию будет инлайниться, если его результат используется один раз. В противном случае будет как раньше материализовываться.

    Поддержать автора
    Поделиться публикацией

    Комментарии 15

      +1
      Наконец-то, жаль пользоваться этим на текущей инфраструктуре не выйдет, Гугл ещё сто лет будет cloud sql обновлять.
        0
        Почему бы это как хинт не сделать, как в оракле?
          +4
          We are not interested in implementing hints in the exact ways they are commonly implemented on other databases.

          https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion

            +3
            Зачем делать «как хинт» то, что можно сделать (и, в итоге, сделано) элементом нормального синтаксиса?
              +1
              Все таки это указание оптимизатору.
                0
                С одной стороны — логично. С другой — в аналогичной ситуации с материализованными представлениями факт требования материализации — тоже, в известной степени, указание оптимизатору.
                  0
                  По материализованным представлениям можно строить индексы.
                    0
                    Строго говоря оптимизатор может делать индексы по любым промежуточным резалтсетам (и у вас, как правило, нет никакой возможности на это повлиять). На что это влияет?
                    По сути, когда вы создаёте материализованное представление вы создаёте (и хотите создать) таблицу. Просто у неё такой синтаксис специальный :) То же самое получается и здесь. С той лишь разницей что таблица эта — временная.
                      +2
                      Не-не, не путайте.

                      оптимизатор может делать индексы по любым промежуточным резалтсетам

                      Не может. Индекс можно построить только по таблице (ну или по материализованному представлению, что суть почти одно и то же) и только вручную. Оптимизатор ничего такого сам по себе никогда не делает.

                      То же самое получается и здесь. С той лишь разницей что таблица эта — временная.

                      Материализация CTE и материализованное представление — это две совсем разные штуки.
                      Материализация CTE состоит в том, что результат CTE запоминается (на время выполнения запроса) и может быть использован в дальнейших соединениях и других операциях. Никакая временная таблица для этого не создается. Если результат CTE помещается в память (work_mem), то в памяти он и сидит. Если не помещается — скидывается на диск, но не в таблицу, а в какой-то служебный файлик, а-ля своп.
                        –2
                        Оптимизатор ничего такого сам по себе никогда не делает.
                        Возможно оптимизатор PostgreSQL (в текущих версиях) этого не делает, но другие делают (и, вероятно, он тоже будет).

                        Материализация CTE и материализованное представление — это две совсем разные штуки.
                        В чём именно состоит принципиальная разница? И там, и там получается кортеж, которых сохраняется (там, куда помещается). Вы, вероятно, слишком узко рассматриваете понятие «временная таблица».
                          +1
                          Другие делают? Пример в студию!
                          Принципиальная разница в том, что в одном случае мы имеем дело с объектом базы данных в системном каталоге, а в другом — нет. А так-то да, можно что угодно свести к набору кортежей.
                            –1
                            С примером проблема, с ходу не нашёл (хотя помню что где-то встречал). Если найду — допишу.
                            По поводу системного каталога: а все ли временные объекты (таблицы и т.п.) присутствуют в системном каталоге? И в чём такая важность их там присутствия или отсутствия?
                              +1
                              Все присутствуют. Объектами БД управляет пользователь, а материализацией CTE — планировщик. Одно видно снаружи, другое — внутренняя кухня. Внутренняя инфраструктура разная, накладные расходы разные.
                +1

                Как вариант, чтобы работало быстро в 12-й версии, и возможно медленно но работало в 11-й.
                Но разработчики PG давно выбрали вариант с частью синтаксиса, пусть уж лучше однообразно будет (кстати у oracle materialize не единственное что можно сделать с подзапросом, в т.ч. из view, что на мой взгляд иногда помогает весьма гибко направлять оптимизатор не создавая новые view).

              0
              Наконец-то разделяются управление сложностью запроса и управление работой с данными в ходе выполнения запроса.

              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

              Самое читаемое