Re: BUG #17564: Planner bug in combination of generate_series(), unnest() and ORDER BY

> On Wed, Aug 03, 2022 at 05:44:17PM -0400, Tom Lane wrote:
>
> Meanwhile, back at the question of whether db0d67db2 is buggy,
> it looks like that reduces to whether it was intentional that that
> made a large change in estimated sort costs.
>
> [...]
>
> So this plan is identical except for the sort costs, which seem to
> be about half of what they were in the older branches. If that was
> intentional, why didn't the commit message mention it? It's not
> exactly a minor change, and enable_group_by_reordering doesn't
> seem to have any effect on it.

I got curious about this one, as I haven't had a chance to look at the
final versions of the "group by reordering" feature. The commit message
indeed doesn't mention it directly, but there are changes inside
cost_tuplesort that are affecting this plan. The description of those
changes and the math behind are pretty neat (kudos to the author), but
to my surprise on the query from this thread the final result for
startup_costs is missing any ~ LOG2(tuples) term in comparison with the
original implementation. This happens because estimate_num_groups_incremental
returns estimation value 1 for number of groups, which sounds strange to
me. Not sure if there is anything wrong here, or I'm missing something,
but at least falling back to geometric mean as an estimation for nGroups
seems to produce results closer to the original and reduces discrepancy
between costs observed here.