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

Qingqing Zhou <zhouqq(dot)postgres(at)gmail(dot)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(dot)1430519406(at)sss(dot)pgh(dot)pa(dot)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