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
