From 805498afb64f092aad29e7b3734eecf266f98b16 Mon Sep 17 00:00:00 2001 From: Ivan Kush 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 Author: Vadim Yacenko Author: Alexander Simonov --- 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