On Thu, Jan 14, 2021 at 7:12 PM Andy Fan <zhihui.fan1213@gmail.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.