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

On Wed, Mar 12, 2025 at 8:11 PM Alena Rybakina
<a(dot)rybakina(at)postgrespro(dot)ru> wrote:
> 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.

I've revised the patchset. Mostly comments/commit messages and minor
refactoring. One thing I have to fix: we must do
IncrementVarSublevelsUp() unconditionally for all expressions as Vars
could be deeper inside. Also, I've removed our subquery check
completely. Not sure if we need it at all. I'll further analyze
that.

------
Regards,
Alexander Korotkov
Supabase