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

Alena Rybakina <a.rybakina@postgrespro.ru>

From: Alena Rybakina <a.rybakina@postgrespro.ru>

To: Alexander Korotkov <aekorotkov@gmail.com>, Andrei Lepikhov <lepihov@gmail.com>

Cc: Ivan Kush <ivan.kush@tantorlabs.com>, pgsql-hackers <pgsql-hackers@postgresql.org>, Tom Lane <tgl@sss.pgh.pa.us>, Laurenz Albe <laurenz.albe@cybertec.at>

Date: 2025-02-28T11:48:47Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Stabilize regression test from c0962a113.
- 3ba2cdaa4541 18.0 landed
Convert 'x IN (VALUES ...)' to 'x = ANY ...' then appropriate
- c0962a113d1f 18.0 landed
Extract make_SAOP_expr() function from match_orclause_to_indexcol()
- d48d2e2dc8be 18.0 landed

Attachments

consider_vars.diff.no-cfbot (text/plain)
v4-0002-Add-an-implementation-of-the-x-IN-VALUES-to-x-ANY.patch (text/x-patch) patch v4-0002
v4-0001-Move-the-function-for-generating-ArrayExpr-to-anothe.patch (text/x-patch) patch v4-0001

Hi!

On 21.02.2025 00:09, Alena Rybakina wrote:
>
> Hi!
>
> 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'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?

-- 
Regards,
Alena Rybakina
Postgres Professional