Re: BUG #18854: PostgreSQL chooses a suboptimal execution plan when using a specific WHERE filter

On 19/3/2025 14:34, Yoni Sade wrote:
> I would like to add that commenting out that WHERE clause doesn't work
> for all of this query variations so I've found another workaround for
> now ("LIMIT 1000000" in *bold*)
>
> to make the optimizer better estimate the cardinality of the subquery
> and change the plan to a better one:*
I wouldn't say it is the ultimate root of the problem, but using
generate_series, especially with non-constant parameters, seems harmful
for the query planning: we can't predict the number of tuples and don't
have any statistics on that set. So, your LIMIT works like a hint that
helps decide how massive a set of tuples it will generate and seems to
be the only option possible.

--
regards, Andrei Lepikhov