Re: Our trial to TPC-DS but optimizer made unreasonable plan

On 08/19/2015 01:32 PM, Tom Lane wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> On 08/18/2015 04:40 PM, Qingqing Zhou wrote:
>>> Attached please find the WIP patch and also the ANALYZE results.
>>> Notes: the patch may not directly apply to head as some network issue
>>> here so my Linux box can't talk to git server.
>
>> So, one of the things we previously mentioned is that currently many
>> users deliberately use CTEs as an optimization barrier in order to force
>> the planner. Given that, we need some kind of option to force the old
>> behavior; either SQL syntax or a GUC option.
>
> I think we already agreed what the syntax would be: ye good olde OFFSET 0
> in the subquery.
>
> We could have a GUC option too if people are sufficiently worried about
> it, but I think that the need for one hasn't really been proven.

Asking users to refactor their applications to add OFFSET 0 is a bit
painful, if we could take care of it via a backwards-compatibility GUC.
We have many users who are specifically using the CTE optimization
barrier to work around planner failures.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com