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

Qingqing Zhou <zhouqq.postgres@gmail.com> writes:
> Above two queries essentially the same, but the second one is a
> non-optimal plan. The reason is that how my patch works: it put a
> substitution in front of SS_process_ctes():

>    /*
>   * If there is a WITH list, process each WITH query and build an initplan
> ! * SubPlan structure for it. Before we process ctes, try to subsitute with
> ! * subqueries to benefits from global optimization.
>   */
>   if (parse->cteList)
> + {
> + substitute_ctes_with_subqueries(root);
>   SS_process_ctes(root);
> + }

> AFAICS, the substitution only handles cteList within a query block, so
> it does not go across the subquery boundary. I can see this is an
> issue but can't see a nice way to fix it. Anybody has some recipe?

It seems like you're doing this in fundamentally the wrong place.

What I had in mind in <38448.1430519406@sss.pgh.pa.us> was to convert CTEs
into plain subqueries during the prepjointree phase, either just before
or as part of the pull_up_subqueries pass (since you'd want the converted
subquery to be flattened if possible).  If you do it later than that,
then you'll have to reinvent a whole bunch of wheels to provide behavior
similar to regular subquery optimization.
        regards, tom lane