From: Andrei Lepikhov <lepihov@gmail.com>
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: tharakan@gmail.com, pgsql-bugs@lists.postgresql.org, PG Bug reporting form <noreply@postgresql.org>
Cc: Tomas Vondra <tomas@vondra.me>
Subject: Re: BUG #18885: ERROR: corrupt MVNDistinct entry - 2
Date: Wed, 16 Apr 2025 12:00:27 +0200
Message-ID: <7cab95a3-0358-4102-96d1-8c94ff3c6fdb@gmail.com> (raw)
In-Reply-To: <c9225640-e2dc-4d9c-a598-0d1d1673b11f@gmail.com>
References: <18885-da51324078588253@postgresql.org>
<ed14891b-d2ec-471a-a91f-2ea258c31795@gmail.com>
<CAPpHfdvhz-p8Wrb0sm+c01Gk2mVR2O-mbbnXDHry4Ax=y1qSTw@mail.gmail.com>
<c9225640-e2dc-4d9c-a598-0d1d1673b11f@gmail.com>
On 4/14/25 16:41, Andrei Lepikhov wrote:
> On 4/12/25 00:30, Alexander Korotkov wrote:
>> When you use add_unique_group_var() which keeps varinfos unique then
>> you can no longer expect that varinfos have the same order as
>> origin_rinfos.
> Ok, here is a patch that considers this issue. Now GroupVarInfo tracks
> source RestrictInfo. Not sure it is an ideal approach, but we don't need
> to synchronise the restrictions and corresponding varinfos.
This is the new version of the patch.
I don't like the previous version because it was too invasive. Also,
add_unique_group_var checks "known equal" keys. It is not applicable in
the current case, of course, but it still seems suspicious: equality
expressions may be applied at an upper level of the join tree, and
equality doesn't exist at this specific place yet. Being correct in the
case of GROUP-BY operator estimation, such conjecture may distort
estimation in the middle of the join tree.
With the current patch, we just stay away from that doubtful code.
--
regards, Andrei Lepikhov
Attachments:
[text/x-patch] v1-0001-Make-the-necessary-preparations-before-estimating.patch (4.2K, ../7cab95a3-0358-4102-96d1-8c94ff3c6fdb@gmail.com/2-v1-0001-Make-the-necessary-preparations-before-estimating.patch)
download | inline diff:
From b55a84c5b7d6f86409acb87291986bf6b7714f0e 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 v1] Make the necessary preparations before estimating the hash
join bucket size.
To estimate with extended statistics, it is necessary to clear
the varnullingrels field in the expression, and duplicates are not allowed
in the GroupVarInfo list.
---
src/backend/utils/adt/selfuncs.c | 26 +++++++++++++++++++++++
src/test/regress/expected/stats_ext.out | 28 +++++++++++++++++++++++++
src/test/regress/sql/stats_ext.sql | 11 ++++++++++
3 files changed, 65 insertions(+)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.cindex 588d991fa57..2fd1e177c20 100644--- a/src/backend/utils/adt/selfuncs.c+++ b/src/backend/utils/adt/selfuncs.c@@ -3850,6 +3850,8 @@ estimate_multivariate_bucketsize(PlannerInfo *root, RelOptInfo *inner,
if (bms_get_singleton_member(relids, &relid) &&
root->simple_rel_array[relid]->statlist != NIL)
{
+ bool is_duplicate = false;+
/*
* This inner-side expression references only one relation.
* Extended statistics on this clause can exist.
@@ -3880,6 +3882,30 @@ estimate_multivariate_bucketsize(PlannerInfo *root, RelOptInfo *inner,
*/
continue;
+ /* Clear nullingrels to correctly match hash keys */+ expr = remove_nulling_relids(expr, root->outer_join_rels, NULL);++ /*+ * Detect and exclude exact duplicates from the list of+ * hash keys (like add_unique_group_var does).+ */+ foreach(lc1, varinfos)+ {+ varinfo = (GroupVarInfo *) lfirst(lc1);++ if (!equal(expr, varinfo->var))+ continue;++ is_duplicate = true;+ break;+ }+ if (is_duplicate)+ /*+ * Skip exact duplicates. Adding them to the otherclauses+ * list also doesn't make sense.+ */+ continue;+
varinfo = (GroupVarInfo *) palloc(sizeof(GroupVarInfo));
varinfo->var = expr;
varinfo->rel = root->simple_rel_array[relid];
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.outindex 686d8c93aa8..6359e5fb689 100644--- a/src/test/regress/expected/stats_ext.out+++ b/src/test/regress/expected/stats_ext.out@@ -3427,4 +3427,32 @@ 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)++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) AND (sb_1.y=sb_2.y);+ QUERY PLAN +------------------------------------------------------------------------------+ Hash Left Join+ Hash Cond: ((sb_1.x = sb_2.x) AND (sb_1.y = sb_2.y) 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..da4f2fe9c93 100644--- a/src/test/regress/sql/stats_ext.sql+++ b/src/test/regress/sql/stats_ext.sql@@ -1747,4 +1747,15 @@ 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);+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) AND (sb_1.y=sb_2.y);+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, aekorotkov@gmail.com, noreply@postgresql.org, tomas@vondra.me
Subject: Re: BUG #18885: ERROR: corrupt MVNDistinct entry - 2
In-Reply-To: <7cab95a3-0358-4102-96d1-8c94ff3c6fdb@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