Re: cost_sort vs cost_agg

Thank you Ashutosh.

On Fri, Jan 15, 2021 at 7:18 PM Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>
wrote:

> On Thu, Jan 14, 2021 at 7:12 PM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:
> >
> > Currently the cost_sort doesn't consider the number of columns to sort,
> which
> > means the cost of SELECT * FROM t ORDER BY a; equals with the SELECT *
> > FROM t ORDER BY a, b; which is obviously wrong. The impact of this is
> when we
> > choose the plan for SELECT DISTINCT * FROM t ORDER BY c between:
> >
> > Sort
> > Sort Key: c
> > -> HashAggregate
> > Group Key: c, a, b, d, e, f, g, h, i, j, k, l, m, n
> >
> > and
> >
> > Unique
> > -> Sort
> > Sort Key: c, a, b, d, e, f, g, h, i, j, k, l, m, n
> >
> >
> > Since "Sort (c)" has the same cost as "Sort (c, a, b, d, e, f, g, h, i,
> j, k,
> > l, m, n)", and Unique node on a sorted input is usually cheaper than
> > HashAggregate, so the later one will win usually which might bad at many
> > places.
>
> I can imagine that HashAggregate + Sort will perform better if there
> are very few distinct rows but otherwise, Unique on top of Sort would
> be a better strategy since it doesn't need two operations.
>
>
Thanks for the hint, I will consider the distinct rows as a factor in the
next
patch.

> >
> > Optimizer chose HashAggregate with my patch, but it takes 6s. after set
> > enable_hashagg = off, it takes 2s.
>
> This example actually shows that using Unique is better than
> HashAggregate + Sort. May be you want to try with some data which has
> very few distinct rows.
>
>

--
Best Regards
Andy Fan (https://www.aliyun.com/)