agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Voillequin, Jean-Marc <Jean-Marc.Voillequin@moodys.com>
Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: Re: CTE materialized/not materialized
Date: Fri, 27 Nov 2020 11:53:47 -0300
Message-ID: <20201127145347.GA17941@alvherre.pgsql> (raw)
In-Reply-To: <MW3PR20MB3338A498F87D99C38C645006BEF90@MW3PR20MB3338.namprd20.prod.outlook.com>
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
view thread (6+ messages) latest in thread
Message-ID: <20201127145347.GA17941@alvherre.pgsql>
Permalink: ../20201127145347.GA17941@alvherre.pgsql/
Also on: postgresql.org/message-id/20201127145347.GA17941@alvherre.pgsql
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: alvherre@alvh.no-ip.org, Jean-Marc.Voillequin@moodys.com, pgsql-sql@lists.postgresql.org
Subject: Re: CTE materialized/not materialized
In-Reply-To: <20201127145347.GA17941@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox