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

> On Thu, Aug 13, 2015 at 2:49 AM, Kouhei Kaigai <kaigai(at)ak(dot)jp(dot)nec(dot)com> wrote:
> > In fact, cost of HashJoin underlying Sort node is:
> > -> Hash Join (cost=621264.91..752685.48 rows=1 width=132)
> >
> > On the other hands, NestedLoop on same place is:
> > -> Nested Loop (cost=0.00..752732.26 rows=1 width=132)
> >
> > Probably, small GUC adjustment may make optimizer to prefer HashJoin towards
> > these kind of queries.
>
> With that kind of discrepancy I doubt adjusting GUCs will be sufficient
>
> > Do you have a good idea?
>
> Do you have EXPLAIN ANALYZE from the plan that finishes? Are there any
> row estimates that are way off?
>
Yes, EXPLAIN ANALYZE is attached.

According to this, CTE year_total generates 384,208 rows. It is much smaller
than estimation (4.78M rows), however, filter's selectivity of CTE Scan was
not large as expectation.
For example, the deepest CTE Scan returns 37923 rows and 26314 rows, even though
40 rows were expected. On the next level, relations join between 11324 rows and
9952 rows, towards to estimation of 40rows x 8 rows.
If NestLoop is placed instead of HashJoin, it will make an explosion of the number
of loops.

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