ora2pg

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

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

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

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

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…

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

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

У нас довольно много генерируемых простых запросов, иногда они генерировались не очень аккуратно. «Оракл» такое прощает, а «Постгрес» — нет. Запрос, где одни и те же таблицы соединяются дважды «Постгресу» не по вкусу:

SELECT * FROM test INNER JOIN test1 USING(n) INNER JOIN test USING(n);

ERROR:  table name "test" specified more than once

«Постгрес» очень трепетно относится к тому, чтобы у подзапроса был алиас, даже если он потом нигде не используется:

SELECT * FROM (SELECT * FROM test);

ERROR:  subquery in FROM must have an alias
СТРОКА 1: SELECT * FROM (SELECT * FROM test);
                        ^
ПОДСКАЗКА:  For example, FROM (SELECT ...) [AS] foo.

В «Постгресе», в запросах на обновление нельзя указать алиас. Тут это логично, так как синтаксис подзапросов в этом случае у этой СУБД совершенно иной — они указываются в специальном месте.

UPDATE test t SET t.n=1;

ERROR:  column "t" of relation "test" does not exist
СТРОКА 1: UPDATE test t SET t.n=1;

Если «Оракл» спокойно относится к двум и более таблицам с одним алиасом, то «Постгресу» от этого плохеет. Жаль, иногда удобно объединить несколько таблиц алиасом прямо на месте и работать дальше с ним, как с единой таблицой.

SELECT t.n FROM test t, another_test t;

ERROR:  table name "t" specified more than once

В «Оракле» ключевое слово FROM не обязательно в запросах на удаление. У нас было несколько запросов без него.

DELETE test;

ERROR:  syntax error at or near "test"
СТРОКА 1: DELETE test;

В следующий раз буду описывать более комплексные примеры.