Subject: pgsql: Convert 'x IN (VALUES ...)' to 'x = ANY ...' then appropriate

From: Alexander Korotkov <[email-masked]>
Date: Fri, 04 Apr 2025 13:17:00 +0000

Convert 'x IN (VALUES ...)' to 'x = ANY ...' then appropriate

This commit implements the automatic conversion of 'x IN (VALUES ...)' into
ScalarArrayOpExpr.  That simplifies the query tree, eliminating the appearance
of an unnecessary join.

Since VALUES describes a relational table, and the value of such a list is
a table row, the optimizer will likely face an underestimation problem due to
the inability to estimate cardinality through MCV statistics.  The cardinality
evaluation mechanism can work with the array inclusion check operation.
If the array is small enough (< 100 elements), it will perform a statistical
evaluation element by element.

We perform the transformation in the convert_ANY_sublink_to_join() if VALUES
RTE is proper and the transformation is convertible.  The conversion is only
possible for operations on scalar values, not rows.  Also, we currently
support the transformation only when it ends up with a constant array.
Otherwise, the evaluation of non-hashed SAOP might be slower than the
corresponding Hash Join with VALUES.

Discussion: https://postgr.es/m/0184212d-1248-4f1f-a42d-f5cb1c1976d2%40tantorlabs.com
Author: Alena Rybakina <[email-masked]>
Author: Andrei Lepikhov <[email-masked]>
Reviewed-by: Ivan Kush <[email-masked]>
Reviewed-by: Alexander Korotkov <[email-masked]>

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/c0962a113d1f2f94cb7222a7ca025a67e9ce3860

Modified Files
--------------
src/backend/optimizer/plan/subselect.c    |  80 ++++++++
src/backend/optimizer/prep/prepjointree.c |  12 ++
src/backend/optimizer/util/clauses.c      |  14 +-
src/include/optimizer/subselect.h         |   3 +
src/test/regress/expected/subselect.out   | 308 ++++++++++++++++++++++++++++++
src/test/regress/sql/subselect.sql        | 100 ++++++++++
6 files changed, 512 insertions(+), 5 deletions(-)

---

From: Melanie Plageman <[email-masked]>
Date: Fri, 4 Apr 2025 11:47:10 -0400

> ScalarArrayOpExpr.  That simplifies the query tree, eliminating the appearance

I haven't looked at this patch, but it seems likely that it is related
to the recent failures I noticed in CI in the subselect test when the
regress suite is run by 002_pg_upgrade.pl

diff --strip-trailing-cr -U3
C:/cirrus/src/test/regress/expected/subselect.out
C:/cirrus/build/testrun/pg_upgrade/002_pg_upgrade/data/results/subselect.out
--- C:/cirrus/src/test/regress/expected/subselect.out 2025-04-04
14:44:17.637206600 +0000
+++ C:/cirrus/build/testrun/pg_upgrade/002_pg_upgrade/data/results/subselect.out
2025-04-04 14:47:20.358393500 +0000
@@ -2769,15 +2769,16 @@
 EXPLAIN (COSTS OFF)
 SELECT c.oid,c.relname FROM pg_class c JOIN pg_am a USING (oid)
 WHERE c.oid IN (VALUES (1), (2));
-                          QUERY PLAN
----------------------------------------------------------------
- Hash Join
-   Hash Cond: (a.oid = c.oid)
-   ->  Seq Scan on pg_am a
-   ->  Hash
-         ->  Index Scan using pg_class_oid_index on pg_class c
-               Index Cond: (oid = ANY ('{1,2}'::oid[]))
-(6 rows)
+                       QUERY PLAN
+---------------------------------------------------------
+ Merge Join
+   Merge Cond: (c.oid = a.oid)
+   ->  Index Scan using pg_class_oid_index on pg_class c
+         Index Cond: (oid = ANY ('{1,2}'::oid[]))
+   ->  Sort
+         Sort Key: a.oid
+         ->  Seq Scan on pg_am a
+(7 rows)

---

From: Alexander Korotkov <[email-masked]>
Date: Fri, 4 Apr 2025 19:03:21 +0300

> regress suite is run by 002_pg_upgrade.pl

Thank you for pointing.  I'm looking right now.

------
Regards,
Alexander Korotkov
Supabase

---

From: David Rowley <[email-masked]>
Date: Mon, 7 Apr 2025 21:15:03 +1200

> +++ C:/cirrus/build/testrun/pg_upgrade/002_pg_upgrade/data/results/subselect.out

Are these failures from patches applied to master prior to 3ba2cdaa?

David

---

From: Alexander Korotkov <[email-masked]>
Date: Mon, 7 Apr 2025 16:08:49 +0300

Hi, David!

> Are these failures from patches applied to master prior to 3ba2cdaa?

Yes, these failures appears before 3ba2cdaa.  Tom committed 3ba2cdaa
to fix the problem before I get into it [1].

Links.
1. https://www.postgresql.org/message-id/srnuqlttuimzmvoulhsrbgvj4vnul6b65osswvua7sfkqsvmuy%40yg7apybpxp34

------
Regards,
Alexander Korotkov
Supabase

---

From: Melanie Plageman <[email-masked]>
Date: Mon, 7 Apr 2025 09:50:42 -0400

> Are these failures from patches applied to master prior to 3ba2cdaa?

Yea, my email was held in moderation for days. I guess cross-posting is
flagged. I thought I saw people regularly cc pgsql-hackers when replying to
pgsql-committers, but I guess not.

---

From: Tom Lane <[email-masked]>
Date: Mon, 07 Apr 2025 11:04:09 -0400

> pgsql-committers, but I guess not.

Yeah, that used to be common practice, but we made a policy change
to discourage cross-posting a few years ago.  Too many newbies
cross-posting questions to umpteen lists.

			regards, tom lane