Re: Improve rowcount estimate for UNNEST(column)

On Sat, 2023-11-25 at 09:19 -0800, Paul A Jungwirth wrote:
> Here is a patch to improve rowcount estimates for
> `UNNEST(some_array_column)`. Today we hard code this to 10, but we
> have statistics about array size, so it's easy to use them.
>
> I've seen plans where this would make a difference. If the array has
> only 1 or 2 elements, then overestimating the rowcount by 10 leads to
> unnecessary seqscans downstream. I can see how an underestimate would
> cause issues too.

The idea sounds good to me.
I didn't test or scrutinize the code, but I noticed that you use
EXPLAIN in the regression tests. I think that makes the tests vulnerable
to changes in the parameters or in the block size.
Perhaps you can write a function that runs EXPLAIN and extracts just the
row count. That should be stable enough.

Yours,
Laurenz Albe