Hi!
On 07.03.2024 17:51, Alexander Korotkov wrote:
Hi!
On Tue, Mar 5, 2024 at 9:59 AM Andrei Lepikhov
<a.lepik...@postgrespro.ru> wrote:
> On 5/3/2024 12:30, Andrei Lepikhov wrote:
> > On 4/3/2024 09:26, jian he wrote:
> ... and the new version of the patchset is attached.
I made some revisions for the patchset.
1) Use hash_combine() to combine hash values.
2) Upper limit the number of array elements by MAX_SAOP_ARRAY_SIZE.
3) Better save the original order of clauses by putting hash entries
and untransformable clauses to the same list. A lot of differences in
regression tests output have gone.
Thank you for your changes. I agree with them.
One important issue I found.
# create table t as (select i::int%100 i from generate_series(1,10000) i);
# analyze t;
# explain select * from t where i = 1 or i = 1;
QUERY PLAN
-----------------------------------------------------
Seq Scan on t (cost=0.00..189.00 rows=200 width=4)
Filter: (i = ANY ('{1,1}'::integer[]))
(2 rows)
# set enable_or_transformation = false;
SET
# explain select * from t where i = 1 or i = 1;
QUERY PLAN
-----------------------------------------------------
Seq Scan on t (cost=0.00..189.00 rows=100 width=4)
Filter: (i = 1)
(2 rows)
We don't make array values unique. That might make query execution
performance somewhat worse, and also makes selectivity estimation
worse. I suggest Andrei and/or Alena should implement making array
values unique.
I have corrected this and some spelling mistakes. The
unique_any_elements_change.no-cfbot file contains changes.
While I was correcting the test results caused by such changes, I
noticed that the same behavior was when converting the IN expression,
and this can be seen in the result of the regression test:
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,A}'::name[])) AND (stringu1 = 'A'::name))
-> Bitmap Index Scan on onek2_u2_prtl
(4 rows)
--
Regards,
Alena Rybakina
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index baeb83e58b..e9813ef6ab 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -312,8 +312,8 @@ TransformOrExprToANY(ParseState *pstate, List *args, int
location)
if (unlikely(found))
{
- entry->consts = lappend(entry->consts, const_expr);
- entry->exprs = lappend(entry->exprs, orqual);
+ entry->consts = list_append_unique(entry->consts,
const_expr);
+ entry->exprs = list_append_unique(entry->exprs, orqual);
}
else
{
@@ -352,7 +352,6 @@ TransformOrExprToANY(ParseState *pstate, List *args, int
location)
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 ||
list_length(entry->consts) > MAX_SAOP_ARRAY_SIZE)
diff --git a/src/test/regress/expected/create_index.out
b/src/test/regress/expected/create_index.out
index 7b721bac71..606a4399f9 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1915,16 +1915,16 @@ SELECT count(*) FROM tenk1
EXPLAIN (COSTS OFF)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand < 42 OR thousand < 99 OR 43 > thousand OR
42 > thousand);
- QUERY PLAN
-------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------
Aggregate
-> Bitmap Heap Scan on tenk1
- Recheck Cond: ((hundred = 42) AND (thousand < ANY
('{42,99,43,42}'::integer[])))
+ Recheck Cond: ((hundred = 42) AND (thousand < ANY
('{42,99,43}'::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,43,42}'::integer[]))
+ Index Cond: (thousand < ANY ('{42,99,43}'::integer[]))
(8 rows)
EXPLAIN (COSTS OFF)
diff --git a/src/test/regress/expected/select.out
b/src/test/regress/expected/select.out
index 0ebaf002e8..f4f5493c43 100644
--- a/src/test/regress/expected/select.out
+++ b/src/test/regress/expected/select.out
@@ -1047,11 +1047,11 @@ 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');
- QUERY PLAN
----------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------
Bitmap Heap Scan on onek2
Recheck Cond: (stringu1 < 'B'::name)
- Filter: ((stringu1 = ANY ('{A,A}'::name[])) AND (stringu1 = ANY
('{A,A}'::name[])))
+ Filter: ((stringu1 = ANY ('{A,A}'::name[])) AND (stringu1 = 'A'::name))
-> Bitmap Index Scan on onek2_u2_prtl
(4 rows)
@@ -1114,7 +1114,7 @@ WHERE unique1 IN ((random()*2)::integer,
(random()*3)::integer);
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,
+-- Combine different saops. Some 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)
@@ -1170,7 +1170,7 @@ WHERE (unique1 < 1 OR stringu1 IN ('A','J'));
-> Bitmap Index Scan on onek2_u2_prtl
(9 rows)
--- Although SAOP doesn't fit partial indexes fully, we can use anded OR clause
+-- Although SAOP doesn't fit partial indexes fully, we can use added OR clause
-- to scan another couple of partial indexes.
EXPLAIN (COSTS OFF)
SELECT count(*) FROM onek2
@@ -1188,6 +1188,17 @@ WHERE stringu1 IN ('B','J') AND (stringu1 = 'A' OR
unique1 = 1);
(8 rows)
RESET enable_indexscan;
+--Check the case when we construct ANY list with unique elements
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM onek2
+WHERE unique1 = 1 OR unique1 = 1;
+ QUERY PLAN
+----------------------------------------------------
+ Aggregate
+ -> Index Only Scan using onek2_u1_prtl on onek2
+ Index Cond: (unique1 = 1)
+(3 rows)
+
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 223f55af4e..b8cee41658 100644
--- a/src/test/regress/sql/select.sql
+++ b/src/test/regress/sql/select.sql
@@ -291,7 +291,7 @@ 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,
+-- Combine different saops. Some 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)
@@ -307,13 +307,18 @@ 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
+-- Although SAOP doesn't fit partial indexes fully, we can use added 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;
+--Check the case when we construct ANY list with unique elements
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM onek2
+WHERE unique1 = 1 OR unique1 = 1;
+
RESET enable_seqscan;
--
From 9721f3e8ecda972241d9bedbfb915fd1514cb9d7 Mon Sep 17 00:00:00 2001
From: "Andrey V. Lepikhov" <a.lepik...@postgrespro.ru>
Date: Tue, 5 Mar 2024 13:29:46 +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 | 293 ++++++++++++++++++++
src/test/regress/sql/select.sql | 87 ++++++
src/tools/pgindent/typedefs.list | 1 +
9 files changed, 722 insertions(+), 53 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 1fdfffd79b..8008b05327 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5446,6 +5446,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 b76896dfbf..a3a6d0024b 100644
--- a/src/backend/optimizer/util/predtest.c
+++ b/src/backend/optimizer/util/predtest.c
@@ -103,6 +103,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 0ee7154e08..4029cf07be 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -160,6 +160,20 @@ 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;
+
/*
* Proof attempts involving large arrays in ScalarArrayOpExpr nodes are
* likely to require O(N^2) time, and more often than not fail anyway.
@@ -169,6 +183,8 @@ extern List *expand_function_arguments(List *args, bool include_out_arguments,
*/
#define MAX_SAOP_ARRAY_SIZE 100
+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..f4f5493c43 100644
--- a/src/test/regress/expected/select.out
+++ b/src/test/regress/expected/select.out
@@ -907,6 +907,299 @@ 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,A}'::name[])) AND (stringu1 = '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. Some 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 added 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;
+--Check the case when we construct ANY list with unique elements
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM onek2
+WHERE unique1 = 1 OR unique1 = 1;
+ QUERY PLAN
+----------------------------------------------------
+ Aggregate
+ -> Index Only Scan using onek2_u1_prtl on onek2
+ Index Cond: (unique1 = 1)
+(3 rows)
+
+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..b8cee41658 100644
--- a/src/test/regress/sql/select.sql
+++ b/src/test/regress/sql/select.sql
@@ -234,6 +234,93 @@ 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. Some 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 added 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;
+--Check the case when we construct ANY list with unique elements
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM onek2
+WHERE unique1 = 1 OR unique1 = 1;
+
+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 f4b6fee893..7930a17e81 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2141,6 +2141,7 @@ PredIterInfoData
PredXactList
PredicateLockData
PredicateLockTargetType
+PredicatesData
PrefetchBufferResult
PrepParallelRestorePtrType
PrepareStmt
--
2.34.1
From af4592111fcd1f5fb85216c7d56f19ed0ffde38a 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/optimizer/util/predtest.c | 9 -
src/backend/parser/parse_expr.c | 356 ++++++++++++++++++
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 | 10 +
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 +
21 files changed, 923 insertions(+), 69 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index c355e8f3f7..48685bf6ff 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -8797,18 +8797,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 b38cbd714a..1fdfffd79b 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5433,6 +5433,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/optimizer/util/predtest.c b/src/backend/optimizer/util/predtest.c
index c37b416e24..b76896dfbf 100644
--- a/src/backend/optimizer/util/predtest.c
+++ b/src/backend/optimizer/util/predtest.c
@@ -30,15 +30,6 @@
#include "utils/syscache.h"
-/*
- * Proof attempts involving large arrays in ScalarArrayOpExpr nodes are
- * likely to require O(N^2) time, and more often than not fail anyway.
- * So we set an arbitrary limit on the number of array elements that
- * we will allow to be treated as an AND or OR clause.
- * XXX is it worth exposing this as a GUC knob?
- */
-#define MAX_SAOP_ARRAY_SIZE 100
-
/*
* To avoid redundant coding in predicate_implied_by_recurse and
* predicate_refuted_by_recurse, we need to abstract out the notion of
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 9300c7b9ab..e9813ef6ab 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,352 @@ 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;
+} 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;
+}
+
+/*
+ * 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;
+
+ 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;
+ }
+
+ /*
+ * 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 = list_append_unique(entry->consts, const_expr);
+ entry->exprs = list_append_unique(entry->exprs, orqual);
+ }
+ else
+ {
+ entry->consts = list_make1(const_expr);
+ entry->exprs = list_make1(orqual);
+
+ /*
+ * 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);
+
+ if (list_length(entry->consts) == 1 ||
+ list_length(entry->consts) > MAX_SAOP_ARRAY_SIZE)
+ {
+ /*
+ * Only one element or more than MAX_SAOP_ARRAY_SIZE elements in
+ * the class Return 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;
+ ListCell *lc1;
+
+ foreach(lc1, entry->consts)
+ {
+ Node *rexpr = (Node *) lfirst(lc1);
+
+ rexpr = coerce_to_common_type(pstate, rexpr,
+ scalar_type,
+ "IN");
+ aexprs = lappend(aexprs, rexpr);
+ }
+
+ 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 +1737,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 45013582a7..c0ea53495b 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 edcc0282b2..d30dc6d51c 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -389,6 +389,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..0ee7154e08 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: */
@@ -159,6 +160,15 @@ extern List *expand_function_arguments(List *args, bool include_out_arguments,
/* in util/predtest.c: */
+/*
+ * Proof attempts involving large arrays in ScalarArrayOpExpr nodes are
+ * likely to require O(N^2) time, and more often than not fail anyway.
+ * So we set an arbitrary limit on the number of array elements that
+ * we will allow to be treated as an AND or OR clause.
+ * XXX is it worth exposing this as a GUC knob?
+ */
+#define MAX_SAOP_ARRAY_SIZE 100
+
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/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 79fa117cb5..606a4399f9 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,99,43}'::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,43}'::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 ('{42,41}'::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 ('{42,41}'::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..2a079e996b 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,2)","(0,1)"}'::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,2)","(0,1)"}'::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 cc3611e606..f4b6fee893 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1656,6 +1656,8 @@ NumericVar
OM_uint32
OP
OSAPerGroupState
+OrClauseGroupEntry
+OrClauseGroupKey
OSAPerQueryState
OSInfo
OSSLCipher
--
2.34.1
