public inbox for [email protected]
help / color / mirror / Atom feedFrom: [email protected]
To: [email protected]
Cc: [email protected]
Subject: Improve EXPLAIN output for multicolumn B-Tree Index
Date: Fri, 21 Jun 2024 07:12:25 +0000
Message-ID: <TYWPR01MB1098260B694D27758FE2BA46FB1C92@TYWPR01MB10982.jpnprd01.prod.outlook.com> (raw)
Hi,
Regarding the multicolumn B-Tree Index, I'm considering
if we can enhance the EXPLAIN output. There have been requests
for this from our customer.
As the document says, we need to use it carefully.
> The exact rule is that equality constraints on leading columns,
> plus any inequality constraints on the first column that does
> not have an equality constraint, will be used to limit the portion
> of the index that is scanned.
https://www.postgresql.org/docs/17/indexes-multicolumn.html
However, it's not easy to confirm whether multi-column indexes are
being used efficiently because we need to compare the index
definitions and query conditions individually.
For instance, just by looking at the following EXPLAIN result, we
can't determine whether the index is being used efficiently or not
at a glance. Indeed, the current index definition is not suitable
for the query, so the cost is significantly high.
=# EXPLAIN (VERBOSE, ANALYZE) SELECT * FROM test WHERE id1 = 1 AND id3 = 101;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using test_idx on public.test (cost=0.42..12754.76 rows=1 width=18) (actual time=0.033..54.115 rows=1 loops=1)
Output: id1, id2, id3, value
Index Cond: ((test.id1 = 1) AND (test.id3 = 101)) -- Is it efficient or not?
Planning Time: 0.145 ms
Execution Time: 54.150 ms
(6 rows)
So, I'd like to improve the output to be more user-friendly.
# Idea
I'm considering adding new information, "Index Bound Cond", which specifies
what quals will be used for the boundary condition of the B-Tree index.
(Since this is just my current idea, I'm open to changing the output.)
Here is an example output.
-- prepare for the test
CREATE TABLE test (id1 int, id2 int, id3 int, value varchar(32));
CREATE INDEX test_idx ON test(id1, id2, id3); -- multicolumn B-Tree index
INSERT INTO test (SELECT i % 2, i, i, 'hello' FROM generate_series(1,1000000) s(i));
ANALYZE;
-- explain
=# EXPLAIN (VERBOSE, ANALYZE) SELECT * FROM test WHERE id1 = 1 AND id2 = 101;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using test_idx on public.test (cost=0.42..8.45 rows=1 width=18) (actual time=0.046..0.047 rows=1 loops=1)
Output: id1, id2, id3, value
Index Cond: ((test.id1 = 1) AND (test.id2 = 101))
Index Bound Cond: ((test.id1 = 1) AND (test.id2 = 101)) -- The B-Tree index is used efficiently.
Planning Time: 0.124 ms
Execution Time: 0.076 ms
(6 rows)
=# EXPLAIN (VERBOSE, ANALYZE) SELECT * FROM test WHERE id1 = 1 AND id3 = 101;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using test_idx on public.test (cost=0.42..12754.76 rows=1 width=18) (actual time=0.033..54.115 rows=1 loops=1)
Output: id1, id2, id3, value
Index Cond: ((test.id1 = 1) AND (test.id3 = 101))
Index Bound Cond: (test.id1 = 1) -- The B-tree index is *not* used efficiently
-- compared to the previous execution conditions,
-- because it differs from "Index Cond".
Planning Time: 0.145 ms
Execution Time: 54.150 ms
(6 rows)
# PoC patch
The PoC patch makes the following changes:
* Adds a new variable related to bound conditions
to IndexPath, IndexScan, IndexOnlyScan, and BitmapIndexScan
* Adds quals for bound conditions to IndexPath when estimating cost, since
the B-Tree index considers the boundary condition in btcostestimate()
* Adds quals for bound conditions to the output of EXPLAIN
Thank you for reading my suggestion. Please feel free to comment.
* Is this feature useful? Is there a possibility it will be accepted?
* Are there any other ideas for determining if multicolumn indexes are
being used efficiently? Although I considered calculating the efficiency using
pg_statio_all_indexes.idx_blks_read and pg_stat_all_indexes.idx_tup_read,
I believe improving the EXPLAIN output is better because it can be output
per query and it's more user-friendly.
* Is "Index Bound Cond" the proper term?I also considered changing
"Index Cond" to only show quals for the boundary condition and adding
a new term "Index Filter".
* Would it be better to add new interfaces to Index AM? Is there any case
to output the EXPLAIN for each index context? At least, I think it's worth
considering whether it's good for amcostestimate() to modify the
IndexPath directly as the PoC patch does.
Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION
Attachments:
[application/octet-stream] v1-0001-PoC-Add-new-information-Index-Bound-Cond-to-EXPLAIN-.patch (228.7K, ../TYWPR01MB1098260B694D27758FE2BA46FB1C92@TYWPR01MB10982.jpnprd01.prod.outlook.com/3-v1-0001-PoC-Add-new-information-Index-Bound-Cond-to-EXPLAIN-.patch)
download | inline diff:
From 78f5cc3649a4253c66a7146b2312f8005f8cee57 Mon Sep 17 00:00:00 2001
From: Masahiro Ikeda <[email protected]>
Date: Fri, 21 Jun 2024 15:18:34 +0900
Subject: [PATCH] PoC: Add new information "Index Bound Cond" to EXPLAIN output
---
.../postgres_fdw/expected/postgres_fdw.out | 18 +-
src/backend/commands/explain.c | 6 +
src/backend/optimizer/plan/createplan.c | 109 +++++---
src/backend/optimizer/plan/setrefs.c | 9 +
src/backend/optimizer/util/pathnode.c | 1 +
src/backend/utils/adt/selfuncs.c | 6 +
src/include/nodes/pathnodes.h | 4 +
src/include/nodes/plannodes.h | 14 +
src/test/regress/expected/aggregates.out | 85 ++++--
src/test/regress/expected/btree_index.out | 69 +++--
src/test/regress/expected/cluster.out | 18 +-
src/test/regress/expected/create_index.out | 118 +++++----
src/test/regress/expected/equivclass.out | 116 +++++---
src/test/regress/expected/explain.out | 3 +-
src/test/regress/expected/expressions.out | 3 +-
src/test/regress/expected/fast_default.out | 7 +-
src/test/regress/expected/foreign_key.out | 4 +-
src/test/regress/expected/generated.out | 18 +-
src/test/regress/expected/groupingsets.out | 3 +-
.../regress/expected/incremental_sort.out | 6 +-
src/test/regress/expected/index_including.out | 21 +-
src/test/regress/expected/inet.out | 28 +-
src/test/regress/expected/inherit.out | 89 +++++--
src/test/regress/expected/insert_conflict.out | 3 +-
src/test/regress/expected/join.out | 248 ++++++++++++------
src/test/regress/expected/memoize.out | 44 +++-
src/test/regress/expected/misc_functions.out | 6 +-
src/test/regress/expected/partition_join.out | 71 +++--
src/test/regress/expected/partition_prune.out | 135 ++++++++--
src/test/regress/expected/plancache.out | 6 +-
src/test/regress/expected/portals.out | 7 +-
src/test/regress/expected/privileges.out | 15 +-
src/test/regress/expected/regex.out | 41 +--
src/test/regress/expected/rowsecurity.out | 30 ++-
src/test/regress/expected/rowtypes.out | 38 +--
src/test/regress/expected/select.out | 29 +-
src/test/regress/expected/select_parallel.out | 25 +-
src/test/regress/expected/stats.out | 9 +-
src/test/regress/expected/subselect.out | 18 +-
src/test/regress/expected/union.out | 24 +-
src/test/regress/expected/updatable_views.out | 79 ++++--
src/test/regress/expected/with.out | 3 +-
42 files changed, 1095 insertions(+), 491 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index ea566d5034..b3c5031d7e 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -739,10 +739,11 @@ EXPLAIN (VERBOSE, COSTS OFF)
-> Index Scan using t1_pkey on "S 1"."T 1" a
Output: a."C 1", a.c2, a.c3, a.c4, a.c5, a.c6, a.c7, a.c8
Index Cond: (a."C 1" = 47)
+ Index Bound Cond: (a."C 1" = 47)
-> Foreign Scan on public.ft2 b
Output: b.c1, b.c2, b.c3, b.c4, b.c5, b.c6, b.c7, b.c8
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = $1::integer))
-(8 rows)
+(9 rows)
SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
C 1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
@@ -3734,6 +3735,7 @@ select c2, sum from "S 1"."T 1" t1, lateral (select sum(t2.c1 + t1."C 1") sum fr
-> Index Scan using t1_pkey on "S 1"."T 1" t1
Output: t1."C 1", t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
Index Cond: (t1."C 1" < 100)
+ Index Bound Cond: (t1."C 1" < 100)
Filter: (t1.c2 < 3)
-> Subquery Scan on qry
Output: qry.sum, t2.c1
@@ -3742,7 +3744,7 @@ select c2, sum from "S 1"."T 1" t1, lateral (select sum(t2.c1 + t1."C 1") sum fr
Output: (sum((t2.c1 + t1."C 1"))), t2.c1
Relations: Aggregate on (public.ft2 t2)
Remote SQL: SELECT sum(("C 1" + $1::integer)), "C 1" FROM "S 1"."T 1" GROUP BY 2
-(16 rows)
+(17 rows)
select c2, sum from "S 1"."T 1" t1, lateral (select sum(t2.c1 + t1."C 1") sum from ft2 t2 group by t2.c1) qry where t1.c2 * 2 = qry.sum and t1.c2 < 3 and t1."C 1" < 100 order by 1;
c2 | sum
@@ -3774,6 +3776,7 @@ ORDER BY ref_0."C 1";
-> Index Scan using t1_pkey on "S 1"."T 1" ref_0
Output: ref_0."C 1", ref_0.c2, ref_0.c3, ref_0.c4, ref_0.c5, ref_0.c6, ref_0.c7, ref_0.c8
Index Cond: (ref_0."C 1" < 10)
+ Index Bound Cond: (ref_0."C 1" < 10)
-> Foreign Scan on public.ft1 ref_1
Output: ref_1.c3, ref_0.c2
Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE ((c3 = '00001'))
@@ -3782,7 +3785,7 @@ ORDER BY ref_0."C 1";
-> Foreign Scan on public.ft2 ref_3
Output: ref_3.c3
Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE ((c3 = '00001'))
-(15 rows)
+(16 rows)
SELECT ref_0.c2, subq_1.*
FROM
@@ -4617,10 +4620,11 @@ explain (verbose, costs off) select * from ft3 f, loct3 l
-> Index Scan using loct3_f1_key on public.loct3 l
Output: l.f1, l.f2, l.f3
Index Cond: (l.f1 = 'foo'::text)
+ Index Bound Cond: (l.f1 = 'foo'::text)
-> Foreign Scan on public.ft3 f
Output: f.f1, f.f2, f.f3
Remote SQL: SELECT f1, f2, f3 FROM public.loct3 WHERE ((f3 = $1::character varying(10)))
-(8 rows)
+(9 rows)
-- can't be sent to remote
explain (verbose, costs off) select * from ft3 where f1 COLLATE "POSIX" = 'foo';
@@ -4675,7 +4679,8 @@ explain (verbose, costs off) select * from ft3 f, loct3 l
-> Index Scan using loct3_f1_key on public.loct3 l
Output: l.f1, l.f2, l.f3
Index Cond: (l.f1 = 'foo'::text)
-(12 rows)
+ Index Bound Cond: (l.f1 = 'foo'::text)
+(13 rows)
-- ===================================================================
-- test SEMI-JOIN pushdown
@@ -8482,9 +8487,10 @@ delete from foo where f1 < 5 returning *;
-> Index Scan using i_foo_f1 on public.foo foo_1
Output: foo_1.tableoid, foo_1.ctid
Index Cond: (foo_1.f1 < 5)
+ Index Bound Cond: (foo_1.f1 < 5)
-> Foreign Delete on public.foo2 foo_2
Remote SQL: DELETE FROM public.loct1 WHERE ((f1 < 5)) RETURNING f1, f2
-(10 rows)
+(11 rows)
delete from foo where f1 < 5 returning *;
f1 | f2
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 94511a5a02..f071c30048 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1966,6 +1966,8 @@ ExplainNode(PlanState *planstate, List *ancestors,
case T_IndexScan:
show_scan_qual(((IndexScan *) plan)->indexqualorig,
"Index Cond", planstate, ancestors, es);
+ show_scan_qual(((IndexScan *) plan)->indexboundqualorig,
+ "Index Bound Cond", planstate, ancestors, es);
if (((IndexScan *) plan)->indexqualorig)
show_instrumentation_count("Rows Removed by Index Recheck", 2,
planstate, es);
@@ -1979,6 +1981,8 @@ ExplainNode(PlanState *planstate, List *ancestors,
case T_IndexOnlyScan:
show_scan_qual(((IndexOnlyScan *) plan)->indexqual,
"Index Cond", planstate, ancestors, es);
+ show_scan_qual(((IndexOnlyScan *) plan)->indexboundqual,
+ "Index Bound Cond", planstate, ancestors, es);
if (((IndexOnlyScan *) plan)->recheckqual)
show_instrumentation_count("Rows Removed by Index Recheck", 2,
planstate, es);
@@ -1995,6 +1999,8 @@ ExplainNode(PlanState *planstate, List *ancestors,
case T_BitmapIndexScan:
show_scan_qual(((BitmapIndexScan *) plan)->indexqualorig,
"Index Cond", planstate, ancestors, es);
+ show_scan_qual(((BitmapIndexScan *) plan)->indexboundqualorig,
+ "Index Bound Cond", planstate, ancestors, es);
break;
case T_BitmapHeapScan:
show_scan_qual(((BitmapHeapScan *) plan)->bitmapqualorig,
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 6b64c4a362..92a33e79c0 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -166,7 +166,9 @@ static Node *replace_nestloop_params(PlannerInfo *root, Node *expr);
static Node *replace_nestloop_params_mutator(Node *node, PlannerInfo *root);
static void fix_indexqual_references(PlannerInfo *root, IndexPath *index_path,
List **stripped_indexquals_p,
- List **fixed_indexquals_p);
+ List **fixed_indexquals_p,
+ List **stripped_indexboundquals_p,
+ List **fixed_indexboundquals_p);
static List *fix_indexorderby_references(PlannerInfo *root, IndexPath *index_path);
static Node *fix_indexqual_clause(PlannerInfo *root,
IndexOptInfo *index, int indexcol,
@@ -183,18 +185,22 @@ static SampleScan *make_samplescan(List *qptlist, List *qpqual, Index scanrelid,
TableSampleClause *tsc);
static IndexScan *make_indexscan(List *qptlist, List *qpqual, Index scanrelid,
Oid indexid, List *indexqual, List *indexqualorig,
+ List *indexboundqualorig,
List *indexorderby, List *indexorderbyorig,
List *indexorderbyops,
ScanDirection indexscandir);
static IndexOnlyScan *make_indexonlyscan(List *qptlist, List *qpqual,
Index scanrelid, Oid indexid,
- List *indexqual, List *recheckqual,
+ List *indexqual,
+ List *indexboundqual,
+ List *recheckqual,
List *indexorderby,
List *indextlist,
ScanDirection indexscandir);
static BitmapIndexScan *make_bitmap_indexscan(Index scanrelid, Oid indexid,
List *indexqual,
- List *indexqualorig);
+ List *indexqualorig,
+ List *indexboundqualorig);
static BitmapHeapScan *make_bitmap_heapscan(List *qptlist,
List *qpqual,
Plan *lefttree,
@@ -3017,6 +3023,8 @@ create_indexscan_plan(PlannerInfo *root,
List *qpqual;
List *stripped_indexquals;
List *fixed_indexquals;
+ List *stripped_indexboundquals;
+ List *fixed_indexboundquals;
List *fixed_indexorderbys;
List *indexorderbyops = NIL;
ListCell *l;
@@ -3036,7 +3044,9 @@ create_indexscan_plan(PlannerInfo *root,
*/
fix_indexqual_references(root, best_path,
&stripped_indexquals,
- &fixed_indexquals);
+ &fixed_indexquals,
+ &stripped_indexboundquals,
+ &fixed_indexboundquals);
/*
* Likewise fix up index attr references in the ORDER BY expressions.
@@ -3106,6 +3116,8 @@ create_indexscan_plan(PlannerInfo *root,
{
stripped_indexquals = (List *)
replace_nestloop_params(root, (Node *) stripped_indexquals);
+ stripped_indexboundquals = (List *)
+ replace_nestloop_params(root, (Node *) stripped_indexboundquals);
qpqual = (List *)
replace_nestloop_params(root, (Node *) qpqual);
indexorderbys = (List *)
@@ -3171,6 +3183,7 @@ create_indexscan_plan(PlannerInfo *root,
baserelid,
indexoid,
fixed_indexquals,
+ fixed_indexboundquals,
stripped_indexquals,
fixed_indexorderbys,
indexinfo->indextlist,
@@ -3182,6 +3195,7 @@ create_indexscan_plan(PlannerInfo *root,
indexoid,
fixed_indexquals,
stripped_indexquals,
+ stripped_indexboundquals,
fixed_indexorderbys,
indexorderbys,
indexorderbyops,
@@ -3475,7 +3489,8 @@ create_bitmap_subplan(PlannerInfo *root, Path *bitmapqual,
plan = (Plan *) make_bitmap_indexscan(iscan->scan.scanrelid,
iscan->indexid,
iscan->indexqual,
- iscan->indexqualorig);
+ iscan->indexqualorig,
+ iscan->indexboundqualorig);
/* and set its cost/width fields appropriately */
plan->startup_cost = 0.0;
plan->total_cost = ipath->indextotalcost;
@@ -4998,6 +5013,39 @@ replace_nestloop_params_mutator(Node *node, PlannerInfo *root)
(void *) root);
}
+static void
+fix_indexqual_references_clauses(PlannerInfo *root, IndexOptInfo *index,
+ List *indexclauses, List **stripped_quals_p,
+ List **fixed_quals_p)
+{
+ List *stripped_quals;
+ List *fixed_quals;
+ ListCell *lc;
+
+ stripped_quals = fixed_quals = NIL;
+
+ foreach(lc, indexclauses)
+ {
+ IndexClause *iclause = lfirst_node(IndexClause, lc);
+ int indexcol = iclause->indexcol;
+ ListCell *lc2;
+
+ foreach(lc2, iclause->indexquals)
+ {
+ RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc2);
+ Node *clause = (Node *) rinfo->clause;
+
+ stripped_quals = lappend(stripped_quals, clause);
+ clause = fix_indexqual_clause(root, index, indexcol,
+ clause, iclause->indexcols);
+ fixed_quals = lappend(fixed_quals, clause);
+ }
+ }
+
+ *stripped_quals_p = stripped_quals;
+ *fixed_quals_p = fixed_quals;
+}
+
/*
* fix_indexqual_references
* Adjust indexqual clauses to the form the executor's indexqual
@@ -5017,38 +5065,25 @@ replace_nestloop_params_mutator(Node *node, PlannerInfo *root)
* that shares no substructure with the original; this is needed in case there
* are subplans in it (we need two separate copies of the subplan tree, or
* things will go awry).
+ *
+ * *stripped_indexboundquals_p and *fixed_indexboundquals_p are the same as above
+ * except that they store only the quals used to scan index efficiently.
+ *
*/
static void
fix_indexqual_references(PlannerInfo *root, IndexPath *index_path,
- List **stripped_indexquals_p, List **fixed_indexquals_p)
+ List **stripped_indexquals_p, List **fixed_indexquals_p,
+ List **stripped_indexboundquals_p, List **fixed_indexboundquals_p)
{
- IndexOptInfo *index = index_path->indexinfo;
- List *stripped_indexquals;
- List *fixed_indexquals;
- ListCell *lc;
-
- stripped_indexquals = fixed_indexquals = NIL;
-
- foreach(lc, index_path->indexclauses)
- {
- IndexClause *iclause = lfirst_node(IndexClause, lc);
- int indexcol = iclause->indexcol;
- ListCell *lc2;
-
- foreach(lc2, iclause->indexquals)
- {
- RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc2);
- Node *clause = (Node *) rinfo->clause;
-
- stripped_indexquals = lappend(stripped_indexquals, clause);
- clause = fix_indexqual_clause(root, index, indexcol,
- clause, iclause->indexcols);
- fixed_indexquals = lappend(fixed_indexquals, clause);
- }
- }
-
- *stripped_indexquals_p = stripped_indexquals;
- *fixed_indexquals_p = fixed_indexquals;
+ fix_indexqual_references_clauses(root, index_path->indexinfo,
+ index_path->indexclauses,
+ stripped_indexquals_p,
+ fixed_indexquals_p);
+
+ fix_indexqual_references_clauses(root, index_path->indexinfo,
+ index_path->indexboundclauses,
+ stripped_indexboundquals_p,
+ fixed_indexboundquals_p);
}
/*
@@ -5547,6 +5582,7 @@ make_indexscan(List *qptlist,
Oid indexid,
List *indexqual,
List *indexqualorig,
+ List *indexboundqualorig,
List *indexorderby,
List *indexorderbyorig,
List *indexorderbyops,
@@ -5563,6 +5599,7 @@ make_indexscan(List *qptlist,
node->indexid = indexid;
node->indexqual = indexqual;
node->indexqualorig = indexqualorig;
+ node->indexboundqualorig = indexboundqualorig;
node->indexorderby = indexorderby;
node->indexorderbyorig = indexorderbyorig;
node->indexorderbyops = indexorderbyops;
@@ -5577,6 +5614,7 @@ make_indexonlyscan(List *qptlist,
Index scanrelid,
Oid indexid,
List *indexqual,
+ List *indexboundqual,
List *recheckqual,
List *indexorderby,
List *indextlist,
@@ -5592,6 +5630,7 @@ make_indexonlyscan(List *qptlist,
node->scan.scanrelid = scanrelid;
node->indexid = indexid;
node->indexqual = indexqual;
+ node->indexboundqual = indexboundqual;
node->recheckqual = recheckqual;
node->indexorderby = indexorderby;
node->indextlist = indextlist;
@@ -5604,7 +5643,8 @@ static BitmapIndexScan *
make_bitmap_indexscan(Index scanrelid,
Oid indexid,
List *indexqual,
- List *indexqualorig)
+ List *indexqualorig,
+ List *indexboundqualorig)
{
BitmapIndexScan *node = makeNode(BitmapIndexScan);
Plan *plan = &node->scan.plan;
@@ -5617,6 +5657,7 @@ make_bitmap_indexscan(Index scanrelid,
node->indexid = indexid;
node->indexqual = indexqual;
node->indexqualorig = indexqualorig;
+ node->indexboundqualorig = indexboundqualorig;
return node;
}
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 7aed84584c..2125b12819 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -666,6 +666,9 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset)
splan->indexqualorig =
fix_scan_list(root, splan->indexqualorig,
rtoffset, NUM_EXEC_QUAL(plan));
+ splan->indexboundqualorig =
+ fix_scan_list(root, splan->indexboundqualorig,
+ rtoffset, NUM_EXEC_QUAL(plan));
splan->indexorderby =
fix_scan_list(root, splan->indexorderby,
rtoffset, 1);
@@ -694,6 +697,9 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset)
splan->indexqualorig =
fix_scan_list(root, splan->indexqualorig,
rtoffset, NUM_EXEC_QUAL(plan));
+ splan->indexboundqualorig =
+ fix_scan_list(root, splan->indexboundqualorig,
+ rtoffset, NUM_EXEC_QUAL(plan));
}
break;
case T_BitmapHeapScan:
@@ -1372,6 +1378,9 @@ set_indexonlyscan_references(PlannerInfo *root,
/* indexqual is already transformed to reference index columns */
plan->indexqual = fix_scan_list(root, plan->indexqual,
rtoffset, 1);
+ /* indexboundqual is already transformed to reference index columns */
+ plan->indexboundqual = fix_scan_list(root, plan->indexboundqual,
+ rtoffset, 1);
/* indexorderby is already transformed to reference index columns */
plan->indexorderby = fix_scan_list(root, plan->indexorderby,
rtoffset, 1);
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index c42742d2c7..a0e56a57c8 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -4190,6 +4190,7 @@ do { \
ADJUST_CHILD_ATTRS(ipath->indexinfo->indrestrictinfo);
ADJUST_CHILD_ATTRS(ipath->indexclauses);
+ ADJUST_CHILD_ATTRS(ipath->indexboundclauses);
new_path = (Path *) ipath;
}
break;
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 5f5d7959d8..21bada12fe 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -6798,6 +6798,7 @@ btcostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
double numIndexTuples;
Cost descentCost;
List *indexBoundQuals;
+ List *indexBoundClauses;
int indexcol;
bool eqQualHere;
bool found_saop;
@@ -6823,6 +6824,7 @@ btcostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
* operator can be considered to act the same as it normally does.
*/
indexBoundQuals = NIL;
+ indexBoundClauses = NIL;
indexcol = 0;
eqQualHere = false;
found_saop = false;
@@ -6844,6 +6846,9 @@ btcostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
break; /* no quals at all for indexcol */
}
+ /* Store the clauses for EXPLAIN */
+ indexBoundClauses = lappend(indexBoundClauses, iclause);
+
/* Examine each indexqual associated with this index clause */
foreach(lc2, iclause->indexquals)
{
@@ -7125,6 +7130,7 @@ btcostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
*indexSelectivity = costs.indexSelectivity;
*indexCorrelation = costs.indexCorrelation;
*indexPages = costs.numIndexPages;
+ path->indexboundclauses = indexBoundClauses;
}
void
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 2ba297c117..1125d611cb 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -1681,6 +1681,9 @@ typedef struct Path
* index-checkable restriction, with implicit AND semantics across the list.
* An empty list implies a full index scan.
*
+ * 'indexboundclauses' is used for EXPLAIN. It is a subset of 'indexclauses',
+ * storing only the quals used to scan index efficiently.
+ *
* 'indexorderbys', if not NIL, is a list of ORDER BY expressions that have
* been found to be usable as ordering operators for an amcanorderbyop index.
* The list must match the path's pathkeys, ie, one expression per pathkey
@@ -1709,6 +1712,7 @@ typedef struct IndexPath
Path path;
IndexOptInfo *indexinfo;
List *indexclauses;
+ List *indexboundclauses;
List *indexorderbys;
List *indexorderbycols;
ScanDirection indexscandir;
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 1aeeaec95e..79e720fdeb 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -421,6 +421,9 @@ typedef struct SampleScan
* column position (but items referencing the same index column can appear
* in any order). indexqualorig is used at runtime only if we have to recheck
* a lossy indexqual.
+ *
+ * indexboundqualorig is used for EXPLAIN. It is a subset of indexqualorig,
+ * storing only the quals used to scan index efficiently.
*
* indexqual has the same form, but the expressions have been commuted if
* necessary to put the indexkeys on the left, and the indexkeys are replaced
@@ -452,6 +455,7 @@ typedef struct IndexScan
Oid indexid; /* OID of index to scan */
List *indexqual; /* list of index quals (usually OpExprs) */
List *indexqualorig; /* the same in original form */
+ List *indexboundqualorig; /* index quals to scan efficiently */
List *indexorderby; /* list of index ORDER BY exprs */
List *indexorderbyorig; /* the same in original form */
List *indexorderbyops; /* OIDs of sort ops for ORDER BY exprs */
@@ -476,6 +480,10 @@ typedef struct IndexScan
* an expression f(x) in a non-retrievable column "ind2", an indexable
* query on f(x) will use "ind2" in indexqual and f(ind1) in recheckqual.
* Without the "ind1" column, an index-only scan would be disallowed.)
+ *
+ * indexboundqualorig is used for EXPLAIN. It is a subset of indexqual,
+ * storing only the quals used to scan index efficiently.
+ *
*
* We don't currently need a recheckable equivalent of indexorderby,
* because we don't support lossy operators in index ORDER BY.
@@ -494,6 +502,7 @@ typedef struct IndexOnlyScan
Scan scan;
Oid indexid; /* OID of index to scan */
List *indexqual; /* list of index quals (usually OpExprs) */
+ List *indexboundqual; /* index quals to scan efficiently */
List *recheckqual; /* index quals in recheckable form */
List *indexorderby; /* list of index ORDER BY exprs */
List *indextlist; /* TargetEntry list describing index's cols */
@@ -515,6 +524,10 @@ typedef struct IndexOnlyScan
* In a BitmapIndexScan plan node, the targetlist and qual fields are
* not used and are always NIL. The indexqualorig field is unused at
* run time too, but is saved for the benefit of EXPLAIN.
+ *
+ * indexboundqualorig is used for EXPLAIN. It is a subset of indexqualorig,
+ * storing only the quals used to scan index efficiently.
+ *
* ----------------
*/
typedef struct BitmapIndexScan
@@ -524,6 +537,7 @@ typedef struct BitmapIndexScan
bool isshared; /* Create shared bitmap if set */
List *indexqual; /* list of index quals (OpExprs) */
List *indexqualorig; /* the same in original form */
+ List *indexboundqualorig; /* index quals to scan efficiently */
} BitmapIndexScan;
/* ----------------
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index 1c1ca7573a..f017131c8e 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -919,7 +919,8 @@ explain (costs off)
-> Limit
-> Index Only Scan using tenk1_unique1 on tenk1
Index Cond: (unique1 IS NOT NULL)
-(5 rows)
+ Index Bound Cond: (unique1 IS NOT NULL)
+(6 rows)
select min(unique1) from tenk1;
min
@@ -936,7 +937,8 @@ explain (costs off)
-> Limit
-> Index Only Scan Backward using tenk1_unique1 on tenk1
Index Cond: (unique1 IS NOT NULL)
-(5 rows)
+ Index Bound Cond: (unique1 IS NOT NULL)
+(6 rows)
select max(unique1) from tenk1;
max
@@ -946,14 +948,15 @@ select max(unique1) from tenk1;
explain (costs off)
select max(unique1) from tenk1 where unique1 < 42;
- QUERY PLAN
-------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------
Result
InitPlan 1
-> Limit
-> Index Only Scan Backward using tenk1_unique1 on tenk1
Index Cond: ((unique1 IS NOT NULL) AND (unique1 < 42))
-(5 rows)
+ Index Bound Cond: ((unique1 IS NOT NULL) AND (unique1 < 42))
+(6 rows)
select max(unique1) from tenk1 where unique1 < 42;
max
@@ -963,14 +966,15 @@ select max(unique1) from tenk1 where unique1 < 42;
explain (costs off)
select max(unique1) from tenk1 where unique1 > 42;
- QUERY PLAN
-------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------
Result
InitPlan 1
-> Limit
-> Index Only Scan Backward using tenk1_unique1 on tenk1
Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42))
-(5 rows)
+ Index Bound Cond: ((unique1 IS NOT NULL) AND (unique1 > 42))
+(6 rows)
select max(unique1) from tenk1 where unique1 > 42;
max
@@ -986,14 +990,15 @@ begin;
set local max_parallel_workers_per_gather = 0;
explain (costs off)
select max(unique1) from tenk1 where unique1 > 42000;
- QUERY PLAN
----------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------
Result
InitPlan 1
-> Limit
-> Index Only Scan Backward using tenk1_unique1 on tenk1
Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42000))
-(5 rows)
+ Index Bound Cond: ((unique1 IS NOT NULL) AND (unique1 > 42000))
+(6 rows)
select max(unique1) from tenk1 where unique1 > 42000;
max
@@ -1005,14 +1010,15 @@ rollback;
-- multi-column index (uses tenk1_thous_tenthous)
explain (costs off)
select max(tenthous) from tenk1 where thousand = 33;
- QUERY PLAN
-----------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------
Result
InitPlan 1
-> Limit
-> Index Only Scan Backward using tenk1_thous_tenthous on tenk1
Index Cond: ((thousand = 33) AND (tenthous IS NOT NULL))
-(5 rows)
+ Index Bound Cond: ((thousand = 33) AND (tenthous IS NOT NULL))
+(6 rows)
select max(tenthous) from tenk1 where thousand = 33;
max
@@ -1022,14 +1028,15 @@ select max(tenthous) from tenk1 where thousand = 33;
explain (costs off)
select min(tenthous) from tenk1 where thousand = 33;
- QUERY PLAN
---------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------
Result
InitPlan 1
-> Limit
-> Index Only Scan using tenk1_thous_tenthous on tenk1
Index Cond: ((thousand = 33) AND (tenthous IS NOT NULL))
-(5 rows)
+ Index Bound Cond: ((thousand = 33) AND (tenthous IS NOT NULL))
+(6 rows)
select min(tenthous) from tenk1 where thousand = 33;
min
@@ -1041,8 +1048,8 @@ select min(tenthous) from tenk1 where thousand = 33;
explain (costs off)
select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt
from int4_tbl;
- QUERY PLAN
------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
Seq Scan on int4_tbl
SubPlan 2
-> Result
@@ -1050,7 +1057,8 @@ explain (costs off)
-> Limit
-> Index Only Scan using tenk1_unique1 on tenk1
Index Cond: ((unique1 IS NOT NULL) AND (unique1 > int4_tbl.f1))
-(7 rows)
+ Index Bound Cond: ((unique1 IS NOT NULL) AND (unique1 > int4_tbl.f1))
+(8 rows)
select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt
from int4_tbl;
@@ -1074,8 +1082,9 @@ explain (costs off)
-> Limit
-> Index Only Scan Backward using tenk1_unique2 on tenk1
Index Cond: (unique2 IS NOT NULL)
+ Index Bound Cond: (unique2 IS NOT NULL)
-> Result
-(7 rows)
+(8 rows)
select distinct max(unique2) from tenk1;
max
@@ -1093,8 +1102,9 @@ explain (costs off)
-> Limit
-> Index Only Scan Backward using tenk1_unique2 on tenk1
Index Cond: (unique2 IS NOT NULL)
+ Index Bound Cond: (unique2 IS NOT NULL)
-> Result
-(7 rows)
+(8 rows)
select max(unique2) from tenk1 order by 1;
max
@@ -1112,8 +1122,9 @@ explain (costs off)
-> Limit
-> Index Only Scan Backward using tenk1_unique2 on tenk1
Index Cond: (unique2 IS NOT NULL)
+ Index Bound Cond: (unique2 IS NOT NULL)
-> Result
-(7 rows)
+(8 rows)
select max(unique2) from tenk1 order by max(unique2);
max
@@ -1131,8 +1142,9 @@ explain (costs off)
-> Limit
-> Index Only Scan Backward using tenk1_unique2 on tenk1
Index Cond: (unique2 IS NOT NULL)
+ Index Bound Cond: (unique2 IS NOT NULL)
-> Result
-(7 rows)
+(8 rows)
select max(unique2) from tenk1 order by max(unique2)+1;
max
@@ -1150,9 +1162,10 @@ explain (costs off)
-> Limit
-> Index Only Scan Backward using tenk1_unique2 on tenk1
Index Cond: (unique2 IS NOT NULL)
+ Index Bound Cond: (unique2 IS NOT NULL)
-> ProjectSet
-> Result
-(8 rows)
+(9 rows)
select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;
max | g
@@ -1205,10 +1218,13 @@ explain (costs off)
Sort Key: minmaxtest.f1
-> Index Only Scan using minmaxtesti on minmaxtest minmaxtest_1
Index Cond: (f1 IS NOT NULL)
+ Index Bound Cond: (f1 IS NOT NULL)
-> Index Only Scan using minmaxtest1i on minmaxtest1 minmaxtest_2
Index Cond: (f1 IS NOT NULL)
+ Index Bound Cond: (f1 IS NOT NULL)
-> Index Only Scan Backward using minmaxtest2i on minmaxtest2 minmaxtest_3
Index Cond: (f1 IS NOT NULL)
+ Index Bound Cond: (f1 IS NOT NULL)
-> Index Only Scan using minmaxtest3i on minmaxtest3 minmaxtest_4
InitPlan 2
-> Limit
@@ -1216,12 +1232,15 @@ explain (costs off)
Sort Key: minmaxtest_5.f1 DESC
-> Index Only Scan Backward using minmaxtesti on minmaxtest minmaxtest_6
Index Cond: (f1 IS NOT NULL)
+ Index Bound Cond: (f1 IS NOT NULL)
-> Index Only Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest_7
Index Cond: (f1 IS NOT NULL)
+ Index Bound Cond: (f1 IS NOT NULL)
-> Index Only Scan using minmaxtest2i on minmaxtest2 minmaxtest_8
Index Cond: (f1 IS NOT NULL)
+ Index Bound Cond: (f1 IS NOT NULL)
-> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest_9
-(23 rows)
+(29 rows)
select min(f1), max(f1) from minmaxtest;
min | max
@@ -1241,10 +1260,13 @@ explain (costs off)
Sort Key: minmaxtest.f1
-> Index Only Scan using minmaxtesti on minmaxtest minmaxtest_1
Index Cond: (f1 IS NOT NULL)
+ Index Bound Cond: (f1 IS NOT NULL)
-> Index Only Scan using minmaxtest1i on minmaxtest1 minmaxtest_2
Index Cond: (f1 IS NOT NULL)
+ Index Bound Cond: (f1 IS NOT NULL)
-> Index Only Scan Backward using minmaxtest2i on minmaxtest2 minmaxtest_3
Index Cond: (f1 IS NOT NULL)
+ Index Bound Cond: (f1 IS NOT NULL)
-> Index Only Scan using minmaxtest3i on minmaxtest3 minmaxtest_4
InitPlan 2
-> Limit
@@ -1252,15 +1274,18 @@ explain (costs off)
Sort Key: minmaxtest_5.f1 DESC
-> Index Only Scan Backward using minmaxtesti on minmaxtest minmaxtest_6
Index Cond: (f1 IS NOT NULL)
+ Index Bound Cond: (f1 IS NOT NULL)
-> Index Only Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest_7
Index Cond: (f1 IS NOT NULL)
+ Index Bound Cond: (f1 IS NOT NULL)
-> Index Only Scan using minmaxtest2i on minmaxtest2 minmaxtest_8
Index Cond: (f1 IS NOT NULL)
+ Index Bound Cond: (f1 IS NOT NULL)
-> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest_9
-> Sort
Sort Key: ((InitPlan 1).col1), ((InitPlan 2).col1)
-> Result
-(26 rows)
+(32 rows)
select distinct min(f1), max(f1) from minmaxtest;
min | max
@@ -2874,7 +2899,8 @@ SELECT y, x, array_agg(distinct w)
-> Index Only Scan using btg_y_x_w_idx on public.btg
Output: y, x, w
Index Cond: (btg.y < 0)
-(6 rows)
+ Index Bound Cond: (btg.y < 0)
+(7 rows)
-- Ensure that we do not select the aggregate pathkeys instead of the grouping
-- pathkeys
@@ -2986,7 +3012,8 @@ FROM agg_sort_order WHERE c2 < 100 GROUP BY c1 ORDER BY 2;
Sort Key: c1, c2
-> Index Scan using agg_sort_order_c2_idx on agg_sort_order
Index Cond: (c2 < 100)
-(8 rows)
+ Index Bound Cond: (c2 < 100)
+(9 rows)
DROP TABLE agg_sort_order CASCADE;
DROP TABLE btg;
diff --git a/src/test/regress/expected/btree_index.out b/src/test/regress/expected/btree_index.out
index 510646cbce..0a99ac1806 100644
--- a/src/test/regress/expected/btree_index.out
+++ b/src/test/regress/expected/btree_index.out
@@ -163,7 +163,8 @@ select hundred, twenty from tenk1 where hundred < 48 order by hundred desc limit
Limit
-> Index Scan Backward using tenk1_hundred on tenk1
Index Cond: (hundred < 48)
-(3 rows)
+ Index Bound Cond: (hundred < 48)
+(4 rows)
select hundred, twenty from tenk1 where hundred < 48 order by hundred desc limit 1;
hundred | twenty
@@ -181,7 +182,8 @@ select hundred, twenty from tenk1 where hundred <= 48 order by hundred desc limi
Limit
-> Index Scan Backward using tenk1_hundred on tenk1
Index Cond: (hundred <= 48)
-(3 rows)
+ Index Bound Cond: (hundred <= 48)
+(4 rows)
select hundred, twenty from tenk1 where hundred <= 48 order by hundred desc limit 1;
hundred | twenty
@@ -194,12 +196,13 @@ select hundred, twenty from tenk1 where hundred <= 48 order by hundred desc limi
--
explain (costs off)
select distinct hundred from tenk1 where hundred in (47, 48, 72, 82);
- QUERY PLAN
-------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------
Unique
-> Index Only Scan using tenk1_hundred on tenk1
Index Cond: (hundred = ANY ('{47,48,72,82}'::integer[]))
-(3 rows)
+ Index Bound Cond: (hundred = ANY ('{47,48,72,82}'::integer[]))
+(4 rows)
select distinct hundred from tenk1 where hundred in (47, 48, 72, 82);
hundred
@@ -212,12 +215,13 @@ select distinct hundred from tenk1 where hundred in (47, 48, 72, 82);
explain (costs off)
select distinct hundred from tenk1 where hundred in (47, 48, 72, 82) order by hundred desc;
- QUERY PLAN
-------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------
Unique
-> Index Only Scan Backward using tenk1_hundred on tenk1
Index Cond: (hundred = ANY ('{47,48,72,82}'::integer[]))
-(3 rows)
+ Index Bound Cond: (hundred = ANY ('{47,48,72,82}'::integer[]))
+(4 rows)
select distinct hundred from tenk1 where hundred in (47, 48, 72, 82) order by hundred desc;
hundred
@@ -230,11 +234,12 @@ select distinct hundred from tenk1 where hundred in (47, 48, 72, 82) order by hu
explain (costs off)
select thousand from tenk1 where thousand in (364, 366,380) and tenthous = 200000;
- QUERY PLAN
----------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------
Index Only Scan using tenk1_thous_tenthous on tenk1
Index Cond: ((thousand = ANY ('{364,366,380}'::integer[])) AND (tenthous = 200000))
-(2 rows)
+ Index Bound Cond: ((thousand = ANY ('{364,366,380}'::integer[])) AND (tenthous = 200000))
+(3 rows)
select thousand from tenk1 where thousand in (364, 366,380) and tenthous = 200000;
thousand
@@ -250,12 +255,13 @@ set enable_indexscan to true;
set enable_bitmapscan to false;
explain (costs off)
select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1;
- QUERY PLAN
-------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------
Index Only Scan using pg_proc_proname_args_nsp_index on pg_proc
Index Cond: ((proname >= 'RI_FKey'::text) AND (proname < 'RI_FKez'::text))
+ Index Bound Cond: ((proname >= 'RI_FKey'::text) AND (proname < 'RI_FKez'::text))
Filter: (proname ~~ 'RI\_FKey%del'::text)
-(3 rows)
+(4 rows)
select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1;
proname
@@ -269,12 +275,13 @@ select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1;
explain (costs off)
select proname from pg_proc where proname ilike '00%foo' order by 1;
- QUERY PLAN
---------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------
Index Only Scan using pg_proc_proname_args_nsp_index on pg_proc
Index Cond: ((proname >= '00'::text) AND (proname < '01'::text))
+ Index Bound Cond: ((proname >= '00'::text) AND (proname < '01'::text))
Filter: (proname ~~* '00%foo'::text)
-(3 rows)
+(4 rows)
select proname from pg_proc where proname ilike '00%foo' order by 1;
proname
@@ -293,15 +300,16 @@ set enable_indexscan to false;
set enable_bitmapscan to true;
explain (costs off)
select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1;
- QUERY PLAN
-------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
Sort
Sort Key: proname
-> Bitmap Heap Scan on pg_proc
Filter: (proname ~~ 'RI\_FKey%del'::text)
-> Bitmap Index Scan on pg_proc_proname_args_nsp_index
Index Cond: ((proname >= 'RI_FKey'::text) AND (proname < 'RI_FKez'::text))
-(6 rows)
+ Index Bound Cond: ((proname >= 'RI_FKey'::text) AND (proname < 'RI_FKez'::text))
+(7 rows)
select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1;
proname
@@ -315,15 +323,16 @@ select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1;
explain (costs off)
select proname from pg_proc where proname ilike '00%foo' order by 1;
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------
Sort
Sort Key: proname
-> Bitmap Heap Scan on pg_proc
Filter: (proname ~~* '00%foo'::text)
-> Bitmap Index Scan on pg_proc_proname_args_nsp_index
Index Cond: ((proname >= '00'::text) AND (proname < '01'::text))
-(6 rows)
+ Index Bound Cond: ((proname >= '00'::text) AND (proname < '01'::text))
+(7 rows)
select proname from pg_proc where proname ilike '00%foo' order by 1;
proname
@@ -378,13 +387,14 @@ select * from btree_bpchar where f1 like 'foo%';
-- these do match the index:
explain (costs off)
select * from btree_bpchar where f1::bpchar like 'foo';
- QUERY PLAN
-----------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------
Bitmap Heap Scan on btree_bpchar
Filter: ((f1)::bpchar ~~ 'foo'::text)
-> Bitmap Index Scan on btree_bpchar_f1_idx
Index Cond: ((f1)::bpchar = 'foo'::bpchar)
-(4 rows)
+ Index Bound Cond: ((f1)::bpchar = 'foo'::bpchar)
+(5 rows)
select * from btree_bpchar where f1::bpchar like 'foo';
f1
@@ -394,13 +404,14 @@ select * from btree_bpchar where f1::bpchar like 'foo';
explain (costs off)
select * from btree_bpchar where f1::bpchar like 'foo%';
- QUERY PLAN
-------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
Bitmap Heap Scan on btree_bpchar
Filter: ((f1)::bpchar ~~ 'foo%'::text)
-> Bitmap Index Scan on btree_bpchar_f1_idx
Index Cond: (((f1)::bpchar >= 'foo'::bpchar) AND ((f1)::bpchar < 'fop'::bpchar))
-(4 rows)
+ Index Bound Cond: (((f1)::bpchar >= 'foo'::bpchar) AND ((f1)::bpchar < 'fop'::bpchar))
+(5 rows)
select * from btree_bpchar where f1::bpchar like 'foo%';
f1
diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out
index a13aafff0b..71804500ef 100644
--- a/src/test/regress/expected/cluster.out
+++ b/src/test/regress/expected/cluster.out
@@ -557,7 +557,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
---------------------------------------------------------------
Index Scan using clstr_expression_upper_b on clstr_expression
Index Cond: (upper(b) = 'PREFIX3'::text)
-(2 rows)
+ Index Bound Cond: (upper(b) = 'PREFIX3'::text)
+(3 rows)
SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
id | a | b
@@ -570,7 +571,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
---------------------------------------------------------------
Index Scan using clstr_expression_minus_a on clstr_expression
Index Cond: ((- a) = '-3'::integer)
-(2 rows)
+ Index Bound Cond: ((- a) = '-3'::integer)
+(3 rows)
SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
id | a | b
@@ -598,7 +600,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
---------------------------------------------------------------
Index Scan using clstr_expression_upper_b on clstr_expression
Index Cond: (upper(b) = 'PREFIX3'::text)
-(2 rows)
+ Index Bound Cond: (upper(b) = 'PREFIX3'::text)
+(3 rows)
SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
id | a | b
@@ -611,7 +614,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
---------------------------------------------------------------
Index Scan using clstr_expression_minus_a on clstr_expression
Index Cond: ((- a) = '-3'::integer)
-(2 rows)
+ Index Bound Cond: ((- a) = '-3'::integer)
+(3 rows)
SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
id | a | b
@@ -639,7 +643,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
---------------------------------------------------------------
Index Scan using clstr_expression_upper_b on clstr_expression
Index Cond: (upper(b) = 'PREFIX3'::text)
-(2 rows)
+ Index Bound Cond: (upper(b) = 'PREFIX3'::text)
+(3 rows)
SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
id | a | b
@@ -652,7 +657,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
---------------------------------------------------------------
Index Scan using clstr_expression_minus_a on clstr_expression
Index Cond: ((- a) = '-3'::integer)
-(2 rows)
+ Index Bound Cond: ((- a) = '-3'::integer)
+(3 rows)
SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
id | a | b
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index cf6eac5734..c45a38e26e 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1851,11 +1851,14 @@ SELECT * FROM tenk1
-> BitmapOr
-> Bitmap Index Scan on tenk1_thous_tenthous
Index Cond: ((thousand = 42) AND (tenthous = 1))
+ Index Bound Cond: ((thousand = 42) AND (tenthous = 1))
-> Bitmap Index Scan on tenk1_thous_tenthous
Index Cond: ((thousand = 42) AND (tenthous = 3))
+ Index Bound Cond: ((thousand = 42) AND (tenthous = 3))
-> Bitmap Index Scan on tenk1_thous_tenthous
Index Cond: ((thousand = 42) AND (tenthous = 42))
-(9 rows)
+ Index Bound Cond: ((thousand = 42) AND (tenthous = 42))
+(12 rows)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
@@ -1875,12 +1878,15 @@ SELECT count(*) FROM tenk1
-> BitmapAnd
-> Bitmap Index Scan on tenk1_hundred
Index Cond: (hundred = 42)
+ Index Bound Cond: (hundred = 42)
-> BitmapOr
-> Bitmap Index Scan on tenk1_thous_tenthous
Index Cond: (thousand = 42)
+ Index Bound Cond: (thousand = 42)
-> Bitmap Index Scan on tenk1_thous_tenthous
Index Cond: (thousand = 99)
-(11 rows)
+ Index Bound Cond: (thousand = 99)
+(14 rows)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
@@ -1906,7 +1912,8 @@ EXPLAIN (COSTS OFF)
Recheck Cond: ((f1 >= 'WA'::text) AND (f1 <= 'ZZZ'::text) AND (id < 1000) AND (f1 ~<~ 'YX'::text))
-> Bitmap Index Scan on dupindexcols_i
Index Cond: ((f1 >= 'WA'::text) AND (f1 <= 'ZZZ'::text) AND (id < 1000) AND (f1 ~<~ 'YX'::text))
-(5 rows)
+ Index Bound Cond: ((f1 >= 'WA'::text) AND (f1 <= 'ZZZ'::text))
+(6 rows)
SELECT count(*) FROM dupindexcols
WHERE f1 BETWEEN 'WA' AND 'ZZZ' and id < 1000 and f1 ~<~ 'YX';
@@ -1922,11 +1929,12 @@ explain (costs off)
SELECT unique1 FROM tenk1
WHERE unique1 IN (1,42,7)
ORDER BY unique1;
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------
Index Only Scan using tenk1_unique1 on tenk1
Index Cond: (unique1 = ANY ('{1,42,7}'::integer[]))
-(2 rows)
+ Index Bound Cond: (unique1 = ANY ('{1,42,7}'::integer[]))
+(3 rows)
SELECT unique1 FROM tenk1
WHERE unique1 IN (1,42,7)
@@ -1947,7 +1955,8 @@ ORDER BY thousand;
--------------------------------------------------------------------------------
Index Only Scan using tenk1_thous_tenthous on tenk1
Index Cond: ((thousand < 2) AND (tenthous = ANY ('{1001,3000}'::integer[])))
-(2 rows)
+ Index Bound Cond: (thousand < 2)
+(3 rows)
SELECT thousand, tenthous FROM tenk1
WHERE thousand < 2 AND tenthous IN (1001,3000)
@@ -1967,7 +1976,8 @@ ORDER BY thousand DESC, tenthous DESC;
--------------------------------------------------------------------------------
Index Only Scan Backward using tenk1_thous_tenthous on tenk1
Index Cond: ((thousand < 2) AND (tenthous = ANY ('{1001,3000}'::integer[])))
-(2 rows)
+ Index Bound Cond: (thousand < 2)
+(3 rows)
SELECT thousand, tenthous FROM tenk1
WHERE thousand < 2 AND tenthous IN (1001,3000)
@@ -1983,11 +1993,12 @@ ORDER BY thousand DESC, tenthous DESC;
--
explain (costs off)
SELECT unique1 FROM tenk1 WHERE unique1 IN (1, 42, 7) and unique1 = ANY('{7, 8, 9}');
- QUERY PLAN
-----------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
Index Only Scan using tenk1_unique1 on tenk1
Index Cond: ((unique1 = ANY ('{1,42,7}'::integer[])) AND (unique1 = ANY ('{7,8,9}'::integer[])))
-(2 rows)
+ Index Bound Cond: ((unique1 = ANY ('{1,42,7}'::integer[])) AND (unique1 = ANY ('{7,8,9}'::integer[])))
+(3 rows)
SELECT unique1 FROM tenk1 WHERE unique1 IN (1, 42, 7) and unique1 = ANY('{7, 8, 9}');
unique1
@@ -1997,11 +2008,12 @@ SELECT unique1 FROM tenk1 WHERE unique1 IN (1, 42, 7) and unique1 = ANY('{7, 8,
explain (costs off)
SELECT unique1 FROM tenk1 WHERE unique1 = ANY('{7, 14, 22}') and unique1 = ANY('{33, 44}'::bigint[]);
- QUERY PLAN
-----------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
Index Only Scan using tenk1_unique1 on tenk1
Index Cond: ((unique1 = ANY ('{7,14,22}'::integer[])) AND (unique1 = ANY ('{33,44}'::bigint[])))
-(2 rows)
+ Index Bound Cond: ((unique1 = ANY ('{7,14,22}'::integer[])) AND (unique1 = ANY ('{33,44}'::bigint[])))
+(3 rows)
SELECT unique1 FROM tenk1 WHERE unique1 = ANY('{7, 14, 22}') and unique1 = ANY('{33, 44}'::bigint[]);
unique1
@@ -2010,11 +2022,12 @@ SELECT unique1 FROM tenk1 WHERE unique1 = ANY('{7, 14, 22}') and unique1 = ANY('
explain (costs off)
SELECT unique1 FROM tenk1 WHERE unique1 IN (1, 42, 7) and unique1 = 1;
- QUERY PLAN
----------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------
Index Only Scan using tenk1_unique1 on tenk1
Index Cond: ((unique1 = ANY ('{1,42,7}'::integer[])) AND (unique1 = 1))
-(2 rows)
+ Index Bound Cond: ((unique1 = ANY ('{1,42,7}'::integer[])) AND (unique1 = 1))
+(3 rows)
SELECT unique1 FROM tenk1 WHERE unique1 IN (1, 42, 7) and unique1 = 1;
unique1
@@ -2024,11 +2037,12 @@ SELECT unique1 FROM tenk1 WHERE unique1 IN (1, 42, 7) and unique1 = 1;
explain (costs off)
SELECT unique1 FROM tenk1 WHERE unique1 IN (1, 42, 7) and unique1 = 12345;
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------
Index Only Scan using tenk1_unique1 on tenk1
Index Cond: ((unique1 = ANY ('{1,42,7}'::integer[])) AND (unique1 = 12345))
-(2 rows)
+ Index Bound Cond: ((unique1 = ANY ('{1,42,7}'::integer[])) AND (unique1 = 12345))
+(3 rows)
SELECT unique1 FROM tenk1 WHERE unique1 IN (1, 42, 7) and unique1 = 12345;
unique1
@@ -2037,11 +2051,12 @@ SELECT unique1 FROM tenk1 WHERE unique1 IN (1, 42, 7) and unique1 = 12345;
explain (costs off)
SELECT unique1 FROM tenk1 WHERE unique1 IN (1, 42, 7) and unique1 >= 42;
- QUERY PLAN
------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------
Index Only Scan using tenk1_unique1 on tenk1
Index Cond: ((unique1 = ANY ('{1,42,7}'::integer[])) AND (unique1 >= 42))
-(2 rows)
+ Index Bound Cond: ((unique1 = ANY ('{1,42,7}'::integer[])) AND (unique1 >= 42))
+(3 rows)
SELECT unique1 FROM tenk1 WHERE unique1 IN (1, 42, 7) and unique1 >= 42;
unique1
@@ -2051,11 +2066,12 @@ SELECT unique1 FROM tenk1 WHERE unique1 IN (1, 42, 7) and unique1 >= 42;
explain (costs off)
SELECT unique1 FROM tenk1 WHERE unique1 IN (1, 42, 7) and unique1 > 42;
- QUERY PLAN
-----------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------
Index Only Scan using tenk1_unique1 on tenk1
Index Cond: ((unique1 = ANY ('{1,42,7}'::integer[])) AND (unique1 > 42))
-(2 rows)
+ Index Bound Cond: ((unique1 = ANY ('{1,42,7}'::integer[])) AND (unique1 > 42))
+(3 rows)
SELECT unique1 FROM tenk1 WHERE unique1 IN (1, 42, 7) and unique1 > 42;
unique1
@@ -2064,11 +2080,12 @@ SELECT unique1 FROM tenk1 WHERE unique1 IN (1, 42, 7) and unique1 > 42;
explain (costs off)
SELECT unique1 FROM tenk1 WHERE unique1 > 9996 and unique1 >= 9999;
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------
Index Only Scan using tenk1_unique1 on tenk1
Index Cond: ((unique1 > 9996) AND (unique1 >= 9999))
-(2 rows)
+ Index Bound Cond: ((unique1 > 9996) AND (unique1 >= 9999))
+(3 rows)
SELECT unique1 FROM tenk1 WHERE unique1 > 9996 and unique1 >= 9999;
unique1
@@ -2078,11 +2095,12 @@ SELECT unique1 FROM tenk1 WHERE unique1 > 9996 and unique1 >= 9999;
explain (costs off)
SELECT unique1 FROM tenk1 WHERE unique1 < 3 and unique1 <= 3;
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------
Index Only Scan using tenk1_unique1 on tenk1
Index Cond: ((unique1 < 3) AND (unique1 <= 3))
-(2 rows)
+ Index Bound Cond: ((unique1 < 3) AND (unique1 <= 3))
+(3 rows)
SELECT unique1 FROM tenk1 WHERE unique1 < 3 and unique1 <= 3;
unique1
@@ -2094,11 +2112,12 @@ SELECT unique1 FROM tenk1 WHERE unique1 < 3 and unique1 <= 3;
explain (costs off)
SELECT unique1 FROM tenk1 WHERE unique1 < 3 and unique1 < (-1)::bigint;
- QUERY PLAN
-------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------
Index Only Scan using tenk1_unique1 on tenk1
Index Cond: ((unique1 < 3) AND (unique1 < '-1'::bigint))
-(2 rows)
+ Index Bound Cond: ((unique1 < 3) AND (unique1 < '-1'::bigint))
+(3 rows)
SELECT unique1 FROM tenk1 WHERE unique1 < 3 and unique1 < (-1)::bigint;
unique1
@@ -2107,11 +2126,12 @@ SELECT unique1 FROM tenk1 WHERE unique1 < 3 and unique1 < (-1)::bigint;
explain (costs off)
SELECT unique1 FROM tenk1 WHERE unique1 IN (1, 42, 7) and unique1 < (-1)::bigint;
- QUERY PLAN
---------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------
Index Only Scan using tenk1_unique1 on tenk1
Index Cond: ((unique1 = ANY ('{1,42,7}'::integer[])) AND (unique1 < '-1'::bigint))
-(2 rows)
+ Index Bound Cond: ((unique1 = ANY ('{1,42,7}'::integer[])) AND (unique1 < '-1'::bigint))
+(3 rows)
SELECT unique1 FROM tenk1 WHERE unique1 IN (1, 42, 7) and unique1 < (-1)::bigint;
unique1
@@ -2123,11 +2143,12 @@ SELECT unique1 FROM tenk1 WHERE unique1 IN (1, 42, 7) and unique1 < (-1)::bigint
--
explain (costs off)
select * from tenk1 where (thousand, tenthous) in ((1,1001), (null,null));
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------
Index Scan using tenk1_thous_tenthous on tenk1
Index Cond: ((thousand = 1) AND (tenthous = 1001))
-(2 rows)
+ Index Bound Cond: ((thousand = 1) AND (tenthous = 1001))
+(3 rows)
--
-- Check matching of boolean index columns to WHERE conditions and sort keys
@@ -2148,7 +2169,8 @@ explain (costs off)
Limit
-> Index Scan using boolindex_b_i_key on boolindex
Index Cond: (b = true)
-(3 rows)
+ Index Bound Cond: (b = true)
+(4 rows)
explain (costs off)
select * from boolindex where b = true order by i desc limit 10;
@@ -2157,7 +2179,8 @@ explain (costs off)
Limit
-> Index Scan Backward using boolindex_b_i_key on boolindex
Index Cond: (b = true)
-(3 rows)
+ Index Bound Cond: (b = true)
+(4 rows)
explain (costs off)
select * from boolindex where not b order by i limit 10;
@@ -2166,7 +2189,8 @@ explain (costs off)
Limit
-> Index Scan using boolindex_b_i_key on boolindex
Index Cond: (b = false)
-(3 rows)
+ Index Bound Cond: (b = false)
+(4 rows)
explain (costs off)
select * from boolindex where b is true order by i desc limit 10;
@@ -2175,7 +2199,8 @@ explain (costs off)
Limit
-> Index Scan Backward using boolindex_b_i_key on boolindex
Index Cond: (b = true)
-(3 rows)
+ Index Bound Cond: (b = true)
+(4 rows)
explain (costs off)
select * from boolindex where b is false order by i desc limit 10;
@@ -2184,7 +2209,8 @@ explain (costs off)
Limit
-> Index Scan Backward using boolindex_b_i_key on boolindex
Index Cond: (b = false)
-(3 rows)
+ Index Bound Cond: (b = false)
+(4 rows)
--
-- REINDEX (VERBOSE)
diff --git a/src/test/regress/expected/equivclass.out b/src/test/regress/expected/equivclass.out
index 126f7047fe..1deab4fe08 100644
--- a/src/test/regress/expected/equivclass.out
+++ b/src/test/regress/expected/equivclass.out
@@ -104,30 +104,33 @@ set enable_mergejoin = off;
--
explain (costs off)
select * from ec0 where ff = f1 and f1 = '42'::int8;
- QUERY PLAN
------------------------------------
+ QUERY PLAN
+-----------------------------------------
Index Scan using ec0_pkey on ec0
Index Cond: (ff = '42'::bigint)
+ Index Bound Cond: (ff = '42'::bigint)
Filter: (f1 = '42'::bigint)
-(3 rows)
+(4 rows)
explain (costs off)
select * from ec0 where ff = f1 and f1 = '42'::int8alias1;
- QUERY PLAN
----------------------------------------
+ QUERY PLAN
+---------------------------------------------
Index Scan using ec0_pkey on ec0
Index Cond: (ff = '42'::int8alias1)
+ Index Bound Cond: (ff = '42'::int8alias1)
Filter: (f1 = '42'::int8alias1)
-(3 rows)
+(4 rows)
explain (costs off)
select * from ec1 where ff = f1 and f1 = '42'::int8alias1;
- QUERY PLAN
----------------------------------------
+ QUERY PLAN
+---------------------------------------------
Index Scan using ec1_pkey on ec1
Index Cond: (ff = '42'::int8alias1)
+ Index Bound Cond: (ff = '42'::int8alias1)
Filter: (f1 = '42'::int8alias1)
-(3 rows)
+(4 rows)
explain (costs off)
select * from ec1 where ff = f1 and f1 = '42'::int8alias2;
@@ -139,48 +142,52 @@ explain (costs off)
explain (costs off)
select * from ec1, ec2 where ff = x1 and ff = '42'::int8;
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------
Nested Loop
Join Filter: (ec1.ff = ec2.x1)
-> Index Scan using ec1_pkey on ec1
Index Cond: ((ff = '42'::bigint) AND (ff = '42'::bigint))
+ Index Bound Cond: ((ff = '42'::bigint) AND (ff = '42'::bigint))
-> Seq Scan on ec2
-(5 rows)
+(6 rows)
explain (costs off)
select * from ec1, ec2 where ff = x1 and ff = '42'::int8alias1;
- QUERY PLAN
----------------------------------------------
+ QUERY PLAN
+---------------------------------------------------
Nested Loop
-> Index Scan using ec1_pkey on ec1
Index Cond: (ff = '42'::int8alias1)
+ Index Bound Cond: (ff = '42'::int8alias1)
-> Seq Scan on ec2
Filter: (x1 = '42'::int8alias1)
-(5 rows)
+(6 rows)
explain (costs off)
select * from ec1, ec2 where ff = x1 and '42'::int8 = x1;
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Nested Loop
Join Filter: (ec1.ff = ec2.x1)
-> Index Scan using ec1_pkey on ec1
Index Cond: (ff = '42'::bigint)
+ Index Bound Cond: (ff = '42'::bigint)
-> Seq Scan on ec2
Filter: ('42'::bigint = x1)
-(6 rows)
+(7 rows)
explain (costs off)
select * from ec1, ec2 where ff = x1 and x1 = '42'::int8alias1;
- QUERY PLAN
----------------------------------------------
+ QUERY PLAN
+---------------------------------------------------
Nested Loop
-> Index Scan using ec1_pkey on ec1
Index Cond: (ff = '42'::int8alias1)
+ Index Bound Cond: (ff = '42'::int8alias1)
-> Seq Scan on ec2
Filter: (x1 = '42'::int8alias1)
-(5 rows)
+(6 rows)
explain (costs off)
select * from ec1, ec2 where ff = x1 and x1 = '42'::int8alias2;
@@ -191,7 +198,8 @@ explain (costs off)
Filter: (x1 = '42'::int8alias2)
-> Index Scan using ec1_pkey on ec1
Index Cond: (ff = ec2.x1)
-(5 rows)
+ Index Bound Cond: (ff = ec2.x1)
+(6 rows)
create unique index ec1_expr1 on ec1((ff + 1));
create unique index ec1_expr2 on ec1((ff + 2 + 1));
@@ -206,19 +214,23 @@ explain (costs off)
union all
select ff + 4 as x from ec1) as ss1
where ss1.x = ec1.f1 and ec1.ff = 42::int8;
- QUERY PLAN
------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------
Nested Loop
-> Index Scan using ec1_pkey on ec1
Index Cond: (ff = '42'::bigint)
+ Index Bound Cond: (ff = '42'::bigint)
-> Append
-> Index Scan using ec1_expr2 on ec1 ec1_1
Index Cond: (((ff + 2) + 1) = ec1.f1)
+ Index Bound Cond: (((ff + 2) + 1) = ec1.f1)
-> Index Scan using ec1_expr3 on ec1 ec1_2
Index Cond: (((ff + 3) + 1) = ec1.f1)
+ Index Bound Cond: (((ff + 3) + 1) = ec1.f1)
-> Index Scan using ec1_expr4 on ec1 ec1_3
Index Cond: ((ff + 4) = ec1.f1)
-(10 rows)
+ Index Bound Cond: ((ff + 4) = ec1.f1)
+(14 rows)
explain (costs off)
select * from ec1,
@@ -229,21 +241,25 @@ explain (costs off)
union all
select ff + 4 as x from ec1) as ss1
where ss1.x = ec1.f1 and ec1.ff = 42::int8 and ec1.ff = ec1.f1;
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------
Nested Loop
Join Filter: ((((ec1_1.ff + 2) + 1)) = ec1.f1)
-> Index Scan using ec1_pkey on ec1
Index Cond: ((ff = '42'::bigint) AND (ff = '42'::bigint))
+ Index Bound Cond: ((ff = '42'::bigint) AND (ff = '42'::bigint))
Filter: (ff = f1)
-> Append
-> Index Scan using ec1_expr2 on ec1 ec1_1
Index Cond: (((ff + 2) + 1) = '42'::bigint)
+ Index Bound Cond: (((ff + 2) + 1) = '42'::bigint)
-> Index Scan using ec1_expr3 on ec1 ec1_2
Index Cond: (((ff + 3) + 1) = '42'::bigint)
+ Index Bound Cond: (((ff + 3) + 1) = '42'::bigint)
-> Index Scan using ec1_expr4 on ec1 ec1_3
Index Cond: ((ff + 4) = '42'::bigint)
-(12 rows)
+ Index Bound Cond: ((ff + 4) = '42'::bigint)
+(16 rows)
explain (costs off)
select * from ec1,
@@ -260,27 +276,34 @@ explain (costs off)
union all
select ff + 4 as x from ec1) as ss2
where ss1.x = ec1.f1 and ss1.x = ss2.x and ec1.ff = 42::int8;
- QUERY PLAN
----------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------
Nested Loop
-> Nested Loop
-> Index Scan using ec1_pkey on ec1
Index Cond: (ff = '42'::bigint)
+ Index Bound Cond: (ff = '42'::bigint)
-> Append
-> Index Scan using ec1_expr2 on ec1 ec1_1
Index Cond: (((ff + 2) + 1) = ec1.f1)
+ Index Bound Cond: (((ff + 2) + 1) = ec1.f1)
-> Index Scan using ec1_expr3 on ec1 ec1_2
Index Cond: (((ff + 3) + 1) = ec1.f1)
+ Index Bound Cond: (((ff + 3) + 1) = ec1.f1)
-> Index Scan using ec1_expr4 on ec1 ec1_3
Index Cond: ((ff + 4) = ec1.f1)
+ Index Bound Cond: ((ff + 4) = ec1.f1)
-> Append
-> Index Scan using ec1_expr2 on ec1 ec1_4
Index Cond: (((ff + 2) + 1) = (((ec1_1.ff + 2) + 1)))
+ Index Bound Cond: (((ff + 2) + 1) = (((ec1_1.ff + 2) + 1)))
-> Index Scan using ec1_expr3 on ec1 ec1_5
Index Cond: (((ff + 3) + 1) = (((ec1_1.ff + 2) + 1)))
+ Index Bound Cond: (((ff + 3) + 1) = (((ec1_1.ff + 2) + 1)))
-> Index Scan using ec1_expr4 on ec1 ec1_6
Index Cond: ((ff + 4) = (((ec1_1.ff + 2) + 1)))
-(18 rows)
+ Index Bound Cond: ((ff + 4) = (((ec1_1.ff + 2) + 1)))
+(25 rows)
-- let's try that as a mergejoin
set enable_mergejoin = on;
@@ -321,7 +344,8 @@ explain (costs off)
Sort Key: ec1.f1 USING <
-> Index Scan using ec1_pkey on ec1
Index Cond: (ff = '42'::bigint)
-(19 rows)
+ Index Bound Cond: (ff = '42'::bigint)
+(20 rows)
-- check partially indexed scan
set enable_nestloop = on;
@@ -336,19 +360,22 @@ explain (costs off)
union all
select ff + 4 as x from ec1) as ss1
where ss1.x = ec1.f1 and ec1.ff = 42::int8;
- QUERY PLAN
------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------
Nested Loop
-> Index Scan using ec1_pkey on ec1
Index Cond: (ff = '42'::bigint)
+ Index Bound Cond: (ff = '42'::bigint)
-> Append
-> Index Scan using ec1_expr2 on ec1 ec1_1
Index Cond: (((ff + 2) + 1) = ec1.f1)
+ Index Bound Cond: (((ff + 2) + 1) = ec1.f1)
-> Seq Scan on ec1 ec1_2
Filter: (((ff + 3) + 1) = ec1.f1)
-> Index Scan using ec1_expr4 on ec1 ec1_3
Index Cond: ((ff + 4) = ec1.f1)
-(10 rows)
+ Index Bound Cond: ((ff + 4) = ec1.f1)
+(13 rows)
-- let's try that as a mergejoin
set enable_mergejoin = on;
@@ -377,7 +404,8 @@ explain (costs off)
Sort Key: ec1.f1 USING <
-> Index Scan using ec1_pkey on ec1
Index Cond: (ff = '42'::bigint)
-(13 rows)
+ Index Bound Cond: (ff = '42'::bigint)
+(14 rows)
-- check effects of row-level security
set enable_nestloop = on;
@@ -391,14 +419,16 @@ grant select on ec1 to regress_user_ectest;
explain (costs off)
select * from ec0 a, ec1 b
where a.ff = b.ff and a.ff = 43::bigint::int8alias1;
- QUERY PLAN
----------------------------------------------
+ QUERY PLAN
+---------------------------------------------------
Nested Loop
-> Index Scan using ec0_pkey on ec0 a
Index Cond: (ff = '43'::int8alias1)
+ Index Bound Cond: (ff = '43'::int8alias1)
-> Index Scan using ec1_pkey on ec1 b
Index Cond: (ff = '43'::int8alias1)
-(5 rows)
+ Index Bound Cond: (ff = '43'::int8alias1)
+(7 rows)
set session authorization regress_user_ectest;
-- with RLS active, the non-leakproof a.ff = 43 clause is not treated
@@ -407,15 +437,17 @@ set session authorization regress_user_ectest;
explain (costs off)
select * from ec0 a, ec1 b
where a.ff = b.ff and a.ff = 43::bigint::int8alias1;
- QUERY PLAN
----------------------------------------------
+ QUERY PLAN
+---------------------------------------------------
Nested Loop
-> Index Scan using ec0_pkey on ec0 a
Index Cond: (ff = '43'::int8alias1)
+ Index Bound Cond: (ff = '43'::int8alias1)
-> Index Scan using ec1_pkey on ec1 b
Index Cond: (ff = a.ff)
+ Index Bound Cond: (ff = a.ff)
Filter: (f1 < '5'::int8alias1)
-(6 rows)
+(8 rows)
reset session authorization;
revoke select on ec0 from regress_user_ectest;
diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out
index 6585c6a69e..e0c91f27f2 100644
--- a/src/test/regress/expected/explain.out
+++ b/src/test/regress/expected/explain.out
@@ -334,7 +334,8 @@ select explain_filter('explain (generic_plan) select unique1 from tenk1 where th
Recheck Cond: (thousand = $N)
-> Bitmap Index Scan on tenk1_thous_tenthous (cost=N.N..N.N rows=N width=N)
Index Cond: (thousand = $N)
-(4 rows)
+ Index Bound Cond: (thousand = $N)
+(5 rows)
-- should fail
select explain_filter('explain (analyze, generic_plan) select unique1 from tenk1 where thousand = $1');
diff --git a/src/test/regress/expected/expressions.out b/src/test/regress/expected/expressions.out
index caeeb19674..d5ff3a2b31 100644
--- a/src/test/regress/expected/expressions.out
+++ b/src/test/regress/expected/expressions.out
@@ -186,7 +186,8 @@ explain (verbose, costs off) select * from bpchar_view
Index Scan using bpchar_tbl_f1_key on public.bpchar_tbl
Output: bpchar_tbl.f1, (bpchar_tbl.f1)::character(14), (bpchar_tbl.f1)::bpchar, bpchar_tbl.f2, (bpchar_tbl.f2)::character(14), bpchar_tbl.f2
Index Cond: ((bpchar_tbl.f1)::bpchar = 'foo'::bpchar)
-(3 rows)
+ Index Bound Cond: ((bpchar_tbl.f1)::bpchar = 'foo'::bpchar)
+(4 rows)
rollback;
--
diff --git a/src/test/regress/expected/fast_default.out b/src/test/regress/expected/fast_default.out
index 59365dad96..19e1af3226 100644
--- a/src/test/regress/expected/fast_default.out
+++ b/src/test/regress/expected/fast_default.out
@@ -441,8 +441,8 @@ DELETE FROM T WHERE pk BETWEEN 10 AND 20 RETURNING *;
EXPLAIN (VERBOSE TRUE, COSTS FALSE)
DELETE FROM T WHERE pk BETWEEN 10 AND 20 RETURNING *;
- QUERY PLAN
------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Delete on fast_default.t
Output: pk, c_bigint, c_text
-> Bitmap Heap Scan on fast_default.t
@@ -450,7 +450,8 @@ DELETE FROM T WHERE pk BETWEEN 10 AND 20 RETURNING *;
Recheck Cond: ((t.pk >= 10) AND (t.pk <= 20))
-> Bitmap Index Scan on t_pkey
Index Cond: ((t.pk >= 10) AND (t.pk <= 20))
-(7 rows)
+ Index Bound Cond: ((t.pk >= 10) AND (t.pk <= 20))
+(8 rows)
-- UPDATE
UPDATE T SET c_text = '"' || c_text || '"' WHERE pk < 10;
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 46764bd9e3..a1d8c74612 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1458,13 +1458,15 @@ explain (costs off) delete from t1 where a = 1;
-> Nested Loop
-> Index Scan using t1_pkey on t1
Index Cond: (a = 1)
+ Index Bound Cond: (a = 1)
-> Seq Scan on t2
Filter: (b = 1)
Delete on t1
-> Index Scan using t1_pkey on t1
Index Cond: (a = 1)
-(10 rows)
+ Index Bound Cond: (a = 1)
+(12 rows)
delete from t1 where a = 1;
-- Test a primary key with attributes located in later attnum positions
diff --git a/src/test/regress/expected/generated.out b/src/test/regress/expected/generated.out
index 44058db7c1..ea3452c946 100644
--- a/src/test/regress/expected/generated.out
+++ b/src/test/regress/expected/generated.out
@@ -640,7 +640,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 4;
---------------------------------------------
Index Scan using gtest22c_b_idx on gtest22c
Index Cond: (b = 4)
-(2 rows)
+ Index Bound Cond: (b = 4)
+(3 rows)
SELECT * FROM gtest22c WHERE b = 4;
a | b
@@ -653,7 +654,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6;
------------------------------------------------
Index Scan using gtest22c_expr_idx on gtest22c
Index Cond: ((b * 3) = 6)
-(2 rows)
+ Index Bound Cond: ((b * 3) = 6)
+(3 rows)
SELECT * FROM gtest22c WHERE b * 3 = 6;
a | b
@@ -666,7 +668,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
------------------------------------------------
Index Scan using gtest22c_pred_idx on gtest22c
Index Cond: (a = 1)
-(2 rows)
+ Index Bound Cond: (a = 1)
+(3 rows)
SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
a | b
@@ -681,7 +684,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 8;
---------------------------------------------
Index Scan using gtest22c_b_idx on gtest22c
Index Cond: (b = 8)
-(2 rows)
+ Index Bound Cond: (b = 8)
+(3 rows)
SELECT * FROM gtest22c WHERE b = 8;
a | b
@@ -694,7 +698,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 12;
------------------------------------------------
Index Scan using gtest22c_expr_idx on gtest22c
Index Cond: ((b * 3) = 12)
-(2 rows)
+ Index Bound Cond: ((b * 3) = 12)
+(3 rows)
SELECT * FROM gtest22c WHERE b * 3 = 12;
a | b
@@ -707,7 +712,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
------------------------------------------------
Index Scan using gtest22c_pred_idx on gtest22c
Index Cond: (a = 1)
-(2 rows)
+ Index Bound Cond: (a = 1)
+(3 rows)
SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
a | b
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index e1f0660810..b265502135 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -563,7 +563,8 @@ explain (costs off)
-> Limit
-> Index Only Scan using tenk1_unique1 on tenk1
Index Cond: (unique1 IS NOT NULL)
-(5 rows)
+ Index Bound Cond: (unique1 IS NOT NULL)
+(6 rows)
-- Views with GROUPING SET queries
CREATE VIEW gstest_view AS select a, b, grouping(a,b), sum(c), count(*), max(c)
diff --git a/src/test/regress/expected/incremental_sort.out b/src/test/regress/expected/incremental_sort.out
index 5fd54a10b1..2b2a9278fa 100644
--- a/src/test/regress/expected/incremental_sort.out
+++ b/src/test/regress/expected/incremental_sort.out
@@ -1615,7 +1615,8 @@ from tenk1 t, generate_series(1, 1000);
SubPlan 1
-> Index Only Scan using tenk1_unique1 on tenk1
Index Cond: (unique1 = t.unique1)
-(11 rows)
+ Index Bound Cond: (unique1 = t.unique1)
+(12 rows)
explain (costs off) select
unique1,
@@ -1634,7 +1635,8 @@ order by 1, 2;
SubPlan 1
-> Index Only Scan using tenk1_unique1 on tenk1
Index Cond: (unique1 = t.unique1)
-(10 rows)
+ Index Bound Cond: (unique1 = t.unique1)
+(11 rows)
-- Parallel sort but with expression not available until the upper rel.
explain (costs off) select distinct sub.unique1, stringu1 || random()::text
diff --git a/src/test/regress/expected/index_including.out b/src/test/regress/expected/index_including.out
index ea8b2454bf..04a4333da8 100644
--- a/src/test/regress/expected/index_including.out
+++ b/src/test/regress/expected/index_including.out
@@ -129,13 +129,14 @@ DETAIL: Failing row contains (1, null, 3, (4,4),(4,4)).
INSERT INTO tbl SELECT x, 2*x, NULL, NULL FROM generate_series(1,300) AS x;
explain (costs off)
select * from tbl where (c1,c2,c3) < (2,5,1);
- QUERY PLAN
-------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Bitmap Heap Scan on tbl
Filter: (ROW(c1, c2, c3) < ROW(2, 5, 1))
-> Bitmap Index Scan on covering
Index Cond: (ROW(c1, c2) <= ROW(2, 5))
-(4 rows)
+ Index Bound Cond: (ROW(c1, c2) <= ROW(2, 5))
+(5 rows)
select * from tbl where (c1,c2,c3) < (2,5,1);
c1 | c2 | c3 | c4
@@ -148,13 +149,14 @@ select * from tbl where (c1,c2,c3) < (2,5,1);
SET enable_seqscan = off;
explain (costs off)
select * from tbl where (c1,c2,c3) < (262,1,1) limit 1;
- QUERY PLAN
-----------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------
Limit
-> Index Only Scan using covering on tbl
Index Cond: (ROW(c1, c2) <= ROW(262, 1))
+ Index Bound Cond: (ROW(c1, c2) <= ROW(262, 1))
Filter: (ROW(c1, c2, c3) < ROW(262, 1, 1))
-(4 rows)
+(5 rows)
select * from tbl where (c1,c2,c3) < (262,1,1) limit 1;
c1 | c2 | c3 | c4
@@ -408,11 +410,12 @@ VACUUM nametbl;
SET enable_seqscan = 0;
-- Ensure we get an index only scan plan
EXPLAIN (COSTS OFF) SELECT c2, c1, c3 FROM nametbl WHERE c2 = 'two' AND c1 = 1;
- QUERY PLAN
-----------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------
Index Only Scan using nametbl_c1_c2_idx on nametbl
Index Cond: ((c2 = 'two'::name) AND (c1 = 1))
-(2 rows)
+ Index Bound Cond: ((c2 = 'two'::name) AND (c1 = 1))
+(3 rows)
-- Validate the results look sane
SELECT c2, c1, c3 FROM nametbl WHERE c2 = 'two' AND c1 = 1;
diff --git a/src/test/regress/expected/inet.out b/src/test/regress/expected/inet.out
index b6895d9ced..e45a1aeb3d 100644
--- a/src/test/regress/expected/inet.out
+++ b/src/test/regress/expected/inet.out
@@ -266,12 +266,13 @@ CREATE INDEX inet_idx1 ON inet_tbl(i);
SET enable_seqscan TO off;
EXPLAIN (COSTS OFF)
SELECT * FROM inet_tbl WHERE i<<'192.168.1.0/24'::cidr;
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------
Index Scan using inet_idx1 on inet_tbl
Index Cond: ((i > '192.168.1.0/24'::inet) AND (i <= '192.168.1.255'::inet))
+ Index Bound Cond: ((i > '192.168.1.0/24'::inet) AND (i <= '192.168.1.255'::inet))
Filter: (i << '192.168.1.0/24'::inet)
-(3 rows)
+(4 rows)
SELECT * FROM inet_tbl WHERE i<<'192.168.1.0/24'::cidr;
c | i
@@ -283,12 +284,13 @@ SELECT * FROM inet_tbl WHERE i<<'192.168.1.0/24'::cidr;
EXPLAIN (COSTS OFF)
SELECT * FROM inet_tbl WHERE i<<='192.168.1.0/24'::cidr;
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------
Index Scan using inet_idx1 on inet_tbl
Index Cond: ((i >= '192.168.1.0/24'::inet) AND (i <= '192.168.1.255'::inet))
+ Index Bound Cond: ((i >= '192.168.1.0/24'::inet) AND (i <= '192.168.1.255'::inet))
Filter: (i <<= '192.168.1.0/24'::inet)
-(3 rows)
+(4 rows)
SELECT * FROM inet_tbl WHERE i<<='192.168.1.0/24'::cidr;
c | i
@@ -303,12 +305,13 @@ SELECT * FROM inet_tbl WHERE i<<='192.168.1.0/24'::cidr;
EXPLAIN (COSTS OFF)
SELECT * FROM inet_tbl WHERE '192.168.1.0/24'::cidr >>= i;
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------
Index Scan using inet_idx1 on inet_tbl
Index Cond: ((i >= '192.168.1.0/24'::inet) AND (i <= '192.168.1.255'::inet))
+ Index Bound Cond: ((i >= '192.168.1.0/24'::inet) AND (i <= '192.168.1.255'::inet))
Filter: ('192.168.1.0/24'::inet >>= i)
-(3 rows)
+(4 rows)
SELECT * FROM inet_tbl WHERE '192.168.1.0/24'::cidr >>= i;
c | i
@@ -323,12 +326,13 @@ SELECT * FROM inet_tbl WHERE '192.168.1.0/24'::cidr >>= i;
EXPLAIN (COSTS OFF)
SELECT * FROM inet_tbl WHERE '192.168.1.0/24'::cidr >> i;
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------
Index Scan using inet_idx1 on inet_tbl
Index Cond: ((i > '192.168.1.0/24'::inet) AND (i <= '192.168.1.255'::inet))
+ Index Bound Cond: ((i > '192.168.1.0/24'::inet) AND (i <= '192.168.1.255'::inet))
Filter: ('192.168.1.0/24'::inet >> i)
-(3 rows)
+(4 rows)
SELECT * FROM inet_tbl WHERE '192.168.1.0/24'::cidr >> i;
c | i
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index ad73213414..6e7f9f1d2c 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -559,7 +559,8 @@ update some_tab set f3 = 11 where f1 = 12 and f2 = 13;
-> Result
-> Index Scan using some_tab_child_f1_f2_idx on some_tab_child some_tab_1
Index Cond: ((f1 = 12) AND (f2 = 13))
-(5 rows)
+ Index Bound Cond: ((f1 = 12) AND (f2 = 13))
+(6 rows)
update some_tab set f3 = 11 where f1 = 12 and f2 = 13;
NOTICE: updating some_tab
@@ -1491,11 +1492,14 @@ select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1;
-> Append
-> Index Scan using patest0i on patest0 patest0_1
Index Cond: (id = int4_tbl.f1)
+ Index Bound Cond: (id = int4_tbl.f1)
-> Index Scan using patest1i on patest1 patest0_2
Index Cond: (id = int4_tbl.f1)
+ Index Bound Cond: (id = int4_tbl.f1)
-> Index Scan using patest2i on patest2 patest0_3
Index Cond: (id = int4_tbl.f1)
-(10 rows)
+ Index Bound Cond: (id = int4_tbl.f1)
+(13 rows)
select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1;
id | x | f1
@@ -1516,11 +1520,13 @@ select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1;
-> Append
-> Index Scan using patest0i on patest0 patest0_1
Index Cond: (id = int4_tbl.f1)
+ Index Bound Cond: (id = int4_tbl.f1)
-> Index Scan using patest1i on patest1 patest0_2
Index Cond: (id = int4_tbl.f1)
+ Index Bound Cond: (id = int4_tbl.f1)
-> Seq Scan on patest2 patest0_3
Filter: (int4_tbl.f1 = id)
-(10 rows)
+(12 rows)
select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1;
id | x | f1
@@ -1654,9 +1660,11 @@ explain (verbose, costs off) select min(1-id) from matest0;
-> Index Scan using matest0i on public.matest0 matest0_1
Output: matest0_1.id, (1 - matest0_1.id)
Index Cond: ((1 - matest0_1.id) IS NOT NULL)
+ Index Bound Cond: ((1 - matest0_1.id) IS NOT NULL)
-> Index Scan using matest1i on public.matest1 matest0_2
Output: matest0_2.id, (1 - matest0_2.id)
Index Cond: ((1 - matest0_2.id) IS NOT NULL)
+ Index Bound Cond: ((1 - matest0_2.id) IS NOT NULL)
-> Sort
Output: matest0_3.id, ((1 - matest0_3.id))
Sort Key: ((1 - matest0_3.id))
@@ -1667,7 +1675,8 @@ explain (verbose, costs off) select min(1-id) from matest0;
-> Index Scan using matest3i on public.matest3 matest0_4
Output: matest0_4.id, (1 - matest0_4.id)
Index Cond: ((1 - matest0_4.id) IS NOT NULL)
-(25 rows)
+ Index Bound Cond: ((1 - matest0_4.id) IS NOT NULL)
+(28 rows)
select min(1-id) from matest0;
min
@@ -1731,11 +1740,12 @@ select * from matest0 where a < 100 order by a;
Sort Key: matest0.a
-> Index Only Scan using matest0_pkey on matest0 matest0_1
Index Cond: (a < 100)
+ Index Bound Cond: (a < 100)
-> Sort
Sort Key: matest0_2.a
-> Seq Scan on matest1 matest0_2
Filter: (a < 100)
-(8 rows)
+(9 rows)
drop table matest0 cascade;
NOTICE: drop cascades to table matest1
@@ -1806,9 +1816,11 @@ SELECT min(x) FROM
Sort Key: a.unique1
-> Index Only Scan using tenk1_unique1 on tenk1 a
Index Cond: (unique1 IS NOT NULL)
+ Index Bound Cond: (unique1 IS NOT NULL)
-> Index Only Scan using tenk1_unique2 on tenk1 b
Index Cond: (unique2 IS NOT NULL)
-(9 rows)
+ Index Bound Cond: (unique2 IS NOT NULL)
+(11 rows)
explain (costs off)
SELECT min(y) FROM
@@ -1824,9 +1836,11 @@ SELECT min(y) FROM
Sort Key: a.unique1
-> Index Only Scan using tenk1_unique1 on tenk1 a
Index Cond: (unique1 IS NOT NULL)
+ Index Bound Cond: (unique1 IS NOT NULL)
-> Index Only Scan using tenk1_unique2 on tenk1 b
Index Cond: (unique2 IS NOT NULL)
-(9 rows)
+ Index Bound Cond: (unique2 IS NOT NULL)
+(11 rows)
-- XXX planner doesn't recognize that index on unique2 is sufficiently sorted
explain (costs off)
@@ -2375,11 +2389,13 @@ explain (costs off) select min(a), max(a) from parted_minmax where b = '12345';
-> Limit
-> Index Only Scan using parted_minmax1i on parted_minmax1 parted_minmax
Index Cond: ((a IS NOT NULL) AND (b = '12345'::text))
+ Index Bound Cond: (a IS NOT NULL)
InitPlan 2
-> Limit
-> Index Only Scan Backward using parted_minmax1i on parted_minmax1 parted_minmax_1
Index Cond: ((a IS NOT NULL) AND (b = '12345'::text))
-(9 rows)
+ Index Bound Cond: (a IS NOT NULL)
+(11 rows)
select min(a), max(a) from parted_minmax where b = '12345';
min | max
@@ -2479,13 +2495,17 @@ explain (costs off) select * from mcrparted where a < 20 order by a, abs(b), c;
Append
-> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
Index Cond: (a < 20)
+ Index Bound Cond: (a < 20)
-> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
Index Cond: (a < 20)
+ Index Bound Cond: (a < 20)
-> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
Index Cond: (a < 20)
+ Index Bound Cond: (a < 20)
-> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
Index Cond: (a < 20)
-(9 rows)
+ Index Bound Cond: (a < 20)
+(13 rows)
set enable_bitmapscan to off;
set enable_sort to off;
@@ -2524,9 +2544,11 @@ explain (costs off) select * from mclparted where a in(3,4,5) order by a;
Sort Key: mclparted.a
-> Index Only Scan using mclparted3_5_a_idx on mclparted3_5 mclparted_1
Index Cond: (a = ANY ('{3,4,5}'::integer[]))
+ Index Bound Cond: (a = ANY ('{3,4,5}'::integer[]))
-> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_2
Index Cond: (a = ANY ('{3,4,5}'::integer[]))
-(6 rows)
+ Index Bound Cond: (a = ANY ('{3,4,5}'::integer[]))
+(8 rows)
-- Introduce a NULL and DEFAULT partition so we can test more complex cases
create table mclparted_null partition of mclparted for values in(null);
@@ -2538,11 +2560,14 @@ explain (costs off) select * from mclparted where a in(1,2,4) order by a;
Append
-> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
Index Cond: (a = ANY ('{1,2,4}'::integer[]))
+ Index Bound Cond: (a = ANY ('{1,2,4}'::integer[]))
-> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
Index Cond: (a = ANY ('{1,2,4}'::integer[]))
+ Index Bound Cond: (a = ANY ('{1,2,4}'::integer[]))
-> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_3
Index Cond: (a = ANY ('{1,2,4}'::integer[]))
-(7 rows)
+ Index Bound Cond: (a = ANY ('{1,2,4}'::integer[]))
+(10 rows)
explain (costs off) select * from mclparted where a in(1,2,4) or a is null order by a;
QUERY PLAN
@@ -2584,13 +2609,17 @@ explain (costs off) select * from mclparted where a in(0,1,2,4) order by a;
Sort Key: mclparted.a
-> Index Only Scan using mclparted_0_null_a_idx on mclparted_0_null mclparted_1
Index Cond: (a = ANY ('{0,1,2,4}'::integer[]))
+ Index Bound Cond: (a = ANY ('{0,1,2,4}'::integer[]))
-> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_2
Index Cond: (a = ANY ('{0,1,2,4}'::integer[]))
+ Index Bound Cond: (a = ANY ('{0,1,2,4}'::integer[]))
-> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_3
Index Cond: (a = ANY ('{0,1,2,4}'::integer[]))
+ Index Bound Cond: (a = ANY ('{0,1,2,4}'::integer[]))
-> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_4
Index Cond: (a = ANY ('{0,1,2,4}'::integer[]))
-(10 rows)
+ Index Bound Cond: (a = ANY ('{0,1,2,4}'::integer[]))
+(14 rows)
-- Ensure Append is used when the null partition is pruned
explain (costs off) select * from mclparted where a in(1,2,4) order by a;
@@ -2599,11 +2628,14 @@ explain (costs off) select * from mclparted where a in(1,2,4) order by a;
Append
-> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
Index Cond: (a = ANY ('{1,2,4}'::integer[]))
+ Index Bound Cond: (a = ANY ('{1,2,4}'::integer[]))
-> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
Index Cond: (a = ANY ('{1,2,4}'::integer[]))
+ Index Bound Cond: (a = ANY ('{1,2,4}'::integer[]))
-> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_3
Index Cond: (a = ANY ('{1,2,4}'::integer[]))
-(7 rows)
+ Index Bound Cond: (a = ANY ('{1,2,4}'::integer[]))
+(10 rows)
-- Ensure MergeAppend is used when the default partition is not pruned
explain (costs off) select * from mclparted where a in(1,2,4,100) order by a;
@@ -2613,13 +2645,17 @@ explain (costs off) select * from mclparted where a in(1,2,4,100) order by a;
Sort Key: mclparted.a
-> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
Index Cond: (a = ANY ('{1,2,4,100}'::integer[]))
+ Index Bound Cond: (a = ANY ('{1,2,4,100}'::integer[]))
-> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
Index Cond: (a = ANY ('{1,2,4,100}'::integer[]))
+ Index Bound Cond: (a = ANY ('{1,2,4,100}'::integer[]))
-> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_3
Index Cond: (a = ANY ('{1,2,4,100}'::integer[]))
+ Index Bound Cond: (a = ANY ('{1,2,4,100}'::integer[]))
-> Index Only Scan using mclparted_def_a_idx on mclparted_def mclparted_4
Index Cond: (a = ANY ('{1,2,4,100}'::integer[]))
-(10 rows)
+ Index Bound Cond: (a = ANY ('{1,2,4,100}'::integer[]))
+(14 rows)
drop table mclparted;
reset enable_sort;
@@ -2642,11 +2678,14 @@ explain (costs off) select * from mcrparted where a < 20 order by a, abs(b), c l
Filter: (a < 20)
-> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
Index Cond: (a < 20)
+ Index Bound Cond: (a < 20)
-> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
Index Cond: (a < 20)
+ Index Bound Cond: (a < 20)
-> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
Index Cond: (a < 20)
-(12 rows)
+ Index Bound Cond: (a < 20)
+(15 rows)
set enable_bitmapscan = 0;
-- Ensure Append node can be used when the partition is ordered by some
@@ -2657,9 +2696,11 @@ explain (costs off) select * from mcrparted where a = 10 order by a, abs(b), c;
Append
-> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_1
Index Cond: (a = 10)
+ Index Bound Cond: (a = 10)
-> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_2
Index Cond: (a = 10)
-(5 rows)
+ Index Bound Cond: (a = 10)
+(7 rows)
reset enable_bitmapscan;
drop table mcrparted;
@@ -2690,9 +2731,11 @@ explain (costs off) select * from bool_rp where b = true order by b,a;
Append
-> Index Only Scan using bool_rp_true_1k_b_a_idx on bool_rp_true_1k bool_rp_1
Index Cond: (b = true)
+ Index Bound Cond: (b = true)
-> Index Only Scan using bool_rp_true_2k_b_a_idx on bool_rp_true_2k bool_rp_2
Index Cond: (b = true)
-(5 rows)
+ Index Bound Cond: (b = true)
+(7 rows)
explain (costs off) select * from bool_rp where b = false order by b,a;
QUERY PLAN
@@ -2700,9 +2743,11 @@ explain (costs off) select * from bool_rp where b = false order by b,a;
Append
-> Index Only Scan using bool_rp_false_1k_b_a_idx on bool_rp_false_1k bool_rp_1
Index Cond: (b = false)
+ Index Bound Cond: (b = false)
-> Index Only Scan using bool_rp_false_2k_b_a_idx on bool_rp_false_2k bool_rp_2
Index Cond: (b = false)
-(5 rows)
+ Index Bound Cond: (b = false)
+(7 rows)
explain (costs off) select * from bool_rp where b = true order by a;
QUERY PLAN
@@ -2710,9 +2755,11 @@ explain (costs off) select * from bool_rp where b = true order by a;
Append
-> Index Only Scan using bool_rp_true_1k_b_a_idx on bool_rp_true_1k bool_rp_1
Index Cond: (b = true)
+ Index Bound Cond: (b = true)
-> Index Only Scan using bool_rp_true_2k_b_a_idx on bool_rp_true_2k bool_rp_2
Index Cond: (b = true)
-(5 rows)
+ Index Bound Cond: (b = true)
+(7 rows)
explain (costs off) select * from bool_rp where b = false order by a;
QUERY PLAN
@@ -2720,9 +2767,11 @@ explain (costs off) select * from bool_rp where b = false order by a;
Append
-> Index Only Scan using bool_rp_false_1k_b_a_idx on bool_rp_false_1k bool_rp_1
Index Cond: (b = false)
+ Index Bound Cond: (b = false)
-> Index Only Scan using bool_rp_false_2k_b_a_idx on bool_rp_false_2k bool_rp_2
Index Cond: (b = false)
-(5 rows)
+ Index Bound Cond: (b = false)
+(7 rows)
drop table bool_rp;
-- Ensure an Append scan is chosen when the partition order is a subset of
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 5cb9cde030..aa93f6dbbd 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -66,7 +66,8 @@ explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on con
SubPlan 1
-> Index Only Scan using both_index_expr_key on insertconflicttest ii
Index Cond: (key = excluded.key)
-(8 rows)
+ Index Bound Cond: (key = excluded.key)
+(9 rows)
-- Neither collation nor operator class specifications are required --
-- supplying them merely *limits* matches to indexes with matching opclasses
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 6b16c3a676..23f79f2974 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2335,8 +2335,8 @@ select a.f1, b.f1, t.thousand, t.tenthous from
(select sum(f1)+1 as f1 from int4_tbl i4a) a,
(select sum(f1) as f1 from int4_tbl i4b) b
where b.f1 = t.thousand and a.f1 = b.f1 and (a.f1+b.f1+999) = t.tenthous;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------
Nested Loop
-> Nested Loop
Join Filter: ((sum(i4b.f1)) = ((sum(i4a.f1) + 1)))
@@ -2346,7 +2346,8 @@ where b.f1 = t.thousand and a.f1 = b.f1 and (a.f1+b.f1+999) = t.tenthous;
-> Seq Scan on int4_tbl i4b
-> Index Only Scan using tenk1_thous_tenthous on tenk1 t
Index Cond: ((thousand = (sum(i4b.f1))) AND (tenthous = ((((sum(i4a.f1) + 1)) + (sum(i4b.f1))) + 999)))
-(9 rows)
+ Index Bound Cond: ((thousand = (sum(i4b.f1))) AND (tenthous = ((((sum(i4a.f1) + 1)) + (sum(i4b.f1))) + 999)))
+(10 rows)
select a.f1, b.f1, t.thousand, t.tenthous from
tenk1 t,
@@ -3256,7 +3257,8 @@ select a.idv, b.idv from tidv a, tidv b where a.idv = b.idv;
-> Seq Scan on tidv a
-> Index Only Scan using tidv_idv_idx on tidv b
Index Cond: (idv = a.idv)
-(4 rows)
+ Index Bound Cond: (idv = a.idv)
+(5 rows)
rollback;
--
@@ -3406,8 +3408,8 @@ SELECT qq, unique1
( SELECT COALESCE(q2, -1) AS qq FROM int8_tbl b ) AS ss2
USING (qq)
INNER JOIN tenk1 c ON qq = unique2;
- QUERY PLAN
----------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------
Nested Loop
-> Hash Full Join
Hash Cond: ((COALESCE(a.q1, '0'::bigint)) = (COALESCE(b.q2, '-1'::bigint)))
@@ -3416,7 +3418,8 @@ SELECT qq, unique1
-> Seq Scan on int8_tbl b
-> Index Scan using tenk1_unique2 on tenk1 c
Index Cond: (unique2 = COALESCE((COALESCE(a.q1, '0'::bigint)), (COALESCE(b.q2, '-1'::bigint))))
-(8 rows)
+ Index Bound Cond: (unique2 = COALESCE((COALESCE(a.q1, '0'::bigint)), (COALESCE(b.q2, '-1'::bigint))))
+(9 rows)
SELECT qq, unique1
FROM
@@ -3474,18 +3477,21 @@ from nt3 as nt3
) as ss2
on ss2.id = nt3.nt2_id
where nt3.id = 1 and ss2.b3;
- QUERY PLAN
------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------
Nested Loop
-> Nested Loop
-> Index Scan using nt3_pkey on nt3
Index Cond: (id = 1)
+ Index Bound Cond: (id = 1)
-> Index Scan using nt2_pkey on nt2
Index Cond: (id = nt3.nt2_id)
+ Index Bound Cond: (id = nt3.nt2_id)
-> Index Only Scan using nt1_pkey on nt1
Index Cond: (id = nt2.nt1_id)
+ Index Bound Cond: (id = nt2.nt1_id)
Filter: (nt2.b1 AND (id IS NOT NULL))
-(9 rows)
+(12 rows)
select nt3.id
from nt3 as nt3
@@ -3687,19 +3693,21 @@ where q1 = thousand or q2 = thousand;
-> BitmapOr
-> Bitmap Index Scan on tenk1_thous_tenthous
Index Cond: (thousand = q1.q1)
+ Index Bound Cond: (thousand = q1.q1)
-> Bitmap Index Scan on tenk1_thous_tenthous
Index Cond: (thousand = q2.q2)
+ Index Bound Cond: (thousand = q2.q2)
-> Hash
-> Seq Scan on int4_tbl
-(15 rows)
+(17 rows)
explain (costs off)
select * from
tenk1 join int4_tbl on f1 = twothousand,
q1, q2
where thousand = (q1 + q2);
- QUERY PLAN
---------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------
Hash Join
Hash Cond: (tenk1.twothousand = int4_tbl.f1)
-> Nested Loop
@@ -3710,9 +3718,10 @@ where thousand = (q1 + q2);
Recheck Cond: (thousand = (q1.q1 + q2.q2))
-> Bitmap Index Scan on tenk1_thous_tenthous
Index Cond: (thousand = (q1.q1 + q2.q2))
+ Index Bound Cond: (thousand = (q1.q1 + q2.q2))
-> Hash
-> Seq Scan on int4_tbl
-(12 rows)
+(13 rows)
--
-- test ability to generate a suitable plan for a star-schema query
@@ -3721,8 +3730,8 @@ explain (costs off)
select * from
tenk1, int8_tbl a, int8_tbl b
where thousand = a.q1 and tenthous = b.q1 and a.q2 = 1 and b.q2 = 2;
- QUERY PLAN
----------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------
Nested Loop
-> Seq Scan on int8_tbl b
Filter: (q2 = 2)
@@ -3731,7 +3740,8 @@ where thousand = a.q1 and tenthous = b.q1 and a.q2 = 1 and b.q2 = 2;
Filter: (q2 = 1)
-> Index Scan using tenk1_thous_tenthous on tenk1
Index Cond: ((thousand = a.q1) AND (tenthous = b.q1))
-(8 rows)
+ Index Bound Cond: ((thousand = a.q1) AND (tenthous = b.q1))
+(9 rows)
--
-- test a corner case in which we shouldn't apply the star-schema optimization
@@ -3749,8 +3759,8 @@ select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from
left join tenk1 t2
on (subq1.y1 = t2.unique1)
where t1.unique2 < 42 and t1.stringu1 > t2.stringu2;
- QUERY PLAN
------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------
Nested Loop
-> Nested Loop
Join Filter: (t1.stringu1 > t2.stringu2)
@@ -3760,11 +3770,13 @@ where t1.unique2 < 42 and t1.stringu1 > t2.stringu2;
-> Seq Scan on onerow onerow_1
-> Index Scan using tenk1_unique2 on tenk1 t1
Index Cond: ((unique2 = (11)) AND (unique2 < 42))
+ Index Bound Cond: ((unique2 = (11)) AND (unique2 < 42))
-> Index Scan using tenk1_unique1 on tenk1 t2
Index Cond: (unique1 = (3))
+ Index Bound Cond: (unique1 = (3))
-> Seq Scan on int4_tbl i1
Filter: (f1 = 0)
-(13 rows)
+(15 rows)
select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from
tenk1 t1
@@ -3817,8 +3829,8 @@ select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from
left join tenk1 t2
on (subq1.y1 = t2.unique1)
where t1.unique2 < 42 and t1.stringu1 > t2.stringu2;
- QUERY PLAN
------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------
Nested Loop
Join Filter: (t1.stringu1 > t2.stringu2)
-> Nested Loop
@@ -3826,9 +3838,11 @@ where t1.unique2 < 42 and t1.stringu1 > t2.stringu2;
Filter: (f1 = 0)
-> Index Scan using tenk1_unique2 on tenk1 t1
Index Cond: ((unique2 = (11)) AND (unique2 < 42))
+ Index Bound Cond: ((unique2 = (11)) AND (unique2 < 42))
-> Index Scan using tenk1_unique1 on tenk1 t2
Index Cond: (unique1 = (3))
-(9 rows)
+ Index Bound Cond: (unique1 = (3))
+(11 rows)
select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from
tenk1 t1
@@ -3978,7 +3992,8 @@ where x = unique1;
----------------------------------------------
Index Only Scan using tenk1_unique1 on tenk1
Index Cond: (unique1 = 1)
-(2 rows)
+ Index Bound Cond: (unique1 = 1)
+(3 rows)
explain (verbose, costs off)
select unique1, x.*
@@ -3993,7 +4008,8 @@ where x = unique1;
-> Index Only Scan using tenk1_unique1 on public.tenk1
Output: tenk1.unique1
Index Cond: (tenk1.unique1 = (1))
-(7 rows)
+ Index Bound Cond: (tenk1.unique1 = (1))
+(8 rows)
explain (costs off)
select unique1 from tenk1, f_immutable_int4(1) x where x = unique1;
@@ -4001,7 +4017,8 @@ select unique1 from tenk1, f_immutable_int4(1) x where x = unique1;
----------------------------------------------
Index Only Scan using tenk1_unique1 on tenk1
Index Cond: (unique1 = 1)
-(2 rows)
+ Index Bound Cond: (unique1 = 1)
+(3 rows)
explain (costs off)
select unique1 from tenk1, lateral f_immutable_int4(1) x where x = unique1;
@@ -4009,7 +4026,8 @@ select unique1 from tenk1, lateral f_immutable_int4(1) x where x = unique1;
----------------------------------------------
Index Only Scan using tenk1_unique1 on tenk1
Index Cond: (unique1 = 1)
-(2 rows)
+ Index Bound Cond: (unique1 = 1)
+(3 rows)
explain (costs off)
select unique1 from tenk1, lateral f_immutable_int4(1) x where x in (select 17);
@@ -4025,7 +4043,8 @@ select unique1, x from tenk1 join f_immutable_int4(1) x on unique1 = x;
----------------------------------------------
Index Only Scan using tenk1_unique1 on tenk1
Index Cond: (unique1 = 1)
-(2 rows)
+ Index Bound Cond: (unique1 = 1)
+(3 rows)
explain (costs off)
select unique1, x from tenk1 left join f_immutable_int4(1) x on unique1 = x;
@@ -4046,7 +4065,8 @@ select unique1, x from tenk1 right join f_immutable_int4(1) x on unique1 = x;
-> Result
-> Index Only Scan using tenk1_unique1 on tenk1
Index Cond: (unique1 = 1)
-(4 rows)
+ Index Bound Cond: (unique1 = 1)
+(5 rows)
explain (costs off)
select unique1, x from tenk1 full join f_immutable_int4(1) x on unique1 = x;
@@ -4082,18 +4102,20 @@ from nt3 as nt3
) as ss2
on ss2.id = nt3.nt2_id
where nt3.id = 1 and ss2.b3;
- QUERY PLAN
-----------------------------------------------
+ QUERY PLAN
+---------------------------------------------------
Nested Loop Left Join
Filter: ((nt2.b1 OR ((0) = 42)))
-> Index Scan using nt3_pkey on nt3
Index Cond: (id = 1)
+ Index Bound Cond: (id = 1)
-> Nested Loop Left Join
Join Filter: (0 = nt2.nt1_id)
-> Index Scan using nt2_pkey on nt2
Index Cond: (id = nt3.nt2_id)
+ Index Bound Cond: (id = nt3.nt2_id)
-> Result
-(9 rows)
+(11 rows)
drop function f_immutable_int4(int);
-- test inlining when function returns composite
@@ -4175,17 +4197,21 @@ select * from tenk1 a join tenk1 b on
-> BitmapOr
-> Bitmap Index Scan on tenk1_unique1
Index Cond: (unique1 = 2)
+ Index Bound Cond: (unique1 = 2)
-> Bitmap Index Scan on tenk1_hundred
Index Cond: (hundred = 4)
+ Index Bound Cond: (hundred = 4)
-> Materialize
-> Bitmap Heap Scan on tenk1 a
Recheck Cond: ((unique1 = 1) OR (unique2 = 3))
-> BitmapOr
-> Bitmap Index Scan on tenk1_unique1
Index Cond: (unique1 = 1)
+ Index Bound Cond: (unique1 = 1)
-> Bitmap Index Scan on tenk1_unique2
Index Cond: (unique2 = 3)
-(17 rows)
+ Index Bound Cond: (unique2 = 3)
+(21 rows)
explain (costs off)
select * from tenk1 a join tenk1 b on
@@ -4202,9 +4228,11 @@ select * from tenk1 a join tenk1 b on
-> BitmapOr
-> Bitmap Index Scan on tenk1_unique1
Index Cond: (unique1 = 1)
+ Index Bound Cond: (unique1 = 1)
-> Bitmap Index Scan on tenk1_unique2
Index Cond: (unique2 = 3)
-(12 rows)
+ Index Bound Cond: (unique2 = 3)
+(14 rows)
explain (costs off)
select * from tenk1 a join tenk1 b on
@@ -4219,19 +4247,24 @@ select * from tenk1 a join tenk1 b on
-> BitmapOr
-> Bitmap Index Scan on tenk1_unique1
Index Cond: (unique1 = 2)
+ Index Bound Cond: (unique1 = 2)
-> Bitmap Index Scan on tenk1_hundred
Index Cond: (hundred = 4)
+ Index Bound Cond: (hundred = 4)
-> Materialize
-> Bitmap Heap Scan on tenk1 a
Recheck Cond: ((unique1 = 1) OR (unique2 = 3) OR (unique2 = 7))
-> BitmapOr
-> Bitmap Index Scan on tenk1_unique1
Index Cond: (unique1 = 1)
+ Index Bound Cond: (unique1 = 1)
-> Bitmap Index Scan on tenk1_unique2
Index Cond: (unique2 = 3)
+ Index Bound Cond: (unique2 = 3)
-> Bitmap Index Scan on tenk1_unique2
Index Cond: (unique2 = 7)
-(19 rows)
+ Index Bound Cond: (unique2 = 7)
+(24 rows)
--
-- test placement of movable quals in a parameterized join tree
@@ -4241,48 +4274,54 @@ select * from tenk1 t1 left join
(tenk1 t2 join tenk1 t3 on t2.thousand = t3.unique2)
on t1.hundred = t2.hundred and t1.ten = t3.ten
where t1.unique1 = 1;
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------
Nested Loop Left Join
-> Index Scan using tenk1_unique1 on tenk1 t1
Index Cond: (unique1 = 1)
+ Index Bound Cond: (unique1 = 1)
-> Nested Loop
Join Filter: (t1.ten = t3.ten)
-> Bitmap Heap Scan on tenk1 t2
Recheck Cond: (t1.hundred = hundred)
-> Bitmap Index Scan on tenk1_hundred
Index Cond: (hundred = t1.hundred)
+ Index Bound Cond: (hundred = t1.hundred)
-> Index Scan using tenk1_unique2 on tenk1 t3
Index Cond: (unique2 = t2.thousand)
-(11 rows)
+ Index Bound Cond: (unique2 = t2.thousand)
+(14 rows)
explain (costs off)
select * from tenk1 t1 left join
(tenk1 t2 join tenk1 t3 on t2.thousand = t3.unique2)
on t1.hundred = t2.hundred and t1.ten + t2.ten = t3.ten
where t1.unique1 = 1;
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------
Nested Loop Left Join
-> Index Scan using tenk1_unique1 on tenk1 t1
Index Cond: (unique1 = 1)
+ Index Bound Cond: (unique1 = 1)
-> Nested Loop
Join Filter: ((t1.ten + t2.ten) = t3.ten)
-> Bitmap Heap Scan on tenk1 t2
Recheck Cond: (t1.hundred = hundred)
-> Bitmap Index Scan on tenk1_hundred
Index Cond: (hundred = t1.hundred)
+ Index Bound Cond: (hundred = t1.hundred)
-> Index Scan using tenk1_unique2 on tenk1 t3
Index Cond: (unique2 = t2.thousand)
-(11 rows)
+ Index Bound Cond: (unique2 = t2.thousand)
+(14 rows)
explain (costs off)
select count(*) from
tenk1 a join tenk1 b on a.unique1 = b.unique2
left join tenk1 c on a.unique2 = b.unique1 and c.thousand = a.thousand
join int4_tbl on b.thousand = f1;
- QUERY PLAN
--------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------
Aggregate
-> Nested Loop Left Join
Join Filter: (a.unique2 = b.unique1)
@@ -4293,11 +4332,14 @@ select count(*) from
Recheck Cond: (thousand = int4_tbl.f1)
-> Bitmap Index Scan on tenk1_thous_tenthous
Index Cond: (thousand = int4_tbl.f1)
+ Index Bound Cond: (thousand = int4_tbl.f1)
-> Index Scan using tenk1_unique1 on tenk1 a
Index Cond: (unique1 = b.unique2)
+ Index Bound Cond: (unique1 = b.unique2)
-> Index Only Scan using tenk1_thous_tenthous on tenk1 c
Index Cond: (thousand = a.thousand)
-(14 rows)
+ Index Bound Cond: (thousand = a.thousand)
+(17 rows)
select count(*) from
tenk1 a join tenk1 b on a.unique1 = b.unique2
@@ -4315,8 +4357,8 @@ select b.unique1 from
join int4_tbl i1 on b.thousand = f1
right join int4_tbl i2 on i2.f1 = b.tenthous
order by 1;
- QUERY PLAN
------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
Sort
Sort Key: b.unique1
-> Nested Loop Left Join
@@ -4328,11 +4370,14 @@ select b.unique1 from
-> Seq Scan on int4_tbl i1
-> Index Scan using tenk1_thous_tenthous on tenk1 b
Index Cond: ((thousand = i1.f1) AND (tenthous = i2.f1))
+ Index Bound Cond: ((thousand = i1.f1) AND (tenthous = i2.f1))
-> Index Scan using tenk1_unique1 on tenk1 a
Index Cond: (unique1 = b.unique2)
+ Index Bound Cond: (unique1 = b.unique2)
-> Index Only Scan using tenk1_thous_tenthous on tenk1 c
Index Cond: (thousand = a.thousand)
-(15 rows)
+ Index Bound Cond: (thousand = a.thousand)
+(18 rows)
select b.unique1 from
tenk1 a join tenk1 b on a.unique1 = b.unique2
@@ -4364,7 +4409,8 @@ order by fault;
-> Seq Scan on int8_tbl
-> Index Scan using tenk1_unique2 on tenk1
Index Cond: (unique2 = int8_tbl.q2)
-(5 rows)
+ Index Bound Cond: (unique2 = int8_tbl.q2)
+(6 rows)
select * from
(
@@ -4419,7 +4465,8 @@ select q1, unique2, thousand, hundred
-> Seq Scan on int8_tbl a
-> Index Scan using tenk1_unique2 on tenk1 b
Index Cond: (unique2 = a.q1)
-(5 rows)
+ Index Bound Cond: (unique2 = a.q1)
+(6 rows)
select q1, unique2, thousand, hundred
from int8_tbl a left join tenk1 b on q1 = unique2
@@ -4439,7 +4486,8 @@ select f1, unique2, case when unique2 is null then f1 else 0 end
-> Seq Scan on int4_tbl a
-> Index Only Scan using tenk1_unique2 on tenk1 b
Index Cond: (unique2 = a.f1)
-(5 rows)
+ Index Bound Cond: (unique2 = a.f1)
+(6 rows)
select f1, unique2, case when unique2 is null then f1 else 0 end
from int4_tbl a left join tenk1 b on f1 = unique2
@@ -4463,13 +4511,16 @@ select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand)
Filter: (COALESCE(b.twothousand, a.twothousand) = 44)
-> Index Scan using tenk1_unique2 on tenk1 a
Index Cond: (unique2 < 10)
+ Index Bound Cond: (unique2 < 10)
-> Bitmap Heap Scan on tenk1 b
Recheck Cond: (thousand = a.unique1)
-> Bitmap Index Scan on tenk1_thous_tenthous
Index Cond: (thousand = a.unique1)
+ Index Bound Cond: (thousand = a.unique1)
-> Index Scan using tenk1_unique2 on tenk1 c
Index Cond: (unique2 = 44)
-(11 rows)
+ Index Bound Cond: (unique2 = 44)
+(14 rows)
select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand)
from tenk1 a left join tenk1 b on b.thousand = a.unique1 left join tenk1 c on c.unique2 = coalesce(b.twothousand, a.twothousand)
@@ -4542,7 +4593,8 @@ using (join_key);
-> Index Only Scan using tenk1_unique2 on public.tenk1 i2
Output: i2.unique2
Index Cond: (i2.unique2 = i1.f1)
-(14 rows)
+ Index Bound Cond: (i2.unique2 = i1.f1)
+(15 rows)
select foo1.join_key as foo1_id, foo3.join_key AS foo3_id, bug_field from
(values (0),(1)) foo1(join_key)
@@ -5210,7 +5262,8 @@ explain (costs off)
Filter: (f1 = 0)
-> Index Scan using tenk1_unique2 on tenk1 b
Index Cond: (unique2 = 0)
-(5 rows)
+ Index Bound Cond: (unique2 = 0)
+(6 rows)
explain (costs off)
select * from tenk1 a full join tenk1 b using(unique2) where unique2 = 42;
@@ -5219,9 +5272,11 @@ explain (costs off)
Merge Full Join
-> Index Scan using tenk1_unique2 on tenk1 a
Index Cond: (unique2 = 42)
+ Index Bound Cond: (unique2 = 42)
-> Index Scan using tenk1_unique2 on tenk1 b
Index Cond: (unique2 = 42)
-(5 rows)
+ Index Bound Cond: (unique2 = 42)
+(7 rows)
--
-- test that quals attached to an outer join have correct semantics,
@@ -5311,9 +5366,11 @@ select a.unique1, b.unique2
Nested Loop Left Join
-> Index Only Scan using onek_unique1 on onek a
Index Cond: (unique1 = 42)
+ Index Bound Cond: (unique1 = 42)
-> Index Only Scan using onek_unique2 on onek b
Index Cond: (unique2 = 42)
-(5 rows)
+ Index Bound Cond: (unique2 = 42)
+(7 rows)
select a.unique1, b.unique2
from onek a full join onek b on a.unique1 = b.unique2
@@ -5332,9 +5389,11 @@ select a.unique1, b.unique2
Nested Loop Left Join
-> Index Only Scan using onek_unique2 on onek b
Index Cond: (unique2 = 43)
+ Index Bound Cond: (unique2 = 43)
-> Index Only Scan using onek_unique1 on onek a
Index Cond: (unique1 = 43)
-(5 rows)
+ Index Bound Cond: (unique1 = 43)
+(7 rows)
select a.unique1, b.unique2
from onek a full join onek b on a.unique1 = b.unique2
@@ -5353,9 +5412,11 @@ select a.unique1, b.unique2
Nested Loop
-> Index Only Scan using onek_unique1 on onek a
Index Cond: (unique1 = 42)
+ Index Bound Cond: (unique1 = 42)
-> Index Only Scan using onek_unique2 on onek b
Index Cond: (unique2 = 42)
-(5 rows)
+ Index Bound Cond: (unique2 = 42)
+(7 rows)
select a.unique1, b.unique2
from onek a full join onek b on a.unique1 = b.unique2
@@ -5697,8 +5758,9 @@ select 1 from a t1
Join Filter: (t2.id = 1)
-> Index Only Scan using a_pkey on a t2
Index Cond: (id = 1)
+ Index Bound Cond: (id = 1)
-> Seq Scan on a t3
-(8 rows)
+(9 rows)
-- check join removal works when uniqueness of the join condition is enforced
-- by a UNION
@@ -5888,7 +5950,8 @@ SELECT q2 FROM
-> Seq Scan on int8_tbl
-> Index Scan using innertab_pkey on innertab
Index Cond: (id = int8_tbl.q2)
-(5 rows)
+ Index Bound Cond: (id = int8_tbl.q2)
+(6 rows)
-- join removal bug #17773: otherwise-removable PHV appears in a qual condition
EXPLAIN (VERBOSE, COSTS OFF)
@@ -5966,10 +6029,11 @@ where ss.stringu2 !~* ss.case1;
-> Seq Scan on int4_tbl i4
-> Index Scan using tenk1_unique2 on tenk1 t1
Index Cond: (unique2 = i4.f1)
+ Index Bound Cond: (unique2 = i4.f1)
Filter: (stringu2 !~* CASE ten WHEN 0 THEN 'doh!'::text ELSE NULL::text END)
-> Materialize
-> Seq Scan on text_tbl t0
-(9 rows)
+(10 rows)
select t0.*
from
@@ -6099,7 +6163,8 @@ where q2 = 456;
-> Index Only Scan using tenk1_unique2 on public.tenk1 t
Output: t.unique2
Index Cond: (t.unique2 = ((i4.f1 + 1)))
-(13 rows)
+ Index Bound Cond: (t.unique2 = ((i4.f1 + 1)))
+(14 rows)
select i8.*, ss.v, t.unique2
from int8_tbl i8
@@ -6228,7 +6293,8 @@ explain (costs off)
-> Seq Scan on int4_tbl b
-> Index Scan using tenk1_unique1 on tenk1 a
Index Cond: (unique1 = b.f1)
-(4 rows)
+ Index Bound Cond: (unique1 = b.f1)
+(5 rows)
select unique2, x.*
from int4_tbl x, lateral (select unique2 from tenk1 where f1 = unique1) ss;
@@ -6246,7 +6312,8 @@ explain (costs off)
-> Seq Scan on int4_tbl x
-> Index Scan using tenk1_unique1 on tenk1
Index Cond: (unique1 = x.f1)
-(4 rows)
+ Index Bound Cond: (unique1 = x.f1)
+(5 rows)
explain (costs off)
select unique2, x.*
@@ -6257,7 +6324,8 @@ explain (costs off)
-> Seq Scan on int4_tbl x
-> Index Scan using tenk1_unique1 on tenk1
Index Cond: (unique1 = x.f1)
-(4 rows)
+ Index Bound Cond: (unique1 = x.f1)
+(5 rows)
select unique2, x.*
from int4_tbl x left join lateral (select unique1, unique2 from tenk1 where f1 = unique1) ss on true;
@@ -6279,7 +6347,8 @@ explain (costs off)
-> Seq Scan on int4_tbl x
-> Index Scan using tenk1_unique1 on tenk1
Index Cond: (unique1 = x.f1)
-(4 rows)
+ Index Bound Cond: (unique1 = x.f1)
+(5 rows)
-- check scoping of lateral versus parent references
-- the first of these should return int8_tbl.q2, the second int8_tbl.q1
@@ -6400,8 +6469,8 @@ select count(*) from tenk1 a,
explain (costs off)
select count(*) from tenk1 a,
tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x;
- QUERY PLAN
-------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------
Aggregate
-> Nested Loop
-> Nested Loop
@@ -6412,7 +6481,8 @@ explain (costs off)
Cache Mode: logical
-> Index Only Scan using tenk1_unique2 on tenk1 b
Index Cond: (unique2 = "*VALUES*".column1)
-(10 rows)
+ Index Bound Cond: (unique2 = "*VALUES*".column1)
+(11 rows)
select count(*) from tenk1 a,
tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x;
@@ -7191,8 +7261,8 @@ select * from
lateral (select f1 from int4_tbl
where f1 = any (select unique1 from tenk1
where unique2 = v.x offset 0)) ss;
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------
Nested Loop
Output: "*VALUES*".column1, "*VALUES*".column2, int4_tbl.f1
-> Values Scan on "*VALUES*"
@@ -7207,7 +7277,8 @@ select * from
-> Index Scan using tenk1_unique2 on public.tenk1
Output: tenk1.unique1
Index Cond: (tenk1.unique2 = "*VALUES*".column2)
-(14 rows)
+ Index Bound Cond: (tenk1.unique2 = "*VALUES*".column2)
+(15 rows)
select * from
(values (0,9998), (1,1000)) v(id,x),
@@ -7439,7 +7510,8 @@ select * from fkest f1
Filter: (x100 = 2)
-> Index Scan using fkest_x_x10_x100_idx on fkest f3
Index Cond: (x = f1.x)
-(10 rows)
+ Index Bound Cond: (x = f1.x)
+(11 rows)
alter table fkest add constraint fk
foreign key (x, x10b, x100) references fkest (x, x10, x100);
@@ -7490,13 +7562,17 @@ where f.c = 1;
-> Nested Loop Left Join
-> Index Scan using fkest_c_key on fkest f
Index Cond: (c = 1)
+ Index Bound Cond: (c = 1)
-> Index Only Scan using fkest1_pkey on fkest1 f1
Index Cond: ((a = f.a) AND (b = f.b))
+ Index Bound Cond: ((a = f.a) AND (b = f.b))
-> Index Only Scan using fkest1_pkey on fkest1 f2
Index Cond: ((a = f.a) AND (b = f.b))
+ Index Bound Cond: ((a = f.a) AND (b = f.b))
-> Index Only Scan using fkest1_pkey on fkest1 f3
Index Cond: ((a = f.a) AND (b = f.b))
-(11 rows)
+ Index Bound Cond: ((a = f.a) AND (b = f.b))
+(15 rows)
rollback;
--
@@ -7819,15 +7895,16 @@ where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1;
explain (costs off) select * from j1
inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2
where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1 and j2.id1 = any (array[1]);
- QUERY PLAN
-----------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------
Merge Join
Merge Cond: (j1.id1 = j2.id1)
Join Filter: (j2.id2 = j1.id2)
-> Index Scan using j1_id1_idx on j1
-> Index Scan using j2_id1_idx on j2
Index Cond: (id1 = ANY ('{1}'::integer[]))
-(6 rows)
+ Index Bound Cond: (id1 = ANY ('{1}'::integer[]))
+(7 rows)
select * from j1
inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2
@@ -7842,15 +7919,16 @@ where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1 and j2.id1 = any (array[1]);
explain (costs off) select * from j1
inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2
where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1 and j2.id1 >= any (array[1,5]);
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------
Merge Join
Merge Cond: (j1.id1 = j2.id1)
Join Filter: (j2.id2 = j1.id2)
-> Index Scan using j1_id1_idx on j1
-> Index Scan using j2_id1_idx on j2
Index Cond: (id1 >= ANY ('{1,5}'::integer[]))
-(6 rows)
+ Index Bound Cond: (id1 >= ANY ('{1,5}'::integer[]))
+(7 rows)
select * from j1
inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2
@@ -7891,10 +7969,12 @@ where exists (select 1 from tenk1 t3
-> Index Only Scan using onek_unique1 on public.onek t1
Output: t1.unique1
Index Cond: (t1.unique1 < 1)
+ Index Bound Cond: (t1.unique1 < 1)
-> Index Only Scan using tenk1_hundred on public.tenk1 t2
Output: t2.hundred
Index Cond: (t2.hundred = t3.tenthous)
-(18 rows)
+ Index Bound Cond: (t2.hundred = t3.tenthous)
+(20 rows)
-- ... unless it actually is unique
create table j3 as select unique1, tenthous from onek;
@@ -7915,13 +7995,16 @@ where exists (select 1 from j3
-> Index Only Scan using onek_unique1 on public.onek t1
Output: t1.unique1
Index Cond: (t1.unique1 < 1)
+ Index Bound Cond: (t1.unique1 < 1)
-> Index Only Scan using j3_unique1_tenthous_idx on public.j3
Output: j3.unique1, j3.tenthous
Index Cond: (j3.unique1 = t1.unique1)
+ Index Bound Cond: (j3.unique1 = t1.unique1)
-> Index Only Scan using tenk1_hundred on public.tenk1 t2
Output: t2.hundred
Index Cond: (t2.hundred = j3.tenthous)
-(13 rows)
+ Index Bound Cond: (t2.hundred = j3.tenthous)
+(16 rows)
drop table j3;
-- Exercise the "skip fetch" Bitmap Heap Scan optimization when candidate
@@ -7951,7 +8034,8 @@ SELECT t1.a FROM skip_fetch t1 LEFT JOIN skip_fetch t2 ON t2.a = 1 WHERE t2.a IS
Recheck Cond: (a = 1)
-> Bitmap Index Scan on skip_fetch_a_idx
Index Cond: (a = 1)
-(7 rows)
+ Index Bound Cond: (a = 1)
+(8 rows)
SELECT t1.a FROM skip_fetch t1 LEFT JOIN skip_fetch t2 ON t2.a = 1 WHERE t2.a IS NULL;
a
diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out
index 0fd103c06b..400433e933 100644
--- a/src/test/regress/expected/memoize.out
+++ b/src/test/regress/expected/memoize.out
@@ -48,8 +48,9 @@ WHERE t2.unique1 < 1000;', false);
Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N)
Index Cond: (unique1 = t2.twenty)
+ Index Bound Cond: (unique1 = t2.twenty)
Heap Fetches: N
-(12 rows)
+(13 rows)
-- And check we get the expected results.
SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1
@@ -79,8 +80,9 @@ WHERE t1.unique1 < 1000;', false);
Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1 loops=N)
Index Cond: (unique1 = t1.twenty)
+ Index Bound Cond: (unique1 = t1.twenty)
Heap Fetches: N
-(12 rows)
+(13 rows)
-- And check we get the expected results.
SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
@@ -106,6 +108,7 @@ WHERE t1.unique1 < 10;', false);
-> Nested Loop Left Join (actual rows=20 loops=N)
-> Index Scan using tenk1_unique1 on tenk1 t1 (actual rows=10 loops=N)
Index Cond: (unique1 < 10)
+ Index Bound Cond: (unique1 < 10)
-> Memoize (actual rows=2 loops=N)
Cache Key: t1.two
Cache Mode: binary
@@ -115,7 +118,8 @@ WHERE t1.unique1 < 10;', false);
Rows Removed by Filter: 2
-> Index Scan using tenk1_unique1 on tenk1 t2_1 (actual rows=4 loops=N)
Index Cond: (unique1 < 4)
-(13 rows)
+ Index Bound Cond: (unique1 < 4)
+(15 rows)
-- And check we get the expected results.
SELECT COUNT(*),AVG(t2.t1two) FROM tenk1 t1 LEFT JOIN
@@ -154,9 +158,10 @@ ON t1.x = t2.t::numeric AND t1.t::numeric = t2.x;', false);
Hits: 20 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using expr_key_idx_x_t on expr_key t2 (actual rows=2 loops=N)
Index Cond: (x = (t1.t)::numeric)
+ Index Bound Cond: (x = (t1.t)::numeric)
Filter: (t1.x = (t)::numeric)
Heap Fetches: N
-(10 rows)
+(11 rows)
DROP TABLE expr_key;
-- Reduce work_mem and hash_mem_multiplier so that we see some cache evictions
@@ -182,8 +187,9 @@ WHERE t2.unique1 < 1200;', true);
Hits: N Misses: N Evictions: N Overflows: 0 Memory Usage: NkB
-> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N)
Index Cond: (unique1 = t2.thousand)
+ Index Bound Cond: (unique1 = t2.thousand)
Heap Fetches: N
-(12 rows)
+(13 rows)
CREATE TABLE flt (f float);
CREATE INDEX flt_f_idx ON flt (f);
@@ -204,8 +210,9 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f = f2.f;', false);
Hits: 1 Misses: 1 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using flt_f_idx on flt f2 (actual rows=2 loops=N)
Index Cond: (f = f1.f)
+ Index Bound Cond: (f = f1.f)
Heap Fetches: N
-(10 rows)
+(11 rows)
-- Ensure memoize operates in binary mode
SELECT explain_memoize('
@@ -221,8 +228,9 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f >= f2.f;', false);
Hits: 0 Misses: 2 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using flt_f_idx on flt f2 (actual rows=2 loops=N)
Index Cond: (f <= f1.f)
+ Index Bound Cond: (f <= f1.f)
Heap Fetches: N
-(10 rows)
+(11 rows)
DROP TABLE flt;
-- Exercise Memoize in binary mode with a large fixed width type and a
@@ -247,7 +255,8 @@ SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.n >= s2.n;', false);
Hits: 3 Misses: 3 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Scan using strtest_n_idx on strtest s2 (actual rows=4 loops=N)
Index Cond: (n <= s1.n)
-(8 rows)
+ Index Bound Cond: (n <= s1.n)
+(9 rows)
-- Ensure we get 3 hits and 3 misses
SELECT explain_memoize('
@@ -262,7 +271,8 @@ SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.t >= s2.t;', false);
Hits: 3 Misses: 3 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Scan using strtest_t_idx on strtest s2 (actual rows=4 loops=N)
Index Cond: (t <= s1.t)
-(8 rows)
+ Index Bound Cond: (t <= s1.t)
+(9 rows)
DROP TABLE strtest;
-- Ensure memoize works with partitionwise join
@@ -289,6 +299,7 @@ SELECT * FROM prt t1 INNER JOIN prt t2 ON t1.a = t2.a;', false);
Hits: 3 Misses: 1 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using iprt_p1_a on prt_p1 t2_1 (actual rows=4 loops=N)
Index Cond: (a = t1_1.a)
+ Index Bound Cond: (a = t1_1.a)
Heap Fetches: N
-> Nested Loop (actual rows=16 loops=N)
-> Index Only Scan using iprt_p2_a on prt_p2 t1_2 (actual rows=4 loops=N)
@@ -299,8 +310,9 @@ SELECT * FROM prt t1 INNER JOIN prt t2 ON t1.a = t2.a;', false);
Hits: 3 Misses: 1 Evictions: Zero Overflows: 0 Memory Usage: NkB
-> Index Only Scan using iprt_p2_a on prt_p2 t2_2 (actual rows=4 loops=N)
Index Cond: (a = t1_2.a)
+ Index Bound Cond: (a = t1_2.a)
Heap Fetches: N
-(21 rows)
+(23 rows)
-- Ensure memoize works with parameterized union-all Append path
SET enable_partitionwise_join TO off;
@@ -320,11 +332,13 @@ ON t1.a = t2.a;', false);
-> Append (actual rows=4 loops=N)
-> Index Only Scan using iprt_p1_a on prt_p1 (actual rows=4 loops=N)
Index Cond: (a = t1.a)
+ Index Bound Cond: (a = t1.a)
Heap Fetches: N
-> Index Only Scan using iprt_p2_a on prt_p2 (actual rows=0 loops=N)
Index Cond: (a = t1.a)
+ Index Bound Cond: (a = t1.a)
Heap Fetches: N
-(14 rows)
+(16 rows)
DROP TABLE prt;
RESET enable_partitionwise_join;
@@ -342,6 +356,7 @@ WHERE unique1 < 3
----------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 t0
Index Cond: (unique1 < 3)
+ Index Bound Cond: (unique1 < 3)
Filter: EXISTS(SubPlan 1)
SubPlan 1
-> Nested Loop
@@ -352,8 +367,9 @@ WHERE unique1 < 3
Cache Mode: logical
-> Index Scan using tenk1_unique1 on tenk1 t1
Index Cond: (unique1 = t2.hundred)
+ Index Bound Cond: (unique1 = t2.hundred)
Filter: (t0.ten = twenty)
-(13 rows)
+(15 rows)
-- Ensure the above query returns the correct result
SELECT unique1 FROM tenk1 t0
@@ -394,12 +410,14 @@ WHERE t1.unique1 < 1000;
Recheck Cond: (unique1 < 1000)
-> Bitmap Index Scan on tenk1_unique1
Index Cond: (unique1 < 1000)
+ Index Bound Cond: (unique1 < 1000)
-> Memoize
Cache Key: t1.twenty
Cache Mode: logical
-> Index Only Scan using tenk1_unique1 on tenk1 t2
Index Cond: (unique1 = t1.twenty)
-(14 rows)
+ Index Bound Cond: (unique1 = t1.twenty)
+(16 rows)
-- And ensure the parallel plan gives us the correct results.
SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index d94056862a..8186f5667e 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -566,7 +566,8 @@ WHERE my_int_eq(a.unique2, 42);
Filter: my_int_eq(unique2, 42)
-> Index Scan using tenk1_unique1 on tenk1 b
Index Cond: (unique1 = a.unique1)
-(5 rows)
+ Index Bound Cond: (unique1 = a.unique1)
+(6 rows)
-- Also test non-default rowcount estimate
CREATE FUNCTION my_gen_series(int, int) RETURNS SETOF integer
@@ -592,7 +593,8 @@ SELECT * FROM tenk1 a JOIN my_gen_series(1,10) g ON a.unique1 = g;
-> Function Scan on my_gen_series g
-> Index Scan using tenk1_unique1 on tenk1 a
Index Cond: (unique1 = g.g)
-(4 rows)
+ Index Bound Cond: (unique1 = g.g)
+(5 rows)
-- Test functions for control data
SELECT count(*) > 0 AS ok FROM pg_control_checkpoint();
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 6d07f86b9b..6f8218fbec 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -173,7 +173,8 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b WHE
Filter: (a = 0)
-> Index Scan using iprt1_p3_a on prt1_p3 t1_3
Index Cond: (a = t2_3.b)
-(20 rows)
+ Index Bound Cond: (a = t2_3.b)
+(21 rows)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b WHERE t2.a = 0 ORDER BY t1.a, t2.b;
a | c | b | c
@@ -413,25 +414,31 @@ SELECT * FROM prt1 t1 LEFT JOIN LATERAL
-> Nested Loop
-> Index Only Scan using iprt1_p1_a on prt1_p1 t2_1
Index Cond: (a = t1_1.a)
+ Index Bound Cond: (a = t1_1.a)
-> Index Scan using iprt2_p1_b on prt2_p1 t3_1
Index Cond: (b = t2_1.a)
+ Index Bound Cond: (b = t2_1.a)
-> Nested Loop Left Join
-> Seq Scan on prt1_p2 t1_2
Filter: (b = 0)
-> Nested Loop
-> Index Only Scan using iprt1_p2_a on prt1_p2 t2_2
Index Cond: (a = t1_2.a)
+ Index Bound Cond: (a = t1_2.a)
-> Index Scan using iprt2_p2_b on prt2_p2 t3_2
Index Cond: (b = t2_2.a)
+ Index Bound Cond: (b = t2_2.a)
-> Nested Loop Left Join
-> Seq Scan on prt1_p3 t1_3
Filter: (b = 0)
-> Nested Loop
-> Index Only Scan using iprt1_p3_a on prt1_p3 t2_3
Index Cond: (a = t1_3.a)
+ Index Bound Cond: (a = t1_3.a)
-> Index Scan using iprt2_p3_b on prt2_p3 t3_3
Index Cond: (b = t2_3.a)
-(27 rows)
+ Index Bound Cond: (b = t2_3.a)
+(33 rows)
SELECT * FROM prt1 t1 LEFT JOIN LATERAL
(SELECT t2.a AS t2a, t3.a AS t3a, least(t1.a,t2.a,t3.b) FROM prt1 t2 JOIN prt2 t3 ON (t2.a = t3.b)) ss
@@ -543,18 +550,21 @@ SELECT count(*) FROM prt1 t1 LEFT JOIN LATERAL
-> Seq Scan on prt1_p1 t1_1
-> Index Scan using iprt2_p1_b on prt2_p1 t2_1
Index Cond: (b = t1_1.a)
+ Index Bound Cond: (b = t1_1.a)
Filter: (t1_1.b = a)
-> Nested Loop
-> Seq Scan on prt1_p2 t1_2
-> Index Scan using iprt2_p2_b on prt2_p2 t2_2
Index Cond: (b = t1_2.a)
+ Index Bound Cond: (b = t1_2.a)
Filter: (t1_2.b = a)
-> Nested Loop
-> Seq Scan on prt1_p3 t1_3
-> Index Scan using iprt2_p3_b on prt2_p3 t2_3
Index Cond: (b = t1_3.a)
+ Index Bound Cond: (b = t1_3.a)
Filter: (t1_3.b = a)
-(17 rows)
+(20 rows)
SELECT count(*) FROM prt1 t1 LEFT JOIN LATERAL
(SELECT t1.b AS t1b, t2.* FROM prt2 t2) s
@@ -576,18 +586,21 @@ SELECT count(*) FROM prt1 t1 LEFT JOIN LATERAL
-> Seq Scan on prt1_p1 t1_1
-> Index Only Scan using iprt2_p1_b on prt2_p1 t2_1
Index Cond: (b = t1_1.a)
+ Index Bound Cond: (b = t1_1.a)
Filter: (b = t1_1.b)
-> Nested Loop
-> Seq Scan on prt1_p2 t1_2
-> Index Only Scan using iprt2_p2_b on prt2_p2 t2_2
Index Cond: (b = t1_2.a)
+ Index Bound Cond: (b = t1_2.a)
Filter: (b = t1_2.b)
-> Nested Loop
-> Seq Scan on prt1_p3 t1_3
-> Index Only Scan using iprt2_p3_b on prt2_p3 t2_3
Index Cond: (b = t1_3.a)
+ Index Bound Cond: (b = t1_3.a)
Filter: (b = t1_3.b)
-(17 rows)
+(20 rows)
SELECT count(*) FROM prt1 t1 LEFT JOIN LATERAL
(SELECT t1.b AS t1b, t2.* FROM prt2 t2) s
@@ -748,6 +761,7 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t
Filter: (b = 0)
-> Index Scan using iprt1_e_p1_ab2 on prt1_e_p1 t3_1
Index Cond: (((a + b) / 2) = t2_1.b)
+ Index Bound Cond: (((a + b) / 2) = t2_1.b)
-> Nested Loop
Join Filter: (t1_2.a = ((t3_2.a + t3_2.b) / 2))
-> Hash Join
@@ -758,6 +772,7 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t
Filter: (b = 0)
-> Index Scan using iprt1_e_p2_ab2 on prt1_e_p2 t3_2
Index Cond: (((a + b) / 2) = t2_2.b)
+ Index Bound Cond: (((a + b) / 2) = t2_2.b)
-> Nested Loop
Join Filter: (t1_3.a = ((t3_3.a + t3_3.b) / 2))
-> Hash Join
@@ -768,7 +783,8 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t
Filter: (b = 0)
-> Index Scan using iprt1_e_p3_ab2 on prt1_e_p3 t3_3
Index Cond: (((a + b) / 2) = t2_3.b)
-(33 rows)
+ Index Bound Cond: (((a + b) / 2) = t2_3.b)
+(36 rows)
SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t3 WHERE t1.a = t2.b AND t1.a = (t3.a + t3.b)/2 AND t1.b = 0 ORDER BY t1.a, t2.b;
a | c | b | c | ?column? | c
@@ -851,6 +867,7 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2
Filter: (c = 0)
-> Index Scan using iprt2_p1_b on prt2_p1 t2_1
Index Cond: (b = t1_1.a)
+ Index Bound Cond: (b = t1_1.a)
-> Nested Loop Left Join
-> Hash Right Join
Hash Cond: (t1_2.a = ((t3_2.a + t3_2.b) / 2))
@@ -860,6 +877,7 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2
Filter: (c = 0)
-> Index Scan using iprt2_p2_b on prt2_p2 t2_2
Index Cond: (b = t1_2.a)
+ Index Bound Cond: (b = t1_2.a)
-> Nested Loop Left Join
-> Hash Right Join
Hash Cond: (t1_3.a = ((t3_3.a + t3_3.b) / 2))
@@ -869,7 +887,8 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2
Filter: (c = 0)
-> Index Scan using iprt2_p3_b on prt2_p3 t2_3
Index Cond: (b = t1_3.a)
-(30 rows)
+ Index Bound Cond: (b = t1_3.a)
+(33 rows)
SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
a | c | b | c | ?column? | c
@@ -1075,6 +1094,7 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1, prt1_e t2 WHER
Filter: (a = 0)
-> Index Scan using iprt1_p1_a on prt1_p1 t1_2
Index Cond: (a = ((t2_1.a + t2_1.b) / 2))
+ Index Bound Cond: (a = ((t2_1.a + t2_1.b) / 2))
Filter: (b = 0)
-> Nested Loop
Join Filter: (t1_3.a = t1_6.b)
@@ -1088,6 +1108,7 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1, prt1_e t2 WHER
Filter: (a = 0)
-> Index Scan using iprt1_p2_a on prt1_p2 t1_3
Index Cond: (a = ((t2_2.a + t2_2.b) / 2))
+ Index Bound Cond: (a = ((t2_2.a + t2_2.b) / 2))
Filter: (b = 0)
-> Nested Loop
Join Filter: (t1_4.a = t1_7.b)
@@ -1098,10 +1119,12 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1, prt1_e t2 WHER
Filter: (a = 0)
-> Index Scan using iprt1_e_p3_ab2 on prt1_e_p3 t2_3
Index Cond: (((a + b) / 2) = t1_7.b)
+ Index Bound Cond: (((a + b) / 2) = t1_7.b)
-> Index Scan using iprt1_p3_a on prt1_p3 t1_4
Index Cond: (a = ((t2_3.a + t2_3.b) / 2))
+ Index Bound Cond: (a = ((t2_3.a + t2_3.b) / 2))
Filter: (b = 0)
-(41 rows)
+(45 rows)
SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1, prt1_e t2 WHERE t1.a = 0 AND t1.b = (t2.a + t2.b)/2) AND t1.b = 0 ORDER BY t1.a;
a | b | c
@@ -1130,6 +1153,7 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (
Filter: (c = 0)
-> Index Scan using iprt1_p1_a on prt1_p1 t1_3
Index Cond: (a = t1_6.b)
+ Index Bound Cond: (a = t1_6.b)
Filter: (b = 0)
-> Nested Loop
-> HashAggregate
@@ -1142,6 +1166,7 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (
Filter: (c = 0)
-> Index Scan using iprt1_p2_a on prt1_p2 t1_4
Index Cond: (a = t1_7.b)
+ Index Bound Cond: (a = t1_7.b)
Filter: (b = 0)
-> Nested Loop
-> HashAggregate
@@ -1154,8 +1179,9 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (
Filter: (c = 0)
-> Index Scan using iprt1_p3_a on prt1_p3 t1_5
Index Cond: (a = t1_8.b)
+ Index Bound Cond: (a = t1_8.b)
Filter: (b = 0)
-(39 rows)
+(42 rows)
SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) AND t1.b = 0 ORDER BY t1.a;
a | b | c
@@ -2249,11 +2275,14 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1 LEFT JOIN prt2 t2 ON (t1.a < t2.b);
-> Append
-> Index Scan using iprt2_p1_b on prt2_p1 t2_1
Index Cond: (b > t1.a)
+ Index Bound Cond: (b > t1.a)
-> Index Scan using iprt2_p2_b on prt2_p2 t2_2
Index Cond: (b > t1.a)
+ Index Bound Cond: (b > t1.a)
-> Index Scan using iprt2_p3_b on prt2_p3 t2_3
Index Cond: (b > t1.a)
-(12 rows)
+ Index Bound Cond: (b > t1.a)
+(15 rows)
-- equi-join with join condition on partial keys does not qualify for
-- partitionwise join
@@ -2409,8 +2438,10 @@ where not exists (select 1 from prtx2
-> BitmapAnd
-> Bitmap Index Scan on prtx2_1_b_idx
Index Cond: (b = prtx1_1.b)
+ Index Bound Cond: (b = prtx1_1.b)
-> Bitmap Index Scan on prtx2_1_c_idx
Index Cond: (c = 123)
+ Index Bound Cond: (c = 123)
-> Nested Loop Anti Join
-> Seq Scan on prtx1_2
Filter: ((a < 20) AND (c = 120))
@@ -2420,9 +2451,11 @@ where not exists (select 1 from prtx2
-> BitmapAnd
-> Bitmap Index Scan on prtx2_2_b_idx
Index Cond: (b = prtx1_2.b)
+ Index Bound Cond: (b = prtx1_2.b)
-> Bitmap Index Scan on prtx2_2_c_idx
Index Cond: (c = 123)
-(23 rows)
+ Index Bound Cond: (c = 123)
+(27 rows)
select * from prtx1
where not exists (select 1 from prtx2
@@ -2438,8 +2471,8 @@ select * from prtx1
where not exists (select 1 from prtx2
where prtx2.a=prtx1.a and (prtx2.b=prtx1.b+1 or prtx2.c=99))
and a<20 and c=91;
- QUERY PLAN
------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------
Append
-> Nested Loop Anti Join
-> Seq Scan on prtx1_1
@@ -2450,8 +2483,10 @@ where not exists (select 1 from prtx2
-> BitmapOr
-> Bitmap Index Scan on prtx2_1_b_idx
Index Cond: (b = (prtx1_1.b + 1))
+ Index Bound Cond: (b = (prtx1_1.b + 1))
-> Bitmap Index Scan on prtx2_1_c_idx
Index Cond: (c = 99)
+ Index Bound Cond: (c = 99)
-> Nested Loop Anti Join
-> Seq Scan on prtx1_2
Filter: ((a < 20) AND (c = 91))
@@ -2461,9 +2496,11 @@ where not exists (select 1 from prtx2
-> BitmapOr
-> Bitmap Index Scan on prtx2_2_b_idx
Index Cond: (b = (prtx1_2.b + 1))
+ Index Bound Cond: (b = (prtx1_2.b + 1))
-> Bitmap Index Scan on prtx2_2_c_idx
Index Cond: (c = 99)
-(23 rows)
+ Index Bound Cond: (c = 99)
+(27 rows)
select * from prtx1
where not exists (select 1 from prtx2
@@ -2963,8 +3000,10 @@ SELECT t1.b, t1.c, t2.a, t2.c, t3.a, t3.c FROM prt2_adv t1 LEFT JOIN prt1_adv t2
Filter: (a = 0)
-> Index Scan using prt1_adv_p1_a_idx on prt1_adv_p1 t3_1
Index Cond: (a = t1_1.b)
+ Index Bound Cond: (a = t1_1.b)
-> Index Scan using prt1_adv_p1_a_idx on prt1_adv_p1 t2_1
Index Cond: (a = t1_1.b)
+ Index Bound Cond: (a = t1_1.b)
-> Hash Right Join
Hash Cond: (t2_2.a = t1_2.b)
-> Seq Scan on prt1_adv_p2 t2_2
@@ -2985,7 +3024,7 @@ SELECT t1.b, t1.c, t2.a, t2.c, t3.a, t3.c FROM prt2_adv t1 LEFT JOIN prt1_adv t2
-> Hash
-> Seq Scan on prt2_adv_p3 t1_3
Filter: (a = 0)
-(31 rows)
+(33 rows)
SELECT t1.b, t1.c, t2.a, t2.c, t3.a, t3.c FROM prt2_adv t1 LEFT JOIN prt1_adv t2 ON (t1.b = t2.a) INNER JOIN prt1_adv t3 ON (t1.b = t3.a) WHERE t1.a = 0 ORDER BY t1.b, t2.a, t3.a;
b | c | a | c | a | c
@@ -5123,11 +5162,13 @@ SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id DE
-> Index Only Scan Backward using fract_t0_pkey on fract_t0 x_1
-> Index Only Scan using fract_t0_pkey on fract_t0 y_1
Index Cond: (id = x_1.id)
+ Index Bound Cond: (id = x_1.id)
-> Nested Loop Left Join
-> Index Only Scan Backward using fract_t1_pkey on fract_t1 x_2
-> Index Only Scan using fract_t1_pkey on fract_t1 y_2
Index Cond: (id = x_2.id)
-(11 rows)
+ Index Bound Cond: (id = x_2.id)
+(13 rows)
-- cleanup
DROP TABLE fract_t;
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 7ca98397ae..ddb139fe3f 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -2463,23 +2463,32 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
-> Append (actual rows=N loops=N)
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (actual rows=N loops=N)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
Index Cond: (a = a.a)
-(27 rows)
+ Index Bound Cond: (a = a.a)
+(36 rows)
-- Ensure the same partitions are pruned when we make the nested loop
-- parameter an Expr rather than a plain Param.
@@ -2497,23 +2506,32 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
-> Append (actual rows=N loops=N)
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N)
Index Cond: (a = (a.a + 0))
+ Index Bound Cond: (a = (a.a + 0))
-> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (actual rows=N loops=N)
Index Cond: (a = (a.a + 0))
+ Index Bound Cond: (a = (a.a + 0))
-> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N)
Index Cond: (a = (a.a + 0))
+ Index Bound Cond: (a = (a.a + 0))
-> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed)
Index Cond: (a = (a.a + 0))
+ Index Bound Cond: (a = (a.a + 0))
-> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed)
Index Cond: (a = (a.a + 0))
+ Index Bound Cond: (a = (a.a + 0))
-> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed)
Index Cond: (a = (a.a + 0))
+ Index Bound Cond: (a = (a.a + 0))
-> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed)
Index Cond: (a = (a.a + 0))
+ Index Bound Cond: (a = (a.a + 0))
-> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed)
Index Cond: (a = (a.a + 0))
+ Index Bound Cond: (a = (a.a + 0))
-> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
Index Cond: (a = (a.a + 0))
-(27 rows)
+ Index Bound Cond: (a = (a.a + 0))
+(36 rows)
insert into lprt_a values(3),(3);
select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 3)');
@@ -2530,23 +2548,32 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
-> Append (actual rows=N loops=N)
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (actual rows=N loops=N)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (actual rows=N loops=N)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (actual rows=N loops=N)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (actual rows=N loops=N)
Index Cond: (a = a.a)
-(27 rows)
+ Index Bound Cond: (a = a.a)
+(36 rows)
select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)');
explain_parallel_append
@@ -2563,23 +2590,32 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
-> Append (actual rows=N loops=N)
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (actual rows=N loops=N)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
Index Cond: (a = a.a)
-(28 rows)
+ Index Bound Cond: (a = a.a)
+(37 rows)
delete from lprt_a where a = 1;
select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)');
@@ -2597,23 +2633,32 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
-> Append (actual rows=N loops=N)
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (never executed)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (never executed)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (never executed)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed)
Index Cond: (a = a.a)
+ Index Bound Cond: (a = a.a)
-> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
Index Cond: (a = a.a)
-(28 rows)
+ Index Bound Cond: (a = a.a)
+(37 rows)
reset enable_hashjoin;
reset enable_mergejoin;
@@ -2639,47 +2684,56 @@ select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1
Filter: (b = (InitPlan 2).col1)
-> Bitmap Index Scan on ab_a1_b1_a_idx (never executed)
Index Cond: (a = (InitPlan 1).col1)
+ Index Bound Cond: (a = (InitPlan 1).col1)
-> Bitmap Heap Scan on ab_a1_b2 ab_2 (never executed)
Recheck Cond: (a = (InitPlan 1).col1)
Filter: (b = (InitPlan 2).col1)
-> Bitmap Index Scan on ab_a1_b2_a_idx (never executed)
Index Cond: (a = (InitPlan 1).col1)
+ Index Bound Cond: (a = (InitPlan 1).col1)
-> Bitmap Heap Scan on ab_a1_b3 ab_3 (never executed)
Recheck Cond: (a = (InitPlan 1).col1)
Filter: (b = (InitPlan 2).col1)
-> Bitmap Index Scan on ab_a1_b3_a_idx (never executed)
Index Cond: (a = (InitPlan 1).col1)
+ Index Bound Cond: (a = (InitPlan 1).col1)
-> Bitmap Heap Scan on ab_a2_b1 ab_4 (never executed)
Recheck Cond: (a = (InitPlan 1).col1)
Filter: (b = (InitPlan 2).col1)
-> Bitmap Index Scan on ab_a2_b1_a_idx (never executed)
Index Cond: (a = (InitPlan 1).col1)
+ Index Bound Cond: (a = (InitPlan 1).col1)
-> Bitmap Heap Scan on ab_a2_b2 ab_5 (never executed)
Recheck Cond: (a = (InitPlan 1).col1)
Filter: (b = (InitPlan 2).col1)
-> Bitmap Index Scan on ab_a2_b2_a_idx (never executed)
Index Cond: (a = (InitPlan 1).col1)
+ Index Bound Cond: (a = (InitPlan 1).col1)
-> Bitmap Heap Scan on ab_a2_b3 ab_6 (never executed)
Recheck Cond: (a = (InitPlan 1).col1)
Filter: (b = (InitPlan 2).col1)
-> Bitmap Index Scan on ab_a2_b3_a_idx (never executed)
Index Cond: (a = (InitPlan 1).col1)
+ Index Bound Cond: (a = (InitPlan 1).col1)
-> Bitmap Heap Scan on ab_a3_b1 ab_7 (never executed)
Recheck Cond: (a = (InitPlan 1).col1)
Filter: (b = (InitPlan 2).col1)
-> Bitmap Index Scan on ab_a3_b1_a_idx (never executed)
Index Cond: (a = (InitPlan 1).col1)
+ Index Bound Cond: (a = (InitPlan 1).col1)
-> Bitmap Heap Scan on ab_a3_b2 ab_8 (actual rows=0 loops=1)
Recheck Cond: (a = (InitPlan 1).col1)
Filter: (b = (InitPlan 2).col1)
-> Bitmap Index Scan on ab_a3_b2_a_idx (actual rows=0 loops=1)
Index Cond: (a = (InitPlan 1).col1)
+ Index Bound Cond: (a = (InitPlan 1).col1)
-> Bitmap Heap Scan on ab_a3_b3 ab_9 (never executed)
Recheck Cond: (a = (InitPlan 1).col1)
Filter: (b = (InitPlan 2).col1)
-> Bitmap Index Scan on ab_a3_b3_a_idx (never executed)
Index Cond: (a = (InitPlan 1).col1)
-(52 rows)
+ Index Bound Cond: (a = (InitPlan 1).col1)
+(61 rows)
-- Test run-time partition pruning with UNION ALL parents
explain (analyze, costs off, summary off, timing off)
@@ -2695,16 +2749,19 @@ select * from (select * from ab where a = 1 union all select * from ab) ab where
Filter: (b = (InitPlan 1).col1)
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
Index Cond: (a = 1)
+ Index Bound Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed)
Recheck Cond: (a = 1)
Filter: (b = (InitPlan 1).col1)
-> Bitmap Index Scan on ab_a1_b2_a_idx (never executed)
Index Cond: (a = 1)
+ Index Bound Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b3 ab_13 (never executed)
Recheck Cond: (a = 1)
Filter: (b = (InitPlan 1).col1)
-> Bitmap Index Scan on ab_a1_b3_a_idx (never executed)
Index Cond: (a = 1)
+ Index Bound Cond: (a = 1)
-> Seq Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1)
Filter: (b = (InitPlan 1).col1)
-> Seq Scan on ab_a1_b2 ab_2 (never executed)
@@ -2723,7 +2780,7 @@ select * from (select * from ab where a = 1 union all select * from ab) ab where
Filter: (b = (InitPlan 1).col1)
-> Seq Scan on ab_a3_b3 ab_9 (never executed)
Filter: (b = (InitPlan 1).col1)
-(37 rows)
+(40 rows)
-- A case containing a UNION ALL with a non-partitioned child.
explain (analyze, costs off, summary off, timing off)
@@ -2739,16 +2796,19 @@ select * from (select * from ab where a = 1 union all (values(10,5)) union all s
Filter: (b = (InitPlan 1).col1)
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
Index Cond: (a = 1)
+ Index Bound Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed)
Recheck Cond: (a = 1)
Filter: (b = (InitPlan 1).col1)
-> Bitmap Index Scan on ab_a1_b2_a_idx (never executed)
Index Cond: (a = 1)
+ Index Bound Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b3 ab_13 (never executed)
Recheck Cond: (a = 1)
Filter: (b = (InitPlan 1).col1)
-> Bitmap Index Scan on ab_a1_b3_a_idx (never executed)
Index Cond: (a = 1)
+ Index Bound Cond: (a = 1)
-> Result (actual rows=0 loops=1)
One-Time Filter: (5 = (InitPlan 1).col1)
-> Seq Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1)
@@ -2769,7 +2829,7 @@ select * from (select * from ab where a = 1 union all (values(10,5)) union all s
Filter: (b = (InitPlan 1).col1)
-> Seq Scan on ab_a3_b3 ab_9 (never executed)
Filter: (b = (InitPlan 1).col1)
-(39 rows)
+(42 rows)
-- Another UNION ALL test, but containing a mix of exec init and exec run-time pruning.
create table xy_1 (x int, y int);
@@ -2840,33 +2900,39 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
Index Cond: (a = 1)
+ Index Bound Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b2 ab_a1_2 (actual rows=1 loops=1)
Recheck Cond: (a = 1)
Heap Blocks: exact=1
-> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
+ Index Bound Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b3 ab_a1_3 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
Heap Blocks: exact=1
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
+ Index Bound Cond: (a = 1)
-> Materialize (actual rows=1 loops=1)
-> Append (actual rows=1 loops=1)
-> Bitmap Heap Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
Index Cond: (a = 1)
+ Index Bound Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b2 ab_2 (actual rows=1 loops=1)
Recheck Cond: (a = 1)
Heap Blocks: exact=1
-> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
+ Index Bound Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
Heap Blocks: exact=1
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
-(36 rows)
+ Index Bound Cond: (a = 1)
+(42 rows)
table ab;
a | b
@@ -2941,17 +3007,23 @@ select * from tbl1 join tprt on tbl1.col1 > tprt.col1;
-> Append (actual rows=3 loops=2)
-> Index Scan using tprt1_idx on tprt_1 (actual rows=2 loops=2)
Index Cond: (col1 < tbl1.col1)
+ Index Bound Cond: (col1 < tbl1.col1)
-> Index Scan using tprt2_idx on tprt_2 (actual rows=2 loops=1)
Index Cond: (col1 < tbl1.col1)
+ Index Bound Cond: (col1 < tbl1.col1)
-> Index Scan using tprt3_idx on tprt_3 (never executed)
Index Cond: (col1 < tbl1.col1)
+ Index Bound Cond: (col1 < tbl1.col1)
-> Index Scan using tprt4_idx on tprt_4 (never executed)
Index Cond: (col1 < tbl1.col1)
+ Index Bound Cond: (col1 < tbl1.col1)
-> Index Scan using tprt5_idx on tprt_5 (never executed)
Index Cond: (col1 < tbl1.col1)
+ Index Bound Cond: (col1 < tbl1.col1)
-> Index Scan using tprt6_idx on tprt_6 (never executed)
Index Cond: (col1 < tbl1.col1)
-(15 rows)
+ Index Bound Cond: (col1 < tbl1.col1)
+(21 rows)
explain (analyze, costs off, summary off, timing off)
select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
@@ -2962,17 +3034,23 @@ select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
-> Append (actual rows=1 loops=2)
-> Index Scan using tprt1_idx on tprt_1 (never executed)
Index Cond: (col1 = tbl1.col1)
+ Index Bound Cond: (col1 = tbl1.col1)
-> Index Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2)
Index Cond: (col1 = tbl1.col1)
+ Index Bound Cond: (col1 = tbl1.col1)
-> Index Scan using tprt3_idx on tprt_3 (never executed)
Index Cond: (col1 = tbl1.col1)
+ Index Bound Cond: (col1 = tbl1.col1)
-> Index Scan using tprt4_idx on tprt_4 (never executed)
Index Cond: (col1 = tbl1.col1)
+ Index Bound Cond: (col1 = tbl1.col1)
-> Index Scan using tprt5_idx on tprt_5 (never executed)
Index Cond: (col1 = tbl1.col1)
+ Index Bound Cond: (col1 = tbl1.col1)
-> Index Scan using tprt6_idx on tprt_6 (never executed)
Index Cond: (col1 = tbl1.col1)
-(15 rows)
+ Index Bound Cond: (col1 = tbl1.col1)
+(21 rows)
select tbl1.col1, tprt.col1 from tbl1
inner join tprt on tbl1.col1 > tprt.col1
@@ -3007,17 +3085,23 @@ select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1;
-> Append (actual rows=5 loops=5)
-> Index Scan using tprt1_idx on tprt_1 (actual rows=2 loops=5)
Index Cond: (col1 < tbl1.col1)
+ Index Bound Cond: (col1 < tbl1.col1)
-> Index Scan using tprt2_idx on tprt_2 (actual rows=3 loops=4)
Index Cond: (col1 < tbl1.col1)
+ Index Bound Cond: (col1 < tbl1.col1)
-> Index Scan using tprt3_idx on tprt_3 (actual rows=1 loops=2)
Index Cond: (col1 < tbl1.col1)
+ Index Bound Cond: (col1 < tbl1.col1)
-> Index Scan using tprt4_idx on tprt_4 (never executed)
Index Cond: (col1 < tbl1.col1)
+ Index Bound Cond: (col1 < tbl1.col1)
-> Index Scan using tprt5_idx on tprt_5 (never executed)
Index Cond: (col1 < tbl1.col1)
+ Index Bound Cond: (col1 < tbl1.col1)
-> Index Scan using tprt6_idx on tprt_6 (never executed)
Index Cond: (col1 < tbl1.col1)
-(15 rows)
+ Index Bound Cond: (col1 < tbl1.col1)
+(21 rows)
explain (analyze, costs off, summary off, timing off)
select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1;
@@ -3028,17 +3112,23 @@ select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1;
-> Append (actual rows=1 loops=5)
-> Index Scan using tprt1_idx on tprt_1 (never executed)
Index Cond: (col1 = tbl1.col1)
+ Index Bound Cond: (col1 = tbl1.col1)
-> Index Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2)
Index Cond: (col1 = tbl1.col1)
+ Index Bound Cond: (col1 = tbl1.col1)
-> Index Scan using tprt3_idx on tprt_3 (actual rows=0 loops=3)
Index Cond: (col1 = tbl1.col1)
+ Index Bound Cond: (col1 = tbl1.col1)
-> Index Scan using tprt4_idx on tprt_4 (never executed)
Index Cond: (col1 = tbl1.col1)
+ Index Bound Cond: (col1 = tbl1.col1)
-> Index Scan using tprt5_idx on tprt_5 (never executed)
Index Cond: (col1 = tbl1.col1)
+ Index Bound Cond: (col1 = tbl1.col1)
-> Index Scan using tprt6_idx on tprt_6 (never executed)
Index Cond: (col1 = tbl1.col1)
-(15 rows)
+ Index Bound Cond: (col1 = tbl1.col1)
+(21 rows)
select tbl1.col1, tprt.col1 from tbl1
inner join tprt on tbl1.col1 > tprt.col1
@@ -3092,17 +3182,23 @@ select * from tbl1 join tprt on tbl1.col1 < tprt.col1;
-> Append (actual rows=1 loops=1)
-> Index Scan using tprt1_idx on tprt_1 (never executed)
Index Cond: (col1 > tbl1.col1)
+ Index Bound Cond: (col1 > tbl1.col1)
-> Index Scan using tprt2_idx on tprt_2 (never executed)
Index Cond: (col1 > tbl1.col1)
+ Index Bound Cond: (col1 > tbl1.col1)
-> Index Scan using tprt3_idx on tprt_3 (never executed)
Index Cond: (col1 > tbl1.col1)
+ Index Bound Cond: (col1 > tbl1.col1)
-> Index Scan using tprt4_idx on tprt_4 (never executed)
Index Cond: (col1 > tbl1.col1)
+ Index Bound Cond: (col1 > tbl1.col1)
-> Index Scan using tprt5_idx on tprt_5 (never executed)
Index Cond: (col1 > tbl1.col1)
+ Index Bound Cond: (col1 > tbl1.col1)
-> Index Scan using tprt6_idx on tprt_6 (actual rows=1 loops=1)
Index Cond: (col1 > tbl1.col1)
-(15 rows)
+ Index Bound Cond: (col1 > tbl1.col1)
+(21 rows)
select tbl1.col1, tprt.col1 from tbl1
inner join tprt on tbl1.col1 < tprt.col1
@@ -3124,17 +3220,23 @@ select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
-> Append (actual rows=0 loops=1)
-> Index Scan using tprt1_idx on tprt_1 (never executed)
Index Cond: (col1 = tbl1.col1)
+ Index Bound Cond: (col1 = tbl1.col1)
-> Index Scan using tprt2_idx on tprt_2 (never executed)
Index Cond: (col1 = tbl1.col1)
+ Index Bound Cond: (col1 = tbl1.col1)
-> Index Scan using tprt3_idx on tprt_3 (never executed)
Index Cond: (col1 = tbl1.col1)
+ Index Bound Cond: (col1 = tbl1.col1)
-> Index Scan using tprt4_idx on tprt_4 (never executed)
Index Cond: (col1 = tbl1.col1)
+ Index Bound Cond: (col1 = tbl1.col1)
-> Index Scan using tprt5_idx on tprt_5 (never executed)
Index Cond: (col1 = tbl1.col1)
+ Index Bound Cond: (col1 = tbl1.col1)
-> Index Scan using tprt6_idx on tprt_6 (never executed)
Index Cond: (col1 = tbl1.col1)
-(15 rows)
+ Index Bound Cond: (col1 = tbl1.col1)
+(21 rows)
select tbl1.col1, tprt.col1 from tbl1
inner join tprt on tbl1.col1 = tprt.col1
@@ -3533,13 +3635,14 @@ explain (analyze, costs off, summary off, timing off) select * from ma_test wher
-> Limit (actual rows=1 loops=1)
-> Index Scan using ma_test_p2_b_idx on ma_test_p2 (actual rows=1 loops=1)
Index Cond: (b IS NOT NULL)
+ Index Bound Cond: (b IS NOT NULL)
-> Index Scan using ma_test_p1_b_idx on ma_test_p1 ma_test_1 (never executed)
Filter: (a >= (InitPlan 2).col1)
-> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_2 (actual rows=10 loops=1)
Filter: (a >= (InitPlan 2).col1)
-> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_3 (actual rows=10 loops=1)
Filter: (a >= (InitPlan 2).col1)
-(14 rows)
+(15 rows)
reset enable_seqscan;
reset enable_sort;
diff --git a/src/test/regress/expected/plancache.out b/src/test/regress/expected/plancache.out
index 4e59188196..c77ec2ab8b 100644
--- a/src/test/regress/expected/plancache.out
+++ b/src/test/regress/expected/plancache.out
@@ -299,7 +299,8 @@ explain (costs off) execute test_mode_pp(2);
Aggregate
-> Index Only Scan using test_mode_a_idx on test_mode
Index Cond: (a = 2)
-(3 rows)
+ Index Bound Cond: (a = 2)
+(4 rows)
select name, generic_plans, custom_plans from pg_prepared_statements
where name = 'test_mode_pp';
@@ -388,7 +389,8 @@ explain (costs off) execute test_mode_pp(2);
Aggregate
-> Index Only Scan using test_mode_a_idx on test_mode
Index Cond: (a = 2)
-(3 rows)
+ Index Bound Cond: (a = 2)
+(4 rows)
select name, generic_plans, custom_plans from pg_prepared_statements
where name = 'test_mode_pp';
diff --git a/src/test/regress/expected/portals.out b/src/test/regress/expected/portals.out
index 06726ed4ab..39c9d703f5 100644
--- a/src/test/regress/expected/portals.out
+++ b/src/test/regress/expected/portals.out
@@ -1340,11 +1340,12 @@ ROLLBACK;
BEGIN;
EXPLAIN (costs off)
DECLARE c1 CURSOR FOR SELECT stringu1 FROM onek WHERE stringu1 = 'DZAAAA';
- QUERY PLAN
----------------------------------------------
+ QUERY PLAN
+-------------------------------------------------
Index Only Scan using onek_stringu1 on onek
Index Cond: (stringu1 = 'DZAAAA'::name)
-(2 rows)
+ Index Bound Cond: (stringu1 = 'DZAAAA'::name)
+(3 rows)
DECLARE c1 CURSOR FOR SELECT stringu1 FROM onek WHERE stringu1 = 'DZAAAA';
FETCH FROM c1;
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index eb4b762ea1..bf5039361a 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -391,8 +391,9 @@ EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b;
Filter: (b <<< 5)
-> Index Scan using atest12_a_idx on atest12
Index Cond: (a = atest12_1.b)
+ Index Bound Cond: (a = atest12_1.b)
Filter: (b <<< 5)
-(6 rows)
+(7 rows)
-- And this one.
EXPLAIN (COSTS OFF) SELECT * FROM atest12 x, atest12 y
@@ -404,7 +405,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM atest12 x, atest12 y
Filter: (abs(a) <<< 5)
-> Index Scan using atest12_a_idx on atest12 x
Index Cond: (a = y.b)
-(5 rows)
+ Index Bound Cond: (a = y.b)
+(6 rows)
-- This should also be a nestloop, but the security barrier forces the inner
-- scan to be materialized
@@ -440,8 +442,9 @@ EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b;
Filter: (b <<< 5)
-> Index Scan using atest12_a_idx on atest12
Index Cond: (a = atest12_1.b)
+ Index Bound Cond: (a = atest12_1.b)
Filter: (b <<< 5)
-(6 rows)
+(7 rows)
EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y WHERE x.a = y.b;
QUERY PLAN
@@ -465,8 +468,9 @@ EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y
Filter: ((b <<< 5) AND (abs(a) <<< 5))
-> Index Scan using atest12_a_idx on atest12
Index Cond: (a = atest12_1.b)
+ Index Bound Cond: (a = atest12_1.b)
Filter: (b <<< 5)
-(6 rows)
+(7 rows)
-- But a security barrier view isolates the leaky operator.
EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y
@@ -496,8 +500,9 @@ EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b;
Filter: (b <<< 5)
-> Index Scan using atest12_a_idx on atest12
Index Cond: (a = atest12_1.b)
+ Index Bound Cond: (a = atest12_1.b)
Filter: (b <<< 5)
-(6 rows)
+(7 rows)
-- But not for this, due to lack of table-wide permissions needed
-- to make use of the expression index's statistics.
diff --git a/src/test/regress/expected/regex.out b/src/test/regress/expected/regex.out
index ae0de7307d..e663323b7c 100644
--- a/src/test/regress/expected/regex.out
+++ b/src/test/regress/expected/regex.out
@@ -296,52 +296,58 @@ explain (costs off) select * from pg_proc where proname ~ 'abc';
(2 rows)
explain (costs off) select * from pg_proc where proname ~ '^abc';
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------
Index Scan using pg_proc_proname_args_nsp_index on pg_proc
Index Cond: ((proname >= 'abc'::text) AND (proname < 'abd'::text))
+ Index Bound Cond: ((proname >= 'abc'::text) AND (proname < 'abd'::text))
Filter: (proname ~ '^abc'::text)
-(3 rows)
+(4 rows)
explain (costs off) select * from pg_proc where proname ~ '^abc$';
QUERY PLAN
------------------------------------------------------------
Index Scan using pg_proc_proname_args_nsp_index on pg_proc
Index Cond: (proname = 'abc'::text)
+ Index Bound Cond: (proname = 'abc'::text)
Filter: (proname ~ '^abc$'::text)
-(3 rows)
+(4 rows)
explain (costs off) select * from pg_proc where proname ~ '^abcd*e';
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------
Index Scan using pg_proc_proname_args_nsp_index on pg_proc
Index Cond: ((proname >= 'abc'::text) AND (proname < 'abd'::text))
+ Index Bound Cond: ((proname >= 'abc'::text) AND (proname < 'abd'::text))
Filter: (proname ~ '^abcd*e'::text)
-(3 rows)
+(4 rows)
explain (costs off) select * from pg_proc where proname ~ '^abc+d';
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------
Index Scan using pg_proc_proname_args_nsp_index on pg_proc
Index Cond: ((proname >= 'abc'::text) AND (proname < 'abd'::text))
+ Index Bound Cond: ((proname >= 'abc'::text) AND (proname < 'abd'::text))
Filter: (proname ~ '^abc+d'::text)
-(3 rows)
+(4 rows)
explain (costs off) select * from pg_proc where proname ~ '^(abc)(def)';
- QUERY PLAN
-----------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------
Index Scan using pg_proc_proname_args_nsp_index on pg_proc
Index Cond: ((proname >= 'abcdef'::text) AND (proname < 'abcdeg'::text))
+ Index Bound Cond: ((proname >= 'abcdef'::text) AND (proname < 'abcdeg'::text))
Filter: (proname ~ '^(abc)(def)'::text)
-(3 rows)
+(4 rows)
explain (costs off) select * from pg_proc where proname ~ '^(abc)$';
QUERY PLAN
------------------------------------------------------------
Index Scan using pg_proc_proname_args_nsp_index on pg_proc
Index Cond: (proname = 'abc'::text)
+ Index Bound Cond: (proname = 'abc'::text)
Filter: (proname ~ '^(abc)$'::text)
-(3 rows)
+(4 rows)
explain (costs off) select * from pg_proc where proname ~ '^(abc)?d';
QUERY PLAN
@@ -351,12 +357,13 @@ explain (costs off) select * from pg_proc where proname ~ '^(abc)?d';
(2 rows)
explain (costs off) select * from pg_proc where proname ~ '^abcd(x|(?=\w\w)q)';
- QUERY PLAN
-------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------
Index Scan using pg_proc_proname_args_nsp_index on pg_proc
Index Cond: ((proname >= 'abcd'::text) AND (proname < 'abce'::text))
+ Index Bound Cond: ((proname >= 'abcd'::text) AND (proname < 'abce'::text))
Filter: (proname ~ '^abcd(x|(?=\w\w)q)'::text)
-(3 rows)
+(4 rows)
-- Test for infinite loop in pullback() (CVE-2007-4772)
select 'a' ~ '($|^)*';
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 319190855b..737f37f415 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -272,7 +272,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
InitPlan 1
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
-(5 rows)
+ Index Bound Cond: (pguser = CURRENT_USER)
+(6 rows)
EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
QUERY PLAN
@@ -282,11 +283,12 @@ EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dt
InitPlan 1
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
+ Index Bound Cond: (pguser = CURRENT_USER)
-> Seq Scan on category
-> Hash
-> Seq Scan on document
Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
-(9 rows)
+(10 rows)
-- viewpoint from regress_rls_dave
SET SESSION AUTHORIZATION regress_rls_dave;
@@ -336,7 +338,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
InitPlan 1
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
-(5 rows)
+ Index Bound Cond: (pguser = CURRENT_USER)
+(6 rows)
EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
QUERY PLAN
@@ -346,11 +349,12 @@ EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dt
InitPlan 1
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
+ Index Bound Cond: (pguser = CURRENT_USER)
-> Seq Scan on category
-> Hash
-> Seq Scan on document
Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
-(9 rows)
+(10 rows)
-- 44 would technically fail for both p2r and p1r, but we should get an error
-- back from p1r for this because it sorts first
@@ -437,7 +441,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dt
Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
-> Index Scan using category_pkey on category
Index Cond: (cid = document.cid)
-(5 rows)
+ Index Bound Cond: (cid = document.cid)
+(6 rows)
-- interaction of FK/PK constraints
SET SESSION AUTHORIZATION regress_rls_alice;
@@ -991,13 +996,14 @@ EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
InitPlan 1
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
+ Index Bound Cond: (pguser = CURRENT_USER)
-> Seq Scan on part_document_fiction part_document_1
Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
-> Seq Scan on part_document_satire part_document_2
Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
-> Seq Scan on part_document_nonfiction part_document_3
Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
-(10 rows)
+(11 rows)
-- viewpoint from regress_rls_carol
SET SESSION AUTHORIZATION regress_rls_carol;
@@ -1033,13 +1039,14 @@ EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
InitPlan 1
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
+ Index Bound Cond: (pguser = CURRENT_USER)
-> Seq Scan on part_document_fiction part_document_1
Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
-> Seq Scan on part_document_satire part_document_2
Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
-> Seq Scan on part_document_nonfiction part_document_3
Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
-(10 rows)
+(11 rows)
-- viewpoint from regress_rls_dave
SET SESSION AUTHORIZATION regress_rls_dave;
@@ -1064,7 +1071,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
InitPlan 1
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
-(5 rows)
+ Index Bound Cond: (pguser = CURRENT_USER)
+(6 rows)
-- pp1 ERROR
INSERT INTO part_document VALUES (100, 11, 5, 'regress_rls_dave', 'testing pp1'); -- fail
@@ -1142,7 +1150,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
InitPlan 1
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
-(5 rows)
+ Index Bound Cond: (pguser = CURRENT_USER)
+(6 rows)
-- viewpoint from regress_rls_carol
SET SESSION AUTHORIZATION regress_rls_carol;
@@ -1180,13 +1189,14 @@ EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
InitPlan 1
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
+ Index Bound Cond: (pguser = CURRENT_USER)
-> Seq Scan on part_document_fiction part_document_1
Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
-> Seq Scan on part_document_satire part_document_2
Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
-> Seq Scan on part_document_nonfiction part_document_3
Filter: ((dlevel <= (InitPlan 1).col1) AND f_leak(dtitle))
-(10 rows)
+(11 rows)
-- only owner can change policies
ALTER POLICY pp1 ON part_document USING (true); --fail
diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out
index b400b58f76..744a4e0f5c 100644
--- a/src/test/regress/expected/rowtypes.out
+++ b/src/test/regress/expected/rowtypes.out
@@ -302,11 +302,12 @@ explain (costs off)
select thousand, tenthous from tenk1
where (thousand, tenthous) >= (997, 5000)
order by thousand, tenthous;
- QUERY PLAN
------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Index Only Scan using tenk1_thous_tenthous on tenk1
Index Cond: (ROW(thousand, tenthous) >= ROW(997, 5000))
-(2 rows)
+ Index Bound Cond: (ROW(thousand, tenthous) >= ROW(997, 5000))
+(3 rows)
select thousand, tenthous from tenk1
where (thousand, tenthous) >= (997, 5000)
@@ -344,15 +345,16 @@ explain (costs off)
select thousand, tenthous, four from tenk1
where (thousand, tenthous, four) > (998, 5000, 3)
order by thousand, tenthous;
- QUERY PLAN
------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------
Sort
Sort Key: thousand, tenthous
-> Bitmap Heap Scan on tenk1
Filter: (ROW(thousand, tenthous, four) > ROW(998, 5000, 3))
-> Bitmap Index Scan on tenk1_thous_tenthous
Index Cond: (ROW(thousand, tenthous) >= ROW(998, 5000))
-(6 rows)
+ Index Bound Cond: (ROW(thousand, tenthous) >= ROW(998, 5000))
+(7 rows)
select thousand, tenthous, four from tenk1
where (thousand, tenthous, four) > (998, 5000, 3)
@@ -380,11 +382,12 @@ explain (costs off)
select thousand, tenthous from tenk1
where (998, 5000) < (thousand, tenthous)
order by thousand, tenthous;
- QUERY PLAN
-----------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------
Index Only Scan using tenk1_thous_tenthous on tenk1
Index Cond: (ROW(thousand, tenthous) > ROW(998, 5000))
-(2 rows)
+ Index Bound Cond: (ROW(thousand, tenthous) > ROW(998, 5000))
+(3 rows)
select thousand, tenthous from tenk1
where (998, 5000) < (thousand, tenthous)
@@ -420,7 +423,8 @@ order by thousand, hundred;
Filter: (ROW(998, 5000) < ROW(thousand, hundred))
-> Bitmap Index Scan on tenk1_thous_tenthous
Index Cond: (thousand >= 998)
-(6 rows)
+ Index Bound Cond: (thousand >= 998)
+(7 rows)
select thousand, hundred from tenk1
where (998, 5000) < (thousand, hundred)
@@ -448,11 +452,12 @@ create index on test_table (a,b);
set enable_sort = off;
explain (costs off)
select a,b from test_table where (a,b) > ('a','a') order by a,b;
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------
Index Only Scan using test_table_a_b_idx on test_table
Index Cond: (ROW(a, b) > ROW('a'::text, 'a'::text))
-(2 rows)
+ Index Bound Cond: (ROW(a, b) > ROW('a'::text, 'a'::text))
+(3 rows)
select a,b from test_table where (a,b) > ('a','a') order by a,b;
a | b
@@ -1132,12 +1137,13 @@ explain (costs off)
select row_to_json(q) from
(select thousand, tenthous from tenk1
where thousand = 42 and tenthous < 2000 offset 0) q;
- QUERY PLAN
--------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------
Subquery Scan on q
-> Index Only Scan using tenk1_thous_tenthous on tenk1
Index Cond: ((thousand = 42) AND (tenthous < 2000))
-(3 rows)
+ Index Bound Cond: ((thousand = 42) AND (tenthous < 2000))
+(4 rows)
select row_to_json(q) from
(select thousand, tenthous from tenk1
diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out
index 33a6dceb0e..6a5e462c96 100644
--- a/src/test/regress/expected/select.out
+++ b/src/test/regress/expected/select.out
@@ -747,8 +747,9 @@ select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
-----------------------------------------
Index Scan using onek2_u2_prtl on onek2
Index Cond: (unique2 = 11)
+ Index Bound Cond: (unique2 = 11)
Filter: (stringu1 = 'ATAAAA'::name)
-(3 rows)
+(4 rows)
select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
@@ -763,8 +764,9 @@ select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
-----------------------------------------------------------------
Index Scan using onek2_u2_prtl on onek2 (actual rows=1 loops=1)
Index Cond: (unique2 = 11)
+ Index Bound Cond: (unique2 = 11)
Filter: (stringu1 = 'ATAAAA'::name)
-(3 rows)
+(4 rows)
explain (costs off)
select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
@@ -772,8 +774,9 @@ select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
-----------------------------------------
Index Scan using onek2_u2_prtl on onek2
Index Cond: (unique2 = 11)
+ Index Bound Cond: (unique2 = 11)
Filter: (stringu1 = 'ATAAAA'::name)
-(3 rows)
+(4 rows)
select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
unique2
@@ -788,7 +791,8 @@ select * from onek2 where unique2 = 11 and stringu1 < 'B';
-----------------------------------------
Index Scan using onek2_u2_prtl on onek2
Index Cond: (unique2 = 11)
-(2 rows)
+ Index Bound Cond: (unique2 = 11)
+(3 rows)
select * from onek2 where unique2 = 11 and stringu1 < 'B';
unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
@@ -802,7 +806,8 @@ select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
----------------------------------------------
Index Only Scan using onek2_u2_prtl on onek2
Index Cond: (unique2 = 11)
-(2 rows)
+ Index Bound Cond: (unique2 = 11)
+(3 rows)
select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
unique2
@@ -818,8 +823,9 @@ select unique2 from onek2 where unique2 = 11 and stringu1 < 'B' for update;
LockRows
-> Index Scan using onek2_u2_prtl on onek2
Index Cond: (unique2 = 11)
+ Index Bound Cond: (unique2 = 11)
Filter: (stringu1 < 'B'::name)
-(4 rows)
+(5 rows)
select unique2 from onek2 where unique2 = 11 and stringu1 < 'B' for update;
unique2
@@ -852,7 +858,8 @@ select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
Recheck Cond: ((unique2 = 11) AND (stringu1 < 'B'::name))
-> Bitmap Index Scan on onek2_u2_prtl
Index Cond: (unique2 = 11)
-(4 rows)
+ Index Bound Cond: (unique2 = 11)
+(5 rows)
select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
unique2
@@ -873,9 +880,11 @@ select unique1, unique2 from onek2
-> BitmapOr
-> Bitmap Index Scan on onek2_u2_prtl
Index Cond: (unique2 = 11)
+ Index Bound Cond: (unique2 = 11)
-> Bitmap Index Scan on onek2_u1_prtl
Index Cond: (unique1 = 0)
-(8 rows)
+ Index Bound Cond: (unique1 = 0)
+(10 rows)
select unique1, unique2 from onek2
where (unique2 = 11 or unique1 = 0) and stringu1 < 'B';
@@ -895,9 +904,11 @@ select unique1, unique2 from onek2
-> BitmapOr
-> Bitmap Index Scan on onek2_u2_prtl
Index Cond: (unique2 = 11)
+ Index Bound Cond: (unique2 = 11)
-> Bitmap Index Scan on onek2_u1_prtl
Index Cond: (unique1 = 0)
-(7 rows)
+ Index Bound Cond: (unique1 = 0)
+(9 rows)
select unique1, unique2 from onek2
where (unique2 = 11 and stringu1 < 'B') or unique1 = 0;
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 87273fa635..16f0866af8 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -372,7 +372,8 @@ explain (costs off)
-> Partial Aggregate
-> Parallel Index Scan using tenk1_hundred on tenk1
Index Cond: (hundred > 1)
-(6 rows)
+ Index Bound Cond: (hundred > 1)
+(7 rows)
select count((unique1)) from tenk1 where hundred > 1;
count
@@ -384,8 +385,8 @@ select count((unique1)) from tenk1 where hundred > 1;
explain (costs off)
select count((unique1)) from tenk1
where hundred = any ((select array_agg(i) from generate_series(1, 100, 15) i)::int[]);
- QUERY PLAN
----------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------
Finalize Aggregate
InitPlan 1
-> Aggregate
@@ -395,7 +396,8 @@ explain (costs off)
-> Partial Aggregate
-> Parallel Index Scan using tenk1_hundred on tenk1
Index Cond: (hundred = ANY ((InitPlan 1).col1))
-(9 rows)
+ Index Bound Cond: (hundred = ANY ((InitPlan 1).col1))
+(10 rows)
select count((unique1)) from tenk1
where hundred = any ((select array_agg(i) from generate_series(1, 100, 15) i)::int[]);
@@ -415,7 +417,8 @@ explain (costs off)
-> Partial Aggregate
-> Parallel Index Only Scan using tenk1_thous_tenthous on tenk1
Index Cond: (thousand > 95)
-(6 rows)
+ Index Bound Cond: (thousand > 95)
+(7 rows)
select count(*) from tenk1 where thousand > 95;
count
@@ -439,7 +442,8 @@ select * from
-> Partial Aggregate
-> Parallel Index Scan using tenk1_hundred on tenk1
Index Cond: (hundred > 10)
-(8 rows)
+ Index Bound Cond: (hundred > 10)
+(9 rows)
select * from
(select count(unique1) from tenk1 where hundred > 10) ss
@@ -465,7 +469,8 @@ select * from
-> Partial Aggregate
-> Parallel Index Only Scan using tenk1_thous_tenthous on tenk1
Index Cond: (thousand > 99)
-(8 rows)
+ Index Bound Cond: (thousand > 99)
+(9 rows)
select * from
(select count(*) from tenk1 where thousand > 99) ss
@@ -546,7 +551,8 @@ explain (costs off)
Recheck Cond: (hundred > 1)
-> Bitmap Index Scan on tenk1_hundred
Index Cond: (hundred > 1)
-(10 rows)
+ Index Bound Cond: (hundred > 1)
+(11 rows)
select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0;
count
@@ -1025,7 +1031,8 @@ explain (costs off)
Single Copy: true
-> Index Scan using tenk1_unique1 on tenk1
Index Cond: (unique1 = 1)
-(5 rows)
+ Index Bound Cond: (unique1 = 1)
+(6 rows)
ROLLBACK TO SAVEPOINT settings;
-- exercise record typmod remapping between backends
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 6e08898b18..1f60eba633 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -618,7 +618,8 @@ EXPLAIN (COSTS off) SELECT count(*) FROM test_last_scan WHERE idx_col = 1;
Aggregate
-> Index Scan using test_last_scan_pkey on test_last_scan
Index Cond: (idx_col = 1)
-(3 rows)
+ Index Bound Cond: (idx_col = 1)
+(4 rows)
SELECT count(*) FROM test_last_scan WHERE idx_col = 1;
count
@@ -696,7 +697,8 @@ EXPLAIN (COSTS off) SELECT count(*) FROM test_last_scan WHERE idx_col = 1;
Aggregate
-> Index Scan using test_last_scan_pkey on test_last_scan
Index Cond: (idx_col = 1)
-(3 rows)
+ Index Bound Cond: (idx_col = 1)
+(4 rows)
SELECT count(*) FROM test_last_scan WHERE idx_col = 1;
count
@@ -741,7 +743,8 @@ EXPLAIN (COSTS off) SELECT count(*) FROM test_last_scan WHERE idx_col = 1;
Recheck Cond: (idx_col = 1)
-> Bitmap Index Scan on test_last_scan_pkey
Index Cond: (idx_col = 1)
-(5 rows)
+ Index Bound Cond: (idx_col = 1)
+(6 rows)
SELECT count(*) FROM test_last_scan WHERE idx_col = 1;
count
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 9eecdc1e92..8d536b8396 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1051,10 +1051,12 @@ where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0)
Filter: (EXISTS(SubPlan 1) OR (ten < 0))
-> Bitmap Index Scan on tenk1_thous_tenthous
Index Cond: (thousand = 1)
+ Index Bound Cond: (thousand = 1)
SubPlan 1
-> Index Only Scan using tenk1_unique1 on tenk1 k
Index Cond: (unique1 = t.unique2)
-(9 rows)
+ Index Bound Cond: (unique1 = t.unique2)
+(11 rows)
select count(*) from tenk1 t
where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0)
@@ -1203,11 +1205,12 @@ where o.ten = 0;
-> Index Scan using onek_unique1 on public.onek i
Output: (ANY (i.ten = (hashed SubPlan 1).col1)), random()
Index Cond: (i.unique1 = o.unique1)
+ Index Bound Cond: (i.unique1 = o.unique1)
SubPlan 1
-> Seq Scan on public.int4_tbl
Output: int4_tbl.f1
Filter: (int4_tbl.f1 <= o.hundred)
-(14 rows)
+(15 rows)
select sum(ss.tst::int) from
onek o cross join lateral (
@@ -1231,8 +1234,8 @@ select count(*) from
select * from onek i2 where i2.unique1 = o.unique2
) ss
where o.ten = 1;
- QUERY PLAN
-------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------
Aggregate
-> Nested Loop
-> Seq Scan on onek o
@@ -1243,10 +1246,12 @@ where o.ten = 1;
-> Subquery Scan on "*SELECT* 1"
-> Index Scan using onek_unique1 on onek i1
Index Cond: (unique1 = o.unique1)
+ Index Bound Cond: (unique1 = o.unique1)
-> Subquery Scan on "*SELECT* 2"
-> Index Scan using onek_unique1 on onek i2
Index Cond: (unique1 = o.unique2)
-(13 rows)
+ Index Bound Cond: (unique1 = o.unique2)
+(15 rows)
select count(*) from
onek o cross join lateral (
@@ -2102,6 +2107,7 @@ ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);
-> Limit
-> Index Scan using tenk2_hundred on tenk2 c
Index Cond: (hundred IS NOT NULL)
+ Index Bound Cond: (hundred IS NOT NULL)
Filter: (odd = b.odd)
-(16 rows)
+(17 rows)
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index 0fd0e1c38b..8735f02adf 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -1105,14 +1105,16 @@ explain (costs off)
UNION ALL
SELECT * FROM t2) t
WHERE ab = 'ab';
- QUERY PLAN
----------------------------------------------
+ QUERY PLAN
+---------------------------------------------------
Append
-> Index Scan using t1_ab_idx on t1
Index Cond: ((a || b) = 'ab'::text)
+ Index Bound Cond: ((a || b) = 'ab'::text)
-> Index Only Scan using t2_pkey on t2
Index Cond: (ab = 'ab'::text)
-(5 rows)
+ Index Bound Cond: (ab = 'ab'::text)
+(7 rows)
explain (costs off)
SELECT * FROM
@@ -1120,16 +1122,18 @@ explain (costs off)
UNION
SELECT * FROM t2) t
WHERE ab = 'ab';
- QUERY PLAN
----------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------
HashAggregate
Group Key: ((t1.a || t1.b))
-> Append
-> Index Scan using t1_ab_idx on t1
Index Cond: ((a || b) = 'ab'::text)
+ Index Bound Cond: ((a || b) = 'ab'::text)
-> Index Only Scan using t2_pkey on t2
Index Cond: (ab = 'ab'::text)
-(7 rows)
+ Index Bound Cond: (ab = 'ab'::text)
+(9 rows)
--
-- Test that ORDER BY for UNION ALL can be pushed down to inheritance
@@ -1399,16 +1403,18 @@ explain (costs off)
select * from
(select * from t3 a union all select * from t3 b) ss
join int4_tbl on f1 = expensivefunc(x);
- QUERY PLAN
-------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------
Nested Loop
-> Seq Scan on int4_tbl
-> Append
-> Index Scan using t3i on t3 a
Index Cond: (expensivefunc(x) = int4_tbl.f1)
+ Index Bound Cond: (expensivefunc(x) = int4_tbl.f1)
-> Index Scan using t3i on t3 b
Index Cond: (expensivefunc(x) = int4_tbl.f1)
-(7 rows)
+ Index Bound Cond: (expensivefunc(x) = int4_tbl.f1)
+(9 rows)
select * from
(select * from t3 a union all select * from t3 b) ss
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 1d1f568bc4..a2e76f57af 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -531,7 +531,8 @@ EXPLAIN (costs off) UPDATE rw_view1 SET a=6 WHERE a=5;
Update on base_tbl
-> Index Scan using base_tbl_pkey on base_tbl
Index Cond: ((a > 0) AND (a = 5))
-(3 rows)
+ Index Bound Cond: ((a > 0) AND (a = 5))
+(4 rows)
EXPLAIN (costs off) DELETE FROM rw_view1 WHERE a=5;
QUERY PLAN
@@ -539,7 +540,8 @@ EXPLAIN (costs off) DELETE FROM rw_view1 WHERE a=5;
Delete on base_tbl
-> Index Scan using base_tbl_pkey on base_tbl
Index Cond: ((a > 0) AND (a = 5))
-(3 rows)
+ Index Bound Cond: ((a > 0) AND (a = 5))
+(4 rows)
EXPLAIN (costs off)
MERGE INTO rw_view1 t USING (VALUES (5, 'X')) AS v(a,b) ON t.a = v.a
@@ -549,7 +551,8 @@ MERGE INTO rw_view1 t USING (VALUES (5, 'X')) AS v(a,b) ON t.a = v.a
Merge on base_tbl
-> Index Scan using base_tbl_pkey on base_tbl
Index Cond: ((a > 0) AND (a = 5))
-(3 rows)
+ Index Bound Cond: ((a > 0) AND (a = 5))
+(4 rows)
EXPLAIN (costs off)
MERGE INTO rw_view1 t
@@ -564,9 +567,10 @@ MERGE INTO rw_view1 t
Recheck Cond: (a > 0)
-> Bitmap Index Scan on base_tbl_pkey
Index Cond: (a > 0)
+ Index Bound Cond: (a > 0)
-> Hash
-> Function Scan on generate_series
-(9 rows)
+(10 rows)
EXPLAIN (costs off)
MERGE INTO rw_view1 t
@@ -581,9 +585,10 @@ MERGE INTO rw_view1 t
Recheck Cond: (a > 0)
-> Bitmap Index Scan on base_tbl_pkey
Index Cond: (a > 0)
+ Index Bound Cond: (a > 0)
-> Hash
-> Function Scan on generate_series
-(9 rows)
+(10 rows)
EXPLAIN (costs off)
MERGE INTO rw_view1 t
@@ -598,9 +603,10 @@ MERGE INTO rw_view1 t
Recheck Cond: (a > 0)
-> Bitmap Index Scan on base_tbl_pkey
Index Cond: (a > 0)
+ Index Bound Cond: (a > 0)
-> Hash
-> Function Scan on generate_series
-(9 rows)
+(10 rows)
-- it's still updatable if we add a DO ALSO rule
CREATE TABLE base_tbl_hist(ts timestamptz default now(), a int, b text);
@@ -723,20 +729,22 @@ SELECT * FROM rw_view2 ORDER BY aaa;
(3 rows)
EXPLAIN (costs off) UPDATE rw_view2 SET aaa=5 WHERE aaa=4;
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------
Update on base_tbl
-> Index Scan using base_tbl_pkey on base_tbl
Index Cond: ((a < 10) AND (a > 0) AND (a = 4))
-(3 rows)
+ Index Bound Cond: ((a < 10) AND (a > 0) AND (a = 4))
+(4 rows)
EXPLAIN (costs off) DELETE FROM rw_view2 WHERE aaa=4;
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------
Delete on base_tbl
-> Index Scan using base_tbl_pkey on base_tbl
Index Cond: ((a < 10) AND (a > 0) AND (a = 4))
-(3 rows)
+ Index Bound Cond: ((a < 10) AND (a > 0) AND (a = 4))
+(4 rows)
DROP TABLE base_tbl CASCADE;
NOTICE: drop cascades to 2 other objects
@@ -925,13 +933,15 @@ EXPLAIN (costs off) UPDATE rw_view2 SET a=3 WHERE a=2;
-> Nested Loop
-> Index Scan using base_tbl_pkey on base_tbl
Index Cond: (a = 2)
+ Index Bound Cond: (a = 2)
-> Subquery Scan on rw_view1
Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2))
-> Bitmap Heap Scan on base_tbl base_tbl_1
Recheck Cond: (a > 0)
-> Bitmap Index Scan on base_tbl_pkey
Index Cond: (a > 0)
-(10 rows)
+ Index Bound Cond: (a > 0)
+(12 rows)
EXPLAIN (costs off) DELETE FROM rw_view2 WHERE a=2;
QUERY PLAN
@@ -940,13 +950,15 @@ EXPLAIN (costs off) DELETE FROM rw_view2 WHERE a=2;
-> Nested Loop
-> Index Scan using base_tbl_pkey on base_tbl
Index Cond: (a = 2)
+ Index Bound Cond: (a = 2)
-> Subquery Scan on rw_view1
Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2))
-> Bitmap Heap Scan on base_tbl base_tbl_1
Recheck Cond: (a > 0)
-> Bitmap Index Scan on base_tbl_pkey
Index Cond: (a > 0)
-(10 rows)
+ Index Bound Cond: (a > 0)
+(12 rows)
DROP TABLE base_tbl CASCADE;
NOTICE: drop cascades to 2 other objects
@@ -1206,7 +1218,8 @@ EXPLAIN (costs off) UPDATE rw_view2 SET a=3 WHERE a=2;
Recheck Cond: (a > 0)
-> Bitmap Index Scan on base_tbl_pkey
Index Cond: (a > 0)
-(7 rows)
+ Index Bound Cond: (a > 0)
+(8 rows)
EXPLAIN (costs off) DELETE FROM rw_view2 WHERE a=2;
QUERY PLAN
@@ -1218,7 +1231,8 @@ EXPLAIN (costs off) DELETE FROM rw_view2 WHERE a=2;
Recheck Cond: (a > 0)
-> Bitmap Index Scan on base_tbl_pkey
Index Cond: (a > 0)
-(7 rows)
+ Index Bound Cond: (a > 0)
+(8 rows)
EXPLAIN (costs off)
MERGE INTO rw_view2 t
@@ -1237,9 +1251,10 @@ MERGE INTO rw_view2 t
Recheck Cond: (a > 0)
-> Bitmap Index Scan on base_tbl_pkey
Index Cond: (a > 0)
+ Index Bound Cond: (a > 0)
-> Hash
-> Function Scan on generate_series x
-(11 rows)
+(12 rows)
-- MERGE with incomplete set of INSTEAD OF triggers
DROP TRIGGER rw_view1_del_trig ON rw_view1;
@@ -1326,7 +1341,8 @@ UPDATE rw_view1 v SET bb='Updated row 2' WHERE rw_view1_aa(v)=2
Update on base_tbl
-> Index Scan using base_tbl_pkey on base_tbl
Index Cond: (a = 2)
-(3 rows)
+ Index Bound Cond: (a = 2)
+(4 rows)
DROP TABLE base_tbl CASCADE;
NOTICE: drop cascades to 2 other objects
@@ -2711,7 +2727,8 @@ EXPLAIN (costs off) INSERT INTO rw_view1 VALUES (5);
SubPlan 1
-> Index Only Scan using ref_tbl_pkey on ref_tbl r
Index Cond: (a = b.a)
-(5 rows)
+ Index Bound Cond: (a = b.a)
+(6 rows)
EXPLAIN (costs off) UPDATE rw_view1 SET a = a + 5;
QUERY PLAN
@@ -2725,7 +2742,8 @@ EXPLAIN (costs off) UPDATE rw_view1 SET a = a + 5;
SubPlan 1
-> Index Only Scan using ref_tbl_pkey on ref_tbl r_1
Index Cond: (a = b.a)
-(9 rows)
+ Index Bound Cond: (a = b.a)
+(10 rows)
DROP TABLE base_tbl, ref_tbl CASCADE;
NOTICE: drop cascades to view rw_view1
@@ -3117,10 +3135,12 @@ EXPLAIN (costs off) DELETE FROM rw_view1 WHERE id = 1 AND snoop(data);
-> Nested Loop
-> Index Scan using base_tbl_pkey on base_tbl base_tbl_1
Index Cond: (id = 1)
+ Index Bound Cond: (id = 1)
-> Index Scan using base_tbl_pkey on base_tbl
Index Cond: (id = 1)
+ Index Bound Cond: (id = 1)
Filter: ((NOT deleted) AND snoop(data))
-(7 rows)
+(9 rows)
DELETE FROM rw_view1 WHERE id = 1 AND snoop(data);
NOTICE: snooped value: Row 1
@@ -3131,6 +3151,7 @@ EXPLAIN (costs off) INSERT INTO rw_view1 VALUES (2, 'New row 2');
InitPlan 1
-> Index Only Scan using base_tbl_pkey on base_tbl t
Index Cond: (id = 2)
+ Index Bound Cond: (id = 2)
-> Result
One-Time Filter: ((InitPlan 1).col1 IS NOT TRUE)
@@ -3138,11 +3159,13 @@ EXPLAIN (costs off) INSERT INTO rw_view1 VALUES (2, 'New row 2');
InitPlan 1
-> Index Only Scan using base_tbl_pkey on base_tbl t
Index Cond: (id = 2)
+ Index Bound Cond: (id = 2)
-> Result
One-Time Filter: (InitPlan 1).col1
-> Index Scan using base_tbl_pkey on base_tbl
Index Cond: (id = 2)
-(15 rows)
+ Index Bound Cond: (id = 2)
+(18 rows)
INSERT INTO rw_view1 VALUES (2, 'New row 2');
SELECT * FROM base_tbl;
@@ -3211,6 +3234,7 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
-> Index Scan using t1_a_idx on public.t1 t1_1
Output: t1_1.tableoid, t1_1.ctid
Index Cond: ((t1_1.a > 5) AND (t1_1.a < 7))
+ Index Bound Cond: ((t1_1.a > 5) AND (t1_1.a < 7))
Filter: ((t1_1.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
SubPlan 1
-> Append
@@ -3221,16 +3245,19 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
-> Index Scan using t11_a_idx on public.t11 t1_2
Output: t1_2.tableoid, t1_2.ctid
Index Cond: ((t1_2.a > 5) AND (t1_2.a < 7))
+ Index Bound Cond: ((t1_2.a > 5) AND (t1_2.a < 7))
Filter: ((t1_2.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
-> Index Scan using t12_a_idx on public.t12 t1_3
Output: t1_3.tableoid, t1_3.ctid
Index Cond: ((t1_3.a > 5) AND (t1_3.a < 7))
+ Index Bound Cond: ((t1_3.a > 5) AND (t1_3.a < 7))
Filter: ((t1_3.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
-> Index Scan using t111_a_idx on public.t111 t1_4
Output: t1_4.tableoid, t1_4.ctid
Index Cond: ((t1_4.a > 5) AND (t1_4.a < 7))
+ Index Bound Cond: ((t1_4.a > 5) AND (t1_4.a < 7))
Filter: ((t1_4.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_4.a) AND leakproof(t1_4.a))
-(30 rows)
+(34 rows)
UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
SELECT * FROM v1 WHERE a=100; -- Nothing should have been changed to 100
@@ -3258,6 +3285,7 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
-> Index Scan using t1_a_idx on public.t1 t1_1
Output: t1_1.a, t1_1.tableoid, t1_1.ctid
Index Cond: ((t1_1.a > 5) AND (t1_1.a = 8))
+ Index Bound Cond: ((t1_1.a > 5) AND (t1_1.a = 8))
Filter: (EXISTS(SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
SubPlan 1
-> Append
@@ -3268,16 +3296,19 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
-> Index Scan using t11_a_idx on public.t11 t1_2
Output: t1_2.a, t1_2.tableoid, t1_2.ctid
Index Cond: ((t1_2.a > 5) AND (t1_2.a = 8))
+ Index Bound Cond: ((t1_2.a > 5) AND (t1_2.a = 8))
Filter: (EXISTS(SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
-> Index Scan using t12_a_idx on public.t12 t1_3
Output: t1_3.a, t1_3.tableoid, t1_3.ctid
Index Cond: ((t1_3.a > 5) AND (t1_3.a = 8))
+ Index Bound Cond: ((t1_3.a > 5) AND (t1_3.a = 8))
Filter: (EXISTS(SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
-> Index Scan using t111_a_idx on public.t111 t1_4
Output: t1_4.a, t1_4.tableoid, t1_4.ctid
Index Cond: ((t1_4.a > 5) AND (t1_4.a = 8))
+ Index Bound Cond: ((t1_4.a > 5) AND (t1_4.a = 8))
Filter: (EXISTS(SubPlan 1) AND snoop(t1_4.a) AND leakproof(t1_4.a))
-(30 rows)
+(34 rows)
UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
NOTICE: snooped value: 8
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index b4f3121751..5c7c257d78 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -670,7 +670,8 @@ select count(*) from tenk1 a
-> CTE Scan on x
-> Index Only Scan using tenk1_unique1 on tenk1 a
Index Cond: (unique1 = x.unique1)
-(10 rows)
+ Index Bound Cond: (unique1 = x.unique1)
+(11 rows)
-- test that pathkeys from a materialized CTE are propagated up to the
-- outer query
--
2.34.1
view thread (3+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Improve EXPLAIN output for multicolumn B-Tree Index
In-Reply-To: <TYWPR01MB1098260B694D27758FE2BA46FB1C92@TYWPR01MB10982.jpnprd01.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox