Hi,
when group by multi-columns, it will multiply all the distinct values together, and if one column is all null,
it also contributes 200 to the final estimate, and if the product is over the relation size, it will be clamp.
So the the value of the agg rel size is not correct, and impacts the upper path's cost estimate, and do not
give a good plan.
I debug some other queries and find this issue, but not sure if this issue is the root cause of my problem,
just open a thread here for discussion.
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Monday, October 26, 2020 10:37 PM
To: Zhenghua Lyu <zlyu@vmware.com>
Cc: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
Subject: Re: Should the function get_variable_numdistinct consider the case when stanullfrac is 1.0?
Zhenghua Lyu <zlyu@vmware.com> writes:
> It seems the function `get_variable_numdistinct` ignore the case when stanullfrac is 1.0:
I don't like this patch at all. What's the argument for having a special
case for this value? When would we ever get exactly 1.0 in practice?
regards, tom lane