CTE inlining
Hello, dear hackers!
There is task in todo list about optional CTE optimization fence
disabling.
I am not interested at this point in disabling mechanism
implementation, but I would like to discuss the optimization
mechanism, that should work when the fence is disabled.
It seems, that we can replace CTE with subquery, so the optimizer
can do all available optimizations. This idea is quite
straightforward, but I could not find a discussion of it.
(Maybe it is so, because everyone knows that the idea is bad and it is
not worth to discuss. But I hope it is not, so I start this thread. =))
First of all, to such replacement to be valid, the CTE must be
1. non-writable (e.g. be of form: SELECT ...),
2. do not use VOLATILE or STABLE functions,
3. ... (maybe there must be more restrictions?)
Also, before inlining, we should check that some optimization
can be applied, using functions from
'pull_up_subqueries_recurse' and 'subquery_push_qual'.
If it is true, and there only one reference to CTE,
we can inline it immediately.
What it is not clear is how we should estimate whether it is worth
to inline, when there is multiple references. Here are my preliminary
ideas.
Let consider "pull up subquery" and "push down qualifiers" cases
separately.
For "push down qualifiers", if `subquery_push_qual` is `true`,
we can do the following:
1. copy CTE subquery,
2. push down quals,
3. find paths,
3. inline if cost of
(CTE scan) > (cheapest_path(subquery) + subquery scan)
Probably, this approach is not feasible, because it involves subquery
replaning, and we should consider a more "lightweight" heuristic.
For "pull up subquery" similar approach may lead to duplicate planning
of the whole query, that almost sure is too expensive.
So I wonder, is it possible to estimate a join predicate selectivity
against CTE subquery result and inline it if selectivity is "high" enough?
(If it is possible the same can be applied to the first case.)
I would be glad to hear feedback on described approach.
Ilya Shkuratov
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 30 Apr. 2017 07:56, "Ilya Shkuratov" <motr.ilya@ya.ru> wrote:
Hello, dear hackers!
There is task in todo list about optional CTE optimization fence
disabling.
I am not interested at this point in disabling mechanism
implementation, but I would like to discuss the optimization
mechanism, that should work when the fence is disabled.
It's looking at what other DBMSes do.
Notably MS SQL Server. AFAIK its CTEs are a lot like query-scoped views.
They are simply updatable where possible, so you can write
WITH x AS (...)
UPDATE x SET ...
I do not know how MS SQL handles inlining and pullup/pushdown vs
materialization, handles multiple evaluation costs, etc.
This is the model I would want to aim for.
It seems, that we can replace CTE with subquery, so the optimizer
can do all available optimizations. This idea is quite
straightforward, but I could not find a discussion of it.
(Maybe it is so, because everyone knows that the idea is bad and it is
not worth to discuss. But I hope it is not, so I start this thread. =))
It's not bad for SELECT.
But there are complexities.
- CTE terms may contain data-mutating functions people are relying on not
multiply executing;
- we document that in postgres CTEs act as optimisation fences even with
the standard syntax. So users rely on this as a query hint. Personally I
want to relnotes this and tell people to use our OFFSET 0 hint instead, or
add a NOINLINE option to our CTEs, then make pg allow inlining by default.
This is a BC break, but not a big one if we restrict inlining of volatile.
And since we don't have query hints (*cough*) by project policy, we can't
really object to removing one can we?
- as you noted, it is hard to decide when it's worth inlining vs
materializing for CTE terms referenced more than once. We should possibly
start small and only inline single reference terms in the first release.
We'd continue to force materializing of multiple reference terms.
That'd at least help people who use CTEs to write clearer queries not
suffer for it. And it'd give us experience to help with conservatively
introducing multiple reference inlining.
Craig Ringer <craig.ringer@2ndquadrant.com> writes:
- as you noted, it is hard to decide when it's worth inlining vs
materializing for CTE terms referenced more than once.
[ raised eyebrow... ] Please explain why the answer isn't trivially
"never".
There's already a pretty large hill to climb here in the way of
breaking peoples' expectations about CTEs being optimization
fences. Breaking the documented semantics about CTEs being
single-evaluation seems to me to be an absolute non-starter.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2017-04-30 6:28 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
Craig Ringer <craig.ringer@2ndquadrant.com> writes:
- as you noted, it is hard to decide when it's worth inlining vs
materializing for CTE terms referenced more than once.[ raised eyebrow... ] Please explain why the answer isn't trivially
"never".There's already a pretty large hill to climb here in the way of
breaking peoples' expectations about CTEs being optimization
fences. Breaking the documented semantics about CTEs being
single-evaluation seems to me to be an absolute non-starter.
why we cannot to introduce GUC option - enable_cteoptfence ?
Regards
Pavel
Show quoted text
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2017-04-30 00:28:46 -0400, Tom Lane wrote:
There's already a pretty large hill to climb here in the way of
breaking peoples' expectations about CTEs being optimization
fences. Breaking the documented semantics about CTEs being
single-evaluation seems to me to be an absolute non-starter.
If all referenced functions are non-volatile, I don't quite see the
problem? Personally I believe we'll have to offer a proper
anti-inlining workaround anyway, and in that case there's really nothing
that should stop us from inlining CTE without volatile functions twice?
- Andres
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 30 Apr. 2017 13:28, "Andres Freund" <andres@anarazel.de> wrote:
On 2017-04-30 00:28:46 -0400, Tom Lane wrote:
There's already a pretty large hill to climb here in the way of
breaking peoples' expectations about CTEs being optimization
fences. Breaking the documented semantics about CTEs being
single-evaluation seems to me to be an absolute non-starter.
If all referenced functions are non-volatile, I don't quite see the
problem? Personally I believe we'll have to offer a proper
anti-inlining workaround anyway, and in that case there's really nothing
that should stop us from inlining CTE without volatile functions twice?
Exactly.
The initial implementation had limitations. So they got documented as
features, not bugs or possible future enhancements. Yay? So we're stuck
with it forever?
I agree we shouldn't break working, correct queries such that they return
different results. But if someone is lying about volatility they don't get
the expectation of correctness. And we have a policy against hints, so
surely we should be keen to remove this hack that serves as a hint - right?
We have OFFSET 0 for anyone really depending on it, and at least when you
read that you know to go "wtf" and look at the manual, wheras the CTE fence
behaviour is invisible and silent.
Yes, experienced and established postgres users expect the optimisation
fence behaviour. They abuse it as a query hint or work around it with
subqueries in FROM. They also know OFFSET 0 ... and ideally should even
read the relnotes. Users from other DMBSes looking to migrate, and new
users, are regularly surprised by our CTEs. I see it a lot on Stack
Overflow and other places outside our comfortable walls.
Personally I find it very annoying when I'd like to use CTEs to structure
queries more readably, but land up having to use subqueries in FROM instead.
Like the work Andes has been doing on our bizarre handing of SRFs in the
SELECT target list I really think it's just something that needs to be done.
Import Notes
Reply to msg id not found: CAMsr+YGgxVW_nOysS_zZeHfMbcdffMjLAPnfHG6ERt+zv1_4RA@mail.gmail.com
Hi,
On 2017-04-30 13:58:14 +0800, Craig Ringer wrote:
We have OFFSET 0 for anyone really depending on it, and at least when you
read that you know to go "wtf" and look at the manual, wheras the CTE fence
behaviour is invisible and silent.
I don't think that's a good idea. What if you need to prevent inlining
of something that actually needs an offset? What if the behaviour of
offset is ever supposed to change? Relying more on that seems to just
be repeating the mistake around CTEs.
Like the work Andes has been doing on our bizarre handing of SRFs in the
SELECT target list I really think it's just something that needs to be
done.
With help from Tom, luckily...
Greetings,
Andres Freund
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
<div> </div><div> </div><div>30.04.2017, 08:58, "Craig Ringer" <craig.ringer@2ndquadrant.com>:</div><blockquote type="cite"><div><div> <div> <div>On 30 Apr. 2017 13:28, "Andres Freund" <<a href="mailto:andres@anarazel.de">andres@anarazel.de</a>> wrote:<blockquote style="margin:0 0 0 0.8ex;border-left:1px #ccc solid;padding-left:1ex;"><div>On <span>2017-04-30 00</span>:28:46 -0400, Tom Lane wrote:<br />> There's already a pretty large hill to climb here in the way of<br />> breaking peoples' expectations about CTEs being optimization<br />> fences. Breaking the documented semantics about CTEs being<br />> single-evaluation seems to me to be an absolute non-starter.<br /> </div>If all referenced functions are non-volatile, I don't quite see the<br />problem? Personally I believe we'll have to offer a proper<br />anti-inlining workaround anyway, and in that case there's really nothing<br />that should stop us from inlining CTE without volatile functions twice?</blockquote></div></div></div><div> </div><div>Exactly.</div><div> </div><div>The initial implementation had limitations. So they got documented as features, not bugs or possible future enhancements. Yay? So we're stuck with it forever?</div><div> </div><div>I agree we shouldn't break working, correct queries such that they return different results. But if someone is lying about volatility they don't get the expectation of correctness. And we have a policy against hints, so surely we should be keen to remove this hack that serves as a hint - right? </div><div> </div><div>We have OFFSET 0 for anyone really depending on it, and at least when you read that you know to go "wtf" and look at the manual, wheras the CTE fence behaviour is invisible and silent.</div><div> </div><div><div style="font-family:sans-serif;">Yes, experienced and established postgres users expect the optimisation fence behaviour. They abuse it as a query hint or work around it with subqueries in FROM. They also know OFFSET 0 ... and ideally should even read the relnotes. Users from other DMBSes looking to migrate, and new users, are regularly surprised by our CTEs. I see it a lot on Stack Overflow and other places outside our comfortable walls. </div><div style="font-family:sans-serif;"> </div><div style="font-family:sans-serif;">Personally I find it very annoying when I'd like to use CTEs to structure queries more readably, but land up having to use subqueries in FROM instead.</div><div style="font-family:sans-serif;"> </div><div style="font-family:sans-serif;">Like the work Andes has been doing on our bizarre handing of SRFs in the SELECT target list I really think it's just something that needs to be done.</div></div><div> </div></div></blockquote><div> </div><div>Also, I would like to remind that the disabling optimization fence is suggested to be OPTIONAL.</div><div>So we don't break peoples' expectations, nor documented semantics.</div>
On 04/30/2017 06:28 AM, Tom Lane wrote:
Craig Ringer <craig.ringer@2ndquadrant.com> writes:
- as you noted, it is hard to decide when it's worth inlining vs
materializing for CTE terms referenced more than once.[ raised eyebrow... ] Please explain why the answer isn't trivially
"never".There's already a pretty large hill to climb here in the way of
breaking peoples' expectations about CTEs being optimization
fences. Breaking the documented semantics about CTEs being
single-evaluation seems to me to be an absolute non-starter.
I'm not sure that's a universal expectation, though. I know there are
people who actually do rely on that intentionally, no doubt about that.
And we'd nee to make it work for them.
But I keep running into people who face serious performance issues
exactly because not realizing this, and using CTEs as named subqueries.
And when I tell them "optimization fence" they react "Whaaaaaaat?"
If I had to make up some numbers, I'd say the "Whaaaaat?" group is about
10x the group of people who intentionally rely on CTEs being
optimization fences.
FWIW I don't know how to do this. There were multiple attempts at this
in the past, none of them succeeded. But perhaps we could at least
propagate some of the CTE features, so that the outside query can
benefit from that (e.g. when the CTE is sorted, we could set the
sortkeys). That wouldn't break the fence thing, but it would allow other
stuff.
regard
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 04/30/2017 09:46 AM, Andres Freund wrote:
Hi,
On 2017-04-30 13:58:14 +0800, Craig Ringer wrote:
We have OFFSET 0 for anyone really depending on it, and at least when you
read that you know to go "wtf" and look at the manual, wheras the CTE fence
behaviour is invisible and silent.I don't think that's a good idea. What if you need to prevent inlining
of something that actually needs an offset? What if the behaviour of
offset is ever supposed to change? Relying more on that seems to just
be repeating the mistake around CTEs.
I agree with this. But OFFSET 0 would force people to modify the queries
anyway, so why not just introduce a new keyword instead? Something like:
WITH FENCED a (SELECT ...)
But I think something like that was proposed not too long ago, and did
not make it for some reason.
There's a lot of other CTE improvements that would be great. Say, being
able to define indexes on them, but that's really a separate topic.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Sun, Apr 30, 2017 at 11:54:48PM +0200, Tomas Vondra wrote:
On 04/30/2017 06:28 AM, Tom Lane wrote:
Craig Ringer <craig.ringer@2ndquadrant.com> writes:
- as you noted, it is hard to decide when it's worth inlining vs
materializing for CTE terms referenced more than once.[ raised eyebrow... ] Please explain why the answer isn't trivially
"never".There's already a pretty large hill to climb here in the way of
breaking peoples' expectations about CTEs being optimization
fences. Breaking the documented semantics about CTEs being
single-evaluation seems to me to be an absolute non-starter.I'm not sure that's a universal expectation, though. I know there
are people who actually do rely on that intentionally, no doubt
about that. And we'd nee to make it work for them.But I keep running into people who face serious performance issues
exactly because not realizing this, and using CTEs as named
subqueries. And when I tell them "optimization fence" they react
"Whaaaaaaat?"If I had to make up some numbers, I'd say the "Whaaaaat?" group is
about 10x the group of people who intentionally rely on CTEs being
optimization fences.
I suspect you're off by at least a couple of orders of magnitude here,
which make this even more important to deal with.
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2017-04-30 11:34:48 +0300, Ilya Shkuratov wrote:
Also, I would like to remind that the disabling optimization fence is suggested
to be OPTIONAL.
So we don't break peoples' expectations, nor documented semantics.
I think however is that that's not good enough, because it'll surprise
people forever, whereas using something as an intentional barrier
usually is, well, intentional.
Greetings,
Andres Freund
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2017-04-30 07:19:21 +0200, Pavel Stehule wrote:
why we cannot to introduce GUC option - enable_cteoptfence ?
Doesn't really solve the issue, and we've generally shied away from GUCs
that influence behaviour after a few bad experiences. What if you want
one CTE inlined, but another one not?
- Andres
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2017-05-01 1:21 GMT+02:00 Andres Freund <andres@anarazel.de>:
On 2017-04-30 07:19:21 +0200, Pavel Stehule wrote:
why we cannot to introduce GUC option - enable_cteoptfence ?
Doesn't really solve the issue, and we've generally shied away from GUCs
that influence behaviour after a few bad experiences. What if you want
one CTE inlined, but another one not?
It change behave in same sense like enable_nestloop, enable_hashjoin, ...
with same limits.
Regards
Pavel
Show quoted text
- Andres
On 05/01/2017 06:22 AM, Pavel Stehule wrote:
2017-05-01 1:21 GMT+02:00 Andres Freund <andres@anarazel.de
<mailto:andres@anarazel.de>>:On 2017-04-30 07:19:21 +0200, Pavel Stehule wrote:
why we cannot to introduce GUC option - enable_cteoptfence ?
Doesn't really solve the issue, and we've generally shied away from GUCs
that influence behaviour after a few bad experiences. What if you want
one CTE inlined, but another one not?It change behave in same sense like enable_nestloop, enable_hashjoin,
... with same limits.
Those (and also the other enable_*) GUCs are a great example why we
should not use GUCs for tweaking planner behavior, except perhaps for
the purpose of investigation. It's an extremely blunt tool.
You typically want to affect just a single node in the query plan (say,
one join), but those options don't allow you to do that. It's all or
nothing thing.
Even if you're OK with affecting the whole query, it's a separate
control channel - it's not embedded in the query, the user has to set it
somehow. So you either set it for the whole session (affecting all the
other queries that don't really need it), or you set it before each
query. Which however sucks for a number of reasons, e.g. if you have a
slow query in the log, how do you know with what GUC values it was
executed? (You don't, and there's no way to find out.)
Exactly the same issues would affect this new GUC. It would be
impossible to use multiple CTEs in the query with different fencing
behavior, and it would be just as difficult to investigate.
So no more planner-affecting GUCs, please, particularly if we expect
regular users to use them.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 05/01/2017 09:05 AM, Tomas Vondra wrote:
On 05/01/2017 06:22 AM, Pavel Stehule wrote:
2017-05-01 1:21 GMT+02:00 Andres Freund <andres@anarazel.de
<mailto:andres@anarazel.de>>:On 2017-04-30 07:19:21 +0200, Pavel Stehule wrote:
why we cannot to introduce GUC option - enable_cteoptfence ?
Doesn't really solve the issue, and we've generally shied away
from GUCs
that influence behaviour after a few bad experiences. What if
you want
one CTE inlined, but another one not?It change behave in same sense like enable_nestloop, enable_hashjoin,
... with same limits.Those (and also the other enable_*) GUCs are a great example why we
should not use GUCs for tweaking planner behavior, except perhaps for
the purpose of investigation. It's an extremely blunt tool.You typically want to affect just a single node in the query plan
(say, one join), but those options don't allow you to do that. It's
all or nothing thing.Even if you're OK with affecting the whole query, it's a separate
control channel - it's not embedded in the query, the user has to set
it somehow. So you either set it for the whole session (affecting all
the other queries that don't really need it), or you set it before
each query. Which however sucks for a number of reasons, e.g. if you
have a slow query in the log, how do you know with what GUC values it
was executed? (You don't, and there's no way to find out.)Exactly the same issues would affect this new GUC. It would be
impossible to use multiple CTEs in the query with different fencing
behavior, and it would be just as difficult to investigate.So no more planner-affecting GUCs, please, particularly if we expect
regular users to use them.
+1
I still see users wanting to use the enable_foo settings in production.
Having had years of telling users that CTEs are an optimization fence it
doesn't seem at all nice for us to turn around and change our mind about
that. I have relied on it in the past and I'm sure I'm very far from
alone in that.
Maybe we could allow a "decorator" that would tell the planner the CTE
could be inlined?
WITH INLINE mycte AS ( ...)
cheers
andrew
--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 1 May 2017 at 21:22, Andrew Dunstan <andrew.dunstan@2ndquadrant.com> wrote:
Having had years of telling users that CTEs are an optimization fence it
doesn't seem at all nice for us to turn around and change our mind about
that. I have relied on it in the past and I'm sure I'm very far from
alone in that.Maybe we could allow a "decorator" that would tell the planner the CTE
could be inlined?WITH INLINE mycte AS ( ...)
I'd rather reverse that so we behave like other implementations by
default, and have extension syntax for our no-inline query hint. And
yes, that's what it is, because we'd only inline when we could produce
semantically equivalent results anyway.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, May 01, 2017 at 09:22:42AM -0400, Andrew Dunstan wrote:
So no more planner-affecting GUCs, please, particularly if we expect
regular users to use them.+1
I still see users wanting to use the enable_foo settings in production.
Having had years of telling users that CTEs are an optimization fence it
doesn't seem at all nice for us to turn around and change our mind about
that. I have relied on it in the past and I'm sure I'm very far from
alone in that.
You are certainly not alone, but I believe that in this you're missing
the vast majority (we hope) of PostgreSQL users. These are the users
who have yet to adopt PostgreSQL, and have the quite reasonable
expectation that ordinary-looking grammar *isn't* an optimization
fence.
Maybe we could allow a "decorator" that would tell the planner the CTE
could be inlined?WITH INLINE mycte AS ( ...)
+1 for a decorator, -1 for this one.
We already have an explicit optimization fence with OFFSET 0, and I
think making optimization fences explicit is how we should continue.
I'd be more in favor of something along the lines of
WITH FENCED /* Somewhat fuzzy. What fence? */
or
WITH AT_MOST_ONCE /* Clearer, but not super precise */
or
WITH UNIQUE_ATOMIC /* More descriptive, but not super clear without the docs in hand */
or something along that line.
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 05/01/2017 04:17 PM, David Fetter wrote:
Maybe we could allow a "decorator" that would tell the planner the CTE
could be inlined?WITH INLINE mycte AS ( ...)
+1 for a decorator, -1 for this one.
I am not sure I like decorators since this means adding an ad hoc query
hint directly into the SQL syntax which is something which I requires
serious consideration.
We already have an explicit optimization fence with OFFSET 0, and I
think making optimization fences explicit is how we should continue.
I'd be more in favor of something along the lines ofWITH FENCED /* Somewhat fuzzy. What fence? */
or
WITH AT_MOST_ONCE /* Clearer, but not super precise */
or
WITH UNIQUE_ATOMIC /* More descriptive, but not super clear without the docs in hand */or something along that line.
What about WITH MATERIALIZED, borrowing from the MySQL terminology
"materialized subquery"?
Andreas
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, May 1, 2017 at 7:26 AM, Andreas Karlsson <andreas@proxel.se> wrote:
On 05/01/2017 04:17 PM, David Fetter wrote:
Maybe we could allow a "decorator" that would tell the planner the CTE
could be inlined?
WITH INLINE mycte AS ( ...)
+1 for a decorator, -1 for this one.
I am not sure I like decorators since this means adding an ad hoc query
hint directly into the SQL syntax which is something which I requires
serious consideration.
Given that we already have
"
prevent optimization
"
syntax why do we need a decorator on the CTE?
We already have an explicit optimization fence with OFFSET 0, and I
think making optimization fences explicit is how we should continue.
I'd be more in favor of something along the lines ofWITH FENCED /* Somewhat fuzzy. What fence? */
or
WITH AT_MOST_ONCE /* Clearer, but not super precise */
or
WITH UNIQUE_ATOMIC /* More descriptive, but not super clear without
the docs in hand */or something along that line.
What about WITH MATERIALIZED, borrowing from the MySQL terminology
"materialized subquery"?
I would shorten that to "WITH MAT" except that I don't think that having
two way to introduce an optimization fence is worthwhile.
If we don't optimize SRFs-in-target-list, and thus avoid multiple function
evaluation for (composite_col).*, I believe a significant number of
intentional optimization fence uses will be safe but behavioral changes.
David J.
