On 07.08.2024 04:11, Alexander Korotkov wrote:
On Mon, Aug 5, 2024 at 11:24 PM Alena Rybakina
<a.rybak...@postgrespro.ru> wrote:
Ok, thank you for your work)
I think we can leave only the two added libraries in the first patch,
others are superfluous.
Thank you.
I also have fixed some grammar issues.
Thank you)
I added some tests to test the functionality of queries using strange
operator classes, type mismatches, and a small number of joins.
At the same time, I faced an assertion when a request with an unusual
operator was processed:
EXPLAIN (COSTS OFF)
SELECT COUNT(*) FROM guid1 WHERE guid_field <>
'11111111111111111111111111111111' OR
guid_field <>
'3f3e3c3b-3a30-3938-3736-353433a2313e';
Coredump:
#0 __pthread_kill_implementation (no_tid=0, signo=6,
threadid=138035230913472)
at ./nptl/pthread_kill.c:44
#1 __pthread_kill_internal (signo=6, threadid=138035230913472) at
./nptl/pthread_kill.c:78
#2 __GI___pthread_kill (threadid=138035230913472, signo=signo@entry=6)
at ./nptl/pthread_kill.c:89
#3 0x00007d8ad3e42476 in __GI_raise (sig=sig@entry=6) at
../sysdeps/posix/raise.c:26
#4 0x00007d8ad3e287f3 in __GI_abort () at ./stdlib/abort.c:79
#5 0x000060ceb55be02f in ExceptionalCondition
(conditionName=0x60ceb58058af "op_strategy != 0",
fileName=0x60ceb58053e6 "selfuncs.c", lineNumber=6900) at assert.c:66
#6 0x000060ceb553ed48 in btcostestimate (root=0x60ceb6f9d2a8,
path=0x60ceb6fbd2a8, loop_count=1,
--Type <RET> for more, q to quit, c to continue without paging--
indexStartupCost=0x7fff7ea15380, indexTotalCost=0x7fff7ea15388,
indexSelectivity=0x7fff7ea15390, indexCorrelation=0x7fff7ea15398,
indexPages=0x7fff7ea153b0)
at selfuncs.c:6900
#7 0x000060ceb521afca in cost_index (path=0x60ceb6fbd2a8,
root=0x60ceb6f9d2a8, loop_count=1,
partial_path=false) at costsize.c:618
#8 0x000060ceb5290c99 in create_index_path (root=0x60ceb6f9d2a8,
index=0x60ceb6fbd5e8,
indexclauses=0x60ceb6fbe4c8, indexorderbys=0x0,
indexorderbycols=0x0, pathkeys=0x0,
indexscandir=ForwardScanDirection, indexonly=true,
required_outer=0x0, loop_count=1,
partial_path=false) at pathnode.c:1024
--Type <RET> for more, q to quit, c to continue without paging--
#9 0x000060ceb522df4d in build_index_paths (root=0x60ceb6f9d2a8,
rel=0x60ceb70716c8, index=0x60ceb6fbd5e8,
clauses=0x7fff7ea15790, useful_predicate=false,
scantype=ST_ANYSCAN, skip_nonnative_saop=0x7fff7ea15607)
at indxpath.c:970
#10 0x000060ceb522d905 in get_index_paths (root=0x60ceb6f9d2a8,
rel=0x60ceb70716c8, index=0x60ceb6fbd5e8,
clauses=0x7fff7ea15790, bitindexpaths=0x7fff7ea15678) at indxpath.c:729
#11 0x000060ceb522c846 in create_index_paths (root=0x60ceb6f9d2a8,
rel=0x60ceb70716c8) at indxpath.c:286
#12 0x000060ceb5212d29 in set_plain_rel_pathlist (root=0x60ceb6f9d2a8,
rel=0x60ceb70716c8, rte=0x60ceb6f63768)
at allpaths.c:794
#13 0x000060ceb5212852 in set_rel_pathlist (root=0x60ceb6f9d2a8,
rel=0x60ceb70716c8, rti=1, rte=0x60ceb6f63768)
at allpaths.c:499
#14 0x000060ceb521248c in set_base_rel_pathlists (root=0x60ceb6f9d2a8)
at allpaths.c:351
#15 0x000060ceb52121af in make_one_rel (root=0x60ceb6f9d2a8,
joinlist=0x60ceb6fbdea8) at allpaths.c:221
#16 0x000060ceb5257a8d in query_planner (root=0x60ceb6f9d2a8,
qp_callback=0x60ceb525e2e6 <standard_qp_callback>,
qp_extra=0x7fff7ea15d90) at planmain.c:280
#17 0x000060ceb525a4f0 in grouping_planner (root=0x60ceb6f9d2a8,
tuple_fraction=0, setops=0x0) at planner.c:1520
#18 0x000060ceb5259b8f in subquery_planner (glob=0x60ceb70715b8,
parse=0x60ceb6f63558, parent_root=0x0,
hasRecursion=false, tuple_fraction=0, setops=0x0) at planner.c:1089
#19 0x000060ceb52581f2 in standard_planner (parse=0x60ceb6f63558,
query_string=0x60ceb6f62020 "EXPLAIN (COSTS OFF)\nSELECT COUNT(*)
FROM guid1 WHERE guid_field <> '", '1' <repeats 32 --Type <RET> for
more, q to quit, c to continue without paging--
times>, "' OR\n\t\t\t\t\t\t\tguid_field <>
'3f3e3c3b-3a30-3938-3736-353433a2313e';", cursorOptions=2048,
boundParams=0x0) at planner.c:415
#20 0x000060ceb5257f1c in planner (parse=0x60ceb6f63558,
query_string=0x60ceb6f62020 "EXPLAIN (COSTS OFF)\nSELECT COUNT(*)
FROM guid1 WHERE guid_field <> '", '1' <repeats 32 times>, "'
OR\n\t\t\t\t\t\t\tguid_field <>
'3f3e3c3b-3a30-3938-3736-353433a2313e';", cursorOptions=2048,
boundParams=0x0) at planner.c:282
#21 0x000060ceb53b89d9 in pg_plan_query (querytree=0x60ceb6f63558,
query_string=0x60ceb6f62020 "EXPLAIN (COSTS OFF)\nSELECT COUNT(*)
FROM guid1 WHERE guid_field <> '", '1' <repeats 32 times>, "'
OR\n\t\t\t\t\t\t\tguid_field <>
'3f3e3c3b-3a30-3938-3736-353433a2313e';", cursorOptions=2048,
boundParams=0x0) at postgres.c:912
#22 0x000060ceb501feeb in standard_ExplainOneQuery
(query=0x60ceb6f63558, cursorOptions=2048, into=0x0,
es=0x60ceb703acc8,
queryString=0x60ceb6f62020 "EXPLAIN (COSTS OFF)\nSELECT COUNT(*)
FROM guid1 WHERE guid_field <> '", '1' <repeats 32 times>, "'
OR\n\t\t\t\t\t\t\tguid_field <>
'3f3e3c3b-3a30-3938-3736-353433a2313e';", params=0x0, queryEnv=0x0)
at explain.c:491
#23 0x000060ceb501fd09 in ExplainOneQuery (query=0x60ceb6f63558,
cursorOptions=2048, into=0x0, es=0x60ceb703acc8,
queryString=0x60ceb6f62020 "EXPLAIN (COSTS OFF)\nSELECT COUNT(*)
FROM guid1 WHERE guid_field <> '", '1' <repeats 32 times>, "'
OR\n\t\t\t\t\t\t\tguid_field <>
'3f3e3c3b-3a30-3938-3736-353433a2313e';", params=0x0, queryEnv=0x0)
at explain.c:447
--Type <RET> for more, q to quit, c to continue without paging--
#24 0x000060ceb501f939 in ExplainQuery (pstate=0x60ceb703abb8,
stmt=0x60ceb6f63398, params=0x0, dest=0x60ceb703ab28)
at explain.c:343
#25 0x000060ceb53c32e0 in standard_ProcessUtility (pstmt=0x60ceb6f63448,
queryString=0x60ceb6f62020 "EXPLAIN (COSTS OFF)\nSELECT COUNT(*)
FROM guid1 WHERE guid_field <> '", '1' <repeats 32 times>, "'
OR\n\t\t\t\t\t\t\tguid_field <>
'3f3e3c3b-3a30-3938-3736-353433a2313e';", readOnlyTree=false,
context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0,
dest=0x60ceb703ab28, qc=0x7fff7ea16530) at utility.c:863
#26 0x000060ceb53c2852 in ProcessUtility (pstmt=0x60ceb6f63448,
queryString=0x60ceb6f62020 "EXPLAIN (COSTS OFF)\nSELECT COUNT(*)
FROM guid1 WHERE guid_field <> '", '1' <repeats 32 times>, "'
OR\n\t\t\t\t\t\t\tguid_field <>
'3f3e3c3b-3a30-3938-3736-353433a2313e';", readOnlyTree=false,
context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0,
dest=0x60ceb703ab28, qc=0x7fff7ea16530) at utility.c:523
#27 0x000060ceb53c10cd in PortalRunUtility (portal=0x60ceb6fe6c50,
pstmt=0x60ceb6f63448, isTopLevel=true,
setHoldSnapshot=true, dest=0x60ceb703ab28, qc=0x7fff7ea16530) at
pquery.c:1158
#28 0x000060ceb53c0e0a in FillPortalStore (portal=0x60ceb6fe6c50,
isTopLevel=true) at pquery.c:1031
#29 0x000060ceb53c06bb in PortalRun (portal=0x60ceb6fe6c50,
count=9223372036854775807, isTopLevel=true, run_once=true,
dest=0x60ceb6f63be8, altdest=0x60ceb6f63be8, qc=0x7fff7ea16780) at
pquery.c:763
#30 0x000060ceb53b911f in exec_simple_query (
query_string=0x60ceb6f62020 "EXPLAIN (COSTS OFF)\nSELECT COUNT(*)
FROM guid1 WHERE guid_field <> '", '1' <repeats 32 times>, "'
OR\n\t\t\t\t\t\t\tguid_field <>
'3f3e3c3b-3a30-3938-3736-353433a2313e';") at postgres.c:1284
#31 0x000060ceb53be4ef in PostgresMain (dbname=0x60ceb6fa0c00
"regression", username=0x60ceb6fa0be8 "alena")
--Type <RET> for more, q to quit, c to continue without paging--
at postgres.c:4766
#32 0x000060ceb53b4c2a in BackendMain (startup_data=0x7fff7ea16a04 "",
startup_data_len=4) at backend_startup.c:107
#33 0x000060ceb52c9b80 in postmaster_child_launch (child_type=B_BACKEND,
startup_data=0x7fff7ea16a04 "",
startup_data_len=4, client_sock=0x7fff7ea16a50) at launch_backend.c:274
#34 0x000060ceb52cfe87 in BackendStartup (client_sock=0x7fff7ea16a50) at
postmaster.c:3495
#35 0x000060ceb52cd0df in ServerLoop () at postmaster.c:1662
#36 0x000060ceb52cc9a6 in PostmasterMain (argc=3, argv=0x60ceb6ec6d10)
at postmaster.c:1360
#37 0x000060ceb517671c in main (argc=3, argv=0x60ceb6ec6d10) at main.c:197
I have fixed it by adding the condition that the opno of the clause must
be a member of the opfamily of the index.
tp = SearchSysCache3(AMOPOPID,
ObjectIdGetDatum(opno),
CharGetDatum(AMOP_SEARCH),
ObjectIdGetDatum(index->opfamily[indexcol]));
if (!HeapTupleIsValid(tp))
return NULL;
ReleaseSysCache(tp);
I attached the diff file and new versions of patches.
--
Regards,
Alena Rybakina
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
From 8763d61f689dc4ebca125868d5764657064fd9c7 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorot...@postgresql.org>
Date: Mon, 5 Aug 2024 21:27:02 +0300
Subject: [PATCH 1/2] Transform OR-clauses to SAOP's during index matching
Replace "(indexkey op C1) OR (indexkey op C2) ... (indexkey op CN)" with
"indexkey op ANY(ARRAY[C1, C2, ...])" (ScalarArrayOpExpr node) during matching
a clause to index.
Here Ci is an i-th constant or parameters 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 'Cn op expr').
This transformation allows handling long OR-clauses with single IndexScan
avoiding slower bitmap scans.
Discussion: https://postgr.es/m/567ED6CA.2040504%40sigaev.ru
Author: Alena Rybakina <lena.riback...@yandex.ru>
Author: Andrey Lepikhov <a.lepik...@postgrespro.ru>
Reviewed-by: Peter Geoghegan <p...@bowt.ie>
Reviewed-by: Ranier Vilela <ranier...@gmail.com>
Reviewed-by: Alexander Korotkov <aekorot...@gmail.com>
Reviewed-by: Robert Haas <robertmh...@gmail.com>
Reviewed-by: Jian He <jian.universal...@gmail.com>
Reviewed-by: Tom Lane <t...@sss.pgh.pa.us>
Reviewed-by: Nikolay Shaplov <dh...@nataraj.su>
---
src/backend/optimizer/path/indxpath.c | 253 +++++++++++++++++++++
src/test/regress/expected/create_index.out | 183 +++++++++++++--
src/test/regress/expected/join.out | 57 ++++-
src/test/regress/sql/create_index.sql | 42 ++++
src/test/regress/sql/join.sql | 9 +
5 files changed, 522 insertions(+), 22 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index c0fcc7d78df..34dda1b6df6 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -32,7 +32,9 @@
#include "optimizer/paths.h"
#include "optimizer/prep.h"
#include "optimizer/restrictinfo.h"
+#include "utils/array.h"
#include "utils/lsyscache.h"
+#include "utils/syscache.h"
#include "utils/selfuncs.h"
@@ -177,6 +179,10 @@ static IndexClause *match_rowcompare_to_indexcol(PlannerInfo *root,
RestrictInfo *rinfo,
int indexcol,
IndexOptInfo *index);
+static IndexClause *match_orclause_to_indexcol(PlannerInfo *root,
+ RestrictInfo *rinfo,
+ int indexcol,
+ IndexOptInfo *index);
static IndexClause *expand_indexqual_rowcompare(PlannerInfo *root,
RestrictInfo *rinfo,
int indexcol,
@@ -2248,6 +2254,10 @@ match_clause_to_indexcol(PlannerInfo *root,
{
return match_rowcompare_to_indexcol(root, rinfo, indexcol, index);
}
+ else if (restriction_is_or_clause(rinfo))
+ {
+ return match_orclause_to_indexcol(root, rinfo, indexcol, index);
+ }
else if (index->amsearchnulls && IsA(clause, NullTest))
{
NullTest *nt = (NullTest *) clause;
@@ -2771,6 +2781,249 @@ match_rowcompare_to_indexcol(PlannerInfo *root,
return NULL;
}
+/*
+ * match_orclause_to_indexcol()
+ * Handles the OR-expr case for match_clause_to_indexcol() in the case
+ * when it could be transformed to ScalarArrayOpExpr.
+ */
+static IndexClause *
+match_orclause_to_indexcol(PlannerInfo *root,
+ RestrictInfo *rinfo,
+ int indexcol,
+ IndexOptInfo *index)
+{
+ ListCell *lc;
+ BoolExpr *orclause = (BoolExpr *) rinfo->orclause;
+ Node *indexExpr = NULL;
+ List *consts = NIL;
+ Node *arrayNode = NULL;
+ ScalarArrayOpExpr *saopexpr = NULL;
+ HeapTuple opertup;
+ Form_pg_operator operform;
+ Oid matchOpno = InvalidOid;
+ IndexClause *iclause;
+ Oid consttype = InvalidOid;
+ Oid arraytype = InvalidOid;
+ Oid inputcollid = InvalidOid;
+ bool firstTime = true;
+ bool have_param = false;
+
+ Assert(IsA(orclause, BoolExpr));
+ Assert(orclause->boolop == OR_EXPR);
+
+ /*
+ * Iterate over OR entries. Check that each OR entry is of the form:
+ * (indexkey operator constant) or (constant operator indexkey). Operators
+ * of all the entries must match. Constant might be either Const or
+ * Param. Exit with NULL on first non-matching entry.
+ */
+ foreach(lc, orclause->args)
+ {
+ RestrictInfo *subRinfo;
+ OpExpr *subClause;
+ Oid opno;
+ Node *leftop,
+ *rightop;
+ Node *constExpr;
+
+ if (!IsA(lfirst(lc), RestrictInfo))
+ return NULL;
+
+ subRinfo = (RestrictInfo *) lfirst(lc);
+
+ /* Only operator clauses scan match */
+ if (!IsA(subRinfo->clause, OpExpr))
+ return NULL;
+
+ subClause = (OpExpr *) subRinfo->clause;
+ opno = subClause->opno;
+
+ /* Only binary operators can match */
+ if (list_length(subClause->args) != 2)
+ return NULL;
+
+ /* RestrictInfo parameters must match parent */
+ if (subRinfo->is_pushed_down != rinfo->is_pushed_down ||
+ subRinfo->is_clone != rinfo->is_clone ||
+ subRinfo->security_level != rinfo->security_level ||
+ !bms_equal(subRinfo->required_relids, rinfo->required_relids) ||
+ !bms_equal(subRinfo->incompatible_relids, rinfo->incompatible_relids) ||
+ !bms_equal(subRinfo->outer_relids, rinfo->outer_relids))
+ return NULL;
+
+ /* Only operator returning boolean suits the transformation */
+ if (get_op_rettype(opno) != BOOLOID)
+ return NULL;
+
+ /*
+ * Check for clauses of the form: (indexkey operator constant) or
+ * (constant operator indexkey). Determine indexkey side first, check
+ * the constant later.
+ */
+ leftop = (Node *) linitial(subClause->args);
+ rightop = (Node *) lsecond(subClause->args);
+ if (match_index_to_operand(leftop, indexcol, index))
+ {
+ indexExpr = leftop;
+ constExpr = rightop;
+ }
+ else if (match_index_to_operand(rightop, indexcol, index))
+ {
+ opno = get_commutator(opno);
+ if (!OidIsValid(opno))
+ {
+ /* commutator doesn't exist, we can't reverse the order */
+ return NULL;
+ }
+ indexExpr = rightop;
+ constExpr = leftop;
+ }
+ else
+ {
+ return NULL;
+ }
+
+ /*
+ * Ignore any RelabelType node above the operands. This is needed to
+ * be able to apply indexscanning in binary-compatible-operator cases.
+ * Note: we can assume there is at most one RelabelType node;
+ * eval_const_expressions() will have simplified if more than one.
+ */
+ if (IsA(constExpr, RelabelType))
+ constExpr = (Node *) ((RelabelType *) constExpr)->arg;
+ if (IsA(indexExpr, RelabelType))
+ indexExpr = (Node *) ((RelabelType *) indexExpr)->arg;
+
+ /* We allow constant to be Const or Param */
+ if (!IsA(constExpr, Const) && !IsA(constExpr, Param))
+ return NULL;
+
+ /* Forbid transformation for composite types, records. */
+ if (type_is_rowtype(exprType(constExpr)) ||
+ type_is_rowtype(exprType(indexExpr)))
+ return NULL;
+
+ /*
+ * For the first matching qual, save information about operator, type
+ * and collation. For the other quals just check the match with the
+ * first.
+ */
+ if (firstTime)
+ {
+ matchOpno = opno;
+ consttype = exprType(constExpr);
+ arraytype = get_array_type(consttype);
+ inputcollid = subClause->inputcollid;
+
+ /*
+ * There must be an array type in order to construct an array
+ * later
+ */
+ if (!OidIsValid(arraytype))
+ return NULL;
+ firstTime = false;
+ }
+ else
+ {
+ if (opno != matchOpno ||
+ inputcollid != subClause->inputcollid ||
+ consttype != exprType(constExpr))
+ return NULL;
+ }
+
+ if (IsA(constExpr, Param))
+ have_param = true;
+ consts = lappend(consts, constExpr);
+ }
+
+ if (have_param)
+ {
+ /*
+ * We need to construct an ArrayExpr given we have Param's not just
+ * Const's.
+ */
+ ArrayExpr *arrayExpr = makeNode(ArrayExpr);
+
+ /* array_collid will be set by parse_collate.c */
+ arrayExpr->element_typeid = consttype;
+ arrayExpr->array_typeid = arraytype;
+ arrayExpr->multidims = false;
+ arrayExpr->elements = consts;
+ arrayExpr->location = -1;
+
+ arrayNode = (Node *) arrayExpr;
+ }
+ else
+ {
+ /*
+ * We have only Const's. In this case we can construct an array
+ * directly.
+ */
+ int16 typlen;
+ bool typbyval;
+ char typalign;
+ Datum *elems;
+ int i = 0;
+ ArrayType *arrayConst;
+
+ get_typlenbyvalalign(consttype, &typlen, &typbyval, &typalign);
+
+ elems = (Datum *) palloc(sizeof(Datum) * list_length(consts));
+ foreach(lc, consts)
+ elems[i++] = ((Const *) lfirst(lc))->constvalue;
+
+ arrayConst = construct_array(elems, i, consttype,
+ typlen, typbyval, typalign);
+ arrayNode = (Node *) makeConst(arraytype, -1, inputcollid,
+ -1, PointerGetDatum(arrayConst),
+ false, false);
+
+ pfree(elems);
+ list_free(consts);
+ }
+
+ /* Lookup for operator to fetch necessary information for the SAOP node */
+ opertup = SearchSysCache1(OPEROID,
+ ObjectIdGetDatum(matchOpno));
+ if (!HeapTupleIsValid(opertup))
+ elog(ERROR, "cache lookup failed for operator %u", matchOpno);
+
+ operform = (Form_pg_operator) GETSTRUCT(opertup);
+
+ /* Build the SAOP expression node */
+ saopexpr = makeNode(ScalarArrayOpExpr);
+ saopexpr->opno = matchOpno;
+ saopexpr->opfuncid = operform->oprcode;
+ saopexpr->hashfuncid = InvalidOid;
+ saopexpr->negfuncid = InvalidOid;
+ saopexpr->useOr = true;
+ saopexpr->inputcollid = inputcollid;
+ saopexpr->args = list_make2(indexExpr, arrayNode);
+ saopexpr->location = -1;
+
+ ReleaseSysCache(opertup);
+
+ /*
+ * Finally build an IndexClause based on the SAOP node.
+ */
+ iclause = makeNode(IndexClause);
+ iclause->rinfo = make_restrictinfo(root,
+ &saopexpr->xpr,
+ rinfo->is_pushed_down,
+ rinfo->has_clone,
+ rinfo->is_clone,
+ rinfo->pseudoconstant,
+ rinfo->security_level,
+ rinfo->required_relids,
+ rinfo->incompatible_relids,
+ rinfo->outer_relids);
+ iclause->indexquals = list_make1(iclause->rinfo);
+ iclause->lossy = false;
+ iclause->indexcol = indexcol;
+ iclause->indexcols = NIL;
+ return iclause;
+}
+
/*
* expand_indexqual_rowcompare --- expand a single indexqual condition
* that is a RowCompareExpr
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index cf6eac57349..c2b25936c8c 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1844,18 +1844,11 @@ 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);
@@ -1864,14 +1857,166 @@ SELECT * FROM tenk1
42 | 5530 | 0 | 2 | 2 | 2 | 42 | 42 | 42 | 42 | 42 | 84 | 85 | QBAAAA | SEIAAA | OOOOxx
(1 row)
+EXPLAIN (COSTS OFF)
+SELECT * FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = (SELECT 1 + 2) OR tenthous = 42);
+ QUERY PLAN
+----------------------------------------------------------------------------------------
+ Index Scan using tenk1_thous_tenthous on tenk1
+ Index Cond: ((thousand = 42) AND (tenthous = ANY (ARRAY[1, (InitPlan 1).col1, 42])))
+ InitPlan 1
+ -> Result
+(4 rows)
+
+SELECT * FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = (SELECT 1 + 2) 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
----------------------------------------------------------------------------------
+ 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,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 * 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);
+ 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)
+
+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,42}'::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[]))
+(8 rows)
+
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand < 42 OR thousand < 99 OR 43 > thousand OR 42 > thousand);
+ count
+-------
+ 10
+(1 row)
+
+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 = 42) OR (thousand = 99) 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 = 42)
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = 99)
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (tenthous < 2)
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = 41)
+(16 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 = 41) OR ((thousand = 99) AND (tenthous = 2))))
-> BitmapAnd
-> Bitmap Index Scan on tenk1_hundred
Index Cond: (hundred = 42)
@@ -1879,11 +2024,13 @@ SELECT count(*) FROM tenk1
-> Bitmap Index Scan on tenk1_thous_tenthous
Index Cond: (thousand = 42)
-> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: (thousand = 99)
-(11 rows)
+ Index Cond: (thousand = 41)
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: ((thousand = 99) AND (tenthous = 2))
+(13 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
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 53f70d72ed6..abe98ff3c53 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4278,15 +4278,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[])))
+ Filter: ((unique1 = 1) OR (unique2 = 3) OR (unique2 = 7))
-> 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[]))
+(18 rows)
+
+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 = 3) OR (a.unique2 = 7)) 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[])))
+ Filter: ((unique1 = 1) OR (unique2 = 3) OR (unique2 = 7))
+ -> 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[]))
+(18 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 = 3) OR (a.unique2 = 7)) 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[])))
+ Filter: ((unique1 < 20) OR (unique1 = 3) OR (unique1 = 1) OR (unique2 = 3) OR (unique2 = 7))
+ -> 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[]))
+(16 rows)
--
-- test placement of movable quals in a parameterized join tree
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index e296891cab8..f74ad415fbf 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -726,6 +726,24 @@ DROP TABLE onek_with_null;
-- Check bitmap index path planning
--
+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 * FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = (SELECT 1 + 2) OR tenthous = 42);
+SELECT * FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = (SELECT 1 + 2) 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 * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
@@ -738,6 +756,30 @@ SELECT count(*) FROM tenk1
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);
+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);
+
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index d81ff63be53..4473b8f04d5 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1433,6 +1433,15 @@ 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 = 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);
+
--
-- test placement of movable quals in a parameterized join tree
--
--
2.34.1
From 6b1e6f538bdfbeafda457fe851fff829d4ebfd26 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorot...@postgresql.org>
Date: Sun, 28 Jul 2024 16:36:34 +0300
Subject: [PATCH 2/2] Teach bitmap path generation about transforming
OR-clauses to SAOP's
When optimizer generates bitmap paths, it considers breaking OR-clause
arguments one-by-one. But now, a group of similar OR-clauses can be
transformed into SAOP during index matching. So, bitmap paths should
keep up.
This commit teaches bitmap paths generation machinery to group similar
OR-clauses into dedicated RestrictInfos. Those RestrictInfos are considered
both to match index as a whole (as SAOP), or to match as a set of individual
OR-clause argument one-by-one (the old way).
Therefore, bitmap path generation will takes advantage of OR-clauses to SAOP's
transformation. The old way of handling them is also considered. So, there
shouldn't be planning regression.
Discussion: https://postgr.es/m/567ED6CA.2040504%40sigaev.ru
Reviewed-by: Alexander Korotkov <aekorot...@gmail.com>
---
src/backend/optimizer/path/indxpath.c | 368 ++++++++++++++++++++-
src/test/regress/expected/create_index.out | 121 ++++++-
src/test/regress/expected/join.out | 56 ++--
src/test/regress/expected/rowsecurity.out | 7 +
src/test/regress/expected/stats_ext.out | 12 +
src/test/regress/expected/uuid.out | 31 ++
src/test/regress/sql/create_index.sql | 27 ++
src/test/regress/sql/rowsecurity.sql | 1 +
src/test/regress/sql/stats_ext.sql | 3 +
src/test/regress/sql/uuid.sql | 12 +
src/tools/pgindent/typedefs.list | 1 +
11 files changed, 592 insertions(+), 47 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index 34dda1b6df6..b836af6fb55 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -20,6 +20,7 @@
#include "access/stratnum.h"
#include "access/sysattr.h"
#include "catalog/pg_am.h"
+#include "catalog/pg_amop.h"
#include "catalog/pg_operator.h"
#include "catalog/pg_opfamily.h"
#include "catalog/pg_type.h"
@@ -1172,6 +1173,325 @@ build_paths_for_OR(PlannerInfo *root, RelOptInfo *rel,
return result;
}
+/*
+ * Data structure representing information about OR-clause argument and its
+ * matching index key. Used for grouping of similar OR-clause arguments in
+ * group_similar_or_args().
+ */
+typedef struct
+{
+ int indexnum; /* index of the matching index */
+ int colnum; /* index of the matching column */
+ Oid opno; /* OID of the OpClause operator */
+ Oid inputcollid; /* OID of the OpClause input collation */
+ int argindex; /* index of the clause in the list of
+ * arguments */
+} OrArgIndexMatch;
+
+/*
+ * Comparison function for OrArgIndexMatch which provides sort order placing
+ * similar OR-clause arguments together.
+ */
+static int
+or_arg_index_match_cmp(const void *a, const void *b)
+{
+ const OrArgIndexMatch *match_a = (const OrArgIndexMatch *) a;
+ const OrArgIndexMatch *match_b = (const OrArgIndexMatch *) b;
+
+ if (match_a->indexnum < match_b->indexnum)
+ return -1;
+ else if (match_a->indexnum > match_b->indexnum)
+ return 1;
+
+ if (match_a->colnum < match_b->colnum)
+ return -1;
+ else if (match_a->colnum > match_b->colnum)
+ return 1;
+
+ if (match_a->opno < match_b->opno)
+ return -1;
+ else if (match_a->opno > match_b->opno)
+ return 1;
+
+ if (match_a->inputcollid < match_b->inputcollid)
+ return -1;
+ else if (match_a->inputcollid > match_b->inputcollid)
+ return 1;
+
+ if (match_a->argindex < match_b->argindex)
+ return -1;
+ else if (match_a->argindex > match_b->argindex)
+ return 1;
+
+ return 0;
+}
+
+/*
+ * group_similar_or_args
+ * Group similar OR-arguments intro dedicated RestrictInfos. Process
+ * arguments of 'rinfo' clause, returns the processed list of arguments.
+ *
+ * Similar arguments clauses of form "indexkey op constant" having same
+ * indexkey, operator, and collation. Constant may comprise either Const
+ * or Param.
+ */
+static List *
+group_similar_or_args(PlannerInfo *root, RelOptInfo *rel, RestrictInfo *rinfo)
+{
+ int n;
+ int i;
+ int group_start;
+ OrArgIndexMatch *matches;
+ ListCell *lc;
+ ListCell *lc2;
+ List *orargs;
+ List *result = NIL;
+
+ Assert(IsA(rinfo->orclause, BoolExpr));
+ orargs = ((BoolExpr *) rinfo->orclause)->args;
+ n = list_length(orargs);
+
+ /*
+ * Allocate and fill OrArgIndexMatch struct for each clause in the
+ * argument list.
+ */
+ i = -1;
+ matches = (OrArgIndexMatch *) palloc(sizeof(OrArgIndexMatch) * n);
+ foreach(lc, orargs)
+ {
+ Node *arg = lfirst(lc);
+ RestrictInfo *argrinfo;
+ OpExpr *clause;
+ Oid opno;
+ Node *leftop,
+ *rightop;
+ Node *nonConstExpr;
+ int indexnum;
+ int colnum;
+
+ i++;
+ matches[i].argindex = i;
+ matches[i].indexnum = -1;
+ matches[i].colnum = -1;
+ matches[i].opno = InvalidOid;
+ matches[i].inputcollid = InvalidOid;
+
+ if (!IsA(arg, RestrictInfo))
+ continue;
+
+ argrinfo = castNode(RestrictInfo, arg);
+
+ /* Only operator clauses scan match */
+ if (!IsA(argrinfo->clause, OpExpr))
+ continue;
+
+ clause = (OpExpr *) argrinfo->clause;
+ opno = clause->opno;
+
+ /* Only binary operators can match */
+ if (list_length(clause->args) != 2)
+ return NULL;
+
+ /*
+ * Ignore any RelabelType node above the operands. This is needed to
+ * be able to apply indexscanning in binary-compatible-operator cases.
+ * Note: we can assume there is at most one RelabelType node;
+ * eval_const_expressions() will have simplified if more than one.
+ */
+ leftop = get_leftop(clause);
+ if (IsA(leftop, RelabelType))
+ leftop = (Node *) ((RelabelType *) leftop)->arg;
+
+ rightop = get_rightop(clause);
+ if (IsA(rightop, RelabelType))
+ rightop = (Node *) ((RelabelType *) rightop)->arg;
+
+ /*
+ * Check for clauses of the form: (indexkey operator constant) or
+ * (constant operator indexkey). But we don't know a particular index
+ * yet. First check for a constant, which must be Const or Param.
+ * That's cheaper than search for an index key among all indexes.
+ */
+ if (IsA(leftop, Const) || IsA(leftop, Param))
+ {
+ opno = get_commutator(opno);
+
+ if (!OidIsValid(opno))
+ {
+ /* commutator doesn't exist, we can't reverse the order */
+ continue;
+ }
+ nonConstExpr = rightop;
+ }
+ else if (IsA(rightop, Const) || IsA(rightop, Param))
+ {
+ nonConstExpr = leftop;
+ }
+ else
+ {
+ continue;
+ }
+
+ /*
+ * Match non-constant part to the index key. It's possible that a
+ * single non-constant part matches multiple index keys. It's OK, we
+ * just stop with first matching index key. Given that this choice is
+ * determined the same for every clause, we will group similar clauses
+ * together anyway.
+ */
+ indexnum = 0;
+ foreach(lc2, rel->indexlist)
+ {
+ IndexOptInfo *index = (IndexOptInfo *) lfirst(lc2);
+
+ /* Ignore index if it doesn't support bitmap scans */
+ if (!index->amhasgetbitmap)
+ continue;
+
+ for (colnum = 0; colnum < index->nkeycolumns; colnum++)
+ {
+ if (match_index_to_operand(nonConstExpr, colnum, index))
+ {
+ matches[i].indexnum = indexnum;
+ matches[i].colnum = colnum;
+ matches[i].opno = opno;
+ matches[i].inputcollid = clause->inputcollid;
+ }
+ }
+ indexnum++;
+ }
+ }
+
+ /* Sort clauses to make similar clauses go together */
+ pg_qsort(matches, n, sizeof(OrArgIndexMatch), or_arg_index_match_cmp);
+
+ /* Group similar clauses into */
+ group_start = 0;
+ for (i = 1; i <= n; i++)
+ {
+ /* Check if it's a group boundary */
+ if (group_start >= 0 &&
+ (i == n ||
+ matches[i].indexnum != matches[group_start].indexnum ||
+ matches[i].colnum != matches[group_start].colnum ||
+ matches[i].opno != matches[group_start].opno ||
+ matches[i].inputcollid != matches[group_start].inputcollid ||
+ matches[i].indexnum == -1))
+ {
+ /*
+ * One clause in group: add it "as is" to the upper-level OR.
+ */
+ if (i - group_start == 1)
+ {
+ result = lappend(result,
+ list_nth(orargs,
+ matches[group_start].argindex));
+ }
+ else
+ {
+ /*
+ * Two or more clauses in a group: create a nested OR.
+ */
+ List *args = NIL;
+ RestrictInfo *subrinfo = makeNode(RestrictInfo);
+ int j;
+
+ Assert(i - group_start >= 2);
+
+ /* Construct the list of nested OR arguments */
+ for (j = group_start; j < i; j++)
+ args = lappend(args, list_nth(orargs, matches[j].argindex));
+
+ /* Construct the nested OR and wrap it with RestrictInfo */
+ *subrinfo = *rinfo;
+ subrinfo->clause = make_orclause(args);
+ subrinfo->orclause = subrinfo->clause;
+ result = lappend(result, subrinfo);
+ }
+
+ group_start = i;
+ }
+ }
+ pfree(matches);
+ return result;
+}
+
+/*
+ * make_bitmap_paths_for_or_group
+ * Generate bitmap paths for a group of similar OR-clause arguments
+ * produced by group_similar_or_args().
+ *
+ * This function considers two cases: (1) matching a group of clauses to
+ * the index as a whole, and (2) matching the individual clauses one-by-one.
+ * (1) typically comprises an optimal solution. If not, (2) typically
+ * comprises fair alternative.
+ *
+ * Ideally, we could consider all arbitrary splits of arguments into
+ * subgroups, but that could lead to unacceptable computational complexity.
+ * This is why we only consider two cases of above.
+ */
+static List *
+make_bitmap_paths_for_or_group(PlannerInfo *root, RelOptInfo *rel,
+ RestrictInfo *ri, List *other_clauses)
+{
+ List *jointlist = NIL;
+ List *splitlist = NIL;
+ ListCell *lc;
+ List *orargs;
+ List *args = ((BoolExpr *) ri->clause)->args;
+ Cost jointcost = 0.0,
+ splitcost = 0.0;
+ Path *bitmapqual;
+ List *indlist;
+
+ /*
+ * First, try to match the whole group to the one index.
+ */
+ orargs = list_make1(ri);
+ indlist = build_paths_for_OR(root, rel,
+ orargs,
+ other_clauses);
+ if (indlist != NIL)
+ {
+ bitmapqual = choose_bitmap_and(root, rel, indlist);
+ jointcost = bitmapqual->total_cost;
+ jointlist = list_make1(bitmapqual);
+ }
+
+ /*
+ * Also try to match all containing clauses one-by-one.
+ */
+ foreach(lc, args)
+ {
+ orargs = list_make1(lfirst(lc));
+
+ indlist = build_paths_for_OR(root, rel,
+ orargs,
+ other_clauses);
+
+ if (indlist == NIL)
+ {
+ splitlist = NIL;
+ break;
+ }
+
+ bitmapqual = choose_bitmap_and(root, rel, indlist);
+ splitcost += bitmapqual->total_cost;
+ splitlist = lappend(splitlist, bitmapqual);
+ }
+
+ /*
+ * Pick the best option.
+ */
+ if (splitlist == NIL)
+ return jointlist;
+ else if (jointlist == NIL)
+ return splitlist;
+ else
+ return (jointcost < splitcost) ? jointlist : splitlist;
+}
+
+
/*
* generate_bitmap_or_paths
* Look through the list of clauses to find OR clauses, and generate
@@ -1202,6 +1522,7 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
List *pathlist;
Path *bitmapqual;
ListCell *j;
+ List *groupedArgs;
/* Ignore RestrictInfos that aren't ORs */
if (!restriction_is_or_clause(rinfo))
@@ -1212,7 +1533,13 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
* the OR, else we can't use it.
*/
pathlist = NIL;
- foreach(j, ((BoolExpr *) rinfo->orclause)->args)
+
+ /*
+ * Group the similar OR-clause argument into dedicated RestrictInfos,
+ * because those RestrictInfos might match to the index as whole.
+ */
+ groupedArgs = group_similar_or_args(root, rel, rinfo);
+ foreach(j, groupedArgs)
{
Node *orarg = (Node *) lfirst(j);
List *indlist;
@@ -1232,12 +1559,37 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
andargs,
all_clauses));
}
+ else if (restriction_is_or_clause(castNode(RestrictInfo, orarg)))
+ {
+ RestrictInfo *ri = castNode(RestrictInfo, orarg);
+
+ /*
+ * Generate bitmap paths for the group of similar OR-clause
+ * arguments. In this case we need to immediately remove the
+ * rinfo from other clauses. This is because rinfo can be
+ * transformed during index matching. So, we might be unable
+ * to remove that later.
+ */
+ indlist = make_bitmap_paths_for_or_group(root,
+ rel, ri,
+ list_delete(all_clauses, rinfo));
+
+ if (indlist == NIL)
+ {
+ pathlist = NIL;
+ break;
+ }
+ else
+ {
+ pathlist = list_concat(pathlist, indlist);
+ continue;
+ }
+ }
else
{
RestrictInfo *ri = castNode(RestrictInfo, orarg);
List *orargs;
- Assert(!restriction_is_or_clause(ri));
orargs = list_make1(ri);
indlist = build_paths_for_OR(root, rel,
@@ -2807,6 +3159,7 @@ match_orclause_to_indexcol(PlannerInfo *root,
Oid inputcollid = InvalidOid;
bool firstTime = true;
bool have_param = false;
+ HeapTuple tp;
Assert(IsA(orclause, BoolExpr));
Assert(orclause->boolop == OR_EXPR);
@@ -2842,7 +3195,7 @@ match_orclause_to_indexcol(PlannerInfo *root,
if (list_length(subClause->args) != 2)
return NULL;
- /* RestrictInfo parameters must match parent */
+ /* RestrictInfo parameters dmust match parent */
if (subRinfo->is_pushed_down != rinfo->is_pushed_down ||
subRinfo->is_clone != rinfo->is_clone ||
subRinfo->security_level != rinfo->security_level ||
@@ -2883,6 +3236,15 @@ match_orclause_to_indexcol(PlannerInfo *root,
return NULL;
}
+ tp = SearchSysCache3(AMOPOPID,
+ ObjectIdGetDatum(opno),
+ CharGetDatum(AMOP_SEARCH),
+ ObjectIdGetDatum(index->opfamily[indexcol]));
+ if (!HeapTupleIsValid(tp))
+ return NULL;
+
+ ReleaseSysCache(tp);
+
/*
* Ignore any RelabelType node above the operands. This is needed to
* be able to apply indexscanning in binary-compatible-operator cases.
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index c2b25936c8c..cac076abb88 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1913,6 +1913,27 @@ SELECT * FROM tenk1
42 | 5530 | 0 | 2 | 2 | 2 | 42 | 42 | 42 | 42 | 42 | 84 | 85 | QBAAAA | SEIAAA | OOOOxx
(1 row)
+EXPLAIN (COSTS OFF)
+SELECT * FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1::numeric OR tenthous = 3::int4 OR tenthous = 42::numeric);
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on tenk1
+ Recheck Cond: (thousand = 42)
+ Filter: (((tenthous)::numeric = '1'::numeric) OR (tenthous = 3) OR ((tenthous)::numeric = '42'::numeric))
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = 42)
+(5 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM tenk1
+ WHERE tenthous = 1::numeric OR tenthous = 3::int4 OR tenthous = 42::numeric;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------
+ Seq Scan on tenk1
+ Filter: (((tenthous)::numeric = '1'::numeric) OR (tenthous = 3) OR ((tenthous)::numeric = '42'::numeric))
+(2 rows)
+
EXPLAIN (COSTS OFF)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
@@ -1982,25 +2003,24 @@ SELECT count(*) FROM tenk1
EXPLAIN (COSTS OFF)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR thousand = 41;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------
Aggregate
-> Bitmap Heap Scan on tenk1
- Recheck Cond: (((hundred = 42) AND ((thousand = 42) OR (thousand = 99) OR (tenthous < 2))) OR (thousand = 41))
+ Recheck Cond: (((hundred = 42) AND ((thousand = ANY ('{42,99}'::integer[])) OR (tenthous < 2))) OR (thousand = 41))
+ Filter: (((hundred = 42) AND ((thousand = 42) OR (thousand = 99) 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 = 42)
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: (thousand = 99)
+ 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)
-(16 rows)
+(15 rows)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR thousand = 41;
@@ -2012,22 +2032,21 @@ SELECT count(*) FROM tenk1
EXPLAIN (COSTS OFF)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND tenthous = 2);
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------
Aggregate
-> Bitmap Heap Scan on tenk1
- Recheck Cond: ((hundred = 42) AND ((thousand = 42) OR (thousand = 41) OR ((thousand = 99) AND (tenthous = 2))))
+ Recheck Cond: ((hundred = 42) AND (((thousand = 99) AND (tenthous = 2)) OR (thousand = ANY ('{42,41}'::integer[]))))
+ Filter: ((thousand = 42) OR (thousand = 41) 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)
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: (thousand = 41)
-> Bitmap Index Scan on tenk1_thous_tenthous
Index Cond: ((thousand = 99) AND (tenthous = 2))
-(13 rows)
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = ANY ('{42,41}'::integer[]))
+(12 rows)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND tenthous = 2);
@@ -2036,6 +2055,78 @@ SELECT count(*) FROM tenk1
10
(1 row)
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1, tenk2
+ WHERE tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk1.thousand = 41 OR tenk2.tenthous = 2) AND
+ tenk2.hundred = tenk1.hundred;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: ((tenk2.thousand = 42) OR (tenk1.thousand = 41) OR (tenk2.tenthous = 2))
+ -> Bitmap Heap Scan on tenk1
+ Recheck Cond: (hundred = 42)
+ -> Bitmap Index Scan on tenk1_hundred
+ Index Cond: (hundred = 42)
+ -> Materialize
+ -> Bitmap Heap Scan on tenk2
+ Recheck Cond: (hundred = 42)
+ -> Bitmap Index Scan on tenk2_hundred
+ Index Cond: (hundred = 42)
+(12 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1, tenk2
+ WHERE tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk2.thousand = 41 OR tenk2.tenthous = 2) AND
+ tenk2.hundred = tenk1.hundred;
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Bitmap Heap Scan on tenk2
+ Recheck Cond: (hundred = 42)
+ Filter: ((thousand = 42) OR (thousand = 41) OR (tenthous = 2))
+ -> Bitmap Index Scan on tenk2_hundred
+ Index Cond: (hundred = 42)
+ -> Index Only Scan using tenk1_hundred on tenk1
+ Index Cond: (hundred = 42)
+(9 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1 JOIN tenk2 ON
+ tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk2.thousand = 41 OR tenk2.tenthous = 2) AND
+ tenk2.hundred = tenk1.hundred;
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Bitmap Heap Scan on tenk2
+ Recheck Cond: (hundred = 42)
+ Filter: ((thousand = 42) OR (thousand = 41) OR (tenthous = 2))
+ -> Bitmap Index Scan on tenk2_hundred
+ Index Cond: (hundred = 42)
+ -> Index Only Scan using tenk1_hundred on tenk1
+ Index Cond: (hundred = 42)
+(9 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1 LEFT JOIN tenk2 ON
+ tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk2.thousand = 41 OR tenk2.tenthous = 2) AND
+ tenk2.hundred = tenk1.hundred;
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop Left Join
+ Join Filter: (tenk1.hundred = 42)
+ -> Index Only Scan using tenk1_hundred on tenk1
+ -> Memoize
+ Cache Key: tenk1.hundred
+ Cache Mode: logical
+ -> Index Scan using tenk2_hundred on tenk2
+ Index Cond: (hundred = tenk1.hundred)
+ Filter: ((thousand = 42) OR (thousand = 41) OR (tenthous = 2))
+(10 rows)
+
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index abe98ff3c53..d26a93831d5 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4226,20 +4226,20 @@ select * from tenk1 a join tenk1 b on
Nested Loop
Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = 3) AND (b.hundred = 4)))
-> Bitmap Heap Scan on tenk1 b
- Recheck Cond: ((unique1 = 2) OR (hundred = 4))
+ Recheck Cond: ((hundred = 4) OR (unique1 = 2))
-> BitmapOr
- -> Bitmap Index Scan on tenk1_unique1
- Index Cond: (unique1 = 2)
-> Bitmap Index Scan on tenk1_hundred
Index Cond: (hundred = 4)
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = 2)
-> Materialize
-> Bitmap Heap Scan on tenk1 a
- Recheck Cond: ((unique1 = 1) OR (unique2 = 3))
+ Recheck Cond: ((unique2 = 3) OR (unique1 = 1))
-> BitmapOr
- -> Bitmap Index Scan on tenk1_unique1
- Index Cond: (unique1 = 1)
-> Bitmap Index Scan on tenk1_unique2
Index Cond: (unique2 = 3)
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = 1)
(17 rows)
explain (costs off)
@@ -4253,12 +4253,12 @@ select * from tenk1 a join tenk1 b on
Filter: ((unique1 = 2) OR (ten = 4))
-> Materialize
-> Bitmap Heap Scan on tenk1 a
- Recheck Cond: ((unique1 = 1) OR (unique2 = 3))
+ Recheck Cond: ((unique2 = 3) OR (unique1 = 1))
-> BitmapOr
- -> Bitmap Index Scan on tenk1_unique1
- Index Cond: (unique1 = 1)
-> Bitmap Index Scan on tenk1_unique2
Index Cond: (unique2 = 3)
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = 1)
(12 rows)
explain (costs off)
@@ -4270,21 +4270,21 @@ select * from tenk1 a join tenk1 b on
Nested Loop
Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR (((a.unique2 = 3) OR (a.unique2 = 7)) AND (b.hundred = 4)))
-> Bitmap Heap Scan on tenk1 b
- Recheck Cond: ((unique1 = 2) OR (hundred = 4))
+ Recheck Cond: ((hundred = 4) OR (unique1 = 2))
-> BitmapOr
- -> Bitmap Index Scan on tenk1_unique1
- Index Cond: (unique1 = 2)
-> Bitmap Index Scan on tenk1_hundred
Index Cond: (hundred = 4)
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = 2)
-> Materialize
-> Bitmap Heap Scan on tenk1 a
- Recheck Cond: ((unique1 = 1) OR (unique2 = ANY ('{3,7}'::integer[])))
+ Recheck Cond: ((unique2 = ANY ('{3,7}'::integer[])) OR (unique1 = 1))
Filter: ((unique1 = 1) OR (unique2 = 3) OR (unique2 = 7))
-> BitmapOr
- -> Bitmap Index Scan on tenk1_unique1
- Index Cond: (unique1 = 1)
-> Bitmap Index Scan on tenk1_unique2
Index Cond: (unique2 = ANY ('{3,7}'::integer[]))
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = 1)
(18 rows)
explain (costs off)
@@ -4296,21 +4296,21 @@ select * from tenk1 a join tenk1 b on
Nested Loop
Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR (((a.unique2 = 3) OR (a.unique2 = 7)) AND (b.hundred = 4)))
-> Bitmap Heap Scan on tenk1 b
- Recheck Cond: ((unique1 = 2) OR (hundred = 4))
+ Recheck Cond: ((hundred = 4) OR (unique1 = 2))
-> BitmapOr
- -> Bitmap Index Scan on tenk1_unique1
- Index Cond: (unique1 = 2)
-> Bitmap Index Scan on tenk1_hundred
Index Cond: (hundred = 4)
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = 2)
-> Materialize
-> Bitmap Heap Scan on tenk1 a
- Recheck Cond: ((unique1 = 1) OR (unique2 = ANY ('{3,7}'::integer[])))
+ Recheck Cond: ((unique2 = ANY ('{3,7}'::integer[])) OR (unique1 = 1))
Filter: ((unique1 = 1) OR (unique2 = 3) OR (unique2 = 7))
-> BitmapOr
- -> Bitmap Index Scan on tenk1_unique1
- Index Cond: (unique1 = 1)
-> Bitmap Index Scan on tenk1_unique2
Index Cond: (unique2 = ANY ('{3,7}'::integer[]))
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = 1)
(18 rows)
explain (costs off)
@@ -4324,18 +4324,16 @@ select * from tenk1 a join tenk1 b on
-> 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[])))
+ Recheck Cond: ((unique2 = ANY ('{3,7}'::integer[])) OR (unique1 = ANY ('{3,1}'::integer[])) OR (unique1 < 20))
Filter: ((unique1 < 20) OR (unique1 = 3) OR (unique1 = 1) OR (unique2 = 3) OR (unique2 = 7))
-> 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[]))
-(16 rows)
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = ANY ('{3,1}'::integer[]))
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 < 20)
+(14 rows)
--
-- test placement of movable quals in a parameterized join tree
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 319190855bd..ef890b96cc6 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -4492,6 +4492,13 @@ SELECT * FROM rls_tbl WHERE a <<< 1000;
---
(0 rows)
+EXPLAIN (COSTS OFF) SELECT * FROM rls_tbl WHERE a <<< 1000 or a <<< 900;
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
DROP OPERATOR <<< (int, int);
DROP FUNCTION op_leak(int, int);
RESET SESSION AUTHORIZATION;
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index 8c4da955084..a4c7be487ef 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -3254,6 +3254,8 @@ CREATE OPERATOR <<< (procedure = op_leak, leftarg = int, rightarg = int,
restrict = scalarltsel);
SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Permission denied
ERROR: permission denied for table priv_test_tbl
+SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 OR b <<< 0;
+ERROR: permission denied for table priv_test_tbl
DELETE FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Permission denied
ERROR: permission denied for table priv_test_tbl
-- Grant access via a security barrier view, but hide all data
@@ -3268,6 +3270,11 @@ SELECT * FROM tststats.priv_test_view WHERE a <<< 0 AND b <<< 0; -- Should not l
---+---
(0 rows)
+SELECT * FROM tststats.priv_test_view WHERE a <<< 0 OR b <<< 0; -- Should not leak
+ a | b
+---+---
+(0 rows)
+
DELETE FROM tststats.priv_test_view WHERE a <<< 0 AND b <<< 0; -- Should not leak
-- Grant table access, but hide all data with RLS
RESET SESSION AUTHORIZATION;
@@ -3280,6 +3287,11 @@ SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Should not le
---+---
(0 rows)
+SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 OR b <<< 0;
+ a | b
+---+---
+(0 rows)
+
DELETE FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Should not leak
-- privilege checks for pg_stats_ext and pg_stats_ext_exprs
RESET SESSION AUTHORIZATION;
diff --git a/src/test/regress/expected/uuid.out b/src/test/regress/expected/uuid.out
index 6026e15ed31..8f4ef0d7a6a 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -129,6 +129,37 @@ CREATE INDEX guid1_btree ON guid1 USING BTREE (guid_field);
CREATE INDEX guid1_hash ON guid1 USING HASH (guid_field);
-- unique index test
CREATE UNIQUE INDEX guid1_unique_BTREE ON guid1 USING BTREE (guid_field);
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM guid1 WHERE guid_field <> '11111111111111111111111111111111' OR
+ guid_field <> '3f3e3c3b-3a30-3938-3736-353433a2313e';
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Seq Scan on guid1
+ Filter: ((guid_field <> '11111111-1111-1111-1111-111111111111'::uuid) OR (guid_field <> '3f3e3c3b-3a30-3938-3736-353433a2313e'::uuid))
+(3 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM guid1 WHERE guid_field <= '22222222-2222-2222-2222-222222222222' OR
+ guid_field <= '11111111111111111111111111111111' OR
+ guid_field <= '3f3e3c3b-3a30-3938-3736-353433a2313e';
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Seq Scan on guid1
+ Filter: ((guid_field <= '22222222-2222-2222-2222-222222222222'::uuid) OR (guid_field <= '11111111-1111-1111-1111-111111111111'::uuid) OR (guid_field <= '3f3e3c3b-3a30-3938-3736-353433a2313e'::uuid))
+(3 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM guid1 WHERE guid_field = '3f3e3c3b-3a30-3938-3736-353433a2313e' OR
+ guid_field = '11111111111111111111111111111111';
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Seq Scan on guid1
+ Filter: ((guid_field = '3f3e3c3b-3a30-3938-3736-353433a2313e'::uuid) OR (guid_field = '11111111-1111-1111-1111-111111111111'::uuid))
+(3 rows)
+
-- should fail
INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-1111-111111111111');
ERROR: duplicate key value violates unique constraint "guid1_unique_btree"
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index f74ad415fbf..7e108f9b283 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -750,6 +750,14 @@ SELECT * FROM tenk1
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
+EXPLAIN (COSTS OFF)
+SELECT * FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1::numeric OR tenthous = 3::int4 OR tenthous = 42::numeric);
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM tenk1
+ WHERE tenthous = 1::numeric OR tenthous = 3::int4 OR tenthous = 42::numeric;
+
EXPLAIN (COSTS OFF)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
@@ -780,6 +788,25 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND tenthous = 2);
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1, tenk2
+ WHERE tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk1.thousand = 41 OR tenk2.tenthous = 2) AND
+ tenk2.hundred = tenk1.hundred;
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1, tenk2
+ WHERE tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk2.thousand = 41 OR tenk2.tenthous = 2) AND
+ tenk2.hundred = tenk1.hundred;
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1 JOIN tenk2 ON
+ tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk2.thousand = 41 OR tenk2.tenthous = 2) AND
+ tenk2.hundred = tenk1.hundred;
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1 LEFT JOIN tenk2 ON
+ tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk2.thousand = 41 OR tenk2.tenthous = 2) AND
+ tenk2.hundred = tenk1.hundred;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 3011d71b12b..6d2414b6044 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -2177,6 +2177,7 @@ CREATE FUNCTION op_leak(int, int) RETURNS bool
CREATE OPERATOR <<< (procedure = op_leak, leftarg = int, rightarg = int,
restrict = scalarltsel);
SELECT * FROM rls_tbl WHERE a <<< 1000;
+EXPLAIN (COSTS OFF) SELECT * FROM rls_tbl WHERE a <<< 1000 or a <<< 900;
DROP OPERATOR <<< (int, int);
DROP FUNCTION op_leak(int, int);
RESET SESSION AUTHORIZATION;
diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql
index 0c08a6cc42e..5c786b16c6f 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -1634,6 +1634,7 @@ CREATE FUNCTION op_leak(int, int) RETURNS bool
CREATE OPERATOR <<< (procedure = op_leak, leftarg = int, rightarg = int,
restrict = scalarltsel);
SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Permission denied
+SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 OR b <<< 0;
DELETE FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Permission denied
-- Grant access via a security barrier view, but hide all data
@@ -1645,6 +1646,7 @@ GRANT SELECT, DELETE ON tststats.priv_test_view TO regress_stats_user1;
-- Should now have access via the view, but see nothing and leak nothing
SET SESSION AUTHORIZATION regress_stats_user1;
SELECT * FROM tststats.priv_test_view WHERE a <<< 0 AND b <<< 0; -- Should not leak
+SELECT * FROM tststats.priv_test_view WHERE a <<< 0 OR b <<< 0; -- Should not leak
DELETE FROM tststats.priv_test_view WHERE a <<< 0 AND b <<< 0; -- Should not leak
-- Grant table access, but hide all data with RLS
@@ -1655,6 +1657,7 @@ GRANT SELECT, DELETE ON tststats.priv_test_tbl TO regress_stats_user1;
-- Should now have direct table access, but see nothing and leak nothing
SET SESSION AUTHORIZATION regress_stats_user1;
SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Should not leak
+SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 OR b <<< 0;
DELETE FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Should not leak
-- privilege checks for pg_stats_ext and pg_stats_ext_exprs
diff --git a/src/test/regress/sql/uuid.sql b/src/test/regress/sql/uuid.sql
index c88f6d087a7..75ee966ded0 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -63,6 +63,18 @@ CREATE INDEX guid1_hash ON guid1 USING HASH (guid_field);
-- unique index test
CREATE UNIQUE INDEX guid1_unique_BTREE ON guid1 USING BTREE (guid_field);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM guid1 WHERE guid_field <> '11111111111111111111111111111111' OR
+ guid_field <> '3f3e3c3b-3a30-3938-3736-353433a2313e';
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM guid1 WHERE guid_field <= '22222222-2222-2222-2222-222222222222' OR
+ guid_field <= '11111111111111111111111111111111' OR
+ guid_field <= '3f3e3c3b-3a30-3938-3736-353433a2313e';
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM guid1 WHERE guid_field = '3f3e3c3b-3a30-3938-3736-353433a2313e' OR
+ guid_field = '11111111111111111111111111111111';
+
-- should fail
INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-1111-111111111111');
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 547d14b3e7c..f5c4b514646 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1763,6 +1763,7 @@ OprCacheKey
OprInfo
OprProofCacheEntry
OprProofCacheKey
+OrArgIndexMatch
OuterJoinClauseInfo
OutputPluginCallbacks
OutputPluginOptions
--
2.34.1
diff --git a/src/backend/optimizer/path/indxpath.c
b/src/backend/optimizer/path/indxpath.c
index 8e524016c6f..b836af6fb55 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -20,6 +20,7 @@
#include "access/stratnum.h"
#include "access/sysattr.h"
#include "catalog/pg_am.h"
+#include "catalog/pg_amop.h"
#include "catalog/pg_operator.h"
#include "catalog/pg_opfamily.h"
#include "catalog/pg_type.h"
@@ -3158,6 +3159,7 @@ match_orclause_to_indexcol(PlannerInfo *root,
Oid inputcollid = InvalidOid;
bool firstTime = true;
bool have_param = false;
+ HeapTuple tp;
Assert(IsA(orclause, BoolExpr));
Assert(orclause->boolop == OR_EXPR);
@@ -3193,7 +3195,7 @@ match_orclause_to_indexcol(PlannerInfo *root,
if (list_length(subClause->args) != 2)
return NULL;
- /* RestrictInfo parameters must match parent */
+ /* RestrictInfo parameters dmust match parent */
if (subRinfo->is_pushed_down != rinfo->is_pushed_down ||
subRinfo->is_clone != rinfo->is_clone ||
subRinfo->security_level != rinfo->security_level ||
@@ -3234,6 +3236,15 @@ match_orclause_to_indexcol(PlannerInfo *root,
return NULL;
}
+ tp = SearchSysCache3(AMOPOPID,
+ ObjectIdGetDatum(opno),
+
CharGetDatum(AMOP_SEARCH),
+
ObjectIdGetDatum(index->opfamily[indexcol]));
+ if (!HeapTupleIsValid(tp))
+ return NULL;
+
+ ReleaseSysCache(tp);
+
/*
* Ignore any RelabelType node above the operands. This is
needed to
* be able to apply indexscanning in binary-compatible-operator
cases.
diff --git a/src/test/regress/expected/create_index.out
b/src/test/regress/expected/create_index.out
index c6feef03810..cac076abb88 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1913,6 +1913,27 @@ SELECT * FROM tenk1
42 | 5530 | 0 | 2 | 2 | 2 | 42 | 42 |
42 | 42 | 42 | 84 | 85 | QBAAAA | SEIAAA | OOOOxx
(1 row)
+EXPLAIN (COSTS OFF)
+SELECT * FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1::numeric OR tenthous = 3::int4 OR
tenthous = 42::numeric);
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on tenk1
+ Recheck Cond: (thousand = 42)
+ Filter: (((tenthous)::numeric = '1'::numeric) OR (tenthous = 3) OR
((tenthous)::numeric = '42'::numeric))
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = 42)
+(5 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM tenk1
+ WHERE tenthous = 1::numeric OR tenthous = 3::int4 OR tenthous = 42::numeric;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------
+ Seq Scan on tenk1
+ Filter: (((tenthous)::numeric = '1'::numeric) OR (tenthous = 3) OR
((tenthous)::numeric = '42'::numeric))
+(2 rows)
+
EXPLAIN (COSTS OFF)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
@@ -2034,6 +2055,78 @@ SELECT count(*) FROM tenk1
10
(1 row)
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1, tenk2
+ WHERE tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk1.thousand = 41 OR
tenk2.tenthous = 2) AND
+ tenk2.hundred = tenk1.hundred;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: ((tenk2.thousand = 42) OR (tenk1.thousand = 41) OR
(tenk2.tenthous = 2))
+ -> Bitmap Heap Scan on tenk1
+ Recheck Cond: (hundred = 42)
+ -> Bitmap Index Scan on tenk1_hundred
+ Index Cond: (hundred = 42)
+ -> Materialize
+ -> Bitmap Heap Scan on tenk2
+ Recheck Cond: (hundred = 42)
+ -> Bitmap Index Scan on tenk2_hundred
+ Index Cond: (hundred = 42)
+(12 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1, tenk2
+ WHERE tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk2.thousand = 41 OR
tenk2.tenthous = 2) AND
+ tenk2.hundred = tenk1.hundred;
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Bitmap Heap Scan on tenk2
+ Recheck Cond: (hundred = 42)
+ Filter: ((thousand = 42) OR (thousand = 41) OR (tenthous = 2))
+ -> Bitmap Index Scan on tenk2_hundred
+ Index Cond: (hundred = 42)
+ -> Index Only Scan using tenk1_hundred on tenk1
+ Index Cond: (hundred = 42)
+(9 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1 JOIN tenk2 ON
+ tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk2.thousand = 41 OR
tenk2.tenthous = 2) AND
+ tenk2.hundred = tenk1.hundred;
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Bitmap Heap Scan on tenk2
+ Recheck Cond: (hundred = 42)
+ Filter: ((thousand = 42) OR (thousand = 41) OR (tenthous = 2))
+ -> Bitmap Index Scan on tenk2_hundred
+ Index Cond: (hundred = 42)
+ -> Index Only Scan using tenk1_hundred on tenk1
+ Index Cond: (hundred = 42)
+(9 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1 LEFT JOIN tenk2 ON
+ tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk2.thousand = 41 OR
tenk2.tenthous = 2) AND
+ tenk2.hundred = tenk1.hundred;
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop Left Join
+ Join Filter: (tenk1.hundred = 42)
+ -> Index Only Scan using tenk1_hundred on tenk1
+ -> Memoize
+ Cache Key: tenk1.hundred
+ Cache Mode: logical
+ -> Index Scan using tenk2_hundred on tenk2
+ Index Cond: (hundred = tenk1.hundred)
+ Filter: ((thousand = 42) OR (thousand = 41) OR (tenthous
= 2))
+(10 rows)
+
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/expected/rowsecurity.out
b/src/test/regress/expected/rowsecurity.out
index 319190855bd..ef890b96cc6 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -4492,6 +4492,13 @@ SELECT * FROM rls_tbl WHERE a <<< 1000;
---
(0 rows)
+EXPLAIN (COSTS OFF) SELECT * FROM rls_tbl WHERE a <<< 1000 or a <<< 900;
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
DROP OPERATOR <<< (int, int);
DROP FUNCTION op_leak(int, int);
RESET SESSION AUTHORIZATION;
diff --git a/src/test/regress/expected/stats_ext.out
b/src/test/regress/expected/stats_ext.out
index 8c4da955084..a4c7be487ef 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -3254,6 +3254,8 @@ CREATE OPERATOR <<< (procedure = op_leak, leftarg = int,
rightarg = int,
restrict = scalarltsel);
SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Permission
denied
ERROR: permission denied for table priv_test_tbl
+SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 OR b <<< 0;
+ERROR: permission denied for table priv_test_tbl
DELETE FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Permission
denied
ERROR: permission denied for table priv_test_tbl
-- Grant access via a security barrier view, but hide all data
@@ -3268,6 +3270,11 @@ SELECT * FROM tststats.priv_test_view WHERE a <<< 0 AND
b <<< 0; -- Should not l
---+---
(0 rows)
+SELECT * FROM tststats.priv_test_view WHERE a <<< 0 OR b <<< 0; -- Should not
leak
+ a | b
+---+---
+(0 rows)
+
DELETE FROM tststats.priv_test_view WHERE a <<< 0 AND b <<< 0; -- Should not
leak
-- Grant table access, but hide all data with RLS
RESET SESSION AUTHORIZATION;
@@ -3280,6 +3287,11 @@ SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 AND b
<<< 0; -- Should not le
---+---
(0 rows)
+SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 OR b <<< 0;
+ a | b
+---+---
+(0 rows)
+
DELETE FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Should not
leak
-- privilege checks for pg_stats_ext and pg_stats_ext_exprs
RESET SESSION AUTHORIZATION;
diff --git a/src/test/regress/expected/uuid.out
b/src/test/regress/expected/uuid.out
index 6026e15ed31..8f4ef0d7a6a 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -129,6 +129,37 @@ CREATE INDEX guid1_btree ON guid1 USING BTREE (guid_field);
CREATE INDEX guid1_hash ON guid1 USING HASH (guid_field);
-- unique index test
CREATE UNIQUE INDEX guid1_unique_BTREE ON guid1 USING BTREE (guid_field);
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM guid1 WHERE guid_field <>
'11111111111111111111111111111111' OR
+ guid_field <>
'3f3e3c3b-3a30-3938-3736-353433a2313e';
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Seq Scan on guid1
+ Filter: ((guid_field <> '11111111-1111-1111-1111-111111111111'::uuid)
OR (guid_field <> '3f3e3c3b-3a30-3938-3736-353433a2313e'::uuid))
+(3 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM guid1 WHERE guid_field <=
'22222222-2222-2222-2222-222222222222' OR
+
guid_field <= '11111111111111111111111111111111' OR
+
guid_field <= '3f3e3c3b-3a30-3938-3736-353433a2313e';
+
QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Seq Scan on guid1
+ Filter: ((guid_field <= '22222222-2222-2222-2222-222222222222'::uuid)
OR (guid_field <= '11111111-1111-1111-1111-111111111111'::uuid) OR (guid_field
<= '3f3e3c3b-3a30-3938-3736-353433a2313e'::uuid))
+(3 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM guid1 WHERE guid_field =
'3f3e3c3b-3a30-3938-3736-353433a2313e' OR
+ guid_field =
'11111111111111111111111111111111';
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Seq Scan on guid1
+ Filter: ((guid_field = '3f3e3c3b-3a30-3938-3736-353433a2313e'::uuid)
OR (guid_field = '11111111-1111-1111-1111-111111111111'::uuid))
+(3 rows)
+
-- should fail
INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-1111-111111111111');
ERROR: duplicate key value violates unique constraint "guid1_unique_btree"
diff --git a/src/test/regress/sql/create_index.sql
b/src/test/regress/sql/create_index.sql
index f74ad415fbf..7e108f9b283 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -750,6 +750,14 @@ SELECT * FROM tenk1
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
+EXPLAIN (COSTS OFF)
+SELECT * FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1::numeric OR tenthous = 3::int4 OR
tenthous = 42::numeric);
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM tenk1
+ WHERE tenthous = 1::numeric OR tenthous = 3::int4 OR tenthous = 42::numeric;
+
EXPLAIN (COSTS OFF)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
@@ -780,6 +788,25 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND
tenthous = 2);
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1, tenk2
+ WHERE tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk1.thousand = 41 OR
tenk2.tenthous = 2) AND
+ tenk2.hundred = tenk1.hundred;
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1, tenk2
+ WHERE tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk2.thousand = 41 OR
tenk2.tenthous = 2) AND
+ tenk2.hundred = tenk1.hundred;
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1 JOIN tenk2 ON
+ tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk2.thousand = 41 OR
tenk2.tenthous = 2) AND
+ tenk2.hundred = tenk1.hundred;
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1 LEFT JOIN tenk2 ON
+ tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk2.thousand = 41 OR
tenk2.tenthous = 2) AND
+ tenk2.hundred = tenk1.hundred;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/rowsecurity.sql
b/src/test/regress/sql/rowsecurity.sql
index 3011d71b12b..6d2414b6044 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -2177,6 +2177,7 @@ CREATE FUNCTION op_leak(int, int) RETURNS bool
CREATE OPERATOR <<< (procedure = op_leak, leftarg = int, rightarg = int,
restrict = scalarltsel);
SELECT * FROM rls_tbl WHERE a <<< 1000;
+EXPLAIN (COSTS OFF) SELECT * FROM rls_tbl WHERE a <<< 1000 or a <<< 900;
DROP OPERATOR <<< (int, int);
DROP FUNCTION op_leak(int, int);
RESET SESSION AUTHORIZATION;
diff --git a/src/test/regress/sql/stats_ext.sql
b/src/test/regress/sql/stats_ext.sql
index 0c08a6cc42e..5c786b16c6f 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -1634,6 +1634,7 @@ CREATE FUNCTION op_leak(int, int) RETURNS bool
CREATE OPERATOR <<< (procedure = op_leak, leftarg = int, rightarg = int,
restrict = scalarltsel);
SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Permission
denied
+SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 OR b <<< 0;
DELETE FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Permission
denied
-- Grant access via a security barrier view, but hide all data
@@ -1645,6 +1646,7 @@ GRANT SELECT, DELETE ON tststats.priv_test_view TO
regress_stats_user1;
-- Should now have access via the view, but see nothing and leak nothing
SET SESSION AUTHORIZATION regress_stats_user1;
SELECT * FROM tststats.priv_test_view WHERE a <<< 0 AND b <<< 0; -- Should not
leak
+SELECT * FROM tststats.priv_test_view WHERE a <<< 0 OR b <<< 0; -- Should not
leak
DELETE FROM tststats.priv_test_view WHERE a <<< 0 AND b <<< 0; -- Should not
leak
-- Grant table access, but hide all data with RLS
@@ -1655,6 +1657,7 @@ GRANT SELECT, DELETE ON tststats.priv_test_tbl TO
regress_stats_user1;
-- Should now have direct table access, but see nothing and leak nothing
SET SESSION AUTHORIZATION regress_stats_user1;
SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Should not
leak
+SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 OR b <<< 0;
DELETE FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Should not
leak
-- privilege checks for pg_stats_ext and pg_stats_ext_exprs
diff --git a/src/test/regress/sql/uuid.sql b/src/test/regress/sql/uuid.sql
index c88f6d087a7..75ee966ded0 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -63,6 +63,18 @@ CREATE INDEX guid1_hash ON guid1 USING HASH (guid_field);
-- unique index test
CREATE UNIQUE INDEX guid1_unique_BTREE ON guid1 USING BTREE (guid_field);
+
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM guid1 WHERE guid_field <>
'11111111111111111111111111111111' OR
+ guid_field <>
'3f3e3c3b-3a30-3938-3736-353433a2313e';
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM guid1 WHERE guid_field <=
'22222222-2222-2222-2222-222222222222' OR
+
guid_field <= '11111111111111111111111111111111' OR
+
guid_field <= '3f3e3c3b-3a30-3938-3736-353433a2313e';
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM guid1 WHERE guid_field =
'3f3e3c3b-3a30-3938-3736-353433a2313e' OR
+ guid_field =
'11111111111111111111111111111111';
+
-- should fail
INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-1111-111111111111');
