public inbox for [email protected]help / color / mirror / Atom feed
[PATCH 2/3] Allow composite types in bootstrap 3+ messages / 3 participants [nested] [flat]
* [PATCH 2/3] Allow composite types in bootstrap @ 2020-11-17 15:28 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Justin Pryzby @ 2020-11-17 15:28 UTC (permalink / raw) --- src/backend/bootstrap/bootstrap.c | 28 ++++++++++++++++++++++++++++ 1 file changed, 28 insertions(+) diff --git a/src/backend/bootstrap/bootstrap.c b/src/backend/bootstrap/bootstrap.c index 18eb62ca47..e4fc75ab84 100644 --- a/src/backend/bootstrap/bootstrap.c +++ b/src/backend/bootstrap/bootstrap.c @@ -916,6 +916,7 @@ gettype(char *type) { if (Typ != NIL) { + static bool did_reread PG_USED_FOR_ASSERTS_ONLY = false; /* Already reread pg_types */ ListCell *lc; foreach (lc, Typ) @@ -927,6 +928,33 @@ gettype(char *type) return app->am_oid; } } + + /* + * The type wasn't known; check again to handle composite + * types, added since first populating the array. + */ + + /* + * Once all the types are populated and we handled composite + * types, shouldn't need to do that again. + */ + Assert(!did_reread); + did_reread = true; + + list_free_deep(Typ); + Typ = NULL; + populate_typ_array(); + + /* Need to avoid infinite recursion... */ + foreach (lc, Typ) + { + struct typmap *app = lfirst(lc); + if (strncmp(NameStr(app->am_typ.typname), type, NAMEDATALEN) == 0) + { + Ap = app; + return app->am_oid; + } + } } else { -- 2.26.2 --------------9399ACFA5093E21681352885 Content-Type: text/x-patch; charset=UTF-8; name="0003-Extended-statistics-on-expressions-20210117.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0003-Extended-statistics-on-expressions-20210117.patch" ^ permalink raw reply [nested|flat] 3+ messages in thread
* Improve EXPLAIN output for multicolumn B-Tree Index @ 2024-06-21 07:12 [email protected] 0 siblings, 1 reply; 3+ messages in thread From: [email protected] @ 2024-06-21 07:12 UTC (permalink / raw) To: [email protected]; +Cc: [email protected] 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Improve EXPLAIN output for multicolumn B-Tree Index @ 2024-06-27 20:01 Peter Geoghegan <[email protected]> parent: [email protected] 0 siblings, 0 replies; 3+ messages in thread From: Peter Geoghegan @ 2024-06-27 20:01 UTC (permalink / raw) To: [email protected]; +Cc: [email protected]; [email protected] On Fri, Jun 21, 2024 at 3:12 AM <[email protected]> wrote: > 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. I agree that this is a real problem -- I'm not surprised to hear that your customer asked about it. In the past, we've heard complaints about this problem from Markus Winand, too: https://use-the-index-luke.com/sql/explain-plan/postgresql/filter-predicates As it happens I have been thinking about this problem a lot recently. Specifically the user-facing aspects, what we show in EXPLAIN. It is relevant to my ongoing work on skip scan: https://commitfest.postgresql.org/48/5081/ https://www.postgresql.org/message-id/flat/CAH2-Wzmn1YsLzOGgjAQZdn1STSG_y8qP__vggTaPAYXJP+G4bw@mail.... Unfortunately, my patch will make the situation more complicated for your patch. I would like to resolve the tension between the two patches, but I'm not sure how to do that. If you look at the example query that I included in my introductory email on the skip scan thread (the query against the sales_mdam_paper table), you'll see that my patch makes it go much faster. My patch will effectively "convert" nbtree scan keys that would traditionally have to use non-index-bound conditions/filter predicates, into index-bound conditions/access predicates. This all happens at runtime, during nbtree preprocessing (not during planning). This may mean that your patch's approach of determining which columns/scan keys are in which category (bound vs. non-bound) cannot rely on its current approach of placing each type of clause into one of two categories inside btcostestimate() -- the view that we see from btcostestimate() will be made less authoritative by skip scan. What actually matters in what happens during nbtree preprocessing, inside _bt_preprocess_keys(). Unfortunately, this is even more complicated than it sounds. It would be a good idea if we moved _bt_preprocess_keys() to plan time, so that btcostestimate() operated off of authoritative information, rather than independently figuring out the same details for the purposes of costing. We've talked about this before, even [1]. That way your patch could just work off of this authoritative information. But even that doesn't necessarily fix the problem. Note that the skip scan patch makes _bt_preprocess_keys() *indiscriminately* "convert" *all* scan keys to index bound conditions -- at least where that's possible at all. There are minor implementation restrictions that mean that we can't always do that. But overall, the patch more or less eliminates non-bound index conditions. That is, it'll be rare to non-existent for nbtree to fail to mark *any* scan key as SK_BT_REQFWD/SK_BT_REQBKWD. Technically speaking, non-bound conditions mostly won't exist anymore. Of course, this doesn't mean that the problem that your patch is solving will actually go away. I fully expect that the skip scan patch will merely make some scan keys "required-by-scan/index bound condition scan keys in name only". Technically they won't be the problematic kind of index condition, but that won't actually be true in any practical sense. Because users (like your customer) will still get full index scans, and be surprised, just like today. As I explain in my email on the skip scan thread, I believe that the patch's aggressive approach to "converting" scan keys is an advantage. The amount of skipping that actually takes place should be decided dynamically, at runtime. It is a decision that should be made at the level of individual leaf pages (or small groups of leaf pages), not at the level of the whole scan. The distinction between index bound conditions and non-bound conditions becomes much more "squishy", which is mostly (though not entirely) a good thing. I really don't know what to do about this. As I said, I agree with the general idea of this patch -- this is definitely a real problem. And, I don't pretend that my skip scan patch will actually define the problem out of existence (except perhaps in those cases that it actually makes it much faster). Maybe we could make a guess (based on statistics) whether or not any skip attributes will leave the lower-order clauses as useful index bound conditions at runtime. But I don't know...that condition is actually a "continuous" condition now -- it is not a strict dichotomy (it is not either/or, but rather a question of degree, perhaps on a scale of 0.0 - 1.0). It's also possible that we should just do something simple, like your patch, even though technically it won't really be accurate in cases where skip scan is used to good effect. Maybe showing the "default working assumption" about how the scan keys/clauses will behave at runtime is actually the right thing to do. Maybe I am just overthinking it. [1] https://www.postgresql.org/message-id/[email protected] -- the final full paragraph mentions moving _bt_preprocess_keys() into the planner -- Peter Geoghegan ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-06-27 20:01 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-11-17 15:28 [PATCH 2/3] Allow composite types in bootstrap Justin Pryzby <[email protected]> 2024-06-21 07:12 Improve EXPLAIN output for multicolumn B-Tree Index [email protected] 2024-06-27 20:01 ` Re: Improve EXPLAIN output for multicolumn B-Tree Index Peter Geoghegan <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox