From: Andrei Lepikhov <lepihov@gmail.com>
To: tharakan@gmail.com, pgsql-bugs@lists.postgresql.org, PG Bug reporting form <noreply@postgresql.org>
To: Alexander Korotkov <aekorotkov@gmail.com>
Subject: Re: BUG #18885: ERROR: corrupt MVNDistinct entry - 2
Date: Thu, 10 Apr 2025 14:28:01 +0200
Message-ID: <ed14891b-d2ec-471a-a91f-2ea258c31795@gmail.com> (raw)
In-Reply-To: <18885-da51324078588253@postgresql.org>
References: <18885-da51324078588253@postgresql.org>
On 4/9/25 15:46, PG Bug reporting form wrote:
> The following SQL triggers "ERROR: corrupt MVNDistinct entry", however this
> seems to be unrelated to a recent bugfix[1] for a similar issue (thus '2' in
> the $SUBJECT).
That's my fault.
We wanted to avoid using of the add_unique_group_var, but forgot it does
some necessary stuff.
Here, I attempt to use this routine in the hash join bucket size
estimation. I transformed it a little, made it more general. Not sure it
is the best design, but it is debatable.
--
regards, Andrei Lepikhov
Attachments:
[text/x-patch] 0001-Be-more-conventional-estimating-hash-join-bucket-siz.patch (5.6K, ../ed14891b-d2ec-471a-a91f-2ea258c31795@gmail.com/2-0001-Be-more-conventional-estimating-hash-join-bucket-siz.patch)
download | inline diff:
From c824159493b5caf27db9fe0ecb0f69a5e25f7713 Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <lepihov@gmail.com>
Date: Thu, 10 Apr 2025 14:16:44 +0200
Subject: [PATCH] Be more conventional estimating hash join bucket size.
Implementing estimation of hash join bucket size by extended statistics
we wanted to avoid use of add_unique_group_var because its logic is too
specific for the groups number estimation routine. But we forgot that this
routine also does over stuff, like duplicates removal from the list
of estimated clauses.
With this commit we make add_unique_group_var a little more general that allows
to use it during the bucket estimation.
---
src/backend/utils/adt/selfuncs.c | 35 +++++++++++++------------
src/test/regress/expected/stats_ext.out | 16 +++++++++++
src/test/regress/sql/stats_ext.sql | 8 ++++++
3 files changed, 42 insertions(+), 17 deletions(-)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.cindex 588d991fa57..8016c4dd3b0 100644--- a/src/backend/utils/adt/selfuncs.c+++ b/src/backend/utils/adt/selfuncs.c@@ -3313,16 +3313,12 @@ typedef struct
} GroupVarInfo;
static List *
-add_unique_group_var(PlannerInfo *root, List *varinfos,- Node *var, VariableStatData *vardata)+add_unique_group_var(PlannerInfo *root, List *varinfos, Node *var,+ RelOptInfo *rel, double ndistinct, bool isdefault)
{
GroupVarInfo *varinfo;
- double ndistinct;- bool isdefault;
ListCell *lc;
- ndistinct = get_variable_numdistinct(vardata, &isdefault);-
/*
* The nullingrels bits within the var could cause the same var to be
* counted multiple times if it's marked with different nullingrels. They
@@ -3345,7 +3341,7 @@ add_unique_group_var(PlannerInfo *root, List *varinfos,
* relations (see comments for estimate_num_groups). We aren't too
* fussy about the semantics of "equal" here.
*/
- if (vardata->rel != varinfo->rel &&+ if (rel != varinfo->rel &&
exprs_known_equal(root, var, varinfo->var, InvalidOid))
{
if (varinfo->ndistinct <= ndistinct)
@@ -3364,7 +3360,7 @@ add_unique_group_var(PlannerInfo *root, List *varinfos,
varinfo = (GroupVarInfo *) palloc(sizeof(GroupVarInfo));
varinfo->var = var;
- varinfo->rel = vardata->rel;+ varinfo->rel = rel;
varinfo->ndistinct = ndistinct;
varinfo->isdefault = isdefault;
varinfos = lappend(varinfos, varinfo);
@@ -3532,8 +3528,12 @@ estimate_num_groups(PlannerInfo *root, List *groupExprs, double input_rows,
examine_variable(root, groupexpr, 0, &vardata);
if (HeapTupleIsValid(vardata.statsTuple) || vardata.isunique)
{
- varinfos = add_unique_group_var(root, varinfos,- groupexpr, &vardata);+ double ndistinct;+ bool isdefault;++ ndistinct = get_variable_numdistinct(&vardata, &isdefault);+ varinfos = add_unique_group_var(root, varinfos, groupexpr,+ vardata.rel, ndistinct, isdefault);
ReleaseVariableStats(vardata);
continue;
}
@@ -3569,9 +3569,13 @@ estimate_num_groups(PlannerInfo *root, List *groupExprs, double input_rows,
foreach(l2, varshere)
{
Node *var = (Node *) lfirst(l2);
+ double ndistinct;+ bool isdefault;
examine_variable(root, var, 0, &vardata);
- varinfos = add_unique_group_var(root, varinfos, var, &vardata);+ ndistinct = get_variable_numdistinct(&vardata, &isdefault);+ varinfos = add_unique_group_var(root, varinfos, var, vardata.rel,+ ndistinct, isdefault);
ReleaseVariableStats(vardata);
}
}
@@ -3880,12 +3884,9 @@ estimate_multivariate_bucketsize(PlannerInfo *root, RelOptInfo *inner,
*/
continue;
- varinfo = (GroupVarInfo *) palloc(sizeof(GroupVarInfo));- varinfo->var = expr;- varinfo->rel = root->simple_rel_array[relid];- varinfo->ndistinct = 0.0;- varinfo->isdefault = false;- varinfos = lappend(varinfos, varinfo);+ varinfos = add_unique_group_var(root, varinfos, expr,+ root->simple_rel_array[relid],+ 0.0, false);
/*
* Remember the link to RestrictInfo for the case the clause
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.outindex 686d8c93aa8..472ef0e44ae 100644--- a/src/test/regress/expected/stats_ext.out+++ b/src/test/regress/expected/stats_ext.out@@ -3427,4 +3427,20 @@ SELECT * FROM sb_1 a, sb_2 b WHERE a.x = b.x AND a.y = b.y AND a.z = b.z;
-> Seq Scan on sb_2 b
(5 rows)
+-- Check that the Hash Join bucket size estimator detects equal clauses correctly.+SET enable_nestloop = 'off';+SET enable_mergejoin = 'off';+EXPLAIN (COSTS OFF)+SELECT FROM sb_1 LEFT JOIN sb_2 ON (sb_2.x=sb_1.x) AND (sb_1.x=sb_2.x);+ QUERY PLAN +--------------------------------------------------------+ Hash Left Join+ Hash Cond: ((sb_1.x = sb_2.x) AND (sb_1.x = sb_2.x))+ -> Seq Scan on sb_1+ -> Hash+ -> Seq Scan on sb_2+(5 rows)++RESET enable_nestloop;+RESET enable_mergejoin;
DROP TABLE sb_1, sb_2 CASCADE;
diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sqlindex b71a6cd089f..001ec72026a 100644--- a/src/test/regress/sql/stats_ext.sql+++ b/src/test/regress/sql/stats_ext.sql@@ -1747,4 +1747,12 @@ ANALYZE sb_2;
EXPLAIN (COSTS OFF) -- Choose hash join
SELECT * FROM sb_1 a, sb_2 b WHERE a.x = b.x AND a.y = b.y AND a.z = b.z;
+-- Check that the Hash Join bucket size estimator detects equal clauses correctly.+SET enable_nestloop = 'off';+SET enable_mergejoin = 'off';+EXPLAIN (COSTS OFF)+SELECT FROM sb_1 LEFT JOIN sb_2 ON (sb_2.x=sb_1.x) AND (sb_1.x=sb_2.x);+RESET enable_nestloop;+RESET enable_mergejoin;+
DROP TABLE sb_1, sb_2 CASCADE;
--
2.39.5
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: lepihov@gmail.com, noreply@postgresql.org, aekorotkov@gmail.com
Subject: Re: BUG #18885: ERROR: corrupt MVNDistinct entry - 2
In-Reply-To: <ed14891b-d2ec-471a-a91f-2ea258c31795@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox