From 7aa3c64e8a38d61880fe80073095233e74f2044b Mon Sep 17 00:00:00 2001 From: "Andrei V. Lepikhov" 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