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

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

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

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

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…

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

Сегодняшняя добыча — результат многочасового труда

Сегодня мы побили все рекорды — выехали в половину шестого утра, а вернулись домой в седьмом часу. Обошли несколько домов, результат следующий: «конина», 2 копейки 1924 года (в отличном состоянии, но гнутые), 3 копейки 1946, пуговица братьев Бух царского периода, кольцо (вероятнее всего — «конина»), пуля, шток от умывальника, безымянная пуговица (думаю, современная), странный значок, маленькая пуговичка «Законъ» в плохом состоянии, капсуль от патрона, алюминиевый значок (кажется с Лениным) и пуговица с непонятным гербом.

Последнюю пуговицу не удалось опознать, кажется на ней герб какого-то города — два вертикальных колоса по краям, некий зверь на щите, остальное пока разобрать не смог.

Ещё была советская медаль «За доблестный труд», но её забрал себе владелец одного из домов, где мы искали и девять советских монет позднего периода в плохом состоянии, их я просто выбросил.