v1-in_values_to_any.patch

From 805498afb64f092aad29e7b3734eecf266f98b16 Mon Sep 17 00:00:00 2001
From: Ivan Kush <ivan.kush@tantorlabs.com>
Date: Thu, 3 Oct 2024 17:30:00 +0000
Subject: [PATCH] In VALUES to ANY

The `VALUES` in the `IN VALUES` construct is replaced with with an array of values when `VALUES` contains 1 column. In the end it will be replaced with ANY by the existing function makeA_Expr (src/backend/nodes/makefuncs.c)
This improves performance, especially if the values are small.


How realized

`VALUES` statement corresponds to `values_clause` nonterminal symbol in gram.y, where it's parsed to `SelectStmt` node.

`IN` is parsed in `a_expr` symbol. When it contains `VALUES` with 1 column, parser extracts data from `SelectStmt` and passes it to function call `makeSimpleA_Expr` where simple `A_Expr` is created.

Later during optimizations of parser tree this `A_Expr` will be transformed to `ArrayExpr` (already realized in Postgres)


Author: Ivan Kush <ivan.kush@tantorlabs.com>
Author: Vadim Yacenko <vadim.yacenko@tantorlabs.com>
Author: Alexander Simonov <alexander.simonov@tantorlabs.com>

---
 src/backend/nodes/Makefile                    |   1 +
 src/backend/nodes/makefuncs_2.c               |  47 +++
 src/backend/parser/gram.y                     |  22 +-
 src/backend/utils/misc/guc_tables.c           |  11 +
 src/backend/utils/misc/postgresql.conf.sample |   1 +
 src/include/nodes/makefuncs_2.h               |  28 ++
 src/test/regress/expected/create_view.out     |  12 +-
 src/test/regress/expected/in_values.out       | 344 ++++++++++++++++++
 src/test/regress/expected/sysviews.out        |  51 +--
 src/test/regress/parallel_schedule            |   5 +
 src/test/regress/sql/in_values.sql            | 252 +++++++++++++
 11 files changed, 736 insertions(+), 38 deletions(-)
 create mode 100644 src/backend/nodes/makefuncs_2.c
 create mode 100644 src/include/nodes/makefuncs_2.h
 create mode 100644 src/test/regress/expected/in_values.out
 create mode 100644 src/test/regress/sql/in_values.sql

diff --git a/src/backend/nodes/Makefile b/src/backend/nodes/Makefile
index 66bbad8..74ea3c2 100644
--- a/src/backend/nodes/Makefile
+++ b/src/backend/nodes/Makefile
@@ -21,6 +21,7 @@ OBJS = \
 	extensible.o \
 	list.o \
 	makefuncs.o \
+	makefuncs_2.o \
 	multibitmapset.o \
 	nodeFuncs.o \
 	outfuncs.o \
diff --git a/src/backend/nodes/makefuncs_2.c b/src/backend/nodes/makefuncs_2.c
new file mode 100644
index 0000000..f16c673
--- /dev/null
+++ b/src/backend/nodes/makefuncs_2.c
@@ -0,0 +1,47 @@
+#include "postgres.h"
+
+#include "catalog/pg_class.h"
+#include "catalog/pg_type.h"
+#include "nodes/makefuncs_2.h"
+#include "nodes/nodeFuncs.h"
+#include "nodes/nodes.h"
+#include "nodes/pg_list.h"
+#include "utils/errcodes.h"
+#include "utils/lsyscache.h"
+
+
+bool enable_convert_in_values_to_any = true;
+
+Node *
+convert_in_values_clause_to_array(Node *lexpr, SelectStmt *stmt, int location)
+{
+	ListCell   *lc;
+	List *vals = NIL;
+	List *sublist = NIL;
+
+	if(!enable_convert_in_values_to_any) {
+		return NULL;
+	}
+
+	if(!stmt->valuesLists)
+	{
+		return NULL;
+	}
+	sublist = (List *) linitial(stmt->valuesLists);
+	if(list_length(sublist) != 1)
+	{
+		return NULL;
+	}
+
+	foreach(lc, stmt->valuesLists)
+	{
+		void *val;
+		sublist = (List *) lfirst(lc);
+		Assert(list_length(sublist) > 0);
+		val = linitial(sublist);
+		vals = (!vals) ? list_make1(val) : lappend(vals, val);
+	}
+
+	// same func as in gram.y for IN with expr_list
+	return (Node *) makeSimpleA_Expr(AEXPR_IN, "=", lexpr, (Node *) vals, location);
+}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index bca627c..ae5f406 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -57,6 +57,7 @@
 #include "commands/trigger.h"
 #include "gramparse.h"
 #include "nodes/makefuncs.h"
+#include "nodes/makefuncs_2.h"
 #include "nodes/nodeFuncs.h"
 #include "parser/parser.h"
 #include "storage/lmgr.h"
@@ -15105,13 +15106,20 @@ a_expr:		c_expr									{ $$ = $1; }
 					{
 						/* generate foo = ANY (subquery) */
 						SubLink	   *n = (SubLink *) $3;
-
-						n->subLinkType = ANY_SUBLINK;
-						n->subLinkId = 0;
-						n->testexpr = $1;
-						n->operName = NIL;		/* show it's IN not = ANY */
-						n->location = @2;
-						$$ = (Node *) n;
+						$$ = NULL;
+						if(IsA(n->subselect, SelectStmt))
+						{
+							$$ = convert_in_values_clause_to_array($1, (SelectStmt *) n->subselect, @2);
+						}
+						if(!$$)
+						{
+							n->subLinkType = ANY_SUBLINK;
+							n->subLinkId = 0;
+							n->testexpr = $1;
+							n->operName = NIL;		/* show it's IN not = ANY */
+							n->location = @2;
+							$$ = (Node *) n;
+						}
 					}
 					else
 					{
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index dc222d9..a808f6d 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -49,6 +49,7 @@
 #include "libpq/auth.h"
 #include "libpq/libpq.h"
 #include "libpq/scram.h"
+#include "nodes/makefuncs_2.h"
 #include "nodes/queryjumble.h"
 #include "optimizer/cost.h"
 #include "optimizer/geqo.h"
@@ -790,6 +791,16 @@ struct config_bool ConfigureNamesBool[] =
 		true,
 		NULL, NULL, NULL
 	},
+	{
+		{"enable_convert_in_values_to_any", PGC_USERSET, QUERY_TUNING_METHOD,
+			gettext_noop("Enables the planner's optimization of converting IN VALUES clauses to ANY clauses."),
+			NULL,
+			GUC_EXPLAIN
+		},
+		&enable_convert_in_values_to_any,
+		true,
+		NULL, NULL, NULL
+	},
 	{
 		{"enable_indexonlyscan", PGC_USERSET, QUERY_TUNING_METHOD,
 			gettext_noop("Enables the planner's use of index-only-scan plans."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 667e0dc..7b09000 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -396,6 +396,7 @@
 #enable_gathermerge = on
 #enable_hashagg = on
 #enable_hashjoin = on
+#enable_convert_in_values_to_any = on
 #enable_incremental_sort = on
 #enable_indexscan = on
 #enable_indexonlyscan = on
diff --git a/src/include/nodes/makefuncs_2.h b/src/include/nodes/makefuncs_2.h
new file mode 100644
index 0000000..f710cda
--- /dev/null
+++ b/src/include/nodes/makefuncs_2.h
@@ -0,0 +1,28 @@
+/*-------------------------------------------------------------------------
+ *
+ * makefuncs_2.h
+ *	  prototypes for the creator functions of various nodes
+ *
+ *
+ * Portions Copyright (c) 1996-2023, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * src/include/nodes/makefuncs_2.h
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef MAKEFUNC_TANTOR_H
+#define MAKEFUNC_TANTOR_H
+
+#include "nodes/execnodes.h"
+#include "nodes/parsenodes.h"
+
+extern PGDLLIMPORT bool enable_convert_in_values_to_any;
+
+extern A_Expr *makeSimpleA_Expr(A_Expr_Kind kind, char *name,
+								Node *lexpr, Node *rexpr, int location);
+
+extern Node *
+convert_in_values_clause_to_array(Node *lexpr, SelectStmt *stmt, int location);
+
+#endif							/* MAKEFUNC_2_H */
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index f551624..51f3e1f 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -1852,12 +1852,12 @@ select * from tt17v;
 (5 rows)
 
 select pg_get_viewdef('tt17v', true);
-               pg_get_viewdef                
----------------------------------------------
-  SELECT q1,                                +
-     q2                                     +
-    FROM int8_tbl i                         +
-   WHERE (i.* IN ( VALUES (i.*::int8_tbl)));
+   pg_get_viewdef   
+--------------------
+  SELECT q1,       +
+     q2            +
+    FROM int8_tbl i+
+   WHERE i.* = i.*;
 (1 row)
 
 select * from int8_tbl i where i.* in (values(i.*::int8_tbl));
diff --git a/src/test/regress/expected/in_values.out b/src/test/regress/expected/in_values.out
new file mode 100644
index 0000000..9760423
--- /dev/null
+++ b/src/test/regress/expected/in_values.out
@@ -0,0 +1,344 @@
+-- The `IN` operator in SQL is often used in conjunction with a `VALUES` expression
+-- to specify a set of values for filtering or matching. Below are various cases
+-- and examples where the `VALUES` expression is followed by the `IN` operator:
+--------------------------------------------------------------
+----- Basic Filtering with `IN` and `VALUES` -----------------
+--------------------------------------------------------------
+----- VALUES (int), (int), etc.
+--------------------------------------------------------------
+CREATE TABLE tbl (a INT);
+INSERT INTO tbl VALUES (2);
+SELECT * FROM tbl
+WHERE a IN VALUES (1), (2), (3);
+ERROR:  syntax error at or near "VALUES"
+LINE 2: WHERE a IN VALUES (1), (2), (3);
+                   ^
+SELECT * FROM tbl
+WHERE a IN (VALUES (1), (2), (3));
+ a 
+---
+ 2
+(1 row)
+
+SELECT * FROM tbl
+WHERE a IN (((((VALUES (1), (2), (3))))));
+ a 
+---
+ 2
+(1 row)
+
+EXPLAIN SELECT * FROM tbl
+WHERE a IN (VALUES (1), (2), (3));
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Seq Scan on tbl  (cost=0.00..45.06 rows=38 width=4)
+   Filter: (a = ANY ('{1,2,3}'::integer[]))
+(2 rows)
+
+-- reference plan, without VALUES
+EXPLAIN SELECT * FROM tbl
+WHERE a IN ((1), (2), (3));
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Seq Scan on tbl  (cost=0.00..45.06 rows=38 width=4)
+   Filter: (a = ANY ('{1,2,3}'::integer[]))
+(2 rows)
+
+DROP TABLE tbl;
+--------------------------------------------------------------
+----- Basic Filtering with `IN` and `VALUES` -----------------
+--------------------------------------------------------------
+----- Basic VALUES (int, int, etc.)
+--------------------------------------------------------------
+CREATE TABLE tbl (a INT);
+INSERT INTO tbl VALUES (2);
+SELECT * FROM tbl
+WHERE a IN VALUES (1, 2, 3);
+ERROR:  syntax error at or near "VALUES"
+LINE 2: WHERE a IN VALUES (1, 2, 3);
+                   ^
+SELECT * FROM tbl
+WHERE a IN (VALUES (1, 2, 3));
+ERROR:  subquery has too many columns
+LINE 2: WHERE a IN (VALUES (1, 2, 3));
+                ^
+SELECT * FROM tbl
+WHERE a IN (((((VALUES (1, 2, 3))))));
+ERROR:  subquery has too many columns
+LINE 2: WHERE a IN (((((VALUES (1, 2, 3))))));
+                ^
+DROP TABLE tbl;
+--------------------------------------------------------------
+----- Enable/disable patch -----------------------------------
+--------------------------------------------------------------
+----- enable_convert_in_values_to_any. By default it's true.
+--------------------------------------------------------------
+CREATE TABLE tbl (a INT);
+INSERT INTO tbl VALUES (2);
+-- by default is ON
+EXPLAIN SELECT * FROM tbl
+WHERE a IN (VALUES (1), (2), (3));
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Seq Scan on tbl  (cost=0.00..45.06 rows=38 width=4)
+   Filter: (a = ANY ('{1,2,3}'::integer[]))
+(2 rows)
+
+SET enable_convert_in_values_to_any TO OFF;
+EXPLAIN SELECT * FROM tbl
+WHERE a IN (VALUES (1), (2), (3));
+                               QUERY PLAN                                
+-------------------------------------------------------------------------
+ Hash Semi Join  (cost=0.08..42.69 rows=38 width=4)
+   Hash Cond: (tbl.a = "*VALUES*".column1)
+   ->  Seq Scan on tbl  (cost=0.00..35.50 rows=2550 width=4)
+   ->  Hash  (cost=0.04..0.04 rows=3 width=4)
+         ->  Values Scan on "*VALUES*"  (cost=0.00..0.04 rows=3 width=4)
+(5 rows)
+
+SET enable_convert_in_values_to_any TO ON;
+EXPLAIN SELECT * FROM tbl
+WHERE a IN (VALUES (1), (2), (3));
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Seq Scan on tbl  (cost=0.00..45.06 rows=38 width=4)
+   Filter: (a = ANY ('{1,2,3}'::integer[]))
+(2 rows)
+
+DROP TABLE tbl;
+--------------------------------------------------------------
+----- Using VARCHAR type -------------------------------------
+--------------------------------------------------------------
+CREATE TABLE tbl (a VARCHAR);
+INSERT INTO tbl VALUES ('A');
+SELECT a FROM tbl
+WHERE a IN (VALUES ('A'), ('B'), ('C'));
+ a 
+---
+ A
+(1 row)
+
+SELECT a FROM tbl
+WHERE a IN ((((VALUES ('A'), ('B'), ('C')))));
+ a 
+---
+ A
+(1 row)
+
+EXPLAIN SELECT * FROM tbl
+WHERE a IN (VALUES ('A'), ('B'), ('C'));
+                      QUERY PLAN                      
+------------------------------------------------------
+ Seq Scan on tbl  (cost=0.00..28.70 rows=20 width=32)
+   Filter: ((a)::text = ANY ('{A,B,C}'::text[]))
+(2 rows)
+
+DROP TABLE tbl;
+--------------------------------------------------------------
+----- Using custom TYPE
+--------------------------------------------------------------
+CREATE TYPE MY_TUPLE AS (
+    first INT,
+    second INT
+);
+CREATE TABLE tbl (a MY_TUPLE);
+INSERT INTO tbl (a) VALUES
+    (ROW(1, 10)),
+    (ROW(2, 20)),
+    (ROW(3, 30));
+SELECT * FROM tbl;
+   a    
+--------
+ (1,10)
+ (2,20)
+ (3,30)
+(3 rows)
+
+EXPLAIN
+SELECT * FROM tbl
+WHERE a IN (ROW(1, 10), ROW(2, 20));
+                          QUERY PLAN                          
+--------------------------------------------------------------
+ Seq Scan on tbl  (cost=0.00..30.40 rows=14 width=32)
+   Filter: ((a = '(1,10)'::record) OR (a = '(2,20)'::record))
+(2 rows)
+
+EXPLAIN
+SELECT * FROM tbl
+WHERE a IN (VALUES (ROW(1, 10)), (ROW(2, 20)));
+                          QUERY PLAN                          
+--------------------------------------------------------------
+ Seq Scan on tbl  (cost=0.00..30.40 rows=14 width=32)
+   Filter: ((a = '(1,10)'::record) OR (a = '(2,20)'::record))
+(2 rows)
+
+SELECT * FROM tbl
+WHERE a IN (VALUES (ROW(1, 10)), (ROW(2, 20)));
+   a    
+--------
+ (1,10)
+ (2,20)
+(2 rows)
+
+DROP TABLE tbl;
+DROP TYPE MY_TUPLE;
+--------------------------------------------------------------
+----- Using different types ----------------------------------
+--------------------------------------------------------------
+----- Must be errors
+--------------------------------------------------------------
+CREATE TABLE tbl (a INT);
+INSERT INTO tbl VALUES (1);
+SELECT a FROM tbl
+WHERE a IN ((1), ('B'), ('\x0f8d'::bytea));
+ERROR:  invalid input syntax for type integer: "B"
+LINE 2: WHERE a IN ((1), ('B'), ('\x0f8d'::bytea));
+                          ^
+SELECT a FROM tbl
+WHERE a IN (VALUES (1), ('B'), ('\x0f8d'::bytea));
+ERROR:  invalid input syntax for type integer: "B"
+LINE 2: WHERE a IN (VALUES (1), ('B'), ('\x0f8d'::bytea));
+                                 ^
+EXPLAIN SELECT * FROM tbl
+WHERE a IN (VALUES (1), ('B'), ('\x0f8d'::bytea));
+ERROR:  invalid input syntax for type integer: "B"
+LINE 2: WHERE a IN (VALUES (1), ('B'), ('\x0f8d'::bytea));
+                                 ^
+DROP TABLE tbl;
+--------------------------------------------------------------
+----- Filtering with Subquery and `VALUES` -------------------
+--------------------------------------------------------------
+CREATE TABLE tbl (a INT);
+INSERT INTO tbl VALUES (2);
+SELECT * FROM tbl
+WHERE a IN (SELECT * FROM (VALUES (1), (2), (3)) AS foo);
+ a 
+---
+ 2
+(1 row)
+
+DROP TABLE tbl;
+--------------------------------------------------------------
+----- Combination with `EXISTS` ------------------------------
+--------------------------------------------------------------
+CREATE TABLE tbl (a INT);
+INSERT INTO tbl VALUES (2);
+SELECT * FROM tbl
+WHERE EXISTS (
+    SELECT 1 FROM (VALUES (1), (2), (3)) AS allowed_depts(dept_id)
+    WHERE a = allowed_depts.dept_id
+);
+ a 
+---
+ 2
+(1 row)
+
+DROP TABLE tbl;
+--------------------------------------------------------------
+----- Using `IN` with `VALUES` for multi-column matching. ----
+--------------------------------------------------------------
+CREATE TABLE tbl (a INT, b INT);
+INSERT INTO tbl VALUES (2, 20);
+SELECT * FROM tbl
+WHERE (a, b) IN (VALUES (1, 10), (1, 20), (2, 20), (3, 30));
+ a | b  
+---+----
+ 2 | 20
+(1 row)
+
+EXPLAIN SELECT * FROM tbl
+WHERE a IN (VALUES (1, 10), (1, 20), (2, 20), (3, 30));
+ERROR:  subquery has too many columns
+LINE 2: WHERE a IN (VALUES (1, 10), (1, 20), (2, 20), (3, 30));
+                ^
+DROP TABLE tbl;
+--------------------------------------------------------------
+----- `VALUES` in CTE and `IN` Clause ------------------------
+--------------------------------------------------------------
+CREATE TABLE tbl (a INT);
+INSERT INTO tbl VALUES (1), (2), (3), (4);
+WITH allowed_products AS (
+    VALUES (1), (2), (3)
+)
+SELECT * FROM tbl
+WHERE a IN (SELECT * FROM allowed_products);
+ a 
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+EXPLAIN WITH allowed_products AS (
+    VALUES (1), (2), (3)
+)
+SELECT * FROM tbl
+WHERE a IN (SELECT * FROM allowed_products);
+                               QUERY PLAN                                
+-------------------------------------------------------------------------
+ Hash Semi Join  (cost=0.08..42.69 rows=38 width=4)
+   Hash Cond: (tbl.a = "*VALUES*".column1)
+   ->  Seq Scan on tbl  (cost=0.00..35.50 rows=2550 width=4)
+   ->  Hash  (cost=0.04..0.04 rows=3 width=4)
+         ->  Values Scan on "*VALUES*"  (cost=0.00..0.04 rows=3 width=4)
+(5 rows)
+
+DROP TABLE tbl;
+--------------------------------------------------------------
+----- Dynamic Lists with `VALUES` and `IN` -------------------
+--------------------------------------------------------------
+CREATE TABLE tbl (a INT, b INT);
+INSERT INTO tbl VALUES (2, 20);
+SELECT * FROM tbl
+WHERE a IN (VALUES (1), (2), (3));
+ a | b  
+---+----
+ 2 | 20
+(1 row)
+
+EXPLAIN SELECT * FROM tbl
+WHERE a IN (VALUES (1), (2), (3));
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Seq Scan on tbl  (cost=0.00..41.08 rows=34 width=8)
+   Filter: (a = ANY ('{1,2,3}'::integer[]))
+(2 rows)
+
+DROP TABLE tbl;
+--------------------------------------------------------------
+----- `VALUES` with Nested `IN` Clauses ----------------------
+--------------------------------------------------------------
+CREATE TABLE tbl (a INT, b INT);
+INSERT INTO tbl VALUES (1, 10), (1, 20), (2, 20), (3, 30);
+CREATE TABLE tbl2 (c INT, d INT);
+INSERT INTO tbl2 VALUES (1, 30), (1, 40), (3, 50);
+SELECT * FROM tbl
+WHERE a IN (
+    SELECT c FROM tbl2
+    WHERE d IN (VALUES (40), (50))
+);
+ a | b  
+---+----
+ 1 | 10
+ 1 | 20
+ 3 | 30
+(3 rows)
+
+EXPLAIN SELECT * FROM tbl
+WHERE a IN (
+    SELECT c FROM tbl2
+    WHERE d IN (VALUES (40), (50))
+);
+                               QUERY PLAN                               
+------------------------------------------------------------------------
+ Hash Join  (cost=38.80..80.23 rows=260 width=8)
+   Hash Cond: (tbl.a = tbl2.c)
+   ->  Seq Scan on tbl  (cost=0.00..32.60 rows=2260 width=8)
+   ->  Hash  (cost=38.53..38.53 rows=22 width=4)
+         ->  HashAggregate  (cost=38.31..38.53 rows=22 width=4)
+               Group Key: tbl2.c
+               ->  Seq Scan on tbl2  (cost=0.00..38.25 rows=23 width=4)
+                     Filter: (d = ANY ('{40,50}'::integer[]))
+(8 rows)
+
+DROP TABLE tbl;
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 2176a54..7f2edf8 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -132,31 +132,32 @@ select count(*) = 0 as ok from pg_stat_wal_receiver;
 -- This is to record the prevailing planner enable_foo settings during
 -- a regression test run.
 select name, setting from pg_settings where name like 'enable%';
-              name              | setting 
---------------------------------+---------
- enable_async_append            | on
- enable_bitmapscan              | on
- enable_gathermerge             | on
- enable_group_by_reordering     | on
- enable_hashagg                 | on
- enable_hashjoin                | on
- enable_incremental_sort        | on
- enable_indexonlyscan           | on
- enable_indexscan               | on
- enable_material                | on
- enable_memoize                 | on
- enable_mergejoin               | on
- enable_nestloop                | on
- enable_parallel_append         | on
- enable_parallel_hash           | on
- enable_partition_pruning       | on
- enable_partitionwise_aggregate | off
- enable_partitionwise_join      | off
- enable_presorted_aggregate     | on
- enable_seqscan                 | on
- enable_sort                    | on
- enable_tidscan                 | on
-(22 rows)
+              name               | setting 
+---------------------------------+---------
+ enable_async_append             | on
+ enable_bitmapscan               | on
+ enable_convert_in_values_to_any | on
+ enable_gathermerge              | on
+ enable_group_by_reordering      | on
+ enable_hashagg                  | on
+ enable_hashjoin                 | on
+ enable_incremental_sort         | on
+ enable_indexonlyscan            | on
+ enable_indexscan                | on
+ enable_material                 | on
+ enable_memoize                  | on
+ enable_mergejoin                | on
+ enable_nestloop                 | on
+ enable_parallel_append          | on
+ enable_parallel_hash            | on
+ enable_partition_pruning        | on
+ enable_partitionwise_aggregate  | off
+ enable_partitionwise_join       | off
+ enable_presorted_aggregate      | on
+ enable_seqscan                  | on
+ enable_sort                     | on
+ enable_tidscan                  | on
+(23 rows)
 
 -- There are always wait event descriptions for various types.  InjectionPoint
 -- may be present or absent, depending on history since last postmaster start.
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index f53a526..99d6078 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -29,6 +29,11 @@ test: strings md5 numerology point lseg line box path polygon circle date time t
 # horology depends on date, time, timetz, timestamp, timestamptz, interval
 # ----------
 test: geometry horology tstypes regex type_sanity opr_sanity misc_sanity comments expressions unicode xid mvcc database
+ 
+# ----------
+# Optimization tests
+# ----------
+test: in_values
 
 # ----------
 # Load huge amounts of data
diff --git a/src/test/regress/sql/in_values.sql b/src/test/regress/sql/in_values.sql
new file mode 100644
index 0000000..e9d2a52
--- /dev/null
+++ b/src/test/regress/sql/in_values.sql
@@ -0,0 +1,252 @@
+-- The `IN` operator in SQL is often used in conjunction with a `VALUES` expression
+-- to specify a set of values for filtering or matching. Below are various cases
+-- and examples where the `VALUES` expression is followed by the `IN` operator:
+
+
+--------------------------------------------------------------
+----- Basic Filtering with `IN` and `VALUES` -----------------
+--------------------------------------------------------------
+----- VALUES (int), (int), etc.
+--------------------------------------------------------------
+
+CREATE TABLE tbl (a INT);
+INSERT INTO tbl VALUES (2);
+
+SELECT * FROM tbl
+WHERE a IN VALUES (1), (2), (3);
+
+SELECT * FROM tbl
+WHERE a IN (VALUES (1), (2), (3));
+
+SELECT * FROM tbl
+WHERE a IN (((((VALUES (1), (2), (3))))));
+
+EXPLAIN SELECT * FROM tbl
+WHERE a IN (VALUES (1), (2), (3));
+
+-- reference plan, without VALUES
+EXPLAIN SELECT * FROM tbl
+WHERE a IN ((1), (2), (3));
+
+DROP TABLE tbl;
+
+
+--------------------------------------------------------------
+----- Basic Filtering with `IN` and `VALUES` -----------------
+--------------------------------------------------------------
+----- Basic VALUES (int, int, etc.)
+--------------------------------------------------------------
+
+CREATE TABLE tbl (a INT);
+INSERT INTO tbl VALUES (2);
+
+SELECT * FROM tbl
+WHERE a IN VALUES (1, 2, 3);
+
+SELECT * FROM tbl
+WHERE a IN (VALUES (1, 2, 3));
+
+SELECT * FROM tbl
+WHERE a IN (((((VALUES (1, 2, 3))))));
+
+DROP TABLE tbl;
+
+
+--------------------------------------------------------------
+----- Enable/disable patch -----------------------------------
+--------------------------------------------------------------
+----- enable_convert_in_values_to_any. By default it's true.
+--------------------------------------------------------------
+
+CREATE TABLE tbl (a INT);
+INSERT INTO tbl VALUES (2);
+
+-- by default is ON
+EXPLAIN SELECT * FROM tbl
+WHERE a IN (VALUES (1), (2), (3));
+
+SET enable_convert_in_values_to_any TO OFF;
+EXPLAIN SELECT * FROM tbl
+WHERE a IN (VALUES (1), (2), (3));
+
+SET enable_convert_in_values_to_any TO ON;
+EXPLAIN SELECT * FROM tbl
+WHERE a IN (VALUES (1), (2), (3));
+
+DROP TABLE tbl;
+
+
+--------------------------------------------------------------
+----- Using VARCHAR type -------------------------------------
+--------------------------------------------------------------
+
+CREATE TABLE tbl (a VARCHAR);
+INSERT INTO tbl VALUES ('A');
+
+SELECT a FROM tbl
+WHERE a IN (VALUES ('A'), ('B'), ('C'));
+
+SELECT a FROM tbl
+WHERE a IN ((((VALUES ('A'), ('B'), ('C')))));
+
+EXPLAIN SELECT * FROM tbl
+WHERE a IN (VALUES ('A'), ('B'), ('C'));
+
+DROP TABLE tbl;
+
+
+--------------------------------------------------------------
+----- Using custom TYPE
+--------------------------------------------------------------
+CREATE TYPE MY_TUPLE AS (
+    first INT,
+    second INT
+);
+
+CREATE TABLE tbl (a MY_TUPLE);
+INSERT INTO tbl (a) VALUES
+    (ROW(1, 10)),
+    (ROW(2, 20)),
+    (ROW(3, 30));
+SELECT * FROM tbl;
+
+EXPLAIN
+SELECT * FROM tbl
+WHERE a IN (ROW(1, 10), ROW(2, 20));
+
+EXPLAIN
+SELECT * FROM tbl
+WHERE a IN (VALUES (ROW(1, 10)), (ROW(2, 20)));
+
+SELECT * FROM tbl
+WHERE a IN (VALUES (ROW(1, 10)), (ROW(2, 20)));
+
+DROP TABLE tbl;
+DROP TYPE MY_TUPLE;
+
+--------------------------------------------------------------
+----- Using different types ----------------------------------
+--------------------------------------------------------------
+----- Must be errors
+--------------------------------------------------------------
+
+CREATE TABLE tbl (a INT);
+INSERT INTO tbl VALUES (1);
+
+SELECT a FROM tbl
+WHERE a IN ((1), ('B'), ('\x0f8d'::bytea));
+
+SELECT a FROM tbl
+WHERE a IN (VALUES (1), ('B'), ('\x0f8d'::bytea));
+
+EXPLAIN SELECT * FROM tbl
+WHERE a IN (VALUES (1), ('B'), ('\x0f8d'::bytea));
+
+DROP TABLE tbl;
+
+--------------------------------------------------------------
+----- Filtering with Subquery and `VALUES` -------------------
+--------------------------------------------------------------
+
+CREATE TABLE tbl (a INT);
+INSERT INTO tbl VALUES (2);
+
+SELECT * FROM tbl
+WHERE a IN (SELECT * FROM (VALUES (1), (2), (3)) AS foo);
+
+DROP TABLE tbl;
+
+
+--------------------------------------------------------------
+----- Combination with `EXISTS` ------------------------------
+--------------------------------------------------------------
+
+CREATE TABLE tbl (a INT);
+INSERT INTO tbl VALUES (2);
+
+SELECT * FROM tbl
+WHERE EXISTS (
+    SELECT 1 FROM (VALUES (1), (2), (3)) AS allowed_depts(dept_id)
+    WHERE a = allowed_depts.dept_id
+);
+
+DROP TABLE tbl;
+
+
+--------------------------------------------------------------
+----- Using `IN` with `VALUES` for multi-column matching. ----
+--------------------------------------------------------------
+
+CREATE TABLE tbl (a INT, b INT);
+INSERT INTO tbl VALUES (2, 20);
+
+SELECT * FROM tbl
+WHERE (a, b) IN (VALUES (1, 10), (1, 20), (2, 20), (3, 30));
+
+EXPLAIN SELECT * FROM tbl
+WHERE a IN (VALUES (1, 10), (1, 20), (2, 20), (3, 30));
+
+DROP TABLE tbl;
+
+
+--------------------------------------------------------------
+----- `VALUES` in CTE and `IN` Clause ------------------------
+--------------------------------------------------------------
+
+CREATE TABLE tbl (a INT);
+INSERT INTO tbl VALUES (1), (2), (3), (4);
+
+WITH allowed_products AS (
+    VALUES (1), (2), (3)
+)
+SELECT * FROM tbl
+WHERE a IN (SELECT * FROM allowed_products);
+
+EXPLAIN WITH allowed_products AS (
+    VALUES (1), (2), (3)
+)
+SELECT * FROM tbl
+WHERE a IN (SELECT * FROM allowed_products);
+
+DROP TABLE tbl;
+
+
+--------------------------------------------------------------
+----- Dynamic Lists with `VALUES` and `IN` -------------------
+--------------------------------------------------------------
+
+CREATE TABLE tbl (a INT, b INT);
+INSERT INTO tbl VALUES (2, 20);
+
+SELECT * FROM tbl
+WHERE a IN (VALUES (1), (2), (3));
+
+EXPLAIN SELECT * FROM tbl
+WHERE a IN (VALUES (1), (2), (3));
+
+DROP TABLE tbl;
+
+
+--------------------------------------------------------------
+----- `VALUES` with Nested `IN` Clauses ----------------------
+--------------------------------------------------------------
+
+CREATE TABLE tbl (a INT, b INT);
+INSERT INTO tbl VALUES (1, 10), (1, 20), (2, 20), (3, 30);
+
+CREATE TABLE tbl2 (c INT, d INT);
+INSERT INTO tbl2 VALUES (1, 30), (1, 40), (3, 50);
+
+SELECT * FROM tbl
+WHERE a IN (
+    SELECT c FROM tbl2
+    WHERE d IN (VALUES (40), (50))
+);
+
+EXPLAIN SELECT * FROM tbl
+WHERE a IN (
+    SELECT c FROM tbl2
+    WHERE d IN (VALUES (40), (50))
+);
+
+DROP TABLE tbl;
-- 
2.30.2