Re: CTE materialized/not materialized

On 2020-Nov-26, Voillequin, Jean-Marc wrote:

> The problem I have now, is that I have to decide the plan that PG must
> follow. It's a kind of Oracle hint.
>
> Is it possible, for PG, to decide, by its own, the best way to go
> (materialized or inlined) for each CTE encountered in the query,
> rather than deciding to materialized as soon as the CTE is found more
> than once in the outer query? Am I clear?

Actually, the planner does make a choice. Only if it's wrong then it is
necessary to mark the CTE for materialization. Previously, it always
materialized, causing many queries to have bad performance with no
recourse.

One could argue that we could do better at making the choice, but that
seems an unimplemented feature, not a bug.

Quoting src/backend/optimizer/plan/subselect.c:

* We cannot inline if any of these conditions hold:
*
* 1. The user said not to (the CTEMaterializeAlways option).
*
* 2. The CTE is recursive.
*
* 3. The CTE has side-effects; this includes either not being a plain
* SELECT, or containing volatile functions. Inlining might change
* the side-effects, which would be bad.
*
* 4. The CTE is multiply-referenced and contains a self-reference to
* a recursive CTE outside itself. Inlining would result in multiple
* recursive self-references, which we don't support.
*
* Otherwise, we have an option whether to inline or not. That should
* always be a win if there's just a single reference, but if the CTE
* is multiply-referenced then it's unclear: inlining adds duplicate
* computations, but the ability to absorb restrictions from the outer
* query level could outweigh that. We do not have nearly enough
* information at this point to tell whether that's true, so we let
* the user express a preference. Our default behavior is to inline
* only singly-referenced CTEs, but a CTE marked CTEMaterializeNever
* will be inlined even if multiply referenced.

--
Álvaro Herrera https://www.EnterpriseDB.com