Re: [HACKERS] CTE inlining

On 13 May 2017 at 08:39, Bruce Momjian wrote: > To summarize, it seems we have two options if we want to add fence > control to CTEs: > > 1. add INLINE to disable the CTE fence > 2. add MATERIALIZE to enable the CTE fence I think #1 is out of the question. What would we do

Re: [HACKERS] CTE inlining

>From a user's perspective: >I think most people prefer #2 because: > >* most users writing queries prefer #2 > >* most users assume full optimization and it seems natural to turn > > _off_ an optimization via a keyword > >* while some queries can be inlined, all queries can be

Re: [HACKERS] CTE inlining

On Fri, May 12, 2017 at 3:39 PM, Bruce Momjian wrote: > On Tue, May 9, 2017 at 05:14:19PM -0400, Tom Lane wrote: >> Ilya Shkuratov writes: >> > Ok, it seems that most people in discussion are agree that removing >> > optimization >> > fence is a right thing

Re: [HACKERS] CTE inlining

On Tue, May 9, 2017 at 05:14:19PM -0400, Tom Lane wrote: > Ilya Shkuratov writes: > > Ok, it seems that most people in discussion are agree that removing > > optimization > > fence is a right thing to do. > > Nonetheless I still hoping to discuss the algorithm and its

Re: [HACKERS] CTE inlining

> Just to se what other RDBMS are doing with CTEs; Look at slide > 31 here:  > https://www.percona.com/live/17/sites/default/files/slides/Recursive%20Query%20Throwdown.pdf That is taken from Markus Winand's post: https://twitter.com/MarkusWinand/status/852862475699707904 "Seems like MySQL

Re: [HACKERS] CTE inlining

På torsdag 11. mai 2017 kl. 23:37:27, skrev Yaroslav >: Ilya Shkuratov wrote > 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

Re: [HACKERS] CTE inlining

Ilya Shkuratov wrote > 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?) What about simple things like this? CREATE OR

Re: [HACKERS] CTE inlining

On 05/09/2017 04:14 PM, Tom Lane wrote: > > Well, TBH that is pre-judging what (if anything) is going to be changed > by a feature that we don't even have design consensus on, let alone a > patch for. I don't think that's an improvement or a good service to > our users; it's just promoting

Re: [HACKERS] CTE inlining

Ilya Shkuratov writes: > Ok, it seems that most people in discussion are agree that removing > optimization > fence is a right thing to do. > Nonetheless I still hoping to discuss the algorithm and its implementation. Yeah, so far we've mainly discussed whether to do that and

Re: [HACKERS] CTE inlining

"David G. Johnston" writes: > On Tue, May 9, 2017 at 12:36 PM, Tom Lane wrote: >> Also, considering that this behavior has been there since 8.4, >> I think it's sheerest chutzpah to claim that changing the docs in >> v10 would materially reduce the

Re: [HACKERS] CTE inlining

On Tue, May 9, 2017 at 12:36 PM, Tom Lane wrote: > Also, considering that this behavior has been there since 8.4, > I think it's sheerest chutzpah to claim that changing the docs in > v10 would materially reduce the backward-compatibility concerns > for whatever we might do

Re: [HACKERS] CTE inlining

"David G. Johnston" writes: > On Tue, May 9, 2017 at 12:15 PM, Peter Eisentraut < > peter.eisentr...@2ndquadrant.com> wrote: >> If we just tell them that the thing they might have relied on might go >> away, without a replacement to suggest, then we're just confusing

Re: [HACKERS] CTE inlining

On Tue, May 9, 2017 at 12:22 PM, David G. Johnston wrote: On Tue, May 9, 2017 at 12:15 PM, Peter Eisentraut < peter.eisentr...@2ndquadrant.com [peter.eisentr...@2ndquadrant.com] > wrote: On 5/5/17 08:43, David Rowley wrote: > How about we get the ball rolling on this

Re: [HACKERS] CTE inlining

On Tue, May 9, 2017 at 12:15 PM, Peter Eisentraut < peter.eisentr...@2ndquadrant.com> wrote: > On 5/5/17 08:43, David Rowley wrote: > > How about we get the ball rolling on this in v10 and pull that part > > out of the docs. If anything that'll buy us a bit more wiggle room to > > change this in

Re: [HACKERS] CTE inlining

On 5/5/17 08:43, David Rowley wrote: > How about we get the ball rolling on this in v10 and pull that part > out of the docs. If anything that'll buy us a bit more wiggle room to > change this in v11. > > I've attached a proposed patch. If we just tell them that the thing they might have relied

Re: [HACKERS] CTE inlining

Ok, it seems that most people in discussion are agree that removing optimization fence is a right thing to do. But so far the main topic was whether it worth to make "inlining" by default, and how we should enable it. Nonetheless I still hoping to discuss the algorithm and its implementation.

Re: [HACKERS] CTE inlining

On 05/03/2017 07:33 PM, Alvaro Herrera wrote: > 1) we switch unmarked CTEs as inlineable by default in pg11. What seems > likely to happen for a user that upgrades to pg11 is that 5 out of 10 > CTE-using queries are going to become faster than with pg10, and they > are going to be happy; 4 out of

Re: [HACKERS] CTE inlining

On 5 May 2017 at 14:04, Tom Lane wrote: > Craig Ringer writes: >> We're carefully maintaining this bizarre cognitive dissonance where we >> justify the need for using this as a planner hint at the same time as >> denying that we have a hint. That

Re: [HACKERS] CTE inlining

Thomas Kellerer wrote: >> 1) we switch unmarked CTEs as inlineable by default in pg11. > > +1 from me for option 1 +1 from me as well, FWIW. Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] CTE inlining

Hi, On 2017-05-04 19:57:21 -0700, Joe Conway wrote: > One thought, is that we treat a CTE in a similar way to foreign tables, > with the same set of push downs. A bit surprised about that suggestion - there seems to be very little similarity between the cases. What'd be the benefit of that?

Re: [HACKERS] CTE inlining

On 05/04/2017 07:04 PM, Tom Lane wrote: > Craig Ringer writes: >> We're carefully maintaining this bizarre cognitive dissonance where we >> justify the need for using this as a planner hint at the same time as >> denying that we have a hint. That makes it hard to

Re: [HACKERS] CTE inlining

Tom, all, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Craig Ringer writes: > > We're carefully maintaining this bizarre cognitive dissonance where we > > justify the need for using this as a planner hint at the same time as > > denying that we have a hint. That makes

Re: [HACKERS] CTE inlining

In my past life when I was faced with such debates I argued that the number of customers We are hoping to attract in the future is much bigger than the ones we risk offending. Doesn't mean I wanted to piss everyone off. Just that I didn't want to be held hostage by history. Cheers Serge PS: On

Re: [HACKERS] CTE inlining

Craig Ringer writes: > We're carefully maintaining this bizarre cognitive dissonance where we > justify the need for using this as a planner hint at the same time as > denying that we have a hint. That makes it hard to make progress here. > I think there's fear that

Re: [HACKERS] CTE inlining

On 5 May 2017 at 08:17, Joe Conway wrote: > On 05/04/2017 05:03 PM, Craig Ringer wrote: >> On 5 May 2017 02:52, "Tom Lane" wrote: >> I haven't been keeping close tabs either, but surely we still have >> to have >> the optimization fence in (at least) all these

Re: [HACKERS] CTE inlining

On 05/04/2017 05:03 PM, Craig Ringer wrote: > On 5 May 2017 02:52, "Tom Lane" wrote: > I haven't been keeping close tabs either, but surely we still have > to have > the optimization fence in (at least) all these cases: > > * CTE contains INSERT/UPDATE/DELETE > * CTE contains

Re: [HACKERS] CTE inlining

On 5 May 2017 06:04, "Andreas Karlsson" wrote: On 05/04/2017 06:22 PM, Andrew Dunstan wrote: > I wrote this query: > > select (json_populate_record(null::mytype, myjson)).* > from mytable; > > > It turned out that this was an order of magnitude faster: > > with r

Re: [HACKERS] CTE inlining

On 5 May 2017 02:52, "Tom Lane" wrote: Tomas Vondra writes: > On 5/4/17 8:03 PM, Joe Conway wrote: >>> I haven't been able to follow this incredibly long thread, so please >>> excuse me if way off base, but are we talking about that a CTE would

Re: [HACKERS] CTE inlining

On 05/04/2017 06:22 PM, Andrew Dunstan wrote: I wrote this query: select (json_populate_record(null::mytype, myjson)).* from mytable; It turned out that this was an order of magnitude faster: with r as ( select json_populate_record(null::mytype, myjson) as x

Re: [HACKERS] CTE inlining

On 05/05/17 06:39, Tomas Vondra wrote: On 5/4/17 8:03 PM, Joe Conway wrote: On 05/04/2017 10:56 AM, Andrew Dunstan wrote: On 05/04/2017 01:52 PM, Joe Conway wrote: On 05/04/2017 10:33 AM, Alvaro Herrera wrote: I'm not sure what your point is. We know that for some cases the optimization

Re: [HACKERS] CTE inlining

I haven't been keeping close tabs either, but surely we still have to have the optimization fence in (at least) all these cases: * CTE contains INSERT/UPDATE/DELETE * CTE contains SELECT FOR UPDATE/SHARE (else the set of rows that get locked might change) * CTE contains volatile functions I'm

Re: [HACKERS] CTE inlining

Tomas Vondra writes: > On 5/4/17 8:03 PM, Joe Conway wrote: >>> I haven't been able to follow this incredibly long thread, so please >>> excuse me if way off base, but are we talking about that a CTE would be >>> silently be rewritten as an inline expression

Re: [HACKERS] CTE inlining

On 5/4/17 8:03 PM, Joe Conway wrote: On 05/04/2017 10:56 AM, Andrew Dunstan wrote: On 05/04/2017 01:52 PM, Joe Conway wrote: On 05/04/2017 10:33 AM, Alvaro Herrera wrote: I'm not sure what your point is. We know that for some cases the optimization barrier semantics are useful, which is

Re: [HACKERS] CTE inlining

Alvaro Herrera writes: > I'm not sure what your point is. We know that for some cases the > optimization barrier semantics are useful, which is why the proposal is > to add a keyword to install one explicitely: > with materialized r as > ( >

Re: [HACKERS] CTE inlining

On 05/04/2017 10:56 AM, Andrew Dunstan wrote: > > > On 05/04/2017 01:52 PM, Joe Conway wrote: >> On 05/04/2017 10:33 AM, Alvaro Herrera wrote: >>> I'm not sure what your point is. We know that for some cases the >>> optimization barrier semantics are useful, which is why the proposal is >>> to

Re: [HACKERS] CTE inlining

On 5/4/17 7:56 PM, Andrew Dunstan wrote: On 05/04/2017 01:52 PM, Joe Conway wrote: On 05/04/2017 10:33 AM, Alvaro Herrera wrote: I'm not sure what your point is. We know that for some cases the optimization barrier semantics are useful, which is why the proposal is to add a keyword to

Re: [HACKERS] CTE inlining

On 05/04/2017 01:52 PM, Joe Conway wrote: > On 05/04/2017 10:33 AM, Alvaro Herrera wrote: >> I'm not sure what your point is. We know that for some cases the >> optimization barrier semantics are useful, which is why the proposal is >> to add a keyword to install one explicitely: >> >>

Re: [HACKERS] CTE inlining

On 05/04/2017 10:33 AM, Alvaro Herrera wrote: > I'm not sure what your point is. We know that for some cases the > optimization barrier semantics are useful, which is why the proposal is > to add a keyword to install one explicitely: > > with materialized r as > ( >

Re: [HACKERS] CTE inlining

On 05/04/2017 01:33 PM, Alvaro Herrera wrote: > Andrew Dunstan wrote: > >> Hadn't though about LATERAL, good point. Still, there will be other cases. > I'm not sure what your point is. We know that for some cases the > optimization barrier semantics are useful, which is why the proposal is > to

Re: [HACKERS] CTE inlining

Andrew Dunstan wrote: > Hadn't though about LATERAL, good point. Still, there will be other cases. I'm not sure what your point is. We know that for some cases the optimization barrier semantics are useful, which is why the proposal is to add a keyword to install one explicitely: with

Re: [HACKERS] CTE inlining

On 05/04/2017 12:34 PM, David G. Johnston wrote: > On Thu, May 4, 2017 at 9:22 AM, Andrew Dunstan > >wrote: > > > Yeah, the idea that this won't cause possibly significant pain is > quite wrong. Quite by accident I

Re: [HACKERS] CTE inlining

On 2017-05-04 09:34:19 -0700, David G. Johnston wrote: > On Thu, May 4, 2017 at 9:22 AM, Andrew Dunstan < > andrew.duns...@2ndquadrant.com> wrote: > > > > > Yeah, the idea that this won't cause possibly significant pain is quite > > wrong. Quite by accident I came across an example just this

Re: [HACKERS] CTE inlining

On Thu, May 4, 2017 at 9:22 AM, Andrew Dunstan < andrew.duns...@2ndquadrant.com> wrote: > > Yeah, the idea that this won't cause possibly significant pain is quite > wrong. Quite by accident I came across an example just this morning where > rewriting as a CTE makes a big improvement. > > I wrote

Re: [HACKERS] CTE inlining

On 05/04/2017 11:36 AM, Tom Lane wrote: > Serge Rielau writes: >>> On May 4, 2017, at 3:02 AM, Gavin Flower >>> wrote: >>> On 30/04/17 16:28, Tom Lane wrote: There's already a pretty large hill to climb here in the way of breaking

Re: [HACKERS] CTE inlining

Serge Rielau writes: >> On May 4, 2017, at 3:02 AM, Gavin Flower >> wrote: >> On 30/04/17 16:28, Tom Lane wrote: >>> There's already a pretty large hill to climb here in the way of >>> breaking peoples' expectations about CTEs being optimization

Re: [HACKERS] CTE inlining

> On May 4, 2017, at 3:02 AM, Gavin Flower > wrote: > > On 30/04/17 16:28, Tom Lane wrote: >> Craig Ringer writes: >>> - as you noted, it is hard to decide when it's worth inlining vs >>> materializing for CTE terms referenced more

Re: [HACKERS] CTE inlining

On 04/05/2017 08:34, Petr Jelinek wrote: > On 03/05/17 23:24, Merlin Moncure wrote: >> On Wed, May 3, 2017 at 12:33 PM, Alvaro Herrera >> wrote: >>> David Fetter wrote: >>> When we add a "temporary" GUC, we're taking on a gigantic burden. Either we support it

Re: [HACKERS] CTE inlining

On 30/04/17 16:28, Tom Lane wrote: Craig Ringer 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

Re: [HACKERS] CTE inlining

On 04/05/17 05:33, Alvaro Herrera wrote: > David Fetter wrote: > >> When we add a "temporary" GUC, we're taking on a gigantic burden. >> Either we support it forever somehow, or we put it on a deprecation >> schedule immediately and expect to be answering questions about it for >> years after

Re: [HACKERS] CTE inlining

> 1) we switch unmarked CTEs as inlineable by default in pg11. +1 from me for option 1 -- View this message in context: http://www.postgresql-archive.org/CTE-inlining-tp5958992p5959615.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers

Re: [HACKERS] CTE inlining

On 03/05/17 23:24, Merlin Moncure wrote: > On Wed, May 3, 2017 at 12:33 PM, Alvaro Herrera > wrote: >> David Fetter wrote: >> >>> When we add a "temporary" GUC, we're taking on a gigantic burden. >>> Either we support it forever somehow, or we put it on a deprecation >>>

Re: [HACKERS] CTE inlining

On 05/03/2017 05:24 PM, Merlin Moncure wrote: > On Wed, May 3, 2017 at 12:33 PM, Alvaro Herrera > wrote: >> David Fetter wrote: >> >>> When we add a "temporary" GUC, we're taking on a gigantic burden. >>> Either we support it forever somehow, or we put it on a

Re: [HACKERS] CTE inlining

On 04/05/17 05:33, Alvaro Herrera wrote: David Fetter wrote: When we add a "temporary" GUC, we're taking on a gigantic burden. Either we support it forever somehow, or we put it on a deprecation schedule immediately and expect to be answering questions about it for years after it's been

Re: [HACKERS] CTE inlining

On Wed, May 3, 2017 at 12:33 PM, Alvaro Herrera wrote: > David Fetter wrote: > >> When we add a "temporary" GUC, we're taking on a gigantic burden. >> Either we support it forever somehow, or we put it on a deprecation >> schedule immediately and expect to be answering

Re: [HACKERS] CTE inlining

On 5/3/17 9:54 PM, Andreas Karlsson wrote: On 05/03/2017 07:33 PM, Alvaro Herrera wrote: 1) we switch unmarked CTEs as inlineable by default in pg11. What seems likely to happen for a user that upgrades to pg11 is that 5 out of 10 CTE-using queries are going to become faster than with pg10,

Re: [HACKERS] CTE inlining

On 05/03/2017 07:33 PM, Alvaro Herrera wrote: 1) we switch unmarked CTEs as inlineable by default in pg11. What seems likely to happen for a user that upgrades to pg11 is that 5 out of 10 CTE-using queries are going to become faster than with pg10, and they are going to be happy; 4 out of five

Re: [HACKERS] CTE inlining

On Wed, May 03, 2017 at 02:33:05PM -0300, Alvaro Herrera wrote: > David Fetter wrote: > > > When we add a "temporary" GUC, we're taking on a gigantic burden. > > Either we support it forever somehow, or we put it on a deprecation > > schedule immediately and expect to be answering questions about

Re: [HACKERS] CTE inlining

2017-05-03 19:33 GMT+02:00 Alvaro Herrera : > David Fetter wrote: > > > When we add a "temporary" GUC, we're taking on a gigantic burden. > > Either we support it forever somehow, or we put it on a deprecation > > schedule immediately and expect to be answering questions

Re: [HACKERS] CTE inlining

David Fetter wrote: > When we add a "temporary" GUC, we're taking on a gigantic burden. > Either we support it forever somehow, or we put it on a deprecation > schedule immediately and expect to be answering questions about it for > years after it's been removed. > > -1 for the GUC. Absolutely.

Re: [HACKERS] CTE inlining

2017-05-03 18:54 GMT+02:00 David Fetter : > On Wed, May 03, 2017 at 01:27:38PM -0300, Claudio Freire wrote: > > On Wed, May 3, 2017 at 11:31 AM, David Fetter wrote: > > > Are you aware of such an ORM which both supports WITH and doesn't > > > also closely

Re: [HACKERS] CTE inlining

On Wed, May 03, 2017 at 01:27:38PM -0300, Claudio Freire wrote: > On Wed, May 3, 2017 at 11:31 AM, David Fetter wrote: > > Are you aware of such an ORM which both supports WITH and doesn't > > also closely track PostgreSQL development? I'm not. > > > > Even assuming that such a

Re: [HACKERS] CTE inlining

On Wed, May 3, 2017 at 11:31 AM, David Fetter wrote: > On Wed, May 03, 2017 at 11:26:27AM -0300, Claudio Freire wrote: >> On Wed, May 3, 2017 at 2:19 AM, Craig Ringer wrote: >> >> Or we will choose WITH MATERIALIZE, and then the users aware of >> >> the

Re: [HACKERS] CTE inlining

> I could tolerate telling people to use OFFSET 0 (and documenting it!) > as a workaround if we can't get something more friendly in. I agree with that. > If we go with WITH INLINE then we're likely not solving anything, because > most people will simply use WITH just like now, and will be

Re: [HACKERS] CTE inlining

On Wed, May 03, 2017 at 11:26:27AM -0300, Claudio Freire wrote: > On Wed, May 3, 2017 at 2:19 AM, Craig Ringer wrote: > >> Or we will choose WITH MATERIALIZE, and then the users aware of > >> the fencing (and using the CTEs for that purpose) will have to > >> modify the

Re: [HACKERS] CTE inlining

On Wed, May 3, 2017 at 2:19 AM, Craig Ringer wrote: >> Or we will choose WITH MATERIALIZE, and then the users aware of the fencing >> (and using the CTEs for that purpose) will have to modify the queries. But >> does adding MATERIALIZE quality as major query rewrite? > >

Re: [HACKERS] CTE inlining

On 05/02/2017 07:00 PM, Tomas Vondra wrote: > > > > I think we agree that: > > * Just removing the optimization fence and telling users to use OFFSET > 0 instead is a no-go, just like removing the fencing and not providing > any sensible replacement. > > * GUC is not the solution. yes > >

Re: [HACKERS] CTE inlining

On 3 May 2017 at 07:00, Tomas Vondra wrote: > I'm not sure what you mean by "jerking this out from users". Isn't most of > this thread about how to allow CTE inlining without hurting users > unnecessarily? He's referring to Andreas Karlsson

Re: [HACKERS] CTE inlining

On 5/2/17 11:23 PM, Merlin Moncure wrote: \On Tue, May 2, 2017 at 12:05 PM, Tomas Vondra wrote: On 5/2/17 6:34 PM, David Fetter wrote: On Tue, May 02, 2017 at 02:40:55PM +0200, Andreas Karlsson wrote: On 05/02/2017 04:38 AM, Craig Ringer wrote: On 1 May

Re: [HACKERS] CTE inlining

\On Tue, May 2, 2017 at 12:05 PM, Tomas Vondra wrote: > On 5/2/17 6:34 PM, David Fetter wrote: >> >> On Tue, May 02, 2017 at 02:40:55PM +0200, Andreas Karlsson wrote: >>> >>> On 05/02/2017 04:38 AM, Craig Ringer wrote: On 1 May 2017 at 22:26, Andreas

Re: [HACKERS] CTE inlining

On 5/2/17 6:34 PM, David Fetter wrote: On Tue, May 02, 2017 at 02:40:55PM +0200, Andreas Karlsson wrote: On 05/02/2017 04:38 AM, Craig Ringer wrote: On 1 May 2017 at 22:26, Andreas Karlsson wrote: >> ... I see some alternatives, none of them perfect. 1. Just remove the

Re: [HACKERS] CTE inlining

> > I get that people with gigantic PostgreSQL installations with > stringent performance requirements sometimes need to do odd things to > squeeze out the last few percentage points of performance. As the > people (well, at least the people close to the ground) at these > organizations are fully

Re: [HACKERS] CTE inlining

On Tue, May 02, 2017 at 02:40:55PM +0200, Andreas Karlsson wrote: > On 05/02/2017 04:38 AM, Craig Ringer wrote: > > On 1 May 2017 at 22:26, Andreas Karlsson wrote: > > > I am not sure I like decorators since this means adding an ad hoc query > > > hint > > > directly into the

Re: [HACKERS] CTE inlining

> Relevant posts where users get confused by our behaviour: > And Markus Winand's blog: http://modern-sql.com/feature/with/performance Databases generally obey this principle, although PostgreSQL represents a big exception and Besides PostgreSQL, all tested databases optimize

Re: [HACKERS] CTE inlining

On 5/2/17 4:44 PM, Andrew Dunstan wrote: On 05/02/2017 10:13 AM, Merlin Moncure wrote: On Sun, Apr 30, 2017 at 6:21 PM, Andres Freund wrote: On 2017-04-30 07:19:21 +0200, Pavel Stehule wrote: why we cannot to introduce GUC option - enable_cteoptfence ? Doesn't really

Re: [HACKERS] CTE inlining

On 05/02/2017 10:13 AM, Merlin Moncure wrote: > On Sun, Apr 30, 2017 at 6:21 PM, Andres Freund wrote: >> 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

Re: [HACKERS] CTE inlining

On Sun, Apr 30, 2017 at 6:21 PM, Andres Freund wrote: > 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

Re: [HACKERS] CTE inlining

On Mon, May 1, 2017 at 7:22 AM, Pavel Stehule wrote: > > > 2017-05-01 1:21 GMT+02:00 Andres Freund : >> >> On 2017-04-30 07:19:21 +0200, Pavel Stehule wrote: >> > why we cannot to introduce GUC option - enable_cteoptfence ? >> >> Doesn't really solve

Re: [HACKERS] CTE inlining

On 05/02/2017 04:38 AM, Craig Ringer wrote: On 1 May 2017 at 22:26, Andreas Karlsson wrote: 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. And mangling the

Re: [HACKERS] CTE inlining

On 2 May 2017 at 10:45, Craig Ringer wrote: > If we want fence behaviour, we should require people to declare their > desire for fence behaviour, rather than treating it as a sort of > hint-as-a-bug that we grandfather in because we're so desperate not to > admit we have

Re: [HACKERS] CTE inlining

On 1 May 2017 at 21:05, Tomas Vondra wrote: > On 05/01/2017 06:22 AM, Pavel Stehule wrote: >> >> >> >> 2017-05-01 1:21 GMT+02:00 Andres Freund > >: >> >> On 2017-04-30 07:19:21 +0200, Pavel Stehule wrote: >> >

Re: [HACKERS] CTE inlining

On Sun, Apr 30, 2017 at 5:54 PM, Tomas Vondra wrote: > 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 "Whaaat?" >

Re: [HACKERS] CTE inlining

On 1 May 2017 at 22:26, Andreas Karlsson wrote: > 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. And mangling the semantics of existing syntax doesn't?

Re: [HACKERS] CTE inlining

  30.04.2017, 08:58, "Craig Ringer" :  On 30 Apr. 2017 13:28, "Andres Freund" 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

Re: [HACKERS] CTE inlining

On 05/01/2017 10:17 AM, David Fetter wrote: > 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

Re: [HACKERS] CTE inlining

On Mon, May 1, 2017 at 7:43 AM, Andreas Karlsson wrote: > On 05/01/2017 04:33 PM, David G. Johnston wrote: > > On Mon, May 1, 2017 at 7:26 AM, Andreas Karlsson > I am not sure I like decorators since this means adding an ad hoc > > query hint

Re: [HACKERS] CTE inlining

On Mon, May 1, 2017 at 10:26 AM, Andreas Karlsson wrote: > What about WITH MATERIALIZED, borrowing from the MySQL terminology > "materialized subquery"? +1, you beat me to it.

Re: [HACKERS] CTE inlining

On 05/01/2017 04:33 PM, David G. Johnston wrote: > On Mon, May 1, 2017 at 7:26 AM, Andreas Karlsson 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

Re: [HACKERS] CTE inlining

On Mon, May 1, 2017 at 7:26 AM, Andreas Karlsson 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

Re: [HACKERS] CTE inlining

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

Re: [HACKERS] CTE inlining

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

Re: [HACKERS] CTE inlining

On 1 May 2017 at 21:22, Andrew Dunstan 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

Re: [HACKERS] CTE inlining

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 > >: >> >> On 2017-04-30 07:19:21 +0200, Pavel Stehule wrote: >> > why we cannot to introduce GUC

Re: [HACKERS] CTE inlining

On 05/01/2017 06:22 AM, Pavel Stehule wrote: 2017-05-01 1:21 GMT+02:00 Andres Freund >: 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,

Re: [HACKERS] CTE inlining

2017-05-01 1:21 GMT+02:00 Andres Freund : > 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

Re: [HACKERS] CTE inlining

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?

Re: [HACKERS] CTE inlining

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

Re: [HACKERS] CTE inlining

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 writes: > > > - as you noted, it is hard to decide when it's worth inlining vs > > > materializing for CTE terms referenced more than once. > > >

Re: [HACKERS] CTE inlining

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

Re: [HACKERS] CTE inlining

On 04/30/2017 06:28 AM, Tom Lane wrote: Craig Ringer 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".

Re: [HACKERS] CTE inlining

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

  1   2   >