0001-Introduce-VALUES-ARRAY-transformation.patch

From 7aa3c64e8a38d61880fe80073095233e74f2044b Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <lepihov@gmail.com>
Date: Fri, 4 Oct 2024 15:54:49 +0700
Subject: [PATCH] Introduce VALUES -> ARRAY transformation.

Transform accidentally appearing 'x IN (VALUES, ...) expressions to
x IN 'ANY ...'. The second variant is better because it lets the planner avoid
one more unnecessary SEMI JOIN operator.

This form of expression usually appears in auto-generated queries as a corner
case of searching an object in a set of other ones when the object is described
by only one property.

Let this unusual optimisation be in the core because the planner would only
spend a few more cycles without this construct.
---
 src/backend/optimizer/plan/subselect.c    | 184 ++++++++++
 src/backend/optimizer/prep/prepjointree.c |   9 +-
 src/include/optimizer/subselect.h         |   2 +
 src/test/regress/expected/subselect.out   | 388 ++++++++++++++++++++++
 src/test/regress/sql/subselect.sql        | 138 ++++++++
 5 files changed, 720 insertions(+), 1 deletion(-)

diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 6d003cc8e5..09ca109cc8 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -34,6 +34,7 @@
 #include "optimizer/subselect.h"
 #include "parser/parse_relation.h"
 #include "rewrite/rewriteManip.h"
+#include "utils/array.h"
 #include "utils/builtins.h"
 #include "utils/lsyscache.h"
 #include "utils/syscache.h"
@@ -1216,6 +1217,189 @@ inline_cte_walker(Node *node, inline_cte_walker_context *context)
 }
 
 
+/*
+ * The function traverses the tree looking for elements of type var.
+ * If it finds it, it returns true.
+ */
+static bool
+values_simplicity_check_walker(Node *node, void *ctx)
+{
+	if (node == NULL)
+	{
+		return false;
+	}
+	else if(IsA(node, Var))
+		return true;
+	else if(IsA(node, Query))
+		return query_tree_walker((Query *) node,
+								 values_simplicity_check_walker,
+								 (void*) ctx,
+								 QTW_EXAMINE_RTES_BEFORE);
+
+	return expression_tree_walker(node, values_simplicity_check_walker,
+								  (void *) ctx);
+}
+
+/*
+ * Designed in analogy with is_simple_values
+ */
+static bool
+is_simple_values_sequence(Query *query)
+{
+	RangeTblEntry *rte;
+
+	/* In theory removing (altering) part of restrictions */
+	if (list_length(query->targetList) > 1 ||
+		query->limitCount != NULL || query->limitOffset != NULL ||
+		query->sortClause != NIL ||
+		list_length(query->rtable) != 1)
+		return false;
+
+	rte = linitial_node(RangeTblEntry,query->rtable);
+
+	/* Permanent restrictions */
+	if (rte->rtekind != RTE_VALUES ||
+		list_length(rte->values_lists) <= 1 ||
+		contain_volatile_functions((Node *) query))
+		return false;
+
+	/*
+	 * Go to the query tree to be sure that expression doesn't
+	 * have any Var type elements.
+	 */
+	return !expression_tree_walker((Node *) (rte->values_lists),
+								   values_simplicity_check_walker,
+								   NULL);
+}
+
+/*
+ * Transform appropriate testexpr and const VALUES expression to SaOpExpr.
+ *
+ * Return NULL, if transformation isn't allowed.
+ */
+ScalarArrayOpExpr *
+convert_VALUES_to_ANY(Query *query, Node *testexpr)
+{
+	RangeTblEntry	   *rte;
+	Node			   *leftop;
+	Oid					consttype;
+	int16				typlen;
+	bool				typbyval;
+	char				typalign;
+	ArrayType		   *arrayConst;
+	Oid					arraytype;
+	Node			   *arrayNode;
+	Oid					matchOpno;
+	Form_pg_operator	operform;
+	ScalarArrayOpExpr  *saopexpr;
+	ListCell		   *lc;
+	Oid					inputcollid;
+	HeapTuple			opertup;
+	bool				have_param = false;
+	List			   *consts = NIL;
+
+	/* Extract left side of SAOP from test epression */
+
+	if (!IsA(testexpr, OpExpr) ||
+		list_length(((OpExpr *) testexpr)->args) != 2 ||
+		!is_simple_values_sequence(query))
+		return NULL;
+
+	rte = linitial_node(RangeTblEntry,query->rtable);
+	leftop = linitial(((OpExpr *) testexpr)->args);
+	matchOpno = ((OpExpr *) testexpr)->opno;
+	inputcollid = linitial_oid(rte->colcollations);
+
+	foreach (lc, rte->values_lists)
+	{
+		List *elem = lfirst(lc);
+		Node *value = linitial(elem);
+
+		value = eval_const_expressions(NULL, value);
+
+		if (!IsA(value, Const))
+			have_param = true;
+		else if (((Const *) value)->constisnull)
+			/*
+			 * Constant expression isn't converted because it is a NULL.
+			 * NULLS just not supported by the construct_array routine.
+			 */
+			return NULL;
+
+		consts = lappend(consts, value);
+
+	}
+	Assert(list_length(consts) == list_length(rte->values_lists));
+
+	consttype = linitial_oid(rte->coltypes);
+	Assert(list_length(rte->coltypes) == 1 && OidIsValid(consttype));
+	arraytype = get_array_type(linitial_oid(rte->coltypes));
+	if (!OidIsValid(arraytype))
+		return NULL;
+
+	/* TODO: remember parameters */
+	if (have_param)
+	{
+		/*
+		 * We need to construct an ArrayExpr given we have Param's not just
+		 * Const's.
+		 */
+		ArrayExpr  *arrayExpr = makeNode(ArrayExpr);
+
+		/* array_collid will be set by parse_collate.c */
+		arrayExpr->element_typeid = consttype;
+		arrayExpr->array_typeid = arraytype;
+		arrayExpr->multidims = false;
+		arrayExpr->elements = consts;
+		arrayExpr->location = -1;
+
+		arrayNode = (Node *) arrayExpr;
+	}
+	else
+	{
+		int			i = 0;
+		ListCell   *lc1;
+		Datum	   *elems;
+
+		/* Direct creation of Const array */
+
+		elems = (Datum *) palloc(sizeof(Datum) * list_length(consts));
+		foreach (lc1, consts)
+			elems[i++] = lfirst_node(Const, lc1)->constvalue;
+
+		get_typlenbyvalalign(consttype, &typlen, &typbyval, &typalign);
+
+		arrayConst = construct_array(elems, i, consttype,
+									 typlen, typbyval, typalign);
+		arrayNode = (Node *) makeConst(arraytype, -1, inputcollid,
+									   -1, PointerGetDatum(arrayConst),
+									   false, false);
+		pfree(elems);
+	}
+
+	/* Lookup for operator to fetch necessary information for the SAOP node */
+	opertup = SearchSysCache1(OPEROID, ObjectIdGetDatum(matchOpno));
+	if (!HeapTupleIsValid(opertup))
+		elog(ERROR, "cache lookup failed for operator %u", matchOpno);
+
+	operform = (Form_pg_operator) GETSTRUCT(opertup);
+
+	/* Build the SAOP expression node */
+	saopexpr = makeNode(ScalarArrayOpExpr);
+	saopexpr->opno = matchOpno;
+	saopexpr->opfuncid = operform->oprcode;
+	saopexpr->hashfuncid = InvalidOid;
+	saopexpr->negfuncid = InvalidOid;
+	saopexpr->useOr = true;
+	saopexpr->inputcollid = inputcollid;
+	saopexpr->args = list_make2(leftop, arrayNode);
+	saopexpr->location = -1;
+
+	ReleaseSysCache(opertup);
+
+	return saopexpr;
+}
+
 /*
  * convert_ANY_sublink_to_join: try to convert an ANY SubLink to a join
  *
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 4d7f972caf..a52d879641 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -633,8 +633,15 @@ pull_up_sublinks_qual_recurse(PlannerInfo *root, Node *node,
 		/* Is it a convertible ANY or EXISTS clause? */
 		if (sublink->subLinkType == ANY_SUBLINK)
 		{
+			ScalarArrayOpExpr *saop;
+
+			if ((saop = convert_VALUES_to_ANY((Query *) sublink->subselect,
+											  sublink->testexpr)) != NULL)
+				/* VALUES sequence was simplified. Nothing more to do here, */
+				return (Node *) saop;
+
 			if ((j = convert_ANY_sublink_to_join(root, sublink,
-												 available_rels1)) != NULL)
+													  available_rels1)) != NULL)
 			{
 				/* Yes; insert the new join node into the join tree */
 				j->larg = *jtlink1;
diff --git a/src/include/optimizer/subselect.h b/src/include/optimizer/subselect.h
index c20bd9924b..530e22321d 100644
--- a/src/include/optimizer/subselect.h
+++ b/src/include/optimizer/subselect.h
@@ -17,6 +17,8 @@
 #include "nodes/plannodes.h"
 
 extern void SS_process_ctes(PlannerInfo *root);
+extern ScalarArrayOpExpr *convert_VALUES_to_ANY(Query *query,
+												Node *testexpr);
 extern JoinExpr *convert_ANY_sublink_to_join(PlannerInfo *root,
 											 SubLink *sublink,
 											 Relids available_rels);
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 2d35de3fad..f42a689abe 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -2134,3 +2134,391 @@ ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);
                                                Filter: (odd = b.odd)
 (16 rows)
 
+--
+-- Test VALUES to ARRAY (VtA) transformation
+--
+EXPLAIN (COSTS OFF)
+SELECT * FROM onek, (VALUES(147, 'RFAAAA'), (931, 'VJAAAA')) AS v (i, j)
+  WHERE onek.unique1 = v.i AND onek.stringu1 = v.j;
+                         QUERY PLAN                          
+-------------------------------------------------------------
+ Nested Loop
+   ->  Values Scan on "*VALUES*"
+   ->  Index Scan using onek_stringu1 on onek
+         Index Cond: (stringu1 = ("*VALUES*".column2)::text)
+         Filter: ("*VALUES*".column1 = unique1)
+(5 rows)
+
+SELECT * FROM onek,
+  (VALUES ((SELECT i FROM
+    (VALUES(10000), (2), (389), (1000), (2000), ((SELECT 10029))) AS foo(i)
+    ORDER BY i ASC LIMIT 1))) bar (i)
+  WHERE onek.unique1 = bar.i;
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | i 
+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+---
+       2 |     326 |   0 |    2 |   2 |      2 |       2 |        2 |           2 |         2 |        2 |   4 |    5 | CAAAAA   | OMAAAA   | OOOOxx  | 2
+(1 row)
+
+-- Forbid VTA transformation for a composite argument
+EXPLAIN (COSTS OFF)
+SELECT * FROM onek
+  WHERE (unique1,ten) IN (VALUES (1,1), (20,0), (99,9), (17,99))
+  ORDER BY unique1;
+                           QUERY PLAN                            
+-----------------------------------------------------------------
+ Sort
+   Sort Key: onek.unique1
+   ->  Nested Loop
+         ->  HashAggregate
+               Group Key: "*VALUES*".column1, "*VALUES*".column2
+               ->  Values Scan on "*VALUES*"
+         ->  Index Scan using onek_unique1 on onek
+               Index Cond: (unique1 = "*VALUES*".column1)
+               Filter: ("*VALUES*".column2 = ten)
+(9 rows)
+
+-- Values to Array transformation
+EXPLAIN (COSTS OFF)
+SELECT * FROM onek
+    WHERE unique1 IN (VALUES(10000), (2), (389), (1000), (2000), (10029))
+    ORDER BY unique1;
+                                       QUERY PLAN                                       
+----------------------------------------------------------------------------------------
+ Sort
+   Sort Key: unique1
+   ->  Bitmap Heap Scan on onek
+         Recheck Cond: (unique1 = ANY ('{10000,2,389,1000,2000,10029}'::integer[]))
+         ->  Bitmap Index Scan on onek_unique1
+               Index Cond: (unique1 = ANY ('{10000,2,389,1000,2000,10029}'::integer[]))
+(6 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM onek
+    WHERE unique1 IN (VALUES(1200), (1));
+                         QUERY PLAN                          
+-------------------------------------------------------------
+ Bitmap Heap Scan on onek
+   Recheck Cond: (unique1 = ANY ('{1200,1}'::integer[]))
+   ->  Bitmap Index Scan on onek_unique1
+         Index Cond: (unique1 = ANY ('{1200,1}'::integer[]))
+(4 rows)
+
+-- TODO: Recursively evaluate constant queries.
+EXPLAIN (COSTS OFF)
+SELECT * FROM onek
+  WHERE unique1 IN (SELECT x*x FROM (VALUES(1200), (1)) AS x(x));
+                                QUERY PLAN                                 
+---------------------------------------------------------------------------
+ Nested Loop
+   ->  Unique
+         ->  Sort
+               Sort Key: (("*VALUES*".column1 * "*VALUES*".column1))
+               ->  Values Scan on "*VALUES*"
+   ->  Index Scan using onek_unique1 on onek
+         Index Cond: (unique1 = ("*VALUES*".column1 * "*VALUES*".column1))
+(7 rows)
+
+-- transformation doesn't apply because values RTE exists in subquery
+EXPLAIN (COSTS OFF)
+select * FROM onek,
+  (VALUES ((select i FROM
+    (VALUES(10000), (2), (389), (1000), (2000), (10029)) as foo(i)
+    ORDER BY i ASC LIMIT 1))) bar (i)
+  WHERE onek.unique1 = bar.i;
+                   QUERY PLAN                    
+-------------------------------------------------
+ Index Scan using onek_unique1 on onek
+   Index Cond: (unique1 = (InitPlan 2).col1)
+   InitPlan 1
+     ->  Limit
+           ->  Sort
+                 Sort Key: "*VALUES*".column1
+                 ->  Values Scan on "*VALUES*"
+   InitPlan 2
+     ->  Limit
+           ->  Sort
+                 Sort Key: "*VALUES*_1".column1
+                 ->  Values Scan on "*VALUES*_1"
+(12 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT unique1, stringu1 FROM onek WHERE stringu1::name IN (VALUES('RFAAAA'), ('VJAAAA'));
+                       QUERY PLAN                       
+--------------------------------------------------------
+ Seq Scan on onek
+   Filter: (stringu1 = ANY ('{RFAAAA,VJAAAA}'::text[]))
+(2 rows)
+
+-- transformation doesn't apply because of type differences
+EXPLAIN (COSTS OFF)
+SELECT unique1, stringu1 FROM onek WHERE stringu1::text IN (VALUES('RFAAAA'), ('VJAAAA'));
+                           QUERY PLAN                           
+----------------------------------------------------------------
+ Seq Scan on onek
+   Filter: ((stringu1)::text = ANY ('{RFAAAA,VJAAAA}'::text[]))
+(2 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * from onek WHERE unique1 in (VALUES(1200::bigint), (1));
+                         QUERY PLAN                         
+------------------------------------------------------------
+ Bitmap Heap Scan on onek
+   Recheck Cond: (unique1 = ANY ('{1200,1}'::bigint[]))
+   ->  Bitmap Index Scan on onek_unique1
+         Index Cond: (unique1 = ANY ('{1200,1}'::bigint[]))
+(4 rows)
+
+-- Recursive VTA transformation
+EXPLAIN (COSTS OFF)
+SELECT * FROM (
+  SELECT ten FROM onek
+  WHERE unique1 IN (VALUES(10000), (2), (389), (1000), (2000), (10029))
+  OFFSET 0
+);
+                                    QUERY PLAN                                    
+----------------------------------------------------------------------------------
+ Bitmap Heap Scan on onek
+   Recheck Cond: (unique1 = ANY ('{10000,2,389,1000,2000,10029}'::integer[]))
+   ->  Bitmap Index Scan on onek_unique1
+         Index Cond: (unique1 = ANY ('{10000,2,389,1000,2000,10029}'::integer[]))
+(4 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT (SELECT avg(ten::integer) FROM onek
+WHERE unique1 IN (VALUES(10000), (2), (389), (1000), (2000), (10029)));
+                                           QUERY PLAN                                           
+------------------------------------------------------------------------------------------------
+ Result
+   InitPlan 1
+     ->  Aggregate
+           ->  Bitmap Heap Scan on onek
+                 Recheck Cond: (unique1 = ANY ('{10000,2,389,1000,2000,10029}'::integer[]))
+                 ->  Bitmap Index Scan on onek_unique1
+                       Index Cond: (unique1 = ANY ('{10000,2,389,1000,2000,10029}'::integer[]))
+(7 rows)
+
+-- VTA shouldn't depend on the side of the join probing with the VALUES expression.
+EXPLAIN (COSTS OFF)
+SELECT c.oid,c.relname FROM pg_class c JOIN pg_am a USING (oid)
+WHERE a.oid IN (VALUES (1), (2));
+                       QUERY PLAN                        
+---------------------------------------------------------
+ Nested Loop
+   ->  Seq Scan on pg_am a
+         Filter: (oid = ANY ('{1,2}'::integer[]))
+   ->  Index Scan using pg_class_oid_index on pg_class c
+         Index Cond: (oid = a.oid)
+(5 rows)
+
+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}'::integer[]))
+(6 rows)
+
+-- Complexity of test expression doesn't matter
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek WHERE sin(two)+four IN (VALUES (sin(0.5)), (2));
+                                                        QUERY PLAN                                                         
+---------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on onek
+   Filter: ((sin((two)::double precision) + (four)::double precision) = ANY ('{0.479425538604203,2}'::double precision[]))
+(2 rows)
+
+EXPLAIN (COSTS OFF)
+-- VTA Doesn't allow NULLs in the list - may be until a better solution.
+SELECT ten FROM onek WHERE sin(two)+four IN (VALUES (sin(0.5)), (NULL), (2));
+                                               QUERY PLAN                                                
+---------------------------------------------------------------------------------------------------------
+ Hash Semi Join
+   Hash Cond: ((sin((onek.two)::double precision) + (onek.four)::double precision) = "*VALUES*".column1)
+   ->  Seq Scan on onek
+   ->  Hash
+         ->  Values Scan on "*VALUES*"
+(5 rows)
+
+-- TODO No.2
+PREPARE test (int,numeric, text) AS
+  SELECT ten FROM onek WHERE sin(two)*four/($3::real) IN (VALUES (sin($2)), (2), ($1));
+EXPLAIN (COSTS OFF) EXECUTE test(42, 3.14, '-1.5');
+                                                                    QUERY PLAN                                                                     
+---------------------------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on onek
+   Filter: (((sin((two)::double precision) * (four)::double precision) / '-1.5'::real) = ANY ('{0.0015926529164868282,2,42}'::double precision[]))
+(2 rows)
+
+EXPLAIN (COSTS OFF) EXECUTE test(NULL, 3.14, NULL);
+        QUERY PLAN        
+--------------------------
+ Result
+   One-Time Filter: false
+(2 rows)
+
+EXPLAIN (COSTS OFF) EXECUTE test(NULL, 3.14, '-1.5');
+                                                                     QUERY PLAN                                                                      
+-----------------------------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on onek
+   Filter: (((sin((two)::double precision) * (four)::double precision) / '-1.5'::real) = ANY ('{0.0015926529164868282,2,NULL}'::double precision[]))
+(2 rows)
+
+PREPARE test1 (int,numeric, text) AS
+  SELECT ten FROM onek
+  WHERE sin(two)*four/($3::real) IN (VALUES (sin($2)), (2), ($2), ($2), ($1));
+-- VTA forbidden because sin($1) can't be evaluated to Const.
+EXPLAIN (COSTS OFF) EXECUTE test1(NULL, 2, '2');
+                                                                    QUERY PLAN                                                                     
+---------------------------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on onek
+   Filter: (((sin((two)::double precision) * (four)::double precision) / '2'::real) = ANY ('{0.9092974268256817,2,2,2,NULL}'::double precision[]))
+(2 rows)
+
+PREPARE test2 (int,numeric, text) AS
+  SELECT ten FROM onek
+  WHERE sin(two)*four/($3::real) IN (VALUES (2), ($2), ($2), ($1));
+-- VTA forbidden because of unresolved casting of numeric parameter to common type
+EXPLAIN (COSTS OFF) EXECUTE test2(2, 2, '2');
+                                                     QUERY PLAN                                                     
+--------------------------------------------------------------------------------------------------------------------
+ Seq Scan on onek
+   Filter: (((sin((two)::double precision) * (four)::double precision) / '2'::real) = ANY ('{2,2,2,2}'::numeric[]))
+(2 rows)
+
+PREPARE test3 (int,int, text) AS
+  SELECT ten FROM onek
+  WHERE sin(two)*four/($3::real) IN (VALUES (2), ($2), ($2), ($1));
+-- Legal VTA transformation
+EXPLAIN (COSTS OFF) EXECUTE test3(2, 2, '2');
+                                                     QUERY PLAN                                                     
+--------------------------------------------------------------------------------------------------------------------
+ Seq Scan on onek
+   Filter: (((sin((two)::double precision) * (four)::double precision) / '2'::real) = ANY ('{2,2,2,2}'::integer[]))
+(2 rows)
+
+-- Be careful in case of sort_specification clauses like LIMIT, OFFSET etc.
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek WHERE unique1 IN (VALUES (1), (2) OFFSET 1);
+                     QUERY PLAN                     
+----------------------------------------------------
+ Nested Loop
+   ->  HashAggregate
+         Group Key: "*VALUES*".column1
+         ->  Limit
+               ->  Values Scan on "*VALUES*"
+   ->  Index Scan using onek_unique1 on onek
+         Index Cond: (unique1 = "*VALUES*".column1)
+(7 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek WHERE unique1 IN (VALUES (1), (2) ORDER BY 1);
+                     QUERY PLAN                     
+----------------------------------------------------
+ Nested Loop
+   ->  Unique
+         ->  Sort
+               Sort Key: "*VALUES*".column1
+               ->  Sort
+                     Sort Key: "*VALUES*".column1
+                     ->  Values Scan on "*VALUES*"
+   ->  Index Scan using onek_unique1 on onek
+         Index Cond: (unique1 = "*VALUES*".column1)
+(9 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek WHERE unique1 IN (VALUES (1), (2) LIMIT 1);
+                     QUERY PLAN                     
+----------------------------------------------------
+ Nested Loop
+   ->  HashAggregate
+         Group Key: "*VALUES*".column1
+         ->  Limit
+               ->  Values Scan on "*VALUES*"
+   ->  Index Scan using onek_unique1 on onek
+         Index Cond: (unique1 = "*VALUES*".column1)
+(7 rows)
+
+SELECT oid,relname FROM pg_class WHERE oid IN (VALUES (sin(0.5)), (2)); -- ERROR
+ERROR:  operator does not exist: oid = double precision
+LINE 1: SELECT oid,relname FROM pg_class WHERE oid IN (VALUES (sin(0...
+                                                   ^
+HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek WHERE unique1 IN (VALUES (sin(0.5)), (2));
+                                         QUERY PLAN                                          
+---------------------------------------------------------------------------------------------
+ Seq Scan on onek
+   Filter: ((unique1)::double precision = ANY ('{0.479425538604203,2}'::double precision[]))
+(2 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek t
+WHERE unique1 IN (VALUES (0), ((2 IN (SELECT unique2 FROM onek c
+  WHERE c.unique2 = t.unique1))::integer));
+                         QUERY PLAN                         
+------------------------------------------------------------
+ Nested Loop Semi Join
+   ->  Seq Scan on onek t
+   ->  Values Scan on "*VALUES*"
+         Filter: (t.unique1 = column1)
+         SubPlan 1
+           ->  Index Only Scan using onek_unique2 on onek c
+                 Index Cond: (unique2 = t.unique1)
+(7 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek t
+WHERE unique1 IN (VALUES (0), ((2 IN (SELECT unique2 FROM onek c
+  WHERE c.unique2 IN (VALUES (sin(0.5)), (2))))::integer));
+                                                      QUERY PLAN                                                       
+-----------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+   ->  Unique
+         ->  Sort
+               Sort Key: "*VALUES*".column1
+               ->  Values Scan on "*VALUES*"
+                     SubPlan 1
+                       ->  Index Only Scan using onek_unique2 on onek c
+                             Filter: ((unique2)::double precision = ANY ('{0.479425538604203,2}'::double precision[]))
+   ->  Index Scan using onek_unique1 on onek t
+         Index Cond: (unique1 = "*VALUES*".column1)
+(10 rows)
+
+-- Doesn't allow the VtA dispite of possibility. XXX: should we improve it later?
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek t WHERE unique1 IN (VALUES (0), ((2 IN (
+  (VALUES (1), (3))))::integer)
+);
+                      QUERY PLAN                       
+-------------------------------------------------------
+ Nested Loop
+   ->  Unique
+         ->  Sort
+               Sort Key: "*VALUES*".column1
+               ->  Values Scan on "*VALUES*"
+                     SubPlan 1
+                       ->  Values Scan on "*VALUES*_1"
+   ->  Index Scan using onek_unique1 on onek t
+         Index Cond: (unique1 = "*VALUES*".column1)
+(9 rows)
+
+-- The VtA works. Do we need to constify the 'SELECT 3' subquery?
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek t WHERE unique1 IN (VALUES (0), ((2 IN
+  (SELECT (3)))::integer)
+);
+                                           QUERY PLAN                                           
+------------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on onek t
+   Recheck Cond: (unique1 = ANY (ARRAY[0, ((ANY (2 = (hashed SubPlan 1).col1)))::integer]))
+   ->  Bitmap Index Scan on onek_unique1
+         Index Cond: (unique1 = ANY (ARRAY[0, ((ANY (2 = (hashed SubPlan 1).col1)))::integer]))
+   SubPlan 1
+     ->  Result
+(6 rows)
+
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index af6e157aca..cf94ed42ef 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -1038,3 +1038,141 @@ WHERE a.thousand < 750;
 explain (costs off)
 SELECT * FROM tenk1 A LEFT JOIN tenk2 B
 ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);
+
+--
+-- Test VALUES to ARRAY (VtA) transformation
+--
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM onek, (VALUES(147, 'RFAAAA'), (931, 'VJAAAA')) AS v (i, j)
+  WHERE onek.unique1 = v.i AND onek.stringu1 = v.j;
+
+SELECT * FROM onek,
+  (VALUES ((SELECT i FROM
+    (VALUES(10000), (2), (389), (1000), (2000), ((SELECT 10029))) AS foo(i)
+    ORDER BY i ASC LIMIT 1))) bar (i)
+  WHERE onek.unique1 = bar.i;
+
+-- Forbid VTA transformation for a composite argument
+EXPLAIN (COSTS OFF)
+SELECT * FROM onek
+  WHERE (unique1,ten) IN (VALUES (1,1), (20,0), (99,9), (17,99))
+  ORDER BY unique1;
+
+-- Values to Array transformation
+EXPLAIN (COSTS OFF)
+SELECT * FROM onek
+    WHERE unique1 IN (VALUES(10000), (2), (389), (1000), (2000), (10029))
+    ORDER BY unique1;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM onek
+    WHERE unique1 IN (VALUES(1200), (1));
+
+-- TODO: Recursively evaluate constant queries.
+EXPLAIN (COSTS OFF)
+SELECT * FROM onek
+  WHERE unique1 IN (SELECT x*x FROM (VALUES(1200), (1)) AS x(x));
+
+-- transformation doesn't apply because values RTE exists in subquery
+EXPLAIN (COSTS OFF)
+select * FROM onek,
+  (VALUES ((select i FROM
+    (VALUES(10000), (2), (389), (1000), (2000), (10029)) as foo(i)
+    ORDER BY i ASC LIMIT 1))) bar (i)
+  WHERE onek.unique1 = bar.i;
+
+EXPLAIN (COSTS OFF)
+SELECT unique1, stringu1 FROM onek WHERE stringu1::name IN (VALUES('RFAAAA'), ('VJAAAA'));
+
+-- transformation doesn't apply because of type differences
+EXPLAIN (COSTS OFF)
+SELECT unique1, stringu1 FROM onek WHERE stringu1::text IN (VALUES('RFAAAA'), ('VJAAAA'));
+
+EXPLAIN (COSTS OFF)
+SELECT * from onek WHERE unique1 in (VALUES(1200::bigint), (1));
+
+-- Recursive VTA transformation
+EXPLAIN (COSTS OFF)
+SELECT * FROM (
+  SELECT ten FROM onek
+  WHERE unique1 IN (VALUES(10000), (2), (389), (1000), (2000), (10029))
+  OFFSET 0
+);
+
+EXPLAIN (COSTS OFF)
+SELECT (SELECT avg(ten::integer) FROM onek
+WHERE unique1 IN (VALUES(10000), (2), (389), (1000), (2000), (10029)));
+
+-- VTA shouldn't depend on the side of the join probing with the VALUES expression.
+EXPLAIN (COSTS OFF)
+SELECT c.oid,c.relname FROM pg_class c JOIN pg_am a USING (oid)
+WHERE a.oid IN (VALUES (1), (2));
+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));
+
+-- Complexity of test expression doesn't matter
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek WHERE sin(two)+four IN (VALUES (sin(0.5)), (2));
+EXPLAIN (COSTS OFF)
+
+-- VTA Doesn't allow NULLs in the list - may be until a better solution.
+SELECT ten FROM onek WHERE sin(two)+four IN (VALUES (sin(0.5)), (NULL), (2));
+
+-- TODO No.2
+PREPARE test (int,numeric, text) AS
+  SELECT ten FROM onek WHERE sin(two)*four/($3::real) IN (VALUES (sin($2)), (2), ($1));
+EXPLAIN (COSTS OFF) EXECUTE test(42, 3.14, '-1.5');
+EXPLAIN (COSTS OFF) EXECUTE test(NULL, 3.14, NULL);
+EXPLAIN (COSTS OFF) EXECUTE test(NULL, 3.14, '-1.5');
+PREPARE test1 (int,numeric, text) AS
+  SELECT ten FROM onek
+  WHERE sin(two)*four/($3::real) IN (VALUES (sin($2)), (2), ($2), ($2), ($1));
+-- VTA forbidden because sin($1) can't be evaluated to Const.
+EXPLAIN (COSTS OFF) EXECUTE test1(NULL, 2, '2');
+
+PREPARE test2 (int,numeric, text) AS
+  SELECT ten FROM onek
+  WHERE sin(two)*four/($3::real) IN (VALUES (2), ($2), ($2), ($1));
+-- VTA forbidden because of unresolved casting of numeric parameter to common type
+EXPLAIN (COSTS OFF) EXECUTE test2(2, 2, '2');
+PREPARE test3 (int,int, text) AS
+  SELECT ten FROM onek
+  WHERE sin(two)*four/($3::real) IN (VALUES (2), ($2), ($2), ($1));
+-- Legal VTA transformation
+EXPLAIN (COSTS OFF) EXECUTE test3(2, 2, '2');
+
+-- Be careful in case of sort_specification clauses like LIMIT, OFFSET etc.
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek WHERE unique1 IN (VALUES (1), (2) OFFSET 1);
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek WHERE unique1 IN (VALUES (1), (2) ORDER BY 1);
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek WHERE unique1 IN (VALUES (1), (2) LIMIT 1);
+
+SELECT oid,relname FROM pg_class WHERE oid IN (VALUES (sin(0.5)), (2)); -- ERROR
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek WHERE unique1 IN (VALUES (sin(0.5)), (2));
+
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek t
+WHERE unique1 IN (VALUES (0), ((2 IN (SELECT unique2 FROM onek c
+  WHERE c.unique2 = t.unique1))::integer));
+
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek t
+WHERE unique1 IN (VALUES (0), ((2 IN (SELECT unique2 FROM onek c
+  WHERE c.unique2 IN (VALUES (sin(0.5)), (2))))::integer));
+
+-- Doesn't allow the VtA dispite of possibility. XXX: should we improve it later?
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek t WHERE unique1 IN (VALUES (0), ((2 IN (
+  (VALUES (1), (3))))::integer)
+);
+
+-- The VtA works. Do we need to constify the 'SELECT 3' subquery?
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek t WHERE unique1 IN (VALUES (0), ((2 IN
+  (SELECT (3)))::integer)
+);
-- 
2.39.5