🐘 «Рекурсивные» запросы в PostgreSQL

Одна из главных бед «Постгреса» — бедные (по сравнению с «Ораклом») «рекурсивные» запросы. Подробнее об этом как-нибудь в другой раз, а сейчас расскажу про частный случай, который удалось побороть с большим трудом и найденное решение, признаться, мне совсем не нравится.

Если вы знаете рецепт лучше, поделитесь, пожалуйста.

Как я уже рассказывал, поскольку продукт у нас мигрирует с «Оракла» на «Постгрес», мы сейчас живём сразу на двух базах и некоторые виды запросов строят унифицированные билдеры.

Есть такой и для «рекурсивных» запросов. Нередко «рекурсивный» запрос приходится строить, выбирая не из таблицы, а из подзапроса. Наш билдер был устроен таким образом, что подобный запрос он строил по следующему образцу:

WITH RECURSIVE prior AS (
    WITH virtual AS (
        -- тут у нас подзапрос
    )
    SELECT … FROM virtual WHERE …некий критерий…
    UNION ALL
    SELECT … FROM virtual INNER JOIN prior …критерий соединения…
)
SELECT … FROM prior…

Ну вот что-то такое. Оказалось, конструкция WITH является барьером для постгреского оптимизатора — он не может заглянуть за него и планы строит отдельно — для запроса внутри WITH и того, что снаружи. Разумеет это дико неэффективно. Например, если внутри соединение со слабыми ограничениями (сильные наложатся снаружи — в «рекурсивном» запросе), то внутренний запрос может выбрать гигантское количество данных, что у нас и происходило в нескольких местах.

Эффективнее оказалось убрать внутренний WITH и повторить запрос дважды — в выборе первой строки и внутреннем соединении. Но беда в том, что билдер так сделать не может, а чтобы его научить, нужно было написать парсер запросов — так как критерии соединения должен содержать правильные алиасы. Оборачивание же части запроса в подзапрос тоже даром не проходит — стоимость запроса вырастает.

Я перебрал несколько решений, которые пришли мне в голову и остановился на следующем: в данный момент билдер создаёт временную «вьюху» с определённым именем, которое указывается в качестве источника запроса. Выглядит это так:

CREATE OR REPLACE TEMP VIEW некоеимя AS (
    -- тут у нас подзапрос
);
WITH RECURSIVE prior AS (
    SELECT … FROM некоеимя WHERE …некий критерий…
    UNION ALL
    SELECT … FROM некоеимя INNER JOIN prior …критерий соединения…
)
SELECT … FROM prior…

Выходит достаточно эффективно, но смущают две вещи: во-первых, всё это выглядит очень неизящно, во-вторых, для «вьюхи» нельзя подготовить план — все параметры приходится вставлять в запрос, так что максимальная производительность, естественно, не достигается.