Re: Planner picks n² query plan when available

Toto guyoyg <thomas(dot)bessou(at)hotmail(dot)fr> writes:
>> What we have here is a straightforward way to write a query versus a much-less-straightforward way [...] So I'm not seeing why we should put our finite development resources into optimizing the much-less-straightforward way.

> Ah, I should have explained this: this was meant as a pure-SQL reproducer for n² query plans with:

> ```sql
> SELECT id FROM indexed_table WHERE indexed_value = ANY ($1)
> ```
> where `$1` is an array bind parameter.

Ah, well, you should have said that was what you wanted. While the
existing EEOP_HASHED_SCALARARRAYOP logic only deals with a Const array
value, it seems to me that we could trivially let it use external
Params too. The case you presented would require being able to cope
with intra-query changes of the array value, but a Param coming from
outside the query doesn't entail that.

> However I have just attempted a reproducer for the `$1` variant (writing the corresponding application code...), and couldn't reproduce the inefficiency.

Depending on what you tested, you might have only seen the behavior
with a "custom plan" where the Param is actually replaced with a
Const. It would go bad again if the plan changed to generic.
I see that cost_qual_eval_walker does charge differently for
hashed than un-hashed ScalarArrayOp, so getting the planner to
incorrectly opt for a generic plan might require a bad estimate
of the array size, but I'm sure that's still possible.

> I also thought I saw that even `= ANY(ARRAY[1,2])` would lose the size to `10` so I assumed the same issue would happen with `$1` (array) but I tried to reproduce that as well and couldn't, so I must have been looking at a different planner node.

Or old code ... as Matthias mentioned, we improved that not so long
ago.

regards, tom lane