On 31.03.2025 23:59, Ilia Evdokimov wrote:
We definitely shouldn’t remove the row counts < 1 check, since there
are many places in the planner where we divide by rows. This mechanism
was added specifically to prevent division by zero. Also, allowing
rows estimates below 1 can sometimes make the planner overly
optimistic, leading it to prefer cheaper-looking plans that may not
perform well in practice. For example, choosing a Nested Loop instead
of a more appropriate Hash Join.
Allowing fractional rows > 1 might help improve planner accuracy in
some cases, but this needs further study to fully understand the impact.
I've been investigating whether it's worth removing rounding in row
estimates - and I believe it is.
[ v1-0001-Always-use-two-fractional-digits-for-estimated-rows_SRC.patch ]
Currently, we round most row estimates using rint() inside
clamp_row_est(). However, this function is also used for rounding tuples
and page counts. These should remain integral, but row estimates can and
should remain fractional for better precision. To address this, I
introduced a new function clamp_tuple_est() which retains the existing
rounding behavior (via rint()), while clamp_tuple_est() no longer
rounds. I use clamp_tuple_est() only for row estimates and
clamp_tuple_est() for tuples and pages elsewhere.
After removing rounding, two small issues needed fixing. First, there
was a check rows > 1 in the cost estimation for Nested Loop joins, which
is no longer reliable for values like 1.3. I updated it to rows >= 2 to
retain the original behavior. This can be refined further, but, in my
opinion, it's a practical compromise. Second, there is still a call to
rint() in cost of mergejoin which likely should be removed too - though
I haven’t included that here yet.
Also, if we're no longer rounding estimates, EXPLAIN should display them
with two decimal digits, just like it already does for actual rows.
[ v1-0002-Always-use-two-fractional-digits-for-estimated-rows_TESTS.patch ]
So, what does this change improve? Here are some of the observed plan
improvements:
* Previously, a Parallel Aggregate was chosen. With slightly more
accurate estimation, the planner switches to FinalizeAggregate,
which can be more efficient in distributed plans.
* In certain nested joins with constant subqueries, the planner
previously inserted an unnecessary Materialize. With improved
estimates, it now skips that step, reducing memory usage and latency.
* When the estimated number of iterations becomes non-integer but
still justifies caching, the planner adds Memoize instead of
re-running a function like generate_series(). This can speed up
execution significantly.
* In one case involving partitioned tables and filter conditions like
t1.b = 0, the planner now chooses an index-based nested loop join
instead of a hash join. This results in a more efficient plan with
fewer memory and CPU costs.
I know this patch still needs documentation updates to describe the new
estimation display and behaviors. But before that, I’d like to gather
feedback: does community agree that more precise estimates and
fractional values are better than always rounding?
If anyone would like to see the EXPLAIN ANALYZE VERBOSE output of
changes in regression tests, I’d be happy to share them.
All feedback and suggestions welcome!
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.
From a042ee22dbdae5049f4830116e83a3a72ea5e405 Mon Sep 17 00:00:00 2001
From: Evdokimov Ilia <ilya.evdoki...@tantorlabs.com>
Date: Wed, 9 Apr 2025 22:24:01 +0300
Subject: [PATCH v1] Always use two fractional digits for estimated rows count
---
contrib/file_fdw/file_fdw.c | 4 +-
contrib/postgres_fdw/postgres_fdw.c | 4 +-
contrib/tsm_system_rows/tsm_system_rows.c | 4 +-
contrib/tsm_system_time/tsm_system_time.c | 4 +-
src/backend/access/table/tableam.c | 2 +-
src/backend/access/tablesample/bernoulli.c | 2 +-
src/backend/access/tablesample/system.c | 4 +-
src/backend/commands/explain.c | 4 +-
src/backend/optimizer/path/costsize.c | 53 +++++++++++++++-------
src/backend/optimizer/util/pathnode.c | 4 +-
src/backend/utils/adt/selfuncs.c | 14 +++---
src/include/optimizer/optimizer.h | 1 +
12 files changed, 61 insertions(+), 39 deletions(-)
diff --git a/contrib/file_fdw/file_fdw.c b/contrib/file_fdw/file_fdw.c
index a9a5671d95a..682bb86adde 100644
--- a/contrib/file_fdw/file_fdw.c
+++ b/contrib/file_fdw/file_fdw.c
@@ -1097,7 +1097,7 @@ estimate_size(PlannerInfo *root, RelOptInfo *baserel,
double density;
density = baserel->tuples / (double) baserel->pages;
- ntuples = clamp_row_est(density * (double) pages);
+ ntuples = clamp_tuple_est(density * (double) pages);
}
else
{
@@ -1113,7 +1113,7 @@ estimate_size(PlannerInfo *root, RelOptInfo *baserel,
tuple_width = MAXALIGN(baserel->reltarget->width) +
MAXALIGN(SizeofHeapTupleHeader);
- ntuples = clamp_row_est((double) stat_buf.st_size /
+ ntuples = clamp_tuple_est((double) stat_buf.st_size /
(double) tuple_width);
}
fdw_private->ntuples = ntuples;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index a7e0cc9f323..5e1c7bb32e1 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -3278,7 +3278,7 @@ estimate_path_cost_size(PlannerInfo *root,
* Back into an estimate of the number of retrieved rows. Just in
* case this is nuts, clamp to at most nrows.
*/
- retrieved_rows = clamp_row_est(rows / fpinfo->local_conds_sel);
+ retrieved_rows = clamp_tuple_est(rows / fpinfo->local_conds_sel);
retrieved_rows = Min(retrieved_rows, nrows);
/*
@@ -3453,7 +3453,7 @@ estimate_path_cost_size(PlannerInfo *root,
* Back into an estimate of the number of retrieved rows. Just in
* case this is nuts, clamp to at most foreignrel->tuples.
*/
- retrieved_rows = clamp_row_est(rows / fpinfo->local_conds_sel);
+ retrieved_rows = clamp_tuple_est(rows / fpinfo->local_conds_sel);
retrieved_rows = Min(retrieved_rows, foreignrel->tuples);
/*
diff --git a/contrib/tsm_system_rows/tsm_system_rows.c b/contrib/tsm_system_rows/tsm_system_rows.c
index f401efa2131..308bb301da1 100644
--- a/contrib/tsm_system_rows/tsm_system_rows.c
+++ b/contrib/tsm_system_rows/tsm_system_rows.c
@@ -135,7 +135,7 @@ system_rows_samplescangetsamplesize(PlannerInfo *root,
/* Clamp to the estimated relation size */
if (ntuples > baserel->tuples)
ntuples = (int64) baserel->tuples;
- ntuples = clamp_row_est(ntuples);
+ ntuples = clamp_tuple_est(ntuples);
if (baserel->tuples > 0 && baserel->pages > 0)
{
@@ -151,7 +151,7 @@ system_rows_samplescangetsamplesize(PlannerInfo *root,
}
/* Clamp to sane value */
- npages = clamp_row_est(Min((double) baserel->pages, npages));
+ npages = clamp_tuple_est(Min((double) baserel->pages, npages));
*pages = npages;
*tuples = ntuples;
diff --git a/contrib/tsm_system_time/tsm_system_time.c b/contrib/tsm_system_time/tsm_system_time.c
index c9c71d8c3af..b2d1d7e37d7 100644
--- a/contrib/tsm_system_time/tsm_system_time.c
+++ b/contrib/tsm_system_time/tsm_system_time.c
@@ -151,7 +151,7 @@ system_time_samplescangetsamplesize(PlannerInfo *root,
npages = millis; /* even more bogus, but whatcha gonna do? */
/* Clamp to sane value */
- npages = clamp_row_est(Min((double) baserel->pages, npages));
+ npages = clamp_tuple_est(Min((double) baserel->pages, npages));
if (baserel->tuples > 0 && baserel->pages > 0)
{
@@ -167,7 +167,7 @@ system_time_samplescangetsamplesize(PlannerInfo *root,
}
/* Clamp to the estimated relation size */
- ntuples = clamp_row_est(Min(baserel->tuples, ntuples));
+ ntuples = clamp_tuple_est(Min(baserel->tuples, ntuples));
*pages = npages;
*tuples = ntuples;
diff --git a/src/backend/access/table/tableam.c b/src/backend/access/table/tableam.c
index a56c5eceb14..e14a315f319 100644
--- a/src/backend/access/table/tableam.c
+++ b/src/backend/access/table/tableam.c
@@ -742,7 +742,7 @@ table_block_relation_estimate_size(Relation rel, int32 *attr_widths,
/* note: integer division is intentional here */
density = (usable_bytes_per_page * fillfactor / 100) / tuple_width;
/* There's at least one row on the page, even with low fillfactor. */
- density = clamp_row_est(density);
+ density = clamp_tuple_est(density);
}
*tuples = rint(density * (double) curpages);
diff --git a/src/backend/access/tablesample/bernoulli.c b/src/backend/access/tablesample/bernoulli.c
index 5e1c5d2b723..d1993299158 100644
--- a/src/backend/access/tablesample/bernoulli.c
+++ b/src/backend/access/tablesample/bernoulli.c
@@ -117,7 +117,7 @@ bernoulli_samplescangetsamplesize(PlannerInfo *root,
/* We'll visit all pages of the baserel */
*pages = baserel->pages;
- *tuples = clamp_row_est(baserel->tuples * samplefract);
+ *tuples = clamp_tuple_est(baserel->tuples * samplefract);
}
/*
diff --git a/src/backend/access/tablesample/system.c b/src/backend/access/tablesample/system.c
index 8db813b89fc..8944fe28799 100644
--- a/src/backend/access/tablesample/system.c
+++ b/src/backend/access/tablesample/system.c
@@ -117,10 +117,10 @@ system_samplescangetsamplesize(PlannerInfo *root,
}
/* We'll visit a sample of the pages ... */
- *pages = clamp_row_est(baserel->pages * samplefract);
+ *pages = clamp_tuple_est(baserel->pages * samplefract);
/* ... and hopefully get a representative number of tuples from them */
- *tuples = clamp_row_est(baserel->tuples * samplefract);
+ *tuples = clamp_tuple_est(baserel->tuples * samplefract);
}
/*
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index ef8aa489af8..9d9da4880a3 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1808,7 +1808,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
{
if (es->format == EXPLAIN_FORMAT_TEXT)
{
- appendStringInfo(es->str, " (cost=%.2f..%.2f rows=%.0f width=%d)",
+ appendStringInfo(es->str, " (cost=%.2f..%.2f rows=%.2f width=%d)",
plan->startup_cost, plan->total_cost,
plan->plan_rows, plan->plan_width);
}
@@ -1819,7 +1819,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
ExplainPropertyFloat("Total Cost", NULL, plan->total_cost,
2, es);
ExplainPropertyFloat("Plan Rows", NULL, plan->plan_rows,
- 0, es);
+ 2, es);
ExplainPropertyInteger("Plan Width", NULL, plan->plan_width,
es);
}
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 60b0fcfb6be..27f8d70eb2c 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -205,6 +205,29 @@ static double page_size(double tuples, int width);
static double get_parallel_divisor(Path *path);
+/*
+ * clamp_tuple_est
+ * Force a tuple-count estimate to a sane value.
+ */
+double
+clamp_tuple_est(double ntuples)
+{
+ /*
+ * Avoid infinite and NaN tuple estimates. Costs derived from such values
+ * are going to be useless. Also force the estimate to be at least one
+ * tuple, to make explain output look better and to avoid possible
+ * divide-by-zero when interpolating costs. Make it an integer, too.
+ */
+ if (ntuples > MAXIMUM_ROWCOUNT || isnan(ntuples))
+ ntuples = MAXIMUM_ROWCOUNT;
+ else if (ntuples <= 1.0)
+ ntuples = 1.0;
+ else
+ ntuples = rint(ntuples);
+
+ return ntuples;
+}
+
/*
* clamp_row_est
* Force a row-count estimate to a sane value.
@@ -216,14 +239,12 @@ clamp_row_est(double nrows)
* Avoid infinite and NaN row estimates. Costs derived from such values
* are going to be useless. Also force the estimate to be at least one
* row, to make explain output look better and to avoid possible
- * divide-by-zero when interpolating costs. Make it an integer, too.
+ * divide-by-zero when interpolating costs.
*/
if (nrows > MAXIMUM_ROWCOUNT || isnan(nrows))
nrows = MAXIMUM_ROWCOUNT;
else if (nrows <= 1.0)
nrows = 1.0;
- else
- nrows = rint(nrows);
return nrows;
}
@@ -249,7 +270,7 @@ clamp_width_est(int64 tuple_width)
return (int32) MaxAllocSize;
/*
- * Unlike clamp_row_est, we just Assert that the value isn't negative,
+ * Unlike clamp_tuple_est, we just Assert that the value isn't negative,
* rather than masking such errors.
*/
Assert(tuple_width >= 0);
@@ -643,7 +664,7 @@ cost_index(IndexPath *path, PlannerInfo *root, double loop_count,
run_cost += indexTotalCost - indexStartupCost;
/* estimate number of main-table tuples fetched */
- tuples_fetched = clamp_row_est(indexSelectivity * baserel->tuples);
+ tuples_fetched = clamp_tuple_est(indexSelectivity * baserel->tuples);
/* fetch estimated page costs for tablespace containing table */
get_tablespace_page_costs(baserel->reltablespace,
@@ -901,7 +922,7 @@ extract_nonindex_conditions(List *qual_clauses, List *indexclauses)
* computed for us by make_one_rel.
*
* Caller is expected to have ensured that tuples_fetched is greater than zero
- * and rounded to integer (see clamp_row_est). The result will likewise be
+ * and rounded to integer (see clamp_tuple_est). The result will likewise be
* greater than zero and integral.
*/
double
@@ -3084,7 +3105,7 @@ get_windowclause_startup_tuples(PlannerInfo *root, WindowClause *wc,
* subnode.
*/
- return clamp_row_est(return_tuples);
+ return clamp_tuple_est(return_tuples);
}
/*
@@ -3298,7 +3319,7 @@ initial_cost_nestloop(PlannerInfo *root, JoinCostWorkspace *workspace,
*/
startup_cost += outer_path->startup_cost + inner_path->startup_cost;
run_cost += outer_path->total_cost - outer_path->startup_cost;
- if (outer_path_rows > 1)
+ if (outer_path_rows >= 2)
run_cost += (outer_path_rows - 1) * inner_rescan_start_cost;
inner_run_cost = inner_path->total_cost - inner_path->startup_cost;
@@ -3323,7 +3344,7 @@ initial_cost_nestloop(PlannerInfo *root, JoinCostWorkspace *workspace,
{
/* Normal case; we'll scan whole input rel for each outer row */
run_cost += inner_run_cost;
- if (outer_path_rows > 1)
+ if (outer_path_rows >= 2)
run_cost += (outer_path_rows - 1) * inner_rescan_run_cost;
}
@@ -3656,8 +3677,8 @@ initial_cost_mergejoin(PlannerInfo *root, JoinCostWorkspace *workspace,
* Convert selectivities to row counts. We force outer_rows and
* inner_rows to be at least 1, but the skip_rows estimates can be zero.
*/
- outer_skip_rows = rint(outer_path_rows * outerstartsel);
- inner_skip_rows = rint(inner_path_rows * innerstartsel);
+ outer_skip_rows = outer_path_rows * outerstartsel;
+ inner_skip_rows = inner_path_rows * innerstartsel;
outer_rows = clamp_row_est(outer_path_rows * outerendsel);
inner_rows = clamp_row_est(inner_path_rows * innerendsel);
@@ -4415,7 +4436,7 @@ final_cost_hashjoin(PlannerInfo *root, HashPath *path,
* that way, so it will be unable to drive the batch size below hash_mem
* when this is true.)
*/
- if (relation_byte_size(clamp_row_est(inner_path_rows * innermcvfreq),
+ if (relation_byte_size(clamp_tuple_est(inner_path_rows * innermcvfreq),
inner_path->pathtarget->width) > get_hash_memory_limit())
startup_cost += disable_cost;
@@ -4449,7 +4470,7 @@ final_cost_hashjoin(PlannerInfo *root, HashPath *path,
* to clamp inner_scan_frac to at most 1.0; but since match_count is
* at least 1, no such clamp is needed now.)
*/
- outer_matched_rows = rint(outer_path_rows * extra->semifactors.outer_match_frac);
+ outer_matched_rows = outer_path_rows * extra->semifactors.outer_match_frac;
inner_scan_frac = 2.0 / (extra->semifactors.match_count + 1.0);
startup_cost += hash_qual_cost.startup;
@@ -4573,7 +4594,7 @@ cost_subplan(PlannerInfo *root, SubPlan *subplan, Plan *plan)
if (subplan->subLinkType == EXISTS_SUBLINK)
{
/* we only need to fetch 1 tuple; clamp to avoid zero divide */
- sp_cost.per_tuple += plan_run_cost / clamp_row_est(plan->plan_rows);
+ sp_cost.per_tuple += plan_run_cost / clamp_tuple_est(plan->plan_rows);
}
else if (subplan->subLinkType == ALL_SUBLINK ||
subplan->subLinkType == ANY_SUBLINK)
@@ -6517,7 +6538,7 @@ compute_bitmap_pages(PlannerInfo *root, RelOptInfo *baserel,
/*
* Estimate number of main-table pages fetched.
*/
- tuples_fetched = clamp_row_est(indexSelectivity * baserel->tuples);
+ tuples_fetched = clamp_tuple_est(indexSelectivity * baserel->tuples);
T = (baserel->pages > 1) ? (double) baserel->pages : 1.0;
@@ -6583,7 +6604,7 @@ compute_bitmap_pages(PlannerInfo *root, RelOptInfo *baserel,
*/
if (lossy_pages > 0)
tuples_fetched =
- clamp_row_est(indexSelectivity *
+ clamp_tuple_est(indexSelectivity *
(exact_pages / heap_pages) * baserel->tuples +
(lossy_pages / heap_pages) * baserel->tuples);
}
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 93e73cb44db..0b9ec6b26fc 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1687,7 +1687,7 @@ create_memoize_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
pathnode->param_exprs = param_exprs;
pathnode->singlerow = singlerow;
pathnode->binary_mode = binary_mode;
- pathnode->calls = clamp_row_est(calls);
+ pathnode->calls = clamp_tuple_est(calls);
/*
* For now we set est_entries to 0. cost_memoize_rescan() does all the
@@ -4049,7 +4049,7 @@ adjust_limit_rows_costs(double *rows, /* in/out parameter */
if (offset_est > 0)
offset_rows = (double) offset_est;
else
- offset_rows = clamp_row_est(input_rows * 0.10);
+ offset_rows = clamp_tuple_est(input_rows * 0.10);
if (offset_rows > *rows)
offset_rows = *rows;
if (input_rows > 0)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 588d991fa57..0c077c09175 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -2183,7 +2183,7 @@ estimate_array_length(PlannerInfo *root, Node *arrayexpr)
ATTSTATSSLOT_NUMBERS))
{
if (sslot.nnumbers > 0)
- nelem = clamp_row_est(sslot.numbers[sslot.nnumbers - 1]);
+ nelem = clamp_tuple_est(sslot.numbers[sslot.nnumbers - 1]);
free_attstatsslot(&sslot);
}
}
@@ -3462,7 +3462,7 @@ estimate_num_groups(PlannerInfo *root, List *groupExprs, double input_rows,
* estimate is usually already at least 1, but clamp it just in case it
* isn't.
*/
- input_rows = clamp_row_est(input_rows);
+ input_rows = clamp_tuple_est(input_rows);
/*
* If no grouping columns, there's exactly one group. (This can't happen
@@ -3755,7 +3755,7 @@ estimate_num_groups(PlannerInfo *root, List *groupExprs, double input_rows,
(1 - pow((rel->tuples - rel->rows) / rel->tuples,
rel->tuples / reldistinct));
}
- reldistinct = clamp_row_est(reldistinct);
+ reldistinct = clamp_tuple_est(reldistinct);
/*
* Update estimate of total distinct groups.
@@ -4071,7 +4071,7 @@ estimate_hash_bucket_stats(PlannerInfo *root, Node *hashkey, double nbuckets,
if (vardata.rel && vardata.rel->tuples > 0)
{
ndistinct *= vardata.rel->rows / vardata.rel->tuples;
- ndistinct = clamp_row_est(ndistinct);
+ ndistinct = clamp_tuple_est(ndistinct);
}
/*
@@ -6168,7 +6168,7 @@ get_variable_numdistinct(VariableStatData *vardata, bool *isdefault)
* If we had an absolute estimate, use that.
*/
if (stadistinct > 0.0)
- return clamp_row_est(stadistinct);
+ return clamp_tuple_est(stadistinct);
/*
* Otherwise we need to get the relation size; punt if not available.
@@ -6189,7 +6189,7 @@ get_variable_numdistinct(VariableStatData *vardata, bool *isdefault)
* If we had a relative estimate, use that.
*/
if (stadistinct < 0.0)
- return clamp_row_est(-stadistinct * ntuples);
+ return clamp_tuple_est(-stadistinct * ntuples);
/*
* With no data, estimate ndistinct = ntuples if the table is small, else
@@ -6197,7 +6197,7 @@ get_variable_numdistinct(VariableStatData *vardata, bool *isdefault)
* that the behavior isn't discontinuous.
*/
if (ntuples < DEFAULT_NUM_DISTINCT)
- return clamp_row_est(ntuples);
+ return clamp_tuple_est(ntuples);
*isdefault = true;
return DEFAULT_NUM_DISTINCT;
diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h
index 546828b54bd..e1be9dc6175 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -90,6 +90,7 @@ extern PGDLLIMPORT double recursive_worktable_factor;
extern PGDLLIMPORT int effective_cache_size;
extern double clamp_row_est(double nrows);
+extern double clamp_tuple_est(double ntuples);
extern int32 clamp_width_est(int64 tuple_width);
extern long clamp_cardinality_to_long(Cardinality x);
--
2.34.1
From a34fd63c47a87e79d6b0fe0a705c527a5b2b22bc Mon Sep 17 00:00:00 2001
From: Evdokimov Ilia <ilya.evdoki...@tantorlabs.com>
Date: Wed, 9 Apr 2025 22:26:16 +0300
Subject: [PATCH v1] Always use two fractional digits for estimated rows count
---
.../expected/pg_overexplain.out | 12 +-
.../postgres_fdw/expected/postgres_fdw.out | 15 +-
src/test/regress/expected/explain.out | 146 +++++++++---------
src/test/regress/expected/inherit.out | 8 +-
src/test/regress/expected/join.out | 134 ++++++++--------
src/test/regress/expected/join_hash.out | 19 +--
src/test/regress/expected/misc_functions.out | 84 +++++-----
src/test/regress/expected/partition_join.out | 112 +++++++-------
src/test/regress/expected/select_views.out | 19 ++-
src/test/regress/expected/stats_ext.out | 12 +-
src/test/regress/sql/stats_ext.sql | 4 +-
11 files changed, 277 insertions(+), 288 deletions(-)
diff --git a/contrib/pg_overexplain/expected/pg_overexplain.out b/contrib/pg_overexplain/expected/pg_overexplain.out
index 28252dbff6c..731a0bf79a2 100644
--- a/contrib/pg_overexplain/expected/pg_overexplain.out
+++ b/contrib/pg_overexplain/expected/pg_overexplain.out
@@ -25,9 +25,9 @@ ERROR: unrecognized EXPLAIN option "debuff"
LINE 1: EXPLAIN (DEBUFF) SELECT 1;
^
EXPLAIN (DEBUG) SELECT 1;
- QUERY PLAN
-------------------------------------------
- Result (cost=0.00..0.01 rows=1 width=4)
+ QUERY PLAN
+---------------------------------------------
+ Result (cost=0.00..0.01 rows=1.00 width=4)
Disabled Nodes: 0
Parallel Safe: false
Plan Node ID: 0
@@ -41,9 +41,9 @@ EXPLAIN (DEBUG) SELECT 1;
(11 rows)
EXPLAIN (RANGE_TABLE) SELECT 1;
- QUERY PLAN
-------------------------------------------
- Result (cost=0.00..0.01 rows=1 width=4)
+ QUERY PLAN
+---------------------------------------------
+ Result (cost=0.00..0.01 rows=1.00 width=4)
RTI 1 (result):
Eref: "*RESULT*" ()
(3 rows)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index d1acee5a5fa..e5b92d2017b 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10151,13 +10151,16 @@ SELECT t1.a,t2.b,t3.c FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) INNER J
-- left outer join + nullable clause
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10) t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a < 10 ORDER BY 1,2,3;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Foreign Scan
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Sort
Output: t1.a, fprt2.b, fprt2.c
- Relations: (public.ftprt1_p1 t1) LEFT JOIN (public.ftprt2_p1 fprt2)
- Remote SQL: SELECT r5.a, r6.b, r6.c FROM (public.fprt1_p1 r5 LEFT JOIN public.fprt2_p1 r6 ON (((r5.a = r6.b)) AND ((r5.b = r6.a)) AND ((r6.a < 10)))) WHERE ((r5.a < 10)) ORDER BY r5.a ASC NULLS LAST, r6.b ASC NULLS LAST, r6.c ASC NULLS LAST
-(4 rows)
+ Sort Key: t1.a, fprt2.b, fprt2.c
+ -> Foreign Scan
+ Output: t1.a, fprt2.b, fprt2.c
+ Relations: (public.ftprt1_p1 t1) LEFT JOIN (public.ftprt2_p1 fprt2)
+ Remote SQL: SELECT r5.a, r6.b, r6.c FROM (public.fprt1_p1 r5 LEFT JOIN public.fprt2_p1 r6 ON (((r5.a = r6.b)) AND ((r5.b = r6.a)) AND ((r6.a < 10)))) WHERE ((r5.a < 10))
+(7 rows)
SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10) t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a < 10 ORDER BY 1,2,3;
a | b | c
diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out
index c53bf9c8aa3..28991ccb665 100644
--- a/src/test/regress/expected/explain.out
+++ b/src/test/regress/expected/explain.out
@@ -55,32 +55,32 @@ set jit = off;
set track_io_timing = off;
-- Simple cases
select explain_filter('explain select * from int8_tbl i8');
- explain_filter
----------------------------------------------------------
- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N)
+ explain_filter
+-----------------------------------------------------------
+ Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N.N width=N)
(1 row)
select explain_filter('explain (analyze, buffers off) select * from int8_tbl i8');
- explain_filter
--------------------------------------------------------------------------------------------------
- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N.N loops=N)
+ explain_filter
+---------------------------------------------------------------------------------------------------
+ Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N.N width=N) (actual time=N.N..N.N rows=N.N loops=N)
Planning Time: N.N ms
Execution Time: N.N ms
(3 rows)
select explain_filter('explain (analyze, buffers off, verbose) select * from int8_tbl i8');
- explain_filter
---------------------------------------------------------------------------------------------------------
- Seq Scan on public.int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N.N loops=N)
+ explain_filter
+----------------------------------------------------------------------------------------------------------
+ Seq Scan on public.int8_tbl i8 (cost=N.N..N.N rows=N.N width=N) (actual time=N.N..N.N rows=N.N loops=N)
Output: q1, q2
Planning Time: N.N ms
Execution Time: N.N ms
(4 rows)
select explain_filter('explain (analyze, buffers, format text) select * from int8_tbl i8');
- explain_filter
--------------------------------------------------------------------------------------------------
- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N.N loops=N)
+ explain_filter
+---------------------------------------------------------------------------------------------------
+ Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N.N width=N) (actual time=N.N..N.N rows=N.N loops=N)
Planning Time: N.N ms
Execution Time: N.N ms
(3 rows)
@@ -98,7 +98,7 @@ select explain_filter('explain (analyze, buffers, format xml) select * from int8
<Alias>i8</Alias> +
<Startup-Cost>N.N</Startup-Cost> +
<Total-Cost>N.N</Total-Cost> +
- <Plan-Rows>N</Plan-Rows> +
+ <Plan-Rows>N.N</Plan-Rows> +
<Plan-Width>N</Plan-Width> +
<Actual-Startup-Time>N.N</Actual-Startup-Time> +
<Actual-Total-Time>N.N</Actual-Total-Time> +
@@ -147,7 +147,7 @@ select explain_filter('explain (analyze, serialize, buffers, format yaml) select
Alias: "i8" +
Startup Cost: N.N +
Total Cost: N.N +
- Plan Rows: N +
+ Plan Rows: N.N +
Plan Width: N +
Actual Startup Time: N.N +
Actual Total Time: N.N +
@@ -195,9 +195,9 @@ select explain_filter('explain (analyze, serialize, buffers, format yaml) select
(1 row)
select explain_filter('explain (buffers, format text) select * from int8_tbl i8');
- explain_filter
----------------------------------------------------------
- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N)
+ explain_filter
+-----------------------------------------------------------
+ Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N.N width=N)
(1 row)
select explain_filter('explain (buffers, format json) select * from int8_tbl i8');
@@ -213,7 +213,7 @@ select explain_filter('explain (buffers, format json) select * from int8_tbl i8'
"Alias": "i8", +
"Startup Cost": N.N, +
"Total Cost": N.N, +
- "Plan Rows": N, +
+ "Plan Rows": N.N, +
"Plan Width": N, +
"Disabled": false, +
"Shared Hit Blocks": N, +
@@ -247,35 +247,35 @@ select explain_filter('explain (buffers, format json) select * from int8_tbl i8'
select explain_filter('explain verbose select sum(unique1) over w, sum(unique2) over (w order by hundred), sum(tenthous) over (w order by hundred) from tenk1 window w as (partition by ten)');
explain_filter
-------------------------------------------------------------------------------------------------------
- WindowAgg (cost=N.N..N.N rows=N width=N)
+ WindowAgg (cost=N.N..N.N rows=N.N width=N)
Output: sum(unique1) OVER w, (sum(unique2) OVER w1), (sum(tenthous) OVER w1), ten, hundred
Window: w AS (PARTITION BY tenk1.ten)
- -> WindowAgg (cost=N.N..N.N rows=N width=N)
+ -> WindowAgg (cost=N.N..N.N rows=N.N width=N)
Output: ten, hundred, unique1, unique2, tenthous, sum(unique2) OVER w1, sum(tenthous) OVER w1
Window: w1 AS (PARTITION BY tenk1.ten ORDER BY tenk1.hundred)
- -> Sort (cost=N.N..N.N rows=N width=N)
+ -> Sort (cost=N.N..N.N rows=N.N width=N)
Output: ten, hundred, unique1, unique2, tenthous
Sort Key: tenk1.ten, tenk1.hundred
- -> Seq Scan on public.tenk1 (cost=N.N..N.N rows=N width=N)
+ -> Seq Scan on public.tenk1 (cost=N.N..N.N rows=N.N width=N)
Output: ten, hundred, unique1, unique2, tenthous
(11 rows)
select explain_filter('explain verbose select sum(unique1) over w1, sum(unique2) over (w1 order by hundred), sum(tenthous) over (w1 order by hundred rows 10 preceding) from tenk1 window w1 as (partition by ten)');
explain_filter
---------------------------------------------------------------------------------------------------------
- WindowAgg (cost=N.N..N.N rows=N width=N)
+ WindowAgg (cost=N.N..N.N rows=N.N width=N)
Output: sum(unique1) OVER w1, (sum(unique2) OVER w2), (sum(tenthous) OVER w3), ten, hundred
Window: w1 AS (PARTITION BY tenk1.ten)
- -> WindowAgg (cost=N.N..N.N rows=N width=N)
+ -> WindowAgg (cost=N.N..N.N rows=N.N width=N)
Output: ten, hundred, unique1, unique2, tenthous, (sum(unique2) OVER w2), sum(tenthous) OVER w3
Window: w3 AS (PARTITION BY tenk1.ten ORDER BY tenk1.hundred ROWS 'N'::bigint PRECEDING)
- -> WindowAgg (cost=N.N..N.N rows=N width=N)
+ -> WindowAgg (cost=N.N..N.N rows=N.N width=N)
Output: ten, hundred, unique1, unique2, tenthous, sum(unique2) OVER w2
Window: w2 AS (PARTITION BY tenk1.ten ORDER BY tenk1.hundred)
- -> Sort (cost=N.N..N.N rows=N width=N)
+ -> Sort (cost=N.N..N.N rows=N.N width=N)
Output: ten, hundred, unique1, unique2, tenthous
Sort Key: tenk1.ten, tenk1.hundred
- -> Seq Scan on public.tenk1 (cost=N.N..N.N rows=N width=N)
+ -> Seq Scan on public.tenk1 (cost=N.N..N.N rows=N.N width=N)
Output: ten, hundred, unique1, unique2, tenthous
(14 rows)
@@ -295,7 +295,7 @@ select explain_filter('explain (analyze, buffers, format json) select * from int
"Alias": "i8", +
"Startup Cost": N.N, +
"Total Cost": N.N, +
- "Plan Rows": N, +
+ "Plan Rows": N.N, +
"Plan Width": N, +
"Actual Startup Time": N.N, +
"Actual Total Time": N.N, +
@@ -368,11 +368,11 @@ select explain_filter_to_json('explain (settings, format json) select * from int
rollback;
-- GENERIC_PLAN option
select explain_filter('explain (generic_plan) select unique1 from tenk1 where thousand = $1');
- explain_filter
----------------------------------------------------------------------------------
- Bitmap Heap Scan on tenk1 (cost=N.N..N.N rows=N width=N)
+ explain_filter
+-----------------------------------------------------------------------------------
+ Bitmap Heap Scan on tenk1 (cost=N.N..N.N rows=N.N width=N)
Recheck Cond: (thousand = $N)
- -> Bitmap Index Scan on tenk1_thous_tenthous (cost=N.N..N.N rows=N width=N)
+ -> Bitmap Index Scan on tenk1_thous_tenthous (cost=N.N..N.N rows=N.N width=N)
Index Cond: (thousand = $N)
(4 rows)
@@ -382,16 +382,16 @@ ERROR: EXPLAIN options ANALYZE and GENERIC_PLAN cannot be used together
CONTEXT: PL/pgSQL function explain_filter(text) line 5 at FOR over EXECUTE statement
-- MEMORY option
select explain_filter('explain (memory) select * from int8_tbl i8');
- explain_filter
----------------------------------------------------------
- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N)
+ explain_filter
+-----------------------------------------------------------
+ Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N.N width=N)
Memory: used=NkB allocated=NkB
(2 rows)
select explain_filter('explain (memory, analyze, buffers off) select * from int8_tbl i8');
- explain_filter
--------------------------------------------------------------------------------------------------
- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N.N loops=N)
+ explain_filter
+---------------------------------------------------------------------------------------------------
+ Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N.N width=N) (actual time=N.N..N.N rows=N.N loops=N)
Memory: used=NkB allocated=NkB
Planning Time: N.N ms
Execution Time: N.N ms
@@ -408,7 +408,7 @@ select explain_filter('explain (memory, summary, format yaml) select * from int8
Alias: "i8" +
Startup Cost: N.N +
Total Cost: N.N +
- Plan Rows: N +
+ Plan Rows: N.N +
Plan Width: N +
Disabled: false +
Planning: +
@@ -430,7 +430,7 @@ select explain_filter('explain (memory, analyze, format json) select * from int8
"Alias": "i8", +
"Startup Cost": N.N, +
"Total Cost": N.N, +
- "Plan Rows": N, +
+ "Plan Rows": N.N, +
"Plan Width": N, +
"Actual Startup Time": N.N, +
"Actual Total Time": N.N, +
@@ -472,9 +472,9 @@ select explain_filter('explain (memory, analyze, format json) select * from int8
prepare int8_query as select * from int8_tbl i8;
select explain_filter('explain (memory) execute int8_query');
- explain_filter
----------------------------------------------------------
- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N)
+ explain_filter
+-----------------------------------------------------------
+ Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N.N width=N)
Memory: used=NkB allocated=NkB
(2 rows)
@@ -496,12 +496,12 @@ create table gen_part_2
partition of gen_part for values in (2);
-- should scan gen_part_1_1 and gen_part_1_2, but not gen_part_2
select explain_filter('explain (generic_plan) select key1, key2 from gen_part where key1 = 1 and key2 = $1');
- explain_filter
----------------------------------------------------------------------------
- Append (cost=N.N..N.N rows=N width=N)
- -> Seq Scan on gen_part_1_1 gen_part_1 (cost=N.N..N.N rows=N width=N)
+ explain_filter
+-----------------------------------------------------------------------------
+ Append (cost=N.N..N.N rows=N.N width=N)
+ -> Seq Scan on gen_part_1_1 gen_part_1 (cost=N.N..N.N rows=N.N width=N)
Filter: ((key1 = N) AND (key2 = $N))
- -> Seq Scan on gen_part_1_2 gen_part_2 (cost=N.N..N.N rows=N width=N)
+ -> Seq Scan on gen_part_1_2 gen_part_2 (cost=N.N..N.N rows=N.N width=N)
Filter: ((key1 = N) AND (key2 = $N))
(5 rows)
@@ -561,7 +561,7 @@ select jsonb_pretty(
"Schema": "public", +
"Disabled": false, +
"Node Type": "Seq Scan", +
- "Plan Rows": 0, +
+ "Plan Rows": 0.0, +
"Plan Width": 0, +
"Total Cost": 0.0, +
"Actual Rows": 0.0, +
@@ -608,7 +608,7 @@ select jsonb_pretty(
"tenk1.tenthous" +
], +
"Node Type": "Sort", +
- "Plan Rows": 0, +
+ "Plan Rows": 0.0, +
"Plan Width": 0, +
"Total Cost": 0.0, +
"Actual Rows": 0.0, +
@@ -652,7 +652,7 @@ select jsonb_pretty(
], +
"Disabled": false, +
"Node Type": "Gather Merge", +
- "Plan Rows": 0, +
+ "Plan Rows": 0.0, +
"Plan Width": 0, +
"Total Cost": 0.0, +
"Actual Rows": 0.0, +
@@ -703,7 +703,7 @@ as 'begin return sin($1); end';
select explain_filter('explain (verbose) select * from t1 where pg_temp.mysin(f1) < 0.5');
explain_filter
------------------------------------------------------------
- Seq Scan on pg_temp.t1 (cost=N.N..N.N rows=N width=N)
+ Seq Scan on pg_temp.t1 (cost=N.N..N.N rows=N.N width=N)
Output: f1
Filter: (pg_temp.mysin(t1.f1) < 'N.N'::double precision)
(3 rows)
@@ -711,53 +711,53 @@ select explain_filter('explain (verbose) select * from t1 where pg_temp.mysin(f1
-- Test compute_query_id
set compute_query_id = on;
select explain_filter('explain (verbose) select * from int8_tbl i8');
- explain_filter
-----------------------------------------------------------------
- Seq Scan on public.int8_tbl i8 (cost=N.N..N.N rows=N width=N)
+ explain_filter
+------------------------------------------------------------------
+ Seq Scan on public.int8_tbl i8 (cost=N.N..N.N rows=N.N width=N)
Output: q1, q2
Query Identifier: N
(3 rows)
-- Test compute_query_id with utility statements containing plannable query
select explain_filter('explain (verbose) declare test_cur cursor for select * from int8_tbl');
- explain_filter
--------------------------------------------------------------
- Seq Scan on public.int8_tbl (cost=N.N..N.N rows=N width=N)
+ explain_filter
+---------------------------------------------------------------
+ Seq Scan on public.int8_tbl (cost=N.N..N.N rows=N.N width=N)
Output: q1, q2
Query Identifier: N
(3 rows)
select explain_filter('explain (verbose) create table test_ctas as select 1');
- explain_filter
-----------------------------------------
- Result (cost=N.N..N.N rows=N width=N)
+ explain_filter
+------------------------------------------
+ Result (cost=N.N..N.N rows=N.N width=N)
Output: N
Query Identifier: N
(3 rows)
-- Test SERIALIZE option
select explain_filter('explain (analyze,buffers off,serialize) select * from int8_tbl i8');
- explain_filter
--------------------------------------------------------------------------------------------------
- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N.N loops=N)
+ explain_filter
+---------------------------------------------------------------------------------------------------
+ Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N.N width=N) (actual time=N.N..N.N rows=N.N loops=N)
Planning Time: N.N ms
Serialization: time=N.N ms output=NkB format=text
Execution Time: N.N ms
(4 rows)
select explain_filter('explain (analyze,serialize text,buffers,timing off) select * from int8_tbl i8');
- explain_filter
------------------------------------------------------------------------------------
- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual rows=N.N loops=N)
+ explain_filter
+-------------------------------------------------------------------------------------
+ Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N.N width=N) (actual rows=N.N loops=N)
Planning Time: N.N ms
Serialization: output=NkB format=text
Execution Time: N.N ms
(4 rows)
select explain_filter('explain (analyze,serialize binary,buffers,timing) select * from int8_tbl i8');
- explain_filter
--------------------------------------------------------------------------------------------------
- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N.N loops=N)
+ explain_filter
+---------------------------------------------------------------------------------------------------
+ Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N.N width=N) (actual time=N.N..N.N rows=N.N loops=N)
Planning Time: N.N ms
Serialization: time=N.N ms output=NkB format=binary
Execution Time: N.N ms
@@ -765,9 +765,9 @@ select explain_filter('explain (analyze,serialize binary,buffers,timing) select
-- this tests an edge case where we have no data to return
select explain_filter('explain (analyze,buffers off,serialize) create temp table explain_temp as select * from int8_tbl i8');
- explain_filter
--------------------------------------------------------------------------------------------------
- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N.N loops=N)
+ explain_filter
+---------------------------------------------------------------------------------------------------
+ Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N.N width=N) (actual time=N.N..N.N rows=N.N loops=N)
Planning Time: N.N ms
Serialization: time=N.N ms output=NkB format=text
Execution Time: N.N ms
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 2a8bfba768e..a6657496d58 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -695,10 +695,10 @@ select tableoid::regclass::text as relname, parted_tab.* from parted_tab order b
-- modifies partition key, but no rows will actually be updated
explain update parted_tab set a = 2 where false;
- QUERY PLAN
---------------------------------------------------------
- Update on parted_tab (cost=0.00..0.00 rows=0 width=0)
- -> Result (cost=0.00..0.00 rows=0 width=10)
+ QUERY PLAN
+-----------------------------------------------------------
+ Update on parted_tab (cost=0.00..0.00 rows=0.00 width=0)
+ -> Result (cost=0.00..0.00 rows=0.00 width=10)
One-Time Filter: false
(3 rows)
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 14da5708451..25ae9f4e714 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2422,8 +2422,8 @@ from int4_tbl t1, int4_tbl t2
left join int4_tbl t3 on t3.f1 > 0
left join int4_tbl t4 on t3.f1 > 1
where t4.f1 is null;
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------
Nested Loop
-> Nested Loop Left Join
Filter: (t4.f1 IS NULL)
@@ -2433,10 +2433,9 @@ where t4.f1 is null;
Join Filter: (t3.f1 > 1)
-> Seq Scan on int4_tbl t3
Filter: (f1 > 0)
- -> Materialize
- -> Seq Scan on int4_tbl t4
+ -> Seq Scan on int4_tbl t4
-> Seq Scan on int4_tbl t1
-(12 rows)
+(11 rows)
select t1.f1
from int4_tbl t1, int4_tbl t2
@@ -2573,11 +2572,10 @@ where t1.f1 = coalesce(t2.f1, 1);
-> Materialize
-> Seq Scan on int4_tbl t2
Filter: (f1 > 1)
- -> Materialize
- -> Seq Scan on int4_tbl t3
+ -> Seq Scan on int4_tbl t3
-> Materialize
-> Seq Scan on int4_tbl t4
-(14 rows)
+(13 rows)
explain (costs off)
select * from int4_tbl t1
@@ -3740,10 +3738,9 @@ where i41.f1 > 0;
-> Seq Scan on int8_tbl i81
-> Materialize
-> Seq Scan on int4_tbl i42
- -> Materialize
- -> Seq Scan on int4_tbl i43
- Filter: (f1 > 1)
-(12 rows)
+ -> Seq Scan on int4_tbl i43
+ Filter: (f1 > 1)
+(11 rows)
select * from
int4_tbl as i41,
@@ -4332,6 +4329,13 @@ select * from tenk1 a join tenk1 b on
-------------------------------------------------------------------------------------------------
Nested Loop
Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = 3) AND (b.hundred = 4)))
+ -> Bitmap Heap Scan on tenk1 a
+ Recheck Cond: ((unique1 = 1) OR (unique2 = 3))
+ -> BitmapOr
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = 1)
+ -> Bitmap Index Scan on tenk1_unique2
+ Index Cond: (unique2 = 3)
-> Bitmap Heap Scan on tenk1 b
Recheck Cond: ((unique1 = 2) OR (hundred = 4))
-> BitmapOr
@@ -4339,15 +4343,7 @@ select * from tenk1 a join tenk1 b on
Index Cond: (unique1 = 2)
-> Bitmap Index Scan on tenk1_hundred
Index Cond: (hundred = 4)
- -> Materialize
- -> Bitmap Heap Scan on tenk1 a
- Recheck Cond: ((unique1 = 1) OR (unique2 = 3))
- -> BitmapOr
- -> Bitmap Index Scan on tenk1_unique1
- Index Cond: (unique1 = 1)
- -> Bitmap Index Scan on tenk1_unique2
- Index Cond: (unique2 = 3)
-(17 rows)
+(16 rows)
explain (costs off)
select * from tenk1 a join tenk1 b on
@@ -4356,17 +4352,16 @@ select * from tenk1 a join tenk1 b on
---------------------------------------------------------------------------------------------
Nested Loop
Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = 3) AND (b.ten = 4)))
+ -> Bitmap Heap Scan on tenk1 a
+ Recheck Cond: ((unique1 = 1) OR (unique2 = 3))
+ -> BitmapOr
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = 1)
+ -> Bitmap Index Scan on tenk1_unique2
+ Index Cond: (unique2 = 3)
-> Seq Scan on tenk1 b
Filter: ((unique1 = 2) OR (ten = 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)
- -> Bitmap Index Scan on tenk1_unique2
- Index Cond: (unique2 = 3)
-(12 rows)
+(11 rows)
explain (costs off)
select * from tenk1 a join tenk1 b on
@@ -5110,13 +5105,12 @@ select 1 from
Join Filter: (i4.f1 IS NOT NULL)
-> Seq Scan on int4_tbl i4
Filter: (2 < f1)
- -> Materialize
- -> Seq Scan on int8_tbl i8
+ -> Seq Scan on int8_tbl i8
-> Result
One-Time Filter: false
-> Materialize
-> Seq Scan on int4_tbl i42
-(16 rows)
+(15 rows)
--
-- test for appropriate join order in the presence of lateral references
@@ -6157,18 +6151,17 @@ FROM int4_tbl
JOIN ((SELECT 42 AS x FROM int8_tbl LEFT JOIN innertab ON q1 = id) AS ss1
RIGHT JOIN tenk1 ON NULL)
ON tenk1.unique1 = ss1.x OR tenk1.unique2 = ss1.x;
- QUERY PLAN
---------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------
Nested Loop
+ -> Nested Loop Left Join
+ Join Filter: NULL::boolean
+ Filter: ((tenk1.unique1 = (42)) OR (tenk1.unique2 = (42)))
+ -> Seq Scan on tenk1
+ -> Result
+ One-Time Filter: false
-> Seq Scan on int4_tbl
- -> Materialize
- -> Nested Loop Left Join
- Join Filter: NULL::boolean
- Filter: ((tenk1.unique1 = (42)) OR (tenk1.unique2 = (42)))
- -> Seq Scan on tenk1
- -> Result
- One-Time Filter: false
-(9 rows)
+(8 rows)
rollback;
-- another join removal bug: we must clean up correctly when removing a PHV
@@ -6566,28 +6559,26 @@ where t1.a = t2.a;
explain (costs off)
select * from sj x join sj y on x.a = y.a
left join int8_tbl z on x.a = z.q1;
- QUERY PLAN
-------------------------------------
+ QUERY PLAN
+---------------------------------
Nested Loop Left Join
Join Filter: (y.a = z.q1)
-> Seq Scan on sj y
Filter: (a IS NOT NULL)
- -> Materialize
- -> Seq Scan on int8_tbl z
-(6 rows)
+ -> Seq Scan on int8_tbl z
+(5 rows)
explain (costs off)
select * from sj x join sj y on x.a = y.a
left join int8_tbl z on y.a = z.q1;
- QUERY PLAN
-------------------------------------
+ QUERY PLAN
+---------------------------------
Nested Loop Left Join
Join Filter: (y.a = z.q1)
-> Seq Scan on sj y
Filter: (a IS NOT NULL)
- -> Materialize
- -> Seq Scan on int8_tbl z
-(6 rows)
+ -> Seq Scan on int8_tbl z
+(5 rows)
explain (costs off)
select * from (
@@ -6614,16 +6605,15 @@ left join (select coalesce(y.q1, 1) from int8_tbl y
right join sj j1 inner join sj j2 on j1.a = j2.a
on true) z
on true;
- QUERY PLAN
-------------------------------------------
+ QUERY PLAN
+---------------------------------------
Nested Loop Left Join
-> Result
-> Nested Loop Left Join
-> Seq Scan on sj j2
Filter: (a IS NOT NULL)
- -> Materialize
- -> Seq Scan on int8_tbl y
-(7 rows)
+ -> Seq Scan on int8_tbl y
+(6 rows)
-- Test that references to the removed rel in lateral subqueries are replaced
-- correctly after join removal
@@ -6980,32 +6970,36 @@ select p.b from sj p join sj q on p.a = q.a group by p.b having sum(p.a) = 1;
explain (verbose, costs off)
select 1 from (select x.* from sj x, sj y where x.a = y.a) q,
lateral generate_series(1, q.a) gs(i);
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------
Nested Loop
Output: 1
-> Seq Scan on public.sj y
Output: y.a, y.b, y.c
Filter: (y.a IS NOT NULL)
- -> Function Scan on pg_catalog.generate_series gs
- Output: gs.i
- Function Call: generate_series(1, y.a)
-(8 rows)
+ -> Memoize
+ Cache Key: y.a
+ Cache Mode: binary
+ -> Function Scan on pg_catalog.generate_series gs
+ Function Call: generate_series(1, y.a)
+(10 rows)
explain (verbose, costs off)
select 1 from (select y.* from sj x, sj y where x.a = y.a) q,
lateral generate_series(1, q.a) gs(i);
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------
Nested Loop
Output: 1
-> Seq Scan on public.sj y
Output: y.a, y.b, y.c
Filter: (y.a IS NOT NULL)
- -> Function Scan on pg_catalog.generate_series gs
- Output: gs.i
- Function Call: generate_series(1, y.a)
-(8 rows)
+ -> Memoize
+ Cache Key: y.a
+ Cache Mode: binary
+ -> Function Scan on pg_catalog.generate_series gs
+ Function Call: generate_series(1, y.a)
+(10 rows)
-- Test that a non-EC-derived join clause is processed correctly. Use an
-- outer join so that we can't form an EC.
diff --git a/src/test/regress/expected/join_hash.out b/src/test/regress/expected/join_hash.out
index 4fc34a0e72a..262fa71ed8d 100644
--- a/src/test/regress/expected/join_hash.out
+++ b/src/test/regress/expected/join_hash.out
@@ -508,17 +508,18 @@ set local hash_mem_multiplier = 1.0;
set local enable_parallel_hash = on;
explain (costs off)
select count(*) from simple r join extremely_skewed s using (id);
- QUERY PLAN
------------------------------------------------------------------
- Aggregate
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Finalize Aggregate
-> Gather
Workers Planned: 1
- -> Parallel Hash Join
- Hash Cond: (r.id = s.id)
- -> Parallel Seq Scan on simple r
- -> Parallel Hash
- -> Parallel Seq Scan on extremely_skewed s
-(8 rows)
+ -> Partial Aggregate
+ -> Parallel Hash Join
+ Hash Cond: (r.id = s.id)
+ -> Parallel Seq Scan on simple r
+ -> Parallel Hash
+ -> Parallel Seq Scan on extremely_skewed s
+(9 rows)
select count(*) from simple r join extremely_skewed s using (id);
count
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index d3f5d16a672..6289d3e03d1 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -647,27 +647,27 @@ SELECT * FROM tenk1 a JOIN my_gen_series(1,10) g ON a.unique1 = g;
SELECT explain_mask_costs($$
SELECT * FROM generate_series(TIMESTAMPTZ '2024-02-01', TIMESTAMPTZ '2024-03-01', INTERVAL '1 day') g(s);$$,
true, true, false, true);
- explain_mask_costs
----------------------------------------------------------------------------------------------
- Function Scan on generate_series g (cost=N..N rows=30 width=N) (actual rows=30.00 loops=1)
+ explain_mask_costs
+------------------------------------------------------------------------------------------------
+ Function Scan on generate_series g (cost=N..N rows=30.00 width=N) (actual rows=30.00 loops=1)
(1 row)
-- As above but with generate_series_timestamp
SELECT explain_mask_costs($$
SELECT * FROM generate_series(TIMESTAMP '2024-02-01', TIMESTAMP '2024-03-01', INTERVAL '1 day') g(s);$$,
true, true, false, true);
- explain_mask_costs
----------------------------------------------------------------------------------------------
- Function Scan on generate_series g (cost=N..N rows=30 width=N) (actual rows=30.00 loops=1)
+ explain_mask_costs
+------------------------------------------------------------------------------------------------
+ Function Scan on generate_series g (cost=N..N rows=30.00 width=N) (actual rows=30.00 loops=1)
(1 row)
-- As above but with generate_series_timestamptz_at_zone()
SELECT explain_mask_costs($$
SELECT * FROM generate_series(TIMESTAMPTZ '2024-02-01', TIMESTAMPTZ '2024-03-01', INTERVAL '1 day', 'UTC') g(s);$$,
true, true, false, true);
- explain_mask_costs
----------------------------------------------------------------------------------------------
- Function Scan on generate_series g (cost=N..N rows=30 width=N) (actual rows=30.00 loops=1)
+ explain_mask_costs
+------------------------------------------------------------------------------------------------
+ Function Scan on generate_series g (cost=N..N rows=30.00 width=N) (actual rows=30.00 loops=1)
(1 row)
-- Ensure the estimated and actual row counts match when the range isn't
@@ -675,36 +675,36 @@ true, true, false, true);
SELECT explain_mask_costs($$
SELECT * FROM generate_series(TIMESTAMPTZ '2024-02-01', TIMESTAMPTZ '2024-03-01', INTERVAL '7 day') g(s);$$,
true, true, false, true);
- explain_mask_costs
--------------------------------------------------------------------------------------------
- Function Scan on generate_series g (cost=N..N rows=5 width=N) (actual rows=5.00 loops=1)
+ explain_mask_costs
+----------------------------------------------------------------------------------------------
+ Function Scan on generate_series g (cost=N..N rows=5.00 width=N) (actual rows=5.00 loops=1)
(1 row)
-- Ensure the estimates match when step is decreasing
SELECT explain_mask_costs($$
SELECT * FROM generate_series(TIMESTAMPTZ '2024-03-01', TIMESTAMPTZ '2024-02-01', INTERVAL '-1 day') g(s);$$,
true, true, false, true);
- explain_mask_costs
----------------------------------------------------------------------------------------------
- Function Scan on generate_series g (cost=N..N rows=30 width=N) (actual rows=30.00 loops=1)
+ explain_mask_costs
+------------------------------------------------------------------------------------------------
+ Function Scan on generate_series g (cost=N..N rows=30.00 width=N) (actual rows=30.00 loops=1)
(1 row)
-- Ensure an empty range estimates 1 row
SELECT explain_mask_costs($$
SELECT * FROM generate_series(TIMESTAMPTZ '2024-03-01', TIMESTAMPTZ '2024-02-01', INTERVAL '1 day') g(s);$$,
true, true, false, true);
- explain_mask_costs
--------------------------------------------------------------------------------------------
- Function Scan on generate_series g (cost=N..N rows=1 width=N) (actual rows=0.00 loops=1)
+ explain_mask_costs
+----------------------------------------------------------------------------------------------
+ Function Scan on generate_series g (cost=N..N rows=1.00 width=N) (actual rows=0.00 loops=1)
(1 row)
-- Ensure we get the default row estimate for infinity values
SELECT explain_mask_costs($$
SELECT * FROM generate_series(TIMESTAMPTZ '-infinity', TIMESTAMPTZ 'infinity', INTERVAL '1 day') g(s);$$,
false, true, false, true);
- explain_mask_costs
--------------------------------------------------------------------
- Function Scan on generate_series g (cost=N..N rows=1000 width=N)
+ explain_mask_costs
+----------------------------------------------------------------------
+ Function Scan on generate_series g (cost=N..N rows=1000.00 width=N)
(1 row)
-- Ensure the row estimate behaves correctly when step size is zero.
@@ -719,36 +719,36 @@ ERROR: step size cannot equal zero
SELECT explain_mask_costs($$
SELECT * FROM generate_series(1.0, 25.0) g(s);$$,
true, true, false, true);
- explain_mask_costs
----------------------------------------------------------------------------------------------
- Function Scan on generate_series g (cost=N..N rows=25 width=N) (actual rows=25.00 loops=1)
+ explain_mask_costs
+------------------------------------------------------------------------------------------------
+ Function Scan on generate_series g (cost=N..N rows=25.00 width=N) (actual rows=25.00 loops=1)
(1 row)
-- As above but with non-default step
SELECT explain_mask_costs($$
SELECT * FROM generate_series(1.0, 25.0, 2.0) g(s);$$,
true, true, false, true);
- explain_mask_costs
----------------------------------------------------------------------------------------------
- Function Scan on generate_series g (cost=N..N rows=13 width=N) (actual rows=13.00 loops=1)
+ explain_mask_costs
+------------------------------------------------------------------------------------------------
+ Function Scan on generate_series g (cost=N..N rows=13.00 width=N) (actual rows=13.00 loops=1)
(1 row)
-- Ensure the estimates match when step is decreasing
SELECT explain_mask_costs($$
SELECT * FROM generate_series(25.0, 1.0, -1.0) g(s);$$,
true, true, false, true);
- explain_mask_costs
----------------------------------------------------------------------------------------------
- Function Scan on generate_series g (cost=N..N rows=25 width=N) (actual rows=25.00 loops=1)
+ explain_mask_costs
+------------------------------------------------------------------------------------------------
+ Function Scan on generate_series g (cost=N..N rows=25.00 width=N) (actual rows=25.00 loops=1)
(1 row)
-- Ensure an empty range estimates 1 row
SELECT explain_mask_costs($$
SELECT * FROM generate_series(25.0, 1.0, 1.0) g(s);$$,
true, true, false, true);
- explain_mask_costs
--------------------------------------------------------------------------------------------
- Function Scan on generate_series g (cost=N..N rows=1 width=N) (actual rows=0.00 loops=1)
+ explain_mask_costs
+----------------------------------------------------------------------------------------------
+ Function Scan on generate_series g (cost=N..N rows=1.00 width=N) (actual rows=0.00 loops=1)
(1 row)
-- Ensure we get the default row estimate for error cases (infinity/NaN values
@@ -756,25 +756,25 @@ true, true, false, true);
SELECT explain_mask_costs($$
SELECT * FROM generate_series('-infinity'::NUMERIC, 'infinity'::NUMERIC, 1.0) g(s);$$,
false, true, false, true);
- explain_mask_costs
--------------------------------------------------------------------
- Function Scan on generate_series g (cost=N..N rows=1000 width=N)
+ explain_mask_costs
+----------------------------------------------------------------------
+ Function Scan on generate_series g (cost=N..N rows=1000.00 width=N)
(1 row)
SELECT explain_mask_costs($$
SELECT * FROM generate_series(1.0, 25.0, 'NaN'::NUMERIC) g(s);$$,
false, true, false, true);
- explain_mask_costs
--------------------------------------------------------------------
- Function Scan on generate_series g (cost=N..N rows=1000 width=N)
+ explain_mask_costs
+----------------------------------------------------------------------
+ Function Scan on generate_series g (cost=N..N rows=1000.00 width=N)
(1 row)
SELECT explain_mask_costs($$
SELECT * FROM generate_series(25.0, 2.0, 0.0) g(s);$$,
false, true, false, true);
- explain_mask_costs
--------------------------------------------------------------------
- Function Scan on generate_series g (cost=N..N rows=1000 width=N)
+ explain_mask_costs
+----------------------------------------------------------------------
+ Function Scan on generate_series g (cost=N..N rows=1000.00 width=N)
(1 row)
-- Test functions for control data
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 6101c8c7cf1..fe960db944f 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -28,8 +28,8 @@ ANALYZE prt2;
-- inner join
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------
Sort
Sort Key: t1.a
-> Append
@@ -45,13 +45,12 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b =
-> Hash
-> Seq Scan on prt1_p2 t1_2
Filter: (b = 0)
- -> Hash Join
- Hash Cond: (t2_3.b = t1_3.a)
- -> Seq Scan on prt2_p3 t2_3
- -> Hash
- -> Seq Scan on prt1_p3 t1_3
- Filter: (b = 0)
-(21 rows)
+ -> Nested Loop
+ -> Seq Scan on prt1_p3 t1_3
+ Filter: (b = 0)
+ -> Index Scan using iprt2_p3_b on prt2_p3 t2_3
+ Index Cond: (b = t1_3.a)
+(20 rows)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
a | c | b | c
@@ -834,15 +833,14 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t
Index Cond: (((a + b) / 2) = t2_2.b)
-> Nested Loop
Join Filter: (t1_3.a = ((t3_3.a + t3_3.b) / 2))
- -> Hash Join
- Hash Cond: (t2_3.b = t1_3.a)
- -> Seq Scan on prt2_p3 t2_3
- -> Hash
- -> Seq Scan on prt1_p3 t1_3
- Filter: (b = 0)
+ -> Nested Loop
+ -> Seq Scan on prt1_p3 t1_3
+ Filter: (b = 0)
+ -> Index Scan using iprt2_p3_b on prt2_p3 t2_3
+ Index Cond: (b = t1_3.a)
-> Index Scan using iprt1_e_p3_ab2 on prt1_e_p3 t3_3
Index Cond: (((a + b) / 2) = t2_3.b)
-(33 rows)
+(32 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
@@ -855,8 +853,8 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t
EXPLAIN (COSTS OFF)
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) LEFT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.b = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
- QUERY PLAN
---------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Sort
Sort Key: t1.a, t2.b, ((t3.a + t3.b))
-> Append
@@ -880,17 +878,15 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2
-> Hash
-> Seq Scan on prt1_p2 t1_2
Filter: (b = 0)
- -> Hash Right Join
- Hash Cond: (((t3_3.a + t3_3.b) / 2) = t1_3.a)
- -> Seq Scan on prt1_e_p3 t3_3
- -> Hash
- -> Hash Right Join
- Hash Cond: (t2_3.b = t1_3.a)
- -> Seq Scan on prt2_p3 t2_3
- -> Hash
- -> Seq Scan on prt1_p3 t1_3
- Filter: (b = 0)
-(33 rows)
+ -> Nested Loop Left Join
+ -> Nested Loop Left Join
+ -> Seq Scan on prt1_p3 t1_3
+ Filter: (b = 0)
+ -> Index Scan using iprt2_p3_b on prt2_p3 t2_3
+ Index Cond: (b = t1_3.a)
+ -> Index Scan using iprt1_e_p3_ab2 on prt1_e_p3 t3_3
+ Index Cond: (((a + b) / 2) = t1_3.a)
+(31 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) LEFT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.b = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
a | c | b | c | ?column? | c
@@ -911,8 +907,8 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2
EXPLAIN (COSTS OFF)
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;
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Sort
Sort Key: t1.a, t2.b, ((t3.a + t3.b))
-> Append
@@ -935,15 +931,14 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2
-> Index Scan using iprt2_p2_b on prt2_p2 t2_2
Index Cond: (b = t1_2.a)
-> Nested Loop Left Join
- -> Hash Right Join
- Hash Cond: (t1_3.a = ((t3_3.a + t3_3.b) / 2))
- -> Seq Scan on prt1_p3 t1_3
- -> Hash
- -> Seq Scan on prt1_e_p3 t3_3
- Filter: (c = 0)
- -> Index Scan using iprt2_p3_b on prt2_p3 t2_3
- Index Cond: (b = t1_3.a)
-(30 rows)
+ -> Seq Scan on prt1_e_p3 t3_3
+ Filter: (c = 0)
+ -> Nested Loop Left Join
+ -> Index Scan using iprt1_p3_a on prt1_p3 t1_3
+ Index Cond: (a = ((t3_3.a + t3_3.b) / 2))
+ -> Index Scan using iprt2_p3_b on prt2_p3 t2_3
+ Index Cond: (b = t1_3.a)
+(29 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
@@ -1749,8 +1744,8 @@ ALTER TABLE prt2 ATTACH PARTITION prt2_p3 DEFAULT;
ANALYZE prt2;
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------
Sort
Sort Key: t1.a
-> Append
@@ -1766,13 +1761,12 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b =
-> Hash
-> Seq Scan on prt1_p2 t1_2
Filter: (b = 0)
- -> Hash Join
- Hash Cond: (t2_3.b = t1_3.a)
- -> Seq Scan on prt2_p3 t2_3
- -> Hash
- -> Seq Scan on prt1_p3 t1_3
- Filter: (b = 0)
-(21 rows)
+ -> Nested Loop
+ -> Seq Scan on prt1_p3 t1_3
+ Filter: (b = 0)
+ -> Index Scan using iprt2_p3_b on prt2_p3 t2_3
+ Index Cond: (b = t1_3.a)
+(20 rows)
-- test default partition behavior for list
ALTER TABLE plt1 DETACH PARTITION plt1_p3;
@@ -5175,20 +5169,18 @@ SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b = t2
Sort
Sort Key: t1.a, t1.b
-> Append
- -> Hash Join
- Hash Cond: ((t1_1.a = t2_1.a) AND (t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c))
+ -> Nested Loop
+ Join Filter: ((t1_1.a = t2_1.a) AND (t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c))
-> Seq Scan on alpha_neg_p1 t1_1
Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
- -> Hash
- -> Seq Scan on beta_neg_p1 t2_1
- Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
- -> Hash Join
- Hash Cond: ((t1_2.a = t2_2.a) AND (t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c))
+ -> Seq Scan on beta_neg_p1 t2_1
+ Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
+ -> Nested Loop
+ Join Filter: ((t1_2.a = t2_2.a) AND (t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c))
-> Seq Scan on alpha_neg_p2 t1_2
Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
- -> Hash
- -> Seq Scan on beta_neg_p2 t2_2
- Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
+ -> Seq Scan on beta_neg_p2 t2_2
+ Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
-> Nested Loop
Join Filter: ((t1_3.a = t2_3.a) AND (t1_3.b = t2_3.b) AND (t1_3.c = t2_3.c))
-> Seq Scan on alpha_pos_p2 t1_3
@@ -5201,7 +5193,7 @@ SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b = t2
Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
-> Seq Scan on beta_pos_p3 t2_4
Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
-(29 rows)
+(27 rows)
SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c) WHERE ((t1.b >= 100 AND t1.b < 110) OR (t1.b >= 200 AND t1.b < 210)) AND ((t2.b >= 100 AND t2.b < 110) OR (t2.b >= 200 AND t2.b < 210)) AND t1.c IN ('0004', '0009') ORDER BY t1.a, t1.b;
a | b | c | a | b | c
diff --git a/src/test/regress/expected/select_views.out b/src/test/regress/expected/select_views.out
index 1aeed8452bd..b3c0cbf0607 100644
--- a/src/test/regress/expected/select_views.out
+++ b/src/test/regress/expected/select_views.out
@@ -1461,18 +1461,17 @@ EXPLAIN (COSTS OFF) SELECT * FROM my_credit_card_usage_normal
------------------------------------------------------------------------------
Nested Loop
Join Filter: (l.cid = r.cid)
+ -> Subquery Scan on l
+ Filter: f_leak(l.cnum)
+ -> Hash Join
+ Hash Cond: (r_1.cid = l_1.cid)
+ -> Seq Scan on credit_card r_1
+ -> Hash
+ -> Seq Scan on customer l_1
+ Filter: (name = CURRENT_USER)
-> Seq Scan on credit_usage r
Filter: ((ymd >= '10-01-2011'::date) AND (ymd < '11-01-2011'::date))
- -> Materialize
- -> Subquery Scan on l
- Filter: f_leak(l.cnum)
- -> Hash Join
- Hash Cond: (r_1.cid = l_1.cid)
- -> Seq Scan on credit_card r_1
- -> Hash
- -> Seq Scan on customer l_1
- Filter: (name = CURRENT_USER)
-(13 rows)
+(12 rows)
SELECT * FROM my_credit_card_usage_secure
WHERE f_leak(cnum) AND ymd >= '2011-10-01' AND ymd < '2011-11-01';
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index 686d8c93aa8..5f17271202a 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -18,8 +18,8 @@ begin
loop
if first_row then
first_row := false;
- tmp := regexp_match(ln, 'rows=(\d*) .* rows=(\d*)');
- return query select tmp[1]::int, tmp[2]::int;
+ tmp := regexp_match(ln, 'rows=(\d+\.\d{2}) .* rows=(\d+\.\d{2})');
+ return query select round(tmp[1]::numeric)::int, round(tmp[2]::numeric)::int;
end if;
end loop;
end;
@@ -1345,13 +1345,13 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND b = ''1''');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND (b = ''1'' OR b = ''2'')');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 2 OR a = 51 OR a = 52) AND (b = ''1'' OR b = ''2'')');
@@ -1687,13 +1687,13 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND upper(b) = ''1''');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND (upper(b) = ''1'' OR upper(b) = ''2'')');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 4 OR (a * 2) = 102 OR (a * 2) = 104) AND (upper(b) = ''1'' OR upper(b) = ''2'')');
diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql
index b71a6cd089f..f49a7b6a237 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -20,8 +20,8 @@ begin
loop
if first_row then
first_row := false;
- tmp := regexp_match(ln, 'rows=(\d*) .* rows=(\d*)');
- return query select tmp[1]::int, tmp[2]::int;
+ tmp := regexp_match(ln, 'rows=(\d+\.\d{2}) .* rows=(\d+\.\d{2})');
+ return query select round(tmp[1]::numeric)::int, round(tmp[2]::numeric)::int;
end if;
end loop;
end;
--
2.34.1
