RE: CTE materialized/not materialized

Thanks.
I agree it's not a bug.
Let me rephrase.
In the case the CTE can be inlined, why PG does not compute 2 plans (P1 with CTE inlined, P2 with CTE materialized) and compare them to choose the best?

-----Original Message-----
From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
Sent: Friday, November 27, 2020 3:54 PM
To: Voillequin, Jean-Marc <Jean-Marc(dot)Voillequin(at)moodys(dot)com>
Cc: pgsql-sql(at)lists(dot)postgresql(dot)org
Subject: Re: CTE materialized/not materialized

CAUTION: This email originated from outside of Moody's. Do not click links or open attachments unless you recognize the sender and know the content is safe.

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

-----------------------------------------

Moody's monitors email communications through its networks for regulatory compliance purposes and to protect its customers, employees and business and where allowed to do so by applicable law. The information contained in this e-mail message, and any attachment thereto, is confidential and may not be disclosed without our express permission. If you are not the intended recipient or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that you have received this message in error and that any review, dissemination, distribution or copying of this message, or any attachment thereto, in whole or in part, is strictly prohibited. If you have received this message in error, please immediately notify us by telephone, fax or e-mail and delete the message and all of its attachments. Every effort is made to keep our network free from viruses. You should, however, review this e-mail message, as well as any attachment thereto, for viruses. We take no responsibility and have no liability for any computer virus which may be transferred via this e-mail message.

This email was sent to you by Moody’s Investors Service EMEA Limited
Registered office address:
One Canada Square
Canary Wharf
London, E14 5FA
Registered in England and Wales No: 8922701

-----------------------------------------