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

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Thursday, August 27, 2015 9:03 AM
> To: Qingqing Zhou
> Cc: Kaigai Kouhei(海外 浩平); Greg Stark; PostgreSQL-development
> Subject: Re: [HACKERS] 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.
>
Hmm... My suggestion might not be reasonable. Sorry.

--
NEC Business Creation Division / PG-Strom Project
KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>