Re: Replace IN VALUES with ANY in WHERE clauses during optimization

Hi, Alexander!

On 06.03.2025 11:23, Alexander Korotkov wrote:
> Hi, Alena!
>
> On Sat, Mar 1, 2025 at 1:39 PM Alena Rybakina<a(dot)rybakina(at)postgrespro(dot)ru> wrote:
>> On 09.02.2025 18:38, Alexander Korotkov wrote:
>>> Also, aren't we too restrictive while requiring is_simple_values_sequence()?
>>> For instance, I believe cases like this (containing Var) could be transformed too.
>>>
>>> select * from t t1, lateral (select * from t t2 where t2.i in (values (t1.i), (1)));
>> I added it and attached a patch with diff file. To be honest, I didn't find queries except for var with volatile functions where the transform can't be applied.
>>
>> I removed the function volatility check that I added in the previous version, since we already check it in is_simple_values_sequence.
>>
>> I'm not sure about only cases where var can refer to something outside available_rels list but I couldn't come up with an example where that's possible, what do you think?
>>
>> Considering it again, I think we can't face problems like that because we don't work with join.
>>
>> I attached a diff file as a difference with the 3rd version of the patch, when we did not consider the values with var for transformation.
> I take detailed look at makeSAOPArrayExpr() function, which is much
> more complex than corresponding fragment from
> match_orclause_to_indexcol(). And I found it to be mostly wrong. We
> are working in post parse-analyze stage. That means it's too late to
> do type coercion or lookup operator by name. We have already all the
> catalog objects nailed down. In connection with that, second argument
> of OpExpr shouldn't be ignored as it might contain amrelevant type
> cast. I think I've fixed the most of them problems in the attached
> patchset.
>
>
I agree with your conclusion and changes.

--
Regards,
Alena Rybakina
Postgres Professional