Re: CTE optimization fence on the todo list?

On Fri, May 1, 2015 at 3:30 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Assuming that that sketch is accurate, it would take more code to provide
> a new user-visible knob to enable/disable the behavior than it would to
> implement the optimization, which makes me pretty much -1 on providing
> such a knob. We should either do it or not. If we do, people who want
> optimization fences should use the traditional "OFFSET 0" hack.
>
> (A possible compromise position would be to offer a new GUC to
> enable/disable the optimization globally; that would add only a reasonably
> small amount of control code, and people who were afraid of the change
> breaking their apps would probably want a global disable anyway.)

​+1 to both. The default should be to allow the user to choose between CTE
and inline subqueries for style reasons alone - as much as possible since
you cannot have a correlated CTE nor a recursive subquery.

Trust in the planner, the planner is good. If it isn't then requiring
OFFSET 0 as the only means to create an optimization fence seems reasonable.

I like the GUC as an cheap means to keep the status-quo for those who
desire it.

While the idea of overriding the status-quo on a per-query basis has some
appeal the apparent cost-benefit ratio doesn't seem convincing.

David J.​