Toto guyoyg <thomas.bes...@hotmail.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
