On 12/3/2024 22:20, Alexander Korotkov wrote:
On Mon, Mar 11, 2024 at 2:43 PM Andrei Lepikhov
I think you are right. It is probably a better place than any other to
remove duplicates in an array. I just think we should sort and remove
duplicates from entry->consts in one pass. Thus, this optimisation
should be applied to sortable constants.
Ok.
New version of the patch set implemented all we have agreed on for now.
We can return MAX_SAOP_ARRAY_SIZE constraint and Alena's approach to
duplicates deletion for non-sortable cases at the end.
Hmm, we already tried to do it at that point. I vaguely recall some
issues caused by this approach. Anyway, it should be done as quickly as
possible to increase the effect of the optimization.
I think there were provided quite strong reasons why this shouldn't be
implemented at the parse analysis stage [1], [2], [3]. The
canonicalize_qual() looks quite appropriate place for that since it
does similar transformations.
Ok. Let's discuss these reasons. In Robert's opinion [1,3], we should do
the transformation based on the cost model. But in the canonicalize_qual
routine, we still make the transformation blindly. Moreover, the second
patch reduces the weight of this reason, doesn't it? Maybe we shouldn't
think about that as about optimisation but some 'general form of
expression'?
Peter [2] worries about the possible transformation outcomes at this
stage. But remember, we already transform clauses like ROW() IN (...) to
a series of ORs here, so it is not an issue. Am I wrong?
Why did we discard the attempt with canonicalize_qual on the previous
iteration? - The stage of parsing is much more native for building SAOP
quals. We can reuse make_scalar_array_op and other stuff, for example.
During the optimisation stage, the only list partitioning machinery
creates SAOP based on a list of constants. So, in theory, it is possible
to implement. But do we really need to make the code more complex?
Links.
1.
https://www.postgresql.org/message-id/CA%2BTgmoZCgP6FrBQEusn4yaWm02XU8OPeoEMk91q7PRBgwaAkFw%40mail.gmail.com
2.
https://www.postgresql.org/message-id/CAH2-Wzm2%3Dnf_JhiM3A2yetxRs8Nd2NuN3JqH%3Dfm_YWYd1oYoPg%40mail.gmail.com
3.
https://www.postgresql.org/message-id/CA%2BTgmoaOiwMXBBTYknczepoZzKTp-Zgk5ss1%2BCuVQE-eFTqBmA%40mail.gmail.com
--
regards,
Andrei Lepikhov
Postgres Professional
From 86d969f2598a03b1eba84c0c064707de34ee60ac Mon Sep 17 00:00:00 2001
From: Alena Rybakina <a.rybak...@postgrespro.ru>
Date: Fri, 2 Feb 2024 22:01:09 +0300
Subject: [PATCH 1/2] Transform OR clauses to ANY expression.
Replace (expr op C1) OR (expr op C2) ... with expr op ANY(ARRAY[C1, C2, ...])
on the
preliminary stage of optimization when we are still working with the
expression tree.
Here C<X> is a constant expression, 'expr' is non-constant expression, 'op' is
an operator which returns boolean result and has a commuter (for the case of
reverse order of constant and non-constant parts of the expression,
like 'CX op expr').
Sometimes it can lead to not optimal plan. But we think it is better to have
array of elements instead of a lot of OR clauses. Here is a room for further
optimizations on decomposing that array into more optimal parts.
Authors: Alena Rybakina <lena.riback...@yandex.ru>, Andrey Lepikhov
<a.lepik...@postgrespro.ru>
Reviewed-by: Peter Geoghegan <p...@bowt.ie>, Ranier Vilela <ranier...@gmail.com>
Reviewed-by: Alexander Korotkov <aekorot...@gmail.com>, Robert Haas
<robertmh...@gmail.com>
Reviewed-by: jian he <jian.universal...@gmail.com>
---
.../postgres_fdw/expected/postgres_fdw.out | 8 +-
doc/src/sgml/config.sgml | 17 +
src/backend/nodes/queryjumblefuncs.c | 27 ++
src/backend/parser/parse_expr.c | 416 ++++++++++++++++++
src/backend/utils/misc/guc_tables.c | 11 +
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/bin/pg_dump/t/002_pg_dump.pl | 6 +-
src/include/nodes/queryjumble.h | 1 +
src/include/optimizer/optimizer.h | 1 +
src/test/regress/expected/create_index.out | 156 ++++++-
src/test/regress/expected/join.out | 62 ++-
src/test/regress/expected/partition_prune.out | 215 ++++++++-
src/test/regress/expected/stats_ext.out | 12 +-
src/test/regress/expected/sysviews.out | 3 +-
src/test/regress/expected/tidscan.out | 23 +-
src/test/regress/sql/create_index.sql | 35 ++
src/test/regress/sql/join.sql | 10 +
src/test/regress/sql/partition_prune.sql | 22 +
src/test/regress/sql/tidscan.sql | 6 +
src/tools/pgindent/typedefs.list | 2 +
20 files changed, 974 insertions(+), 60 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
index 58a603ac56..a965b43cc6 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -8838,18 +8838,18 @@ insert into utrtest values (2, 'qux');
-- Check case where the foreign partition is a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 1 or a = 2 returning *;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------
Update on public.utrtest
Output: utrtest_1.a, utrtest_1.b
Foreign Update on public.remp utrtest_1
Update on public.locp utrtest_2
-> Append
-> Foreign Update on public.remp utrtest_1
- Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a
= 2))) RETURNING a, b
+ Remote SQL: UPDATE public.loct SET a = 1 WHERE ((a = ANY
('{1,2}'::integer[]))) RETURNING a, b
-> Seq Scan on public.locp utrtest_2
Output: 1, utrtest_2.tableoid, utrtest_2.ctid, NULL::record
- Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
+ Filter: (utrtest_2.a = ANY ('{1,2}'::integer[]))
(10 rows)
-- The new values are concatenated with ' triggered !'
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 65a6e6c408..2de6ae301a 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5472,6 +5472,23 @@ ANY <replaceable
class="parameter">num_sync</replaceable> ( <replaceable class="
</listitem>
</varlistentry>
+ <varlistentry id="guc-enable-or-transformation"
xreflabel="enable_or_transformation">
+ <term><varname>enable_or_transformation</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>enable_or_transformation</varname> configuration
parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Enables or disables the query planner's ability to lookup and group
multiple
+ similar OR expressions to ANY (<xref
linkend="functions-comparisons-any-some"/>) expressions.
+ The grouping technique of this transformation is based on the
equivalence of variable sides.
+ One side of such an expression must be a constant clause, and the
other must contain a variable clause.
+ The default is <literal>on</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-enable-parallel-append"
xreflabel="enable_parallel_append">
<term><varname>enable_parallel_append</varname> (<type>boolean</type>)
<indexterm>
diff --git a/src/backend/nodes/queryjumblefuncs.c
b/src/backend/nodes/queryjumblefuncs.c
index 2c116c8728..0c5b4a011b 100644
--- a/src/backend/nodes/queryjumblefuncs.c
+++ b/src/backend/nodes/queryjumblefuncs.c
@@ -141,6 +141,33 @@ JumbleQuery(Query *query)
return jstate;
}
+JumbleState *
+JumbleExpr(Expr *expr, uint64 *queryId)
+{
+ JumbleState *jstate = NULL;
+
+ Assert(queryId != NULL);
+
+ jstate = (JumbleState *) palloc(sizeof(JumbleState));
+
+ /* Set up workspace for query jumbling */
+ jstate->jumble = (unsigned char *) palloc(JUMBLE_SIZE);
+ jstate->jumble_len = 0;
+ jstate->clocations_buf_size = 32;
+ jstate->clocations = (LocationLen *)
+ palloc(jstate->clocations_buf_size * sizeof(LocationLen));
+ jstate->clocations_count = 0;
+ jstate->highest_extern_param_id = 0;
+
+ /* Compute query ID */
+ _jumbleNode(jstate, (Node *) expr);
+ *queryId = DatumGetUInt64(hash_any_extended(jstate->jumble,
+
jstate->jumble_len,
+
0));
+
+ return jstate;
+}
+
/*
* Enables query identifier computation.
*
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 9300c7b9ab..84038ab68f 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -16,12 +16,15 @@
#include "postgres.h"
#include "catalog/pg_aggregate.h"
+#include "catalog/pg_operator.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "commands/dbcommands.h"
+#include "common/hashfn.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "nodes/queryjumble.h"
#include "optimizer/optimizer.h"
#include "parser/analyze.h"
#include "parser/parse_agg.h"
@@ -38,11 +41,13 @@
#include "utils/date.h"
#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
+#include "utils/syscache.h"
#include "utils/timestamp.h"
#include "utils/xml.h"
/* GUC parameters */
bool Transform_null_equals = false;
+bool enable_or_transformation = true;
static Node *transformExprRecurse(ParseState *pstate, Node *expr);
@@ -99,6 +104,412 @@ static Expr *make_distinct_op(ParseState *pstate, List
*opname,
static Node *make_nulltest_from_distinct(ParseState *pstate,
A_Expr *distincta, Node *arg);
+typedef struct OrClauseGroupKey
+{
+ NodeTag type;
+
+ Expr *expr; /* Pointer to the expression tree which has been a source
for
+ the hashkey value */
+ Oid opno;
+ Oid exprtype;
+} OrClauseGroupKey;
+
+typedef struct OrClauseGroupEntry
+{
+ OrClauseGroupKey key;
+
+ List *consts;
+ List *exprs;
+ FunctionCallInfo fcinfo;
+} OrClauseGroupEntry;
+
+/*
+ * Hash function to find candidate clauses.
+ */
+static uint32
+orclause_hash(const void *data, Size keysize)
+{
+ OrClauseGroupKey *key = (OrClauseGroupKey *) data;
+ uint64 exprHash;
+
+ Assert(keysize == sizeof(OrClauseGroupKey));
+ Assert(IsA(data, Invalid));
+
+ (void) JumbleExpr(key->expr, &exprHash);
+
+ return hash_combine((uint32) exprHash,
+ hash_combine((uint32) key->opno,
+
(uint32) key->exprtype));
+}
+
+static void *
+orclause_keycopy(void *dest, const void *src, Size keysize)
+{
+ OrClauseGroupKey *src_key = (OrClauseGroupKey *) src;
+ OrClauseGroupKey *dst_key = (OrClauseGroupKey *) dest;
+
+ Assert(sizeof(OrClauseGroupKey) == keysize);
+ Assert(IsA(src, Invalid));
+
+ dst_key->type = T_Invalid;
+ dst_key->expr = src_key->expr;
+ dst_key->opno = src_key->opno;
+ dst_key->exprtype = src_key->exprtype;
+ return dst_key;
+}
+
+/*
+ * Dynahash match function to use in or_group_htab
+ */
+static int
+orclause_match(const void *data1, const void *data2, Size keysize)
+{
+ OrClauseGroupKey *key1 = (OrClauseGroupKey *) data1;
+ OrClauseGroupKey *key2 = (OrClauseGroupKey *) data2;
+
+ Assert(sizeof(OrClauseGroupKey) == keysize);
+ Assert(IsA(key1, Invalid));
+ Assert(IsA(key2, Invalid));
+
+ if (key1->opno == key2->opno &&
+ key1->exprtype == key2->exprtype &&
+ equal(key1->expr, key2->expr))
+ return 0;
+
+ return 1;
+}
+
+static FunctionCallInfo locfcinfo = NULL;
+
+static int
+saop_const_comparator(const ListCell *a, const ListCell *b)
+{
+ Node *leftop = (Node *) lfirst(a);
+ Node *rightop = (Node *) lfirst(b);
+
+ if (IsA(leftop, RelabelType))
+ leftop = (Node *) ((RelabelType *) leftop)->arg;
+
+ if (IsA(rightop, RelabelType))
+ rightop = (Node *) ((RelabelType *) rightop)->arg;
+
+ Assert(IsA(leftop, Const) && IsA(rightop, Const));
+
+ locfcinfo->args[0].value = ((Const *) leftop)->constvalue;
+ locfcinfo->args[0].isnull = false;
+ locfcinfo->args[1].value = ((Const *) rightop)->constvalue;
+ locfcinfo->args[1].isnull = false;
+ return DatumGetInt32(FunctionCallInvoke(locfcinfo));
+}
+
+static List *
+saop_delete_duplicates(ParseState *pstate, OrClauseGroupEntry *entry,
+ Oid scalar_type)
+{
+ ListCell *lc;
+ List *result;
+
+ locfcinfo = entry->fcinfo;
+ list_sort(entry->consts, saop_const_comparator);
+
+ result = list_make1(linitial(entry->consts));
+ for_each_from(lc, entry->consts, 1)
+ {
+ Node *node = (Node *) lfirst(lc);
+
+ if (equal(llast(result), node))
+ continue;
+
+ node = coerce_to_common_type(pstate, node, scalar_type, "IN");
+ result = lappend(result, node);
+ }
+
+ return result;
+}
+
+/*
+ * TransformOrExprToANY -
+ * Discover the args of an OR expression and try to group similar OR
+ * expressions to an ANY operation.
+ * Transformation must be already done on input args list before the
call.
+ * Transformation groups two-sided equality operations. One side of such
an
+ * operation must be plain constant or constant expression. The other
side of
+ * the clause must be a variable expression without volatile functions.
+ * The grouping technique is based on an equivalence of variable sides
of the
+ * expression: using queryId and equal() routine, it groups constant
sides of
+ * similar clauses into an array. After the grouping procedure, each
couple
+ * ('variable expression' and 'constant array') form a new SAOP
operation,
+ * which is added to the args list of the returning expression.
+ *
+ * NOTE: function returns OR BoolExpr if more than one clause are
detected in
+ * the final args list, or ScalarArrayOpExpr if all args were grouped
into
+ * the single SAOP expression.
+ */
+static Node *
+TransformOrExprToANY(ParseState *pstate, List *args, int location)
+{
+ List *or_list = NIL;
+ List *entries = NIL;
+ ListCell *lc;
+ HASHCTL info;
+ HTAB *or_group_htab = NULL;
+ int len_ors = list_length(args);
+ OrClauseGroupEntry *entry = NULL;
+
+ Assert(enable_or_transformation && len_ors > 1);
+
+ MemSet(&info, 0, sizeof(info));
+ info.keysize = sizeof(OrClauseGroupKey);
+ info.entrysize = sizeof(OrClauseGroupEntry);
+ info.hash = orclause_hash;
+ info.keycopy = orclause_keycopy;
+ info.match = orclause_match;
+ or_group_htab = hash_create("OR Groups",
+ len_ors,
+ &info,
+ HASH_ELEM |
HASH_FUNCTION | HASH_COMPARE | HASH_KEYCOPY);
+
+ foreach(lc, args)
+ {
+ Node *orqual = lfirst(lc);
+ Node *const_expr;
+ Node *nconst_expr;
+ OrClauseGroupKey hashkey;
+ bool found;
+ Oid opno;
+ Oid exprtype;
+ Node *leftop, *rightop;
+ TypeCacheEntry *typentry;
+
+ if (!IsA(orqual, OpExpr))
+ {
+ entries = lappend(entries, orqual);
+ continue;
+ }
+
+ opno = ((OpExpr *) orqual)->opno;
+ if (get_op_rettype(opno) != BOOLOID)
+ {
+ /* Only operator returning boolean suits OR -> ANY
transformation */
+ entries = lappend(entries, orqual);
+ continue;
+ }
+
+ /*
+ * Detect the constant side of the clause. Recall non-constant
+ * expression can be made not only with Vars, but also with
Params,
+ * which is not bonded with any relation. Thus, we detect the
const
+ * side - if another side is constant too, the orqual couldn't
be
+ * an OpExpr.
+ * Get pointers to constant and expression sides of the qual.
+ */
+ leftop = get_leftop(orqual);
+ if (IsA(leftop, RelabelType))
+ leftop = (Node *) ((RelabelType *) leftop)->arg;
+ rightop = get_rightop(orqual);
+ if (IsA(rightop, RelabelType))
+ rightop = (Node *) ((RelabelType *) rightop)->arg;
+
+ if (IsA(leftop, Const))
+ {
+ opno = get_commutator(opno);
+
+ if (!OidIsValid(opno))
+ {
+ /* commutator doesn't exist, we can't reverse
the order */
+ entries = lappend(entries, orqual);
+ continue;
+ }
+
+ nconst_expr = get_rightop(orqual);
+ const_expr = get_leftop(orqual);
+ }
+ else if (IsA(rightop, Const))
+ {
+ const_expr = get_rightop(orqual);
+ nconst_expr = get_leftop(orqual);
+ }
+ else
+ {
+ entries = lappend(entries, orqual);
+ continue;
+ }
+
+ /*
+ * Transformation only works with both side type is not
+ * { array | composite | domain | record }.
+ * Also, forbid it for volatile expressions.
+ */
+ exprtype = exprType(nconst_expr);
+ if (type_is_rowtype(exprType(const_expr)) ||
+ type_is_rowtype(exprtype) ||
+ contain_volatile_functions((Node *) nconst_expr))
+ {
+ entries = lappend(entries, orqual);
+ continue;
+ }
+
+ typentry = lookup_type_cache(exprtype,
TYPECACHE_CMP_PROC_FINFO);
+
+ /*
+ * Within this transformation we remove duplicates. To avoid
+ * quadratic behavior we need to sort clauses after making a
list of
+ * elements. So, need sort operator to implement that.
+ */
+ if (!OidIsValid(typentry->cmp_proc_finfo.fn_oid))
+ {
+ entries = lappend(entries, orqual);
+ continue;
+ }
+
+ /*
+ * At this point we definitely have a transformable clause.
+ * Classify it and add into specific group of clauses, or create
new
+ * group.
+ */
+ hashkey.type = T_Invalid;
+ hashkey.expr = (Expr *) nconst_expr;
+ hashkey.opno = opno;
+ hashkey.exprtype = exprtype;
+ entry = hash_search(or_group_htab, &hashkey, HASH_ENTER,
&found);
+
+ if (unlikely(found))
+ {
+ entry->consts = lappend(entry->consts, const_expr);
+ entry->exprs = lappend(entry->exprs, orqual);
+ }
+ else
+ {
+ entry->consts = list_make1(const_expr);
+ entry->exprs = list_make1(orqual);
+
+ /* Prepare funcall for sort comparator */
+ entry->fcinfo =
+ (FunctionCallInfo)
palloc(SizeForFunctionCallInfo(2));
+ InitFunctionCallInfoData(*(entry->fcinfo),
+
&typentry->cmp_proc_finfo, 2,
+
typentry->typcollation, NULL, NULL);
+
+ /*
+ * Add the entry to the list. It is needed exclusively
to manage the
+ * problem with the order of transformed clauses in
explain.
+ * Hash value can depend on the platform and version.
Hence,
+ * sequental scan of the hash table would prone to
change the order
+ * of clauses in lists and, as a result, break
regression tests
+ * accidentially.
+ */
+ entries = lappend(entries, entry);
+ }
+ }
+
+ /* Let's convert each group of clauses to an IN operation. */
+
+ /*
+ * Go through the list of groups and convert each, where number of
+ * consts more than 1. trivial groups move to OR-list again
+ */
+ foreach (lc, entries)
+ {
+ Oid scalar_type;
+ Oid array_type;
+
+ if (!IsA(lfirst(lc), Invalid))
+ {
+ or_list = lappend(or_list, lfirst(lc));
+ continue;
+ }
+
+ entry = (OrClauseGroupEntry *) lfirst(lc);
+
+ Assert(list_length(entry->consts) > 0);
+ Assert(list_length(entry->exprs) == list_length(entry->consts));
+
+ if (list_length(entry->consts) == 1)
+ {
+ /*
+ * Only one element returns origin expression into the
BoolExpr args
+ * list unchanged.
+ */
+ list_free(entry->consts);
+ or_list = list_concat(or_list, entry->exprs);
+ continue;
+ }
+
+ /*
+ * Do the transformation.
+ */
+
+ scalar_type = entry->key.exprtype;
+ array_type = OidIsValid(scalar_type) ?
get_array_type(scalar_type) :
+
InvalidOid;
+
+ if (OidIsValid(array_type))
+ {
+ /*
+ * OK: coerce all the right-hand non-Var inputs to the
common
+ * type and build an ArrayExpr for them.
+ */
+ List *aexprs = NIL;
+ ArrayExpr *newa = NULL;
+ ScalarArrayOpExpr *saopexpr = NULL;
+ HeapTuple opertup;
+ Form_pg_operator operform;
+ List *namelist = NIL;
+
+ aexprs = saop_delete_duplicates(pstate, entry,
scalar_type);
+
+ newa = makeNode(ArrayExpr);
+ /* array_collid will be set by parse_collate.c */
+ newa->element_typeid = scalar_type;
+ newa->array_typeid = array_type;
+ newa->multidims = false;
+ newa->elements = aexprs;
+ newa->location = -1;
+
+ opertup = SearchSysCache1(OPEROID,
+
ObjectIdGetDatum(entry->key.opno));
+ if (!HeapTupleIsValid(opertup))
+ elog(ERROR, "cache lookup failed for operator
%u",
+ entry->key.opno);
+
+ operform = (Form_pg_operator) GETSTRUCT(opertup);
+ if (!OperatorIsVisible(entry->key.opno))
+ namelist = lappend(namelist,
makeString(get_namespace_name(operform->oprnamespace)));
+
+ namelist = lappend(namelist,
makeString(pstrdup(NameStr(operform->oprname))));
+ ReleaseSysCache(opertup);
+
+ saopexpr =
+ (ScalarArrayOpExpr *)
+ make_scalar_array_op(pstate,
+
namelist,
+
true,
+
(Node *) entry->key.expr,
+
(Node *) newa,
+
-1);
+
+ or_list = lappend(or_list, (void *) saopexpr);
+ }
+ else
+ {
+ /*
+ * If the const node (right side of operator
expression) 's type
+ * don't have “true” array type, then we cannnot do
the transformation.
+ * We simply concatenate the expression node.
+ *
+ */
+ list_free(entry->consts);
+ or_list = list_concat(or_list, entry->exprs);
+ }
+ }
+ hash_destroy(or_group_htab);
+ list_free(entries);
+
+ /* One more trick: assemble correct clause */
+ return (Node *) ((list_length(or_list) > 1) ?
+ makeBoolExpr(OR_EXPR, or_list,
location) :
+ linitial(or_list));
+}
/*
* transformExpr -
@@ -1386,6 +1797,11 @@ transformBoolExpr(ParseState *pstate, BoolExpr *a)
args = lappend(args, arg);
}
+ /* Make an attempt to group similar OR clauses into ANY operation */
+ if (enable_or_transformation && a->boolop == OR_EXPR &&
+ list_length(args) > 1)
+ return TransformOrExprToANY(pstate, args, a->location);
+
return (Node *) makeBoolExpr(a->boolop, args, a->location);
}
diff --git a/src/backend/utils/misc/guc_tables.c
b/src/backend/utils/misc/guc_tables.c
index d77214795d..0c3f278420 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -1026,6 +1026,17 @@ struct config_bool ConfigureNamesBool[] =
true,
NULL, NULL, NULL
},
+ {
+ {"enable_or_transformation", PGC_USERSET, QUERY_TUNING_OTHER,
+ gettext_noop("Transform a sequence of OR clauses to an
array expression."),
+ gettext_noop("The planner will replace expression like
'x=c1 OR x=c2 ..'"
+ "to the expression 'x =
ANY(ARRAY[c1,c2,..])'"),
+ GUC_EXPLAIN
+ },
+ &enable_or_transformation,
+ true,
+ NULL, NULL, NULL
+ },
{
/*
* Not for general use --- used by SET SESSION AUTHORIZATION
and SET
diff --git a/src/backend/utils/misc/postgresql.conf.sample
b/src/backend/utils/misc/postgresql.conf.sample
index 2244ee52f7..524516593f 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -391,6 +391,7 @@
# - Planner Method Configuration -
#enable_async_append = on
+#enable_or_transformation = on
#enable_bitmapscan = on
#enable_gathermerge = on
#enable_hashagg = on
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 00b5092713..d28bf617db 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -2095,9 +2095,9 @@ my %tests = (
regexp => qr/^
\QCREATE DOMAIN dump_test.us_postal_code AS text
COLLATE pg_catalog."C" DEFAULT '10014'::text\E\n\s+
\QCONSTRAINT us_postal_code_check CHECK \E
- \Q(((VALUE ~ '^\d{5}\E
- \$\Q'::text) OR (VALUE ~ '^\d{5}-\d{4}\E\$
- \Q'::text)));\E(.|\n)*
+ \Q((VALUE ~ ANY (ARRAY['^\d{5}\E
+ \$\Q'::text, '^\d{5}-\d{4}\E\$
+ \Q'::text])));\E(.|\n)*
\QCOMMENT ON CONSTRAINT us_postal_code_check ON DOMAIN
dump_test.us_postal_code IS 'check it';\E
/xm,
like =>
diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h
index f1c55c8067..a9ae048af5 100644
--- a/src/include/nodes/queryjumble.h
+++ b/src/include/nodes/queryjumble.h
@@ -65,6 +65,7 @@ extern PGDLLIMPORT int compute_query_id;
extern const char *CleanQuerytext(const char *query, int *location, int *len);
extern JumbleState *JumbleQuery(Query *query);
+extern JumbleState *JumbleExpr(Expr *expr, uint64 *queryId);
extern void EnableQueryId(void);
extern PGDLLIMPORT bool query_id_enabled;
diff --git a/src/include/optimizer/optimizer.h
b/src/include/optimizer/optimizer.h
index 7b63c5cf71..35ab577501 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -50,6 +50,7 @@ struct PlannedStmt;
struct ParamListInfoData;
struct HeapTupleData;
+extern PGDLLIMPORT bool enable_or_transformation;
/* in path/clausesel.c: */
diff --git a/src/test/regress/expected/create_index.out
b/src/test/regress/expected/create_index.out
index 79fa117cb5..4a4dfb3bb5 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1838,18 +1838,50 @@ DROP TABLE onek_with_null;
EXPLAIN (COSTS OFF)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
- Bitmap Heap Scan on tenk1
- Recheck Cond: (((thousand = 42) AND (tenthous = 1)) OR ((thousand = 42) AND
(tenthous = 3)) OR ((thousand = 42) AND (tenthous = 42)))
- -> BitmapOr
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: ((thousand = 42) AND (tenthous = 1))
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: ((thousand = 42) AND (tenthous = 3))
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: ((thousand = 42) AND (tenthous = 42))
-(9 rows)
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Index Scan using tenk1_thous_tenthous on tenk1
+ Index Cond: ((thousand = 42) AND (tenthous = ANY ('{1,3,42}'::integer[])))
+(2 rows)
+
+SELECT * FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand |
twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+ 42 | 5530 | 0 | 2 | 2 | 2 | 42 | 42 |
42 | 42 | 42 | 84 | 85 | QBAAAA | SEIAAA | OOOOxx
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on tenk1
+ Recheck Cond: ((hundred = 42) AND (thousand = ANY
('{42,99}'::integer[])))
+ -> BitmapAnd
+ -> Bitmap Index Scan on tenk1_hundred
+ Index Cond: (hundred = 42)
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = ANY ('{42,99}'::integer[]))
+(8 rows)
+
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+ count
+-------
+ 10
+(1 row)
+
+SET enable_or_transformation = on;
+EXPLAIN (COSTS OFF)
+SELECT * FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Index Scan using tenk1_thous_tenthous on tenk1
+ Index Cond: ((thousand = 42) AND (tenthous = ANY ('{1,3,42}'::integer[])))
+(2 rows)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
@@ -1861,28 +1893,116 @@ SELECT * FROM tenk1
EXPLAIN (COSTS OFF)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
- QUERY PLAN
----------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on tenk1
+ Recheck Cond: ((hundred = 42) AND (thousand = ANY
('{42,99}'::integer[])))
+ -> BitmapAnd
+ -> Bitmap Index Scan on tenk1_hundred
+ Index Cond: (hundred = 42)
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = ANY ('{42,99}'::integer[]))
+(8 rows)
+
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+ count
+-------
+ 10
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand < 42 OR thousand < 99 OR 43 > thousand OR
42 > thousand);
+ QUERY PLAN
+---------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on tenk1
+ Recheck Cond: ((hundred = 42) AND (thousand < ANY
('{42,43,99}'::integer[])))
+ -> BitmapAnd
+ -> Bitmap Index Scan on tenk1_hundred
+ Index Cond: (hundred = 42)
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand < ANY ('{42,43,99}'::integer[]))
+(8 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on tenk1
+ Recheck Cond: (((thousand = 42) AND (tenthous = ANY
('{1,3}'::integer[]))) OR (thousand = 41))
+ -> BitmapOr
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: ((thousand = 42) AND (tenthous = ANY
('{1,3}'::integer[])))
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = 41)
+(8 rows)
+
+SELECT count(*) FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41;
+ count
+-------
+ 10
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR
thousand = 41;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on tenk1
+ Recheck Cond: (((hundred = 42) AND ((thousand = ANY
('{42,99}'::integer[])) OR (tenthous < 2))) OR (thousand = 41))
+ -> BitmapOr
+ -> BitmapAnd
+ -> Bitmap Index Scan on tenk1_hundred
+ Index Cond: (hundred = 42)
+ -> BitmapOr
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = ANY
('{42,99}'::integer[]))
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (tenthous < 2)
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = 41)
+(14 rows)
+
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR
thousand = 41;
+ count
+-------
+ 20
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND
tenthous = 2);
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------
Aggregate
-> Bitmap Heap Scan on tenk1
- Recheck Cond: ((hundred = 42) AND ((thousand = 42) OR (thousand =
99)))
+ Recheck Cond: ((hundred = 42) AND ((thousand = ANY
('{41,42}'::integer[])) OR ((thousand = 99) AND (tenthous = 2))))
-> BitmapAnd
-> Bitmap Index Scan on tenk1_hundred
Index Cond: (hundred = 42)
-> BitmapOr
-> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: (thousand = 42)
+ Index Cond: (thousand = ANY ('{41,42}'::integer[]))
-> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: (thousand = 99)
+ Index Cond: ((thousand = 99) AND (tenthous = 2))
(11 rows)
SELECT count(*) FROM tenk1
- WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND
tenthous = 2);
count
-------
10
(1 row)
+RESET enable_or_transformation;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/expected/join.out
b/src/test/regress/expected/join.out
index 9605400021..d8018bef4f 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4210,10 +4210,10 @@ explain (costs off)
select * from tenk1 a join tenk1 b on
(a.unique1 = 1 and b.unique1 = 2) or
((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------
Nested Loop
- Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR (((a.unique2 = 3) OR
(a.unique2 = 7)) AND (b.hundred = 4)))
+ Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = ANY
('{3,7}'::integer[])) AND (b.hundred = 4)))
-> Bitmap Heap Scan on tenk1 b
Recheck Cond: ((unique1 = 2) OR (hundred = 4))
-> BitmapOr
@@ -4223,16 +4223,64 @@ select * from tenk1 a join tenk1 b on
Index Cond: (hundred = 4)
-> Materialize
-> Bitmap Heap Scan on tenk1 a
- Recheck Cond: ((unique1 = 1) OR (unique2 = 3) OR (unique2 = 7))
+ Recheck Cond: ((unique1 = 1) OR (unique2 = ANY
('{3,7}'::integer[])))
-> BitmapOr
-> Bitmap Index Scan on tenk1_unique1
Index Cond: (unique1 = 1)
-> Bitmap Index Scan on tenk1_unique2
- Index Cond: (unique2 = 3)
+ Index Cond: (unique2 = ANY ('{3,7}'::integer[]))
+(17 rows)
+
+SET enable_or_transformation = on;
+explain (costs off)
+select * from tenk1 a join tenk1 b on
+ (a.unique1 = 1 and b.unique1 = 2) or
+ ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = ANY
('{3,7}'::integer[])) AND (b.hundred = 4)))
+ -> Bitmap Heap Scan on tenk1 b
+ Recheck Cond: ((unique1 = 2) OR (hundred = 4))
+ -> BitmapOr
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = 2)
+ -> Bitmap Index Scan on tenk1_hundred
+ Index Cond: (hundred = 4)
+ -> Materialize
+ -> Bitmap Heap Scan on tenk1 a
+ Recheck Cond: ((unique1 = 1) OR (unique2 = ANY
('{3,7}'::integer[])))
+ -> BitmapOr
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = 1)
-> Bitmap Index Scan on tenk1_unique2
- Index Cond: (unique2 = 7)
-(19 rows)
+ Index Cond: (unique2 = ANY ('{3,7}'::integer[]))
+(17 rows)
+
+explain (costs off)
+select * from tenk1 a join tenk1 b on
+ (a.unique1 < 20 or a.unique1 = 3 or a.unique1 = 1 and b.unique1 = 2) or
+ ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
+
QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Join Filter: ((a.unique1 < 20) OR (a.unique1 = 3) OR ((a.unique1 = 1) AND
(b.unique1 = 2)) OR ((a.unique2 = ANY ('{3,7}'::integer[])) AND (b.hundred =
4)))
+ -> Seq Scan on tenk1 b
+ -> Materialize
+ -> Bitmap Heap Scan on tenk1 a
+ Recheck Cond: ((unique1 < 20) OR (unique1 = 3) OR (unique1 = 1)
OR (unique2 = ANY ('{3,7}'::integer[])))
+ -> BitmapOr
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 < 20)
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = 3)
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = 1)
+ -> Bitmap Index Scan on tenk1_unique2
+ Index Cond: (unique2 = ANY ('{3,7}'::integer[]))
+(15 rows)
+RESET enable_or_transformation;
--
-- test placement of movable quals in a parameterized join tree
--
diff --git a/src/test/regress/expected/partition_prune.out
b/src/test/regress/expected/partition_prune.out
index bf0657b9f2..1e153c3bb5 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -82,25 +82,47 @@ explain (costs off) select * from lp where a is null;
(2 rows)
explain (costs off) select * from lp where a = 'a' or a = 'c';
- QUERY PLAN
-----------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Append
-> Seq Scan on lp_ad lp_1
- Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
+ Filter: (a = ANY ('{a,c}'::bpchar[]))
-> Seq Scan on lp_bc lp_2
- Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
+ Filter: (a = ANY ('{a,c}'::bpchar[]))
(5 rows)
explain (costs off) select * from lp where a is not null and (a = 'a' or a =
'c');
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Append
-> Seq Scan on lp_ad lp_1
- Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
+ Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
-> Seq Scan on lp_bc lp_2
- Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
+ Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
(5 rows)
+SET enable_or_transformation = on;
+explain (costs off) select * from lp where a = 'a' or a = 'c';
+ QUERY PLAN
+-----------------------------------------------
+ Append
+ -> Seq Scan on lp_ad lp_1
+ Filter: (a = ANY ('{a,c}'::bpchar[]))
+ -> Seq Scan on lp_bc lp_2
+ Filter: (a = ANY ('{a,c}'::bpchar[]))
+(5 rows)
+
+explain (costs off) select * from lp where a is not null and (a = 'a' or a =
'c');
+ QUERY PLAN
+---------------------------------------------------------------------
+ Append
+ -> Seq Scan on lp_ad lp_1
+ Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
+ -> Seq Scan on lp_bc lp_2
+ Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
+(5 rows)
+
+RESET enable_or_transformation;
explain (costs off) select * from lp where a <> 'g';
QUERY PLAN
------------------------------------
@@ -515,10 +537,10 @@ explain (costs off) select * from rlp where a <= 31;
(27 rows)
explain (costs off) select * from rlp where a = 1 or a = 7;
- QUERY PLAN
---------------------------------
+ QUERY PLAN
+------------------------------------------
Seq Scan on rlp2 rlp
- Filter: ((a = 1) OR (a = 7))
+ Filter: (a = ANY ('{1,7}'::integer[]))
(2 rows)
explain (costs off) select * from rlp where a = 1 or b = 'ab';
@@ -596,13 +618,13 @@ explain (costs off) select * from rlp where a < 1 or (a >
20 and a < 25);
-- where clause contradicts sub-partition's constraint
explain (costs off) select * from rlp where a = 20 or a = 40;
- QUERY PLAN
-----------------------------------------
+ QUERY PLAN
+--------------------------------------------------
Append
-> Seq Scan on rlp4_1 rlp_1
- Filter: ((a = 20) OR (a = 40))
+ Filter: (a = ANY ('{20,40}'::integer[]))
-> Seq Scan on rlp5_default rlp_2
- Filter: ((a = 20) OR (a = 40))
+ Filter: (a = ANY ('{20,40}'::integer[]))
(5 rows)
explain (costs off) select * from rlp3 where a = 20; /* empty */
@@ -671,6 +693,163 @@ explain (costs off) select * from rlp where (a = 1 and a
= 3) or (a > 1 and a =
Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
(11 rows)
+SET enable_or_transformation = on;
+explain (costs off) select * from rlp where a = 1 or a = 7;
+ QUERY PLAN
+------------------------------------------
+ Seq Scan on rlp2 rlp
+ Filter: (a = ANY ('{1,7}'::integer[]))
+(2 rows)
+
+explain (costs off) select * from rlp where a = 1 or b = 'ab';
+ QUERY PLAN
+-------------------------------------------------------
+ Append
+ -> Seq Scan on rlp1 rlp_1
+ Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ -> Seq Scan on rlp2 rlp_2
+ Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ -> Seq Scan on rlp3abcd rlp_3
+ Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ -> Seq Scan on rlp4_1 rlp_4
+ Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ -> Seq Scan on rlp4_2 rlp_5
+ Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ -> Seq Scan on rlp4_default rlp_6
+ Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ -> Seq Scan on rlp5_1 rlp_7
+ Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ -> Seq Scan on rlp5_default rlp_8
+ Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ -> Seq Scan on rlp_default_10 rlp_9
+ Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ -> Seq Scan on rlp_default_30 rlp_10
+ Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ -> Seq Scan on rlp_default_null rlp_11
+ Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ -> Seq Scan on rlp_default_default rlp_12
+ Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+(25 rows)
+
+explain (costs off) select * from rlp where a > 20 and a < 27;
+ QUERY PLAN
+-----------------------------------------
+ Append
+ -> Seq Scan on rlp4_1 rlp_1
+ Filter: ((a > 20) AND (a < 27))
+ -> Seq Scan on rlp4_2 rlp_2
+ Filter: ((a > 20) AND (a < 27))
+(5 rows)
+
+explain (costs off) select * from rlp where a = 29;
+ QUERY PLAN
+------------------------------
+ Seq Scan on rlp4_default rlp
+ Filter: (a = 29)
+(2 rows)
+
+explain (costs off) select * from rlp where a >= 29;
+ QUERY PLAN
+---------------------------------------------
+ Append
+ -> Seq Scan on rlp4_default rlp_1
+ Filter: (a >= 29)
+ -> Seq Scan on rlp5_1 rlp_2
+ Filter: (a >= 29)
+ -> Seq Scan on rlp5_default rlp_3
+ Filter: (a >= 29)
+ -> Seq Scan on rlp_default_30 rlp_4
+ Filter: (a >= 29)
+ -> Seq Scan on rlp_default_default rlp_5
+ Filter: (a >= 29)
+(11 rows)
+
+explain (costs off) select * from rlp where a < 1 or (a > 20 and a < 25);
+ QUERY PLAN
+------------------------------------------------------
+ Append
+ -> Seq Scan on rlp1 rlp_1
+ Filter: ((a < 1) OR ((a > 20) AND (a < 25)))
+ -> Seq Scan on rlp4_1 rlp_2
+ Filter: ((a < 1) OR ((a > 20) AND (a < 25)))
+(5 rows)
+
+explain (costs off) select * from rlp where a = 20 or a = 40;
+ QUERY PLAN
+--------------------------------------------------
+ Append
+ -> Seq Scan on rlp4_1 rlp_1
+ Filter: (a = ANY ('{20,40}'::integer[]))
+ -> Seq Scan on rlp5_default rlp_2
+ Filter: (a = ANY ('{20,40}'::integer[]))
+(5 rows)
+
+explain (costs off) select * from rlp3 where a = 20; /* empty */
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+explain (costs off) select * from rlp where a > 1 and a = 10; /* only default
*/
+ QUERY PLAN
+----------------------------------
+ Seq Scan on rlp_default_10 rlp
+ Filter: ((a > 1) AND (a = 10))
+(2 rows)
+
+explain (costs off) select * from rlp where a > 1 and a >=15; /* rlp3
onwards, including default */
+ QUERY PLAN
+----------------------------------------------
+ Append
+ -> Seq Scan on rlp3abcd rlp_1
+ Filter: ((a > 1) AND (a >= 15))
+ -> Seq Scan on rlp3efgh rlp_2
+ Filter: ((a > 1) AND (a >= 15))
+ -> Seq Scan on rlp3nullxy rlp_3
+ Filter: ((a > 1) AND (a >= 15))
+ -> Seq Scan on rlp3_default rlp_4
+ Filter: ((a > 1) AND (a >= 15))
+ -> Seq Scan on rlp4_1 rlp_5
+ Filter: ((a > 1) AND (a >= 15))
+ -> Seq Scan on rlp4_2 rlp_6
+ Filter: ((a > 1) AND (a >= 15))
+ -> Seq Scan on rlp4_default rlp_7
+ Filter: ((a > 1) AND (a >= 15))
+ -> Seq Scan on rlp5_1 rlp_8
+ Filter: ((a > 1) AND (a >= 15))
+ -> Seq Scan on rlp5_default rlp_9
+ Filter: ((a > 1) AND (a >= 15))
+ -> Seq Scan on rlp_default_30 rlp_10
+ Filter: ((a > 1) AND (a >= 15))
+ -> Seq Scan on rlp_default_default rlp_11
+ Filter: ((a > 1) AND (a >= 15))
+(23 rows)
+
+explain (costs off) select * from rlp where a = 1 and a = 3; /* empty */
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a
= 15);
+ QUERY PLAN
+-------------------------------------------------------------------
+ Append
+ -> Seq Scan on rlp2 rlp_1
+ Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
+ -> Seq Scan on rlp3abcd rlp_2
+ Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
+ -> Seq Scan on rlp3efgh rlp_3
+ Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
+ -> Seq Scan on rlp3nullxy rlp_4
+ Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
+ -> Seq Scan on rlp3_default rlp_5
+ Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
+(11 rows)
+
+RESET enable_or_transformation;
-- multi-column keys
create table mc3p (a int, b int, c int) partition by range (a, abs(b), c);
create table mc3p_default partition of mc3p default;
@@ -2072,10 +2251,10 @@ explain (costs off) select * from hp where a = 1 and b
= 'abcde';
explain (costs off) select * from hp where a = 1 and b = 'abcde' and
(c = 2 or c = 3);
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------
Seq Scan on hp2 hp
- Filter: ((a = 1) AND (b = 'abcde'::text) AND ((c = 2) OR (c = 3)))
+ Filter: ((c = ANY ('{2,3}'::integer[])) AND (a = 1) AND (b = 'abcde'::text))
(2 rows)
drop table hp2;
diff --git a/src/test/regress/expected/stats_ext.out
b/src/test/regress/expected/stats_ext.out
index 10903bdab0..6f55b9e3ec 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -1322,19 +1322,19 @@ SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies WHERE
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies
WHERE (a = 1 OR a = 51) AND b = ''1''');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies
WHERE (a = 1 OR a = 51) AND (b = ''1'' OR b = ''2'')');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies
WHERE (a = 1 OR a = 2 OR a = 51 OR a = 52) AND (b = ''1'' OR b = ''2'')');
estimated | actual
-----------+--------
- 197 | 200
+ 200 | 200
(1 row)
-- OR clauses referencing different attributes are incompatible
@@ -1664,19 +1664,19 @@ SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies WHERE
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies
WHERE ((a * 2) = 2 OR (a * 2) = 102) AND upper(b) = ''1''');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies
WHERE ((a * 2) = 2 OR (a * 2) = 102) AND (upper(b) = ''1'' OR upper(b) =
''2'')');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies
WHERE ((a * 2) = 2 OR (a * 2) = 4 OR (a * 2) = 102 OR (a * 2) = 104) AND
(upper(b) = ''1'' OR upper(b) = ''2'')');
estimated | actual
-----------+--------
- 197 | 200
+ 200 | 200
(1 row)
-- OR clauses referencing different attributes
diff --git a/src/test/regress/expected/sysviews.out
b/src/test/regress/expected/sysviews.out
index 9be7aca2b8..1f9029b5b2 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -124,6 +124,7 @@ select name, setting from pg_settings where name like
'enable%';
enable_memoize | on
enable_mergejoin | on
enable_nestloop | on
+ enable_or_transformation | on
enable_parallel_append | on
enable_parallel_hash | on
enable_partition_pruning | on
@@ -134,7 +135,7 @@ select name, setting from pg_settings where name like
'enable%';
enable_seqscan | on
enable_sort | on
enable_tidscan | on
-(23 rows)
+(24 rows)
-- There are always wait event descriptions for various types.
select type, count(*) > 0 as ok FROM pg_wait_events
diff --git a/src/test/regress/expected/tidscan.out
b/src/test/regress/expected/tidscan.out
index f133b5a4ac..ad91abff7b 100644
--- a/src/test/regress/expected/tidscan.out
+++ b/src/test/regress/expected/tidscan.out
@@ -43,10 +43,26 @@ SELECT ctid, * FROM tidscan WHERE '(0,1)' = ctid;
-- OR'd clauses
EXPLAIN (COSTS OFF)
SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
- QUERY PLAN
---------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------
+ Tid Scan on tidscan
+ TID Cond: (ctid = ANY ('{"(0,1)","(0,2)"}'::tid[]))
+(2 rows)
+
+SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
+ ctid | id
+-------+----
+ (0,1) | 1
+ (0,2) | 2
+(2 rows)
+
+SET enable_or_transformation = on;
+EXPLAIN (COSTS OFF)
+SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
+ QUERY PLAN
+-------------------------------------------------------
Tid Scan on tidscan
- TID Cond: ((ctid = '(0,2)'::tid) OR ('(0,1)'::tid = ctid))
+ TID Cond: (ctid = ANY ('{"(0,1)","(0,2)"}'::tid[]))
(2 rows)
SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
@@ -56,6 +72,7 @@ SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' =
ctid;
(0,2) | 2
(2 rows)
+RESET enable_or_transformation;
-- ctid = ScalarArrayOp - implemented as tidscan
EXPLAIN (COSTS OFF)
SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
diff --git a/src/test/regress/sql/create_index.sql
b/src/test/regress/sql/create_index.sql
index d49ce9f300..56fde15bc1 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -737,6 +737,41 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+SET enable_or_transformation = on;
+EXPLAIN (COSTS OFF)
+SELECT * FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
+SELECT * FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand < 42 OR thousand < 99 OR 43 > thousand OR
42 > thousand);
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41;
+SELECT count(*) FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41;
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR
thousand = 41;
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR
thousand = 41;
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND
tenthous = 2);
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND
tenthous = 2);
+RESET enable_or_transformation;
+
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index c4c6c7b8ba..1663608043 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1408,6 +1408,16 @@ explain (costs off)
select * from tenk1 a join tenk1 b on
(a.unique1 = 1 and b.unique1 = 2) or
((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
+SET enable_or_transformation = on;
+explain (costs off)
+select * from tenk1 a join tenk1 b on
+ (a.unique1 = 1 and b.unique1 = 2) or
+ ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
+explain (costs off)
+select * from tenk1 a join tenk1 b on
+ (a.unique1 < 20 or a.unique1 = 3 or a.unique1 = 1 and b.unique1 = 2) or
+ ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
+RESET enable_or_transformation;
--
-- test placement of movable quals in a parameterized join tree
diff --git a/src/test/regress/sql/partition_prune.sql
b/src/test/regress/sql/partition_prune.sql
index a09b27d820..9717c8c835 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -21,6 +21,12 @@ explain (costs off) select * from lp where a is not null;
explain (costs off) select * from lp where a is null;
explain (costs off) select * from lp where a = 'a' or a = 'c';
explain (costs off) select * from lp where a is not null and (a = 'a' or a =
'c');
+
+SET enable_or_transformation = on;
+explain (costs off) select * from lp where a = 'a' or a = 'c';
+explain (costs off) select * from lp where a is not null and (a = 'a' or a =
'c');
+RESET enable_or_transformation;
+
explain (costs off) select * from lp where a <> 'g';
explain (costs off) select * from lp where a <> 'a' and a <> 'd';
explain (costs off) select * from lp where a not in ('a', 'd');
@@ -99,6 +105,22 @@ explain (costs off) select * from rlp where a > 1 and a
>=15; /* rlp3 onwards, i
explain (costs off) select * from rlp where a = 1 and a = 3; /* empty */
explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a
= 15);
+
+SET enable_or_transformation = on;
+explain (costs off) select * from rlp where a = 1 or a = 7;
+explain (costs off) select * from rlp where a = 1 or b = 'ab';
+explain (costs off) select * from rlp where a > 20 and a < 27;
+explain (costs off) select * from rlp where a = 29;
+explain (costs off) select * from rlp where a >= 29;
+explain (costs off) select * from rlp where a < 1 or (a > 20 and a < 25);
+explain (costs off) select * from rlp where a = 20 or a = 40;
+explain (costs off) select * from rlp3 where a = 20; /* empty */
+explain (costs off) select * from rlp where a > 1 and a = 10; /* only default
*/
+explain (costs off) select * from rlp where a > 1 and a >=15; /* rlp3
onwards, including default */
+explain (costs off) select * from rlp where a = 1 and a = 3; /* empty */
+explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a
= 15);
+RESET enable_or_transformation;
+
-- multi-column keys
create table mc3p (a int, b int, c int) partition by range (a, abs(b), c);
create table mc3p_default partition of mc3p default;
diff --git a/src/test/regress/sql/tidscan.sql b/src/test/regress/sql/tidscan.sql
index 313e0fb9b6..0499bedb9e 100644
--- a/src/test/regress/sql/tidscan.sql
+++ b/src/test/regress/sql/tidscan.sql
@@ -22,6 +22,12 @@ EXPLAIN (COSTS OFF)
SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
+SET enable_or_transformation = on;
+EXPLAIN (COSTS OFF)
+SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
+SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
+RESET enable_or_transformation;
+
-- ctid = ScalarArrayOp - implemented as tidscan
EXPLAIN (COSTS OFF)
SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index aa7a25b8f8..c6027c588d 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1657,6 +1657,8 @@ NumericVar
OM_uint32
OP
OSAPerGroupState
+OrClauseGroupEntry
+OrClauseGroupKey
OSAPerQueryState
OSInfo
OSSLCipher
--
2.44.0
From c53b658521f9112fe4d9b7e9ccaba978fe422828 Mon Sep 17 00:00:00 2001
From: "Andrey V. Lepikhov" <a.lepik...@postgrespro.ru>
Date: Wed, 13 Mar 2024 12:26:02 +0700
Subject: [PATCH 2/2] Teach generate_bitmap_or_paths to build BitmapOr paths
over SAOP clauses.
Likewise OR clauses, discover SAOP array and try to split its elements
between smaller sized arrays to fit a set of partial indexes.
---
doc/src/sgml/config.sgml | 3 +
src/backend/optimizer/path/indxpath.c | 315 ++++++++++++++++++----
src/backend/optimizer/util/predtest.c | 46 ++++
src/backend/optimizer/util/restrictinfo.c | 13 +
src/include/optimizer/optimizer.h | 16 ++
src/include/optimizer/restrictinfo.h | 1 +
src/test/regress/expected/select.out | 282 +++++++++++++++++++
src/test/regress/sql/select.sql | 82 ++++++
src/tools/pgindent/typedefs.list | 1 +
9 files changed, 706 insertions(+), 53 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 2de6ae301a..0df56f44e3 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5485,6 +5485,9 @@ ANY <replaceable class="parameter">num_sync</replaceable>
( <replaceable class="
The grouping technique of this transformation is based on the
equivalence of variable sides.
One side of such an expression must be a constant clause, and the
other must contain a variable clause.
The default is <literal>on</literal>.
+ Also, during BitmapScan paths generation it enables analysis of
elements
+ of IN or ANY constant arrays to cover such clause with BitmapOr set of
+ partial index scans.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/optimizer/path/indxpath.c
b/src/backend/optimizer/path/indxpath.c
index 32c6a8bbdc..f92a47c3d5 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -32,6 +32,7 @@
#include "optimizer/paths.h"
#include "optimizer/prep.h"
#include "optimizer/restrictinfo.h"
+#include "utils/array.h"
#include "utils/lsyscache.h"
#include "utils/selfuncs.h"
@@ -1220,11 +1221,188 @@ build_paths_for_OR(PlannerInfo *root, RelOptInfo *rel,
return result;
}
+/*
+ * Building index paths over SAOP clause differs from the logic of OR clauses.
+ * Here we iterate across all the array elements and split them to SAOPs,
+ * corresponding to different indexes. We must match each element to an index.
+ */
+static List *
+build_paths_for_SAOP(PlannerInfo *root, RelOptInfo *rel, RestrictInfo *rinfo,
+ List *other_clauses)
+{
+ List *result = NIL;
+ List *predicate_lists = NIL;
+ ListCell *lc;
+ PredicatesData *pd;
+ ScalarArrayOpExpr *saop = (ScalarArrayOpExpr *) rinfo->clause;
+
+ Assert(IsA(saop, ScalarArrayOpExpr) && saop->useOr);
+
+ if (!IsA(lsecond(saop->args), Const))
+ /*
+ * Has it practical outcome to merge arrays which couldn't
constantified
+ * before that step?
+ */
+ return NIL;
+
+ /* Collect predicates */
+ foreach(lc, rel->indexlist)
+ {
+ IndexOptInfo *index = (IndexOptInfo *) lfirst(lc);
+
+ /* Take into consideration partial indexes supporting bitmap
scans */
+ if (!index->amhasgetbitmap || index->indpred == NIL ||
index->predOK)
+ continue;
+
+ pd = palloc0(sizeof(PredicatesData));
+ pd->id = foreach_current_index(lc);
+ /* The trick with reducing recursion is stolen from
predicate_implied_by */
+ pd->predicate = list_length(index->indpred) == 1 ?
+
(Node *) linitial(index->indpred) :
+
(Node *) index->indpred;
+ predicate_lists = lappend(predicate_lists, (void *) pd);
+ }
+
+ /* Split the array data according to index predicates. */
+ if (predicate_lists == NIL ||
+ !saop_covered_by_predicates(saop, predicate_lists))
+ return NIL;
+
+ other_clauses = list_delete_ptr(other_clauses, rinfo);
+
+ /*
+ * Having incoming SAOP split to set of smaller SAOPs which can be
applied
+ * to partial indexes, generate paths for each one.
+ */
+ foreach(lc, predicate_lists)
+ {
+ IndexOptInfo *index;
+ IndexClauseSet clauseset;
+ List *indexpaths;
+ RestrictInfo *rinfo1 = NULL;
+ Expr *clause;
+ ArrayType *arrayval = NULL;
+ ArrayExpr *arr = NULL;
+ Const *arrayconst;
+ ScalarArrayOpExpr *dest;
+
+ pd = (PredicatesData *) lfirst(lc);
+ if (pd->elems == NIL)
+ /* The index doesn't participate in this operation */
+ continue;
+
+ /* Make up new array */
+ arrayconst = lsecond_node(Const, saop->args);
+ arrayval = DatumGetArrayTypeP(arrayconst->constvalue);
+ arr = makeNode(ArrayExpr);
+ arr->array_collid = arrayconst->constcollid;
+ arr->array_typeid = arrayconst->consttype;
+ arr->element_typeid = arrayval->elemtype;
+ arr->elements = pd->elems;
+ arr->location = -1;
+ arr->multidims = false;
+
+ /* Compose new SAOP, partially covering the source one */
+ dest = makeNode(ScalarArrayOpExpr);
+ memcpy(dest, saop, sizeof(ScalarArrayOpExpr));
+ dest->args = list_make2(linitial(saop->args), arr);
+ clause = (Expr *) estimate_expression_value(root, (Node *)
dest);
+
+ /*
+ * Create new RestrictInfo. It maybe more heavy than just copy
node,
+ * but remember some internals: the serial number, selectivity
+ * cache etc.
+ */
+ rinfo1 = make_restrictinfo(root, clause,
+
rinfo->is_pushed_down,
+
rinfo->has_clone,
+
rinfo->is_clone,
+
rinfo->pseudoconstant,
+
rinfo->security_level,
+
rinfo->required_relids,
+
rinfo->incompatible_relids,
+
rinfo->outer_relids);
+
+ index = list_nth(rel->indexlist, pd->id);
+ Assert(predicate_implied_by(index->indpred, list_make1(rinfo1),
true));
+
+ /* Excluding partial indexes with predOK we make this statement
false */
+ Assert(!predicate_implied_by(index->indpred, other_clauses,
false));
+
+ /* Time to generate index paths */
+
+ MemSet(&clauseset, 0, sizeof(clauseset));
+ match_clauses_to_index(root, list_make1(rinfo1), index,
&clauseset);
+ match_clauses_to_index(root, other_clauses, index, &clauseset);
+
+ /* Predicate has found already. So, it is ok for the empty
match */
+
+ indexpaths = build_index_paths(root, rel,
+
index, &clauseset,
+ true,
+
ST_BITMAPSCAN,
+ NULL,
+
NULL);
+ Assert(indexpaths != NIL);
+ result = lappend(result, indexpaths);
+ }
+ return result;
+}
+
+/*
+ * Analyse incoming SAOP node to cover it by partial indexes.
+ *
+ * The returning pathlist must be ANDed to the final BitmapScan path.
+ * The function returns NULL when an array element cannot be fitted with some
+ * partial index. The Rationale for such an operation is that when schema
+ * contains many partial indexes, the SAOP clause may be effectively fulfilled
+ * by appending results of scanning some minimal set of tiny partial indexes.
+ *
+ * Working jointly with the TransformOrExprToANY routine, it provides a user
+ * with some sort of independence of the query plan from the approach to
writing
+ * alternatives for the same entity in the WHERE section.
+ */
+static List *
+generate_saop_pathlist(PlannerInfo *root, RelOptInfo *rel,
+ RestrictInfo *rinfo, List
*all_clauses)
+{
+ List *pathlist = NIL;
+ Path *bitmapqual;
+ List *indlist;
+ ListCell *lc;
+
+ if (!enable_or_transformation)
+ return NIL;
+
+ /*
+ * We must be able to match at least one index to each element of
+ * the array, else we can't use it.
+ */
+ indlist = build_paths_for_SAOP(root, rel, rinfo, all_clauses);
+ if (indlist == NIL)
+ return NIL;
+
+ /*
+ * OK, pick the most promising AND combination, and add it to
+ * pathlist.
+ */
+ foreach (lc, indlist)
+ {
+ List *plist = lfirst_node(List, lc);
+
+ bitmapqual = choose_bitmap_and(root, rel, plist);
+ pathlist = lappend(pathlist, bitmapqual);
+ }
+
+ return pathlist;
+}
+
/*
* generate_bitmap_or_paths
- * Look through the list of clauses to find OR clauses, and
generate
- * a BitmapOrPath for each one we can handle that way. Return a
list
- * of the generated BitmapOrPaths.
+ * Look through the list of clauses to find OR and SAOP clauses,
and
+ * Each saop clause are splitted to be covered by partial indexes.
+ * generate a BitmapOrPath for each one we can handle that way.
+ * Return a list of the generated BitmapOrPaths.
*
* other_clauses is a list of additional clauses that can be assumed true
* for the purpose of generating indexquals, but are not to be searched for
@@ -1247,68 +1425,99 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo
*rel,
foreach(lc, clauses)
{
RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
- List *pathlist;
+ List *pathlist = NIL;
Path *bitmapqual;
ListCell *j;
- /* Ignore RestrictInfos that aren't ORs */
- if (!restriction_is_or_clause(rinfo))
+ if (restriction_is_saop_clause(rinfo))
+ {
+ pathlist = generate_saop_pathlist(root, rel, rinfo,
+
all_clauses);
+ }
+ else if (!restriction_is_or_clause(rinfo))
+ /* Ignore RestrictInfos that aren't ORs */
continue;
-
- /*
- * We must be able to match at least one index to each of the
arms of
- * the OR, else we can't use it.
- */
- pathlist = NIL;
- foreach(j, ((BoolExpr *) rinfo->orclause)->args)
+ else
{
- Node *orarg = (Node *) lfirst(j);
- List *indlist;
-
- /* OR arguments should be ANDs or sub-RestrictInfos */
- if (is_andclause(orarg))
+ /*
+ * We must be able to match at least one index to each
of the arms of
+ * the OR, else we can't use it.
+ */
+ foreach(j, ((BoolExpr *) rinfo->orclause)->args)
{
- List *andargs = ((BoolExpr *)
orarg)->args;
+ Node *orarg = (Node *) lfirst(j);
+ List *indlist;
- indlist = build_paths_for_OR(root, rel,
-
andargs,
-
all_clauses);
+ /* OR arguments should be ANDs or
sub-RestrictInfos */
+ if (is_andclause(orarg))
+ {
+ List *andargs = ((BoolExpr *)
orarg)->args;
- /* Recurse in case there are sub-ORs */
- indlist = list_concat(indlist,
-
generate_bitmap_or_paths(root, rel,
-
andargs,
-
all_clauses));
- }
- else
- {
- RestrictInfo *ri = castNode(RestrictInfo,
orarg);
- List *orargs;
+ indlist = build_paths_for_OR(root, rel,
+
andargs,
+
all_clauses);
- Assert(!restriction_is_or_clause(ri));
- orargs = list_make1(ri);
+ /* Recurse in case there are sub-ORs */
+ indlist = list_concat(indlist,
+
generate_bitmap_or_paths(root, rel,
+
andargs,
+
all_clauses));
+ }
+ else
+ {
+ RestrictInfo *ri =
castNode(RestrictInfo, orarg);
+ List *orargs;
- indlist = build_paths_for_OR(root, rel,
-
orargs,
-
all_clauses);
- }
+ Assert(!restriction_is_or_clause(ri));
- /*
- * If nothing matched this arm, we can't do anything
with this OR
- * clause.
- */
- if (indlist == NIL)
- {
- pathlist = NIL;
- break;
- }
+ orargs = list_make1(ri);
- /*
- * OK, pick the most promising AND combination, and add
it to
- * pathlist.
- */
- bitmapqual = choose_bitmap_and(root, rel, indlist);
- pathlist = lappend(pathlist, bitmapqual);
+ if (restriction_is_saop_clause(ri))
+ {
+ List *paths;
+
+ paths =
generate_saop_pathlist(root, rel, ri,
+
all_clauses);
+
+ if (paths != NIL)
+ {
+ /*
+ * Add paths to
pathlist and immediately jump to the
+ * next element of the
OR clause.
+ */
+ pathlist =
list_concat(pathlist, paths);
+ continue;
+ }
+
+ /*
+ * Pass down out of this if
construction:
+ * If saop isn't covered by
partial indexes, try to
+ * build scan path for the saop
as a whole.
+ */
+ }
+
+ indlist = build_paths_for_OR(root, rel,
+
orargs,
+
all_clauses);
+ }
+
+ /*
+ * If nothing matched this arm, we can't do
anything with this OR
+ * clause.
+ */
+ if (indlist == NIL)
+ {
+ pathlist = NIL;
+ break;
+ }
+
+ /*
+ * OK, pick the most promising AND combination,
and add it to
+ * pathlist.
+ */
+ bitmapqual = choose_bitmap_and(root, rel,
indlist);
+ pathlist = lappend(pathlist, bitmapqual);
+ }
}
/*
diff --git a/src/backend/optimizer/util/predtest.c
b/src/backend/optimizer/util/predtest.c
index c37b416e24..8ed80a78b4 100644
--- a/src/backend/optimizer/util/predtest.c
+++ b/src/backend/optimizer/util/predtest.c
@@ -112,6 +112,52 @@ static Oid get_btree_test_op(Oid pred_op, Oid clause_op,
bool refute_it);
static void InvalidateOprProofCacheCallBack(Datum arg, int cacheid, uint32
hashvalue);
+/*
+ * Could this ANY () expression can be split into a set of ANYs over partial
+ * indexes? If yes, return these saops in the PredicatesData structure.
+ */
+bool
+saop_covered_by_predicates(ScalarArrayOpExpr *saop, List *predicate_lists)
+{
+ ListCell *lc;
+ PredIterInfoData clause_info;
+ bool result = false;
+
+ if (predicate_classify((Node *) saop, &clause_info) != CLASS_OR)
+ return false;
+
+ iterate_begin(pitem, (Node *) saop, clause_info)
+ {
+ result = false;
+
+ foreach(lc, predicate_lists)
+ {
+ PredicatesData *pd = (PredicatesData *) lfirst(lc);
+
+ if (!predicate_implied_by_recurse(pitem, pd->predicate,
false))
+ continue;
+
+ /* Predicate is found. Add the elem to the saop clause
*/
+ Assert(IsA(pitem, OpExpr));
+
+ /* Extract constant from the expression */
+ pd->elems = lappend(pd->elems,
+ copyObject(lsecond_node(Const, ((OpExpr
*) pitem)->args)));
+ result = true;
+ break;
+ }
+
+ if (!result)
+ /*
+ * The element doesn't fit any index. Interrupt the
process immediately
+ */
+ break;
+ }
+ iterate_end(clause_info);
+
+ return result;
+}
+
/*
* predicate_implied_by
* Recursively checks whether the clauses in clause_list imply that the
diff --git a/src/backend/optimizer/util/restrictinfo.c
b/src/backend/optimizer/util/restrictinfo.c
index 0b406e9334..1dad1dc654 100644
--- a/src/backend/optimizer/util/restrictinfo.c
+++ b/src/backend/optimizer/util/restrictinfo.c
@@ -421,6 +421,19 @@ restriction_is_or_clause(RestrictInfo *restrictinfo)
return false;
}
+bool
+restriction_is_saop_clause(RestrictInfo *restrictinfo)
+{
+ if (restrictinfo->clause && IsA(restrictinfo->clause,
ScalarArrayOpExpr))
+ {
+ ScalarArrayOpExpr *saop = (ScalarArrayOpExpr *)
restrictinfo->clause;
+
+ if (saop->useOr)
+ return true;
+ }
+ return false;
+}
+
/*
* restriction_is_securely_promotable
*
diff --git a/src/include/optimizer/optimizer.h
b/src/include/optimizer/optimizer.h
index 35ab577501..72cf88dcbd 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -160,6 +160,22 @@ extern List *expand_function_arguments(List *args, bool
include_out_arguments,
/* in util/predtest.c: */
+/*
+ * Contains information needed to extract from saop a set of elements which can
+ * be covered by the partial index:
+ * id - caller's identification of the index.
+ * predicate - predicate expression of the index
+ * elems - returning list of array elements which corresponds to this predicate
+ */
+typedef struct PredicatesData
+{
+ int id;
+ Node *predicate;
+ List *elems;
+} PredicatesData;
+
+extern bool saop_covered_by_predicates(ScalarArrayOpExpr *saop,
+ List
*predicate_lists);
extern bool predicate_implied_by(List *predicate_list, List *clause_list,
bool weak);
extern bool predicate_refuted_by(List *predicate_list, List *clause_list,
diff --git a/src/include/optimizer/restrictinfo.h
b/src/include/optimizer/restrictinfo.h
index 1b42c832c5..2cd5fbf943 100644
--- a/src/include/optimizer/restrictinfo.h
+++ b/src/include/optimizer/restrictinfo.h
@@ -34,6 +34,7 @@ extern RestrictInfo *make_restrictinfo(PlannerInfo *root,
Relids outer_relids);
extern RestrictInfo *commute_restrictinfo(RestrictInfo *rinfo, Oid comm_op);
extern bool restriction_is_or_clause(RestrictInfo *restrictinfo);
+extern bool restriction_is_saop_clause(RestrictInfo *restrictinfo);
extern bool restriction_is_securely_promotable(RestrictInfo *restrictinfo,
RelOptInfo *rel);
extern List *get_actual_clauses(List *restrictinfo_list);
diff --git a/src/test/regress/expected/select.out
b/src/test/regress/expected/select.out
index 33a6dceb0e..81399ec5b1 100644
--- a/src/test/regress/expected/select.out
+++ b/src/test/regress/expected/select.out
@@ -907,6 +907,288 @@ select unique1, unique2 from onek2
0 | 998
(2 rows)
+SET enable_seqscan TO off;
+SET enable_indexscan TO off; -- Only BitmapScan is a subject matter here
+SET enable_or_transformation = 'off';
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM onek2 WHERE stringu1 = 'A' OR stringu1 = 'J';
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on onek2
+ Recheck Cond: ((stringu1 < 'B'::name) OR (stringu1 = 'J'::name))
+ Filter: ((stringu1 = 'A'::name) OR (stringu1 = 'J'::name))
+ -> BitmapOr
+ -> Bitmap Index Scan on onek2_u2_prtl
+ -> Bitmap Index Scan on onek2_stu1_prtl
+ Index Cond: (stringu1 = 'J'::name)
+(8 rows)
+
+-- Without the transformation only seqscan possible here
+EXPLAIN (COSTS OFF)
+SELECT unique2, stringu1 FROM onek2
+WHERE unique2 < 1 AND stringu1 IN ('A','J') AND stringu1 < 'Z';
+ QUERY PLAN
+---------------------------------------------------------------------------------------------
+ Seq Scan on onek2
+ Filter: ((unique2 < 1) AND (stringu1 = ANY ('{A,J}'::name[])) AND (stringu1
< 'Z'::name))
+(2 rows)
+
+-- Use partial indexes
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM onek2
+WHERE stringu1 IN ('B','J') AND (stringu1 = 'A' OR unique1 = 1);
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on onek2
+ Recheck Cond: ((stringu1 < 'B'::name) OR (unique1 = 1))
+ Filter: ((stringu1 = ANY ('{B,J}'::name[])) AND ((stringu1 =
'A'::name) OR (unique1 = 1)))
+ -> BitmapOr
+ -> Bitmap Index Scan on onek2_u2_prtl
+ -> Bitmap Index Scan on onek2_u1_prtl
+ Index Cond: (unique1 = 1)
+(8 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT unique2, stringu1 FROM onek2
+WHERE unique1 < 1 OR (stringu1 = 'A' OR stringu1 = 'J');
+ QUERY PLAN
+-------------------------------------------------------------------------------------
+ Bitmap Heap Scan on onek2
+ Recheck Cond: ((unique1 < 1) OR (stringu1 < 'B'::name) OR (stringu1 =
'J'::name))
+ Filter: ((unique1 < 1) OR (stringu1 = 'A'::name) OR (stringu1 = 'J'::name))
+ -> BitmapOr
+ -> Bitmap Index Scan on onek2_u1_prtl
+ Index Cond: (unique1 < 1)
+ -> Bitmap Index Scan on onek2_u2_prtl
+ -> Bitmap Index Scan on onek2_stu1_prtl
+ Index Cond: (stringu1 = 'J'::name)
+(9 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT unique2, stringu1 FROM onek2
+WHERE unique1 = 1 OR unique2 = PI()::integer;
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on onek2
+ Filter: ((unique1 = 1) OR (unique2 = 3))
+(2 rows)
+
+RESET enable_or_transformation;
+-- OR <-> ANY transformation must find a path with partial indexes scan
+-- regardless the clause representation.
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM onek2 WHERE stringu1 = 'A' OR stringu1 = 'J';
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on onek2
+ Recheck Cond: ((stringu1 = ANY ('{J}'::name[])) OR (stringu1 <
'B'::name))
+ Filter: (stringu1 = ANY ('{A,J}'::name[]))
+ -> BitmapOr
+ -> Bitmap Index Scan on onek2_stu1_prtl
+ Index Cond: (stringu1 = ANY ('{J}'::name[]))
+ -> Bitmap Index Scan on onek2_u2_prtl
+(8 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM onek2 WHERE stringu1 IN ('A','J');
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on onek2
+ Recheck Cond: ((stringu1 = ANY ('{J}'::name[])) OR (stringu1 <
'B'::name))
+ Filter: (stringu1 = ANY ('{A,J}'::name[]))
+ -> BitmapOr
+ -> Bitmap Index Scan on onek2_stu1_prtl
+ Index Cond: (stringu1 = ANY ('{J}'::name[]))
+ -> Bitmap Index Scan on onek2_u2_prtl
+(8 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM onek2 WHERE stringu1 = ANY ('{A,J}');
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on onek2
+ Recheck Cond: ((stringu1 = ANY ('{J}'::name[])) OR (stringu1 <
'B'::name))
+ Filter: (stringu1 = ANY ('{A,J}'::name[]))
+ -> BitmapOr
+ -> Bitmap Index Scan on onek2_stu1_prtl
+ Index Cond: (stringu1 = ANY ('{J}'::name[]))
+ -> Bitmap Index Scan on onek2_u2_prtl
+(8 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM onek2 WHERE stringu1 IN ('A') OR stringu1 IN ('J');
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on onek2
+ Recheck Cond: ((stringu1 = ANY ('{J}'::name[])) OR (stringu1 <
'B'::name))
+ Filter: (stringu1 = ANY ('{A,J}'::name[]))
+ -> BitmapOr
+ -> Bitmap Index Scan on onek2_stu1_prtl
+ Index Cond: (stringu1 = ANY ('{J}'::name[]))
+ -> Bitmap Index Scan on onek2_u2_prtl
+(8 rows)
+
+-- Don't scan partial indexes because of extra value.
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM onek2 WHERE stringu1 IN ('A', 'J', 'C');
+ QUERY PLAN
+------------------------------------------------------
+ Aggregate
+ -> Seq Scan on onek2
+ Filter: (stringu1 = ANY ('{A,J,C}'::name[]))
+(3 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT unique2 FROM onek2
+WHERE stringu1 IN ('A', 'A') AND (stringu1 = 'A' OR stringu1 = 'A');
+ QUERY PLAN
+-------------------------------------------------------------------------------------
+ Bitmap Heap Scan on onek2
+ Recheck Cond: (stringu1 < 'B'::name)
+ Filter: ((stringu1 = ANY ('{A}'::name[])) AND (stringu1 = ANY
('{A,A}'::name[])))
+ -> Bitmap Index Scan on onek2_u2_prtl
+(4 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT unique2, stringu1 FROM onek2
+WHERE unique2 < 1 AND stringu1 IN ('A','J') AND stringu1 < 'Z';
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on onek2
+ Recheck Cond: (((stringu1 = ANY ('{J}'::name[])) AND (stringu1 <
'Z'::name)) OR ((unique2 < 1) AND (stringu1 < 'B'::name)))
+ Filter: ((unique2 < 1) AND (stringu1 = ANY ('{A,J}'::name[])))
+ -> BitmapOr
+ -> Bitmap Index Scan on onek2_stu1_prtl
+ Index Cond: ((stringu1 = ANY ('{J}'::name[])) AND (stringu1 <
'Z'::name))
+ -> Bitmap Index Scan on onek2_u2_prtl
+ Index Cond: (unique2 < 1)
+(8 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT unique2, stringu1 FROM onek2
+WHERE unique1 = 1 OR unique1 = PI()::integer;
+ QUERY PLAN
+--------------------------------------------------
+ Bitmap Heap Scan on onek2
+ Recheck Cond: ((unique1 = 1) OR (unique1 = 3))
+ -> BitmapOr
+ -> Bitmap Index Scan on onek2_u1_prtl
+ Index Cond: (unique1 = 1)
+ -> Bitmap Index Scan on onek2_u1_prtl
+ Index Cond: (unique1 = 3)
+(7 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT unique2, stringu1 FROM onek2
+WHERE unique1 IN (1, PI()::integer); -- TODO: why it is differs from previous
example?
+ QUERY PLAN
+----------------------------------------------------------
+ Bitmap Heap Scan on onek2
+ Recheck Cond: (unique1 = ANY ('{1,3}'::integer[]))
+ -> Bitmap Index Scan on onek2_u1_prtl
+ Index Cond: (unique1 = ANY ('{1,3}'::integer[]))
+(4 rows)
+
+-- Don't apply the optimization to clauses, containing volatile functions
+EXPLAIN (COSTS OFF)
+SELECT unique2,stringu1 FROM onek2
+WHERE unique1 = (random()*2)::integer OR unique1 = (random()*3)::integer;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on onek2
+ Filter: ((unique1 = ((random() * '2'::double precision))::integer) OR
(unique1 = ((random() * '3'::double precision))::integer))
+(2 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT unique2,stringu1 FROM onek2
+WHERE unique1 IN ((random()*2)::integer, (random()*3)::integer);
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on onek2
+ Filter: (unique1 = ANY (ARRAY[((random() * '2'::double
precision))::integer, ((random() * '3'::double precision))::integer]))
+(2 rows)
+
+-- Combine different saops. Soe of them doesnt' fit a set of partial indexes,
+-- but other fits.
+-- Unfortunately, we don't combine saop and OR clauses so far.
+EXPLAIN (COSTS OFF)
+SELECT unique2,stringu1 FROM onek2
+WHERE
+ unique1 IN (1,2,21) AND
+ (stringu1 IN ('A','J') OR unique1 IN (3,4) OR stringu1 = 'J');
+
QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on onek2
+ Recheck Cond: ((stringu1 = ANY ('{J}'::name[])) OR (stringu1 < 'B'::name)
OR ((unique1 = ANY ('{3,4}'::integer[])) AND (unique1 = ANY
('{1,2,21}'::integer[]))) OR (stringu1 = 'J'::name))
+ Filter: ((unique1 = ANY ('{1,2,21}'::integer[])) AND ((stringu1 = ANY
('{A,J}'::name[])) OR (unique1 = ANY ('{3,4}'::integer[])) OR (stringu1 =
'J'::name)))
+ -> BitmapOr
+ -> Bitmap Index Scan on onek2_stu1_prtl
+ Index Cond: (stringu1 = ANY ('{J}'::name[]))
+ -> Bitmap Index Scan on onek2_u2_prtl
+ -> Bitmap Index Scan on onek2_u1_prtl
+ Index Cond: ((unique1 = ANY ('{3,4}'::integer[])) AND (unique1
= ANY ('{1,2,21}'::integer[])))
+ -> Bitmap Index Scan on onek2_stu1_prtl
+ Index Cond: (stringu1 = 'J'::name)
+(11 rows)
+
+-- Check recursive combination of OR and SAOP expressions
+EXPLAIN (COSTS OFF)
+SELECT unique2, stringu1 FROM onek2
+WHERE unique1 < 1 OR (stringu1 = 'A' OR stringu1 = 'J');
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on onek2
+ Recheck Cond: ((unique1 < 1) OR (stringu1 = ANY ('{J}'::name[])) OR
(stringu1 < 'B'::name))
+ Filter: ((unique1 < 1) OR (stringu1 = ANY ('{A,J}'::name[])))
+ -> BitmapOr
+ -> Bitmap Index Scan on onek2_u1_prtl
+ Index Cond: (unique1 < 1)
+ -> Bitmap Index Scan on onek2_stu1_prtl
+ Index Cond: (stringu1 = ANY ('{J}'::name[]))
+ -> Bitmap Index Scan on onek2_u2_prtl
+(9 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT unique2, stringu1 FROM onek2
+WHERE (unique1 < 1 OR stringu1 IN ('A','J'));
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on onek2
+ Recheck Cond: ((unique1 < 1) OR (stringu1 = ANY ('{J}'::name[])) OR
(stringu1 < 'B'::name))
+ Filter: ((unique1 < 1) OR (stringu1 = ANY ('{A,J}'::name[])))
+ -> BitmapOr
+ -> Bitmap Index Scan on onek2_u1_prtl
+ Index Cond: (unique1 < 1)
+ -> Bitmap Index Scan on onek2_stu1_prtl
+ Index Cond: (stringu1 = ANY ('{J}'::name[]))
+ -> Bitmap Index Scan on onek2_u2_prtl
+(9 rows)
+
+-- Although SAOP doesn't fit partial indexes fully, we can use anded OR clause
+-- to scan another couple of partial indexes.
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM onek2
+WHERE stringu1 IN ('B','J') AND (stringu1 = 'A' OR unique1 = 1);
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on onek2
+ Recheck Cond: ((stringu1 < 'B'::name) OR (unique1 = 1))
+ Filter: ((stringu1 = ANY ('{B,J}'::name[])) AND ((stringu1 =
'A'::name) OR (unique1 = 1)))
+ -> BitmapOr
+ -> Bitmap Index Scan on onek2_u2_prtl
+ -> Bitmap Index Scan on onek2_u1_prtl
+ Index Cond: (unique1 = 1)
+(8 rows)
+
+RESET enable_indexscan;
+RESET enable_seqscan;
--
-- Test some corner cases that have been known to confuse the planner
--
diff --git a/src/test/regress/sql/select.sql b/src/test/regress/sql/select.sql
index 019f1e7673..223f55af4e 100644
--- a/src/test/regress/sql/select.sql
+++ b/src/test/regress/sql/select.sql
@@ -234,6 +234,88 @@ select unique1, unique2 from onek2
select unique1, unique2 from onek2
where (unique2 = 11 and stringu1 < 'B') or unique1 = 0;
+SET enable_seqscan TO off;
+SET enable_indexscan TO off; -- Only BitmapScan is a subject matter here
+SET enable_or_transformation = 'off';
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM onek2 WHERE stringu1 = 'A' OR stringu1 = 'J';
+-- Without the transformation only seqscan possible here
+EXPLAIN (COSTS OFF)
+SELECT unique2, stringu1 FROM onek2
+WHERE unique2 < 1 AND stringu1 IN ('A','J') AND stringu1 < 'Z';
+-- Use partial indexes
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM onek2
+WHERE stringu1 IN ('B','J') AND (stringu1 = 'A' OR unique1 = 1);
+EXPLAIN (COSTS OFF)
+SELECT unique2, stringu1 FROM onek2
+WHERE unique1 < 1 OR (stringu1 = 'A' OR stringu1 = 'J');
+EXPLAIN (COSTS OFF)
+SELECT unique2, stringu1 FROM onek2
+WHERE unique1 = 1 OR unique2 = PI()::integer;
+RESET enable_or_transformation;
+
+-- OR <-> ANY transformation must find a path with partial indexes scan
+-- regardless the clause representation.
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM onek2 WHERE stringu1 = 'A' OR stringu1 = 'J';
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM onek2 WHERE stringu1 IN ('A','J');
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM onek2 WHERE stringu1 = ANY ('{A,J}');
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM onek2 WHERE stringu1 IN ('A') OR stringu1 IN ('J');
+
+-- Don't scan partial indexes because of extra value.
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM onek2 WHERE stringu1 IN ('A', 'J', 'C');
+EXPLAIN (COSTS OFF)
+SELECT unique2 FROM onek2
+WHERE stringu1 IN ('A', 'A') AND (stringu1 = 'A' OR stringu1 = 'A');
+
+EXPLAIN (COSTS OFF)
+SELECT unique2, stringu1 FROM onek2
+WHERE unique2 < 1 AND stringu1 IN ('A','J') AND stringu1 < 'Z';
+EXPLAIN (COSTS OFF)
+SELECT unique2, stringu1 FROM onek2
+WHERE unique1 = 1 OR unique1 = PI()::integer;
+EXPLAIN (COSTS OFF)
+SELECT unique2, stringu1 FROM onek2
+WHERE unique1 IN (1, PI()::integer); -- TODO: why it is differs from previous
example?
+
+-- Don't apply the optimization to clauses, containing volatile functions
+EXPLAIN (COSTS OFF)
+SELECT unique2,stringu1 FROM onek2
+WHERE unique1 = (random()*2)::integer OR unique1 = (random()*3)::integer;
+EXPLAIN (COSTS OFF)
+SELECT unique2,stringu1 FROM onek2
+WHERE unique1 IN ((random()*2)::integer, (random()*3)::integer);
+
+-- Combine different saops. Soe of them doesnt' fit a set of partial indexes,
+-- but other fits.
+-- Unfortunately, we don't combine saop and OR clauses so far.
+EXPLAIN (COSTS OFF)
+SELECT unique2,stringu1 FROM onek2
+WHERE
+ unique1 IN (1,2,21) AND
+ (stringu1 IN ('A','J') OR unique1 IN (3,4) OR stringu1 = 'J');
+
+-- Check recursive combination of OR and SAOP expressions
+EXPLAIN (COSTS OFF)
+SELECT unique2, stringu1 FROM onek2
+WHERE unique1 < 1 OR (stringu1 = 'A' OR stringu1 = 'J');
+EXPLAIN (COSTS OFF)
+SELECT unique2, stringu1 FROM onek2
+WHERE (unique1 < 1 OR stringu1 IN ('A','J'));
+-- Although SAOP doesn't fit partial indexes fully, we can use anded OR clause
+-- to scan another couple of partial indexes.
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM onek2
+WHERE stringu1 IN ('B','J') AND (stringu1 = 'A' OR unique1 = 1);
+
+RESET enable_indexscan;
+RESET enable_seqscan;
+
--
-- Test some corner cases that have been known to confuse the planner
--
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index c6027c588d..bac4e3c2c8 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2143,6 +2143,7 @@ PredIterInfoData
PredXactList
PredicateLockData
PredicateLockTargetType
+PredicatesData
PrefetchBufferResult
PrepParallelRestorePtrType
PrepareStmt
--
2.44.0
