Re: Remove restrictions in recursive query

On Thu, Mar 27, 2025 at 12:37:53PM -0400, Robert Haas wrote:
> A recursive CTE effectively takes two queries that will be run as
> arguments. For some reason, instead of choosing syntax like WITH
> RECURSIVE t1 AS BASE_CASE (initial_query) RECURSIVE_CASE
> (iterated_query), somebody chose WITH RECURSIVE t1 AS (initial_query
> UNION iterated_query) which really doesn't make it very clear that we
> need to be able to break it apart into two separate queries, one of
> which will be run once and one of which will be iterated.

Perhaps because one could have multiple UNIONs.

> It's not a problem if UNION ALL is used within the initial_query and
> it's not a problem if UNION ALL is used within the iterated_query. But
> you can't apply ORDER BY to the result of the UNION, because the UNION
> is kind of fake -- we're not running the UNION as a single query,
> we're running the two halves separately, the first once and the second
> as many times as needed.

Even in the UNION ALL case there's still iteration. Either the internal
table used to hold the CTE's rows has to be kept in order as rows are
added or it has to be sorted when the CTE query completes.

I see ORDER BY in a recursive query more as either a hint for creating
an internal index on the CTE, or a request to sort the CTE when the
recursive query completes (or as it goes even).

Something I wish were possible is to have indices on CTEs so they can
function more like temp tables that don't need to touch the pg_catalog.
Or maybe the optimizer can create indices on CTEs automatically based on
how the CTEs are used.

Speaking of CTEs as temp tables, GLOBAL TEMP tables would be nice.

Nico
--