On 10/4/24 04:08, Tom Lane wrote:
Laurenz Albe <laurenz.a...@cybertec.at> writes:
I wonder if it is worth the extra planning time to detect and improve
such queries.
I'm skeptical too. I'm *very* skeptical of implementing it in the
grammar as shown here --- I'd go so far as to say that that approach
cannot be accepted. That's far too early, and it risks all sorts
of problems. An example is that the code as given seems to assume
that all the sublists are the same length ... but we haven't checked
that yet. I also suspect that this does not behave the same as the
original construct for purposes like resolving dissimilar types in
the VALUES list. (In an ideal world, perhaps it'd behave the same,
but that ship sailed a couple decades ago.)
We also have an implementation of VALUES -> ARRAY transformation.
Because enterprises must deal with users' problems, many of these users
employ automatically generated queries.
Being informed very well of the consensus about that stuff, we've
designed it as a library. But, looking into the code now, I see that it
only needs a few cycles if no one 'x IN VALUES' expression is presented
in the query. Who knows? It may be OK for the core.
So, I've rewritten the code into the patch - see it in the attachment.
The idea is quite simple - at the same place as
convert_ANY_sublink_to_join, we can test the SubLink on proper VALUES
RTE and perform the transformation if it's convertible.
--
regards, Andrei Lepikhov
From 7aa3c64e8a38d61880fe80073095233e74f2044b Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <lepi...@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
