Re: Planner picks n² query plan when available

Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com> writes:
> On Thu, 21 Nov 2024 at 13:03, Toto guyoyg <thomas(dot)bessou(at)hotmail(dot)fr> wrote:
>>
>> Offending O(n²) query:

> I disagree with the O(n^2) claims.

I think these cases actually are O(n^2). But I'm finding it hard
to care. What we have here is a straightforward way to write a
query versus a much-less-straightforward way --- indeed, a way
that isn't even syntactically legal per the SQL standard.
The straightforward way is already well optimized, and no reason has
been given why the much-less-straightforward way should be considered
preferable. So I'm not seeing why we should put our finite
development resources into optimizing the much-less-straightforward
way.

>> 4. The EXPLAIN ANALYZE output shows that the planner always supposes that arrays are of size 10, instead of using the estimated sizes of subqueries they are created from, or actual size provided as argument.

It's a little disingenuous to complain about bad estimates with
this test methodology: the test tables are never vacuumed or
analyzed. And since they're temporary, there's no hope of
autovacuum curing that oversight. It's not clear that having
done that would improve anything in this particular case,
but it's certainly not helping.

regards, tom lane