Оптимизировано ли внешнее предложение WHERE в рекурсивном CTE?

Question

Со следующим определением таблицы:

CREATE TABLE Nodes(id INTEGER, child INTEGER);

INSERT INTO Nodes(id, child) VALUES(1, 10);
INSERT INTO Nodes(id, child) VALUES(1, 11);
INSERT INTO Nodes(id, child) VALUES(1, 12);

INSERT INTO Nodes(id, child) VALUES(10, 100);
INSERT INTO Nodes(id, child) VALUES(10, 101);
INSERT INTO Nodes(id, child) VALUES(10, 102);

INSERT INTO Nodes(id, child) VALUES(2, 20);
INSERT INTO Nodes(id, child) VALUES(2, 21);
INSERT INTO Nodes(id, child) VALUES(2, 22);

INSERT INTO Nodes(id, child) VALUES(20, 200);
INSERT INTO Nodes(id, child) VALUES(20, 201);
INSERT INTO Nodes(id, child) VALUES(20, 202);

С помощью следующего запроса:

WITH RECURSIVE members(base, id, level) AS ( 
    SELECT n1.id, n1.id, 0
    FROM Nodes n1
    LEFT OUTER JOIN Nodes n2 ON n2.child = n1.id
    WHERE n2.id IS NULL

    UNION 

    SELECT m.base, n.child, m.level + 1
    FROM members m 
    INNER JOIN Nodes n ON  m.id=n.id
) 
SELECT m.id, m.level 
FROM members m 
WHERE m.base IN (1)

Оптимизировано ли внешнее предложение WHERE в рекурсивном CTE? Альтернатива, которую я рассмотрел с помощью:

WITH RECURSIVE members(id, level) AS ( 
    VALUES (1, 0)

    UNION 

    SELECT n.child, m.level + 1
    FROM members m 
    INNER JOIN Nodes n ON  m.id=n.id
) 
SELECT m.id, m.level 
FROM members m 

но проблема в том, что он не может создать из этого вид. Поэтому, если разница в производительности между ними минимальна, я бы предпочел создать представление из рекурсивного CTE, а затем просто запросить его.

Answer 1

Чтобы иметь возможность применять предложение WHERE к запросам внутри CTE, база данных должна была бы доказать, что

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

Такого прувера не существует. См. Ограничение 22 " Сглаживание подзапроса".

Answer 2

Чтобы понять, почему ваш первый запрос неоптимален, попробуйте запустить оба с UNION ALL вместо просто UNION. При наличии данных примера первая вернет 21 строку, а вторая - только 7.

Повторяющиеся строки в фактическом первом запросе впоследствии удаляются путем выполнения сортировки и удаления дубликатов, хотя этот шаг не является необходимым в фактическом втором запросе.