Re: Improve rowcount estimate for UNNEST(column)

Paul Jungwirth <pj(at)illuminatedcomputing(dot)com> writes:
> Here is a patch with an improved test. With the old "10" estimate we get a Merge Join, but now that
> the planner can see there are only ~4 elements per array, we get a Nested Loop.

Pushed with minor editorialization. I ended up not using the test
case, because I was afraid it wouldn't be all that stable, and
code coverage shows that we are exercising the added code path
even without a bespoke test case.

> On 11/29/23 20:35, jian he wrote:
>>> I did a minor change. change estimate_array_length return type to double

> I'm not sure I want to change estimate_array_length from returning
> ints to returning doubles, since it's called in many places.

But your patch forces every one of those places to be touched anyway,
as a consequence of adding the "root" argument. I looked at the
callers and saw that every single one of them (in core anyway) ends up
using the result in a "double" rowcount calculation, so we're really
not buying anything by converting to integer and back again. There's
also a question of whether the number from DECHIST could be big enough
to overflow an int. Perhaps not given the current calculation method,
but since it's a float4 there's at least a theoretical risk. Hence,
I adopted Jian's suggestion.

One other point is that examine_variable is capable of succeeding
on things that aren't Vars, so I thought the restriction to Vars
was inappropriate.

regards, tom lane