Как работает использование нескольких CTE с JOIN в SQL: по индексам или в памяти?

Что проверяют

Понимаете ли вы, что CTE это не временная таблица, а часть запроса, и что ответ на «по индексам или в памяти» зависит от СУБД и от того, решит ли планировщик материализовать выражение. Следом обычно дают EXPLAIN и просят показать, где индекс перестал работать.

Как отвечать

Начните с того, что Go-код здесь ни при чём: pgx или database/sql отправляют запрос целиком, а план строит база. Дальше на примере PostgreSQL:

• встраивание: с версии 12 нерекурсивное CTE без побочных эффектов, на которое ссылаются один раз, встраивается в основной запрос. Условия из внешнего WHERE проталкиваются внутрь, и планировщик использует индексы базовых таблиц;
• материализация: если на CTE ссылаются два раза и больше, по умолчанию оно вычисляется один раз и складывается во временное хранилище в пределах work_mem, а при нехватке уходит во временные файлы. Индексов у результата нет, соединение идёт через Hash Join, Merge Join или перебор. В плане это узел CTE Scan;
• управление: WITH x AS MATERIALIZED (...) и AS NOT MATERIALIZED задают поведение явно. Материализация полезна, если тяжёлое выражение нужно посчитать один раз;
• всегда материализуются: рекурсивные CTE и CTE с INSERT, UPDATE, DELETE внутри.

До PostgreSQL 12 каждое CTE было барьером оптимизации, отсюда старый совет «CTE медленные». В других базах своё поведение: SQL Server всегда встраивает CTE и при двух ссылках вычисляет его дважды, MySQL 8 умеет и то и другое и для материализованного результата может построить автоматический индекс.

Проверяется всё через EXPLAIN (ANALYZE, BUFFERS): видно, есть ли CTE Scan, какой тип соединения выбран и не ушёл ли хэш на диск.

Где спотыкаются

Отвечают «CTE всегда в памяти» или «всегда по индексам». Всё зависит от версии, числа ссылок и подсказок.

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

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