pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Matheus Alcantara <matheusssilv97@gmail.com>
To: Aleksander Alekseev <aleksander@tigerdata.com>
To: pgsql-bugs@lists.postgresql.org
Cc: alpkeles99@gmail.com
Cc: Richard Guo <guofenglinux@gmail.com>
Subject: Re: BUG #19772: ERROR: corrupt MVNDistinct entry for GROUP BY c0, ROLLUP (abs(c0)) with ndistinct statistics
Date: Mon, 16 Nov 2026 13:07:34 -0300
Message-ID: <DMWRJIXJZICW.35POJKKJA72UM@gmail.com> (raw)
In-Reply-To: <CAJ7c6TNSVFhZtiNfztW3qiiis=AGYu+v0-eobFreTCRZPny6ug@mail.gmail.com>
References: <19772-d8c886b4db230132@postgresql.org>
	<CAFY6G8e0CxzGoD7wAukjzeroP7XyBi_sTM_ePLtMFycWRht1LA@mail.gmail.com>
	<CAJ7c6TNSVFhZtiNfztW3qiiis=AGYu+v0-eobFreTCRZPny6ug@mail.gmail.com>

Hi Aleksander,

Thanks for the review!

On Mon Nov 16, 2026 at 7:45 AM -03, Aleksander Alekseev wrote:
>> The attached patch fixes this by also stripping the grouping step's RT
>> index wherever selfuncs.c already strips the outer join nullingrels
>> before looking up statistics.
>
> I reviewed v1.  It applies cleanly and the tests pass. Your analysis
> matches what I see. The three new cases do fail with "corrupt
> MVNDistinct entry" without the fix.
>
> However, only add_unique_group_var() is covered.  If I revert the
> examine_variable() and estimate_multivariate_bucketsize() but keep the
> tests, the whole regression suite still passes.

Right. (a+1) has the same number of distinct values as a, so falling
back to the column's statistics gives the same estimate either way. v2
adds tests with GROUP BY ROLLUP ((d % 2)), using statistics on (d % 2)
in one and an expression index in the other. Both estimate 3 groups with
the fix and 21 with the examine_variable() change reverted.

I think that the estimate_multivariate_bucketsize() change can't
actually take effect, because hash join keys are planned below the
grouping step and never carry its RT index. v2 drops that change and
keeps stripping only outer-join nullingrels there.

v2 attached.

--
Matheus Alcantara
EDB: https://www.enterprisedb.com

Attachments:

v2-0001-Ignore-grouping-step-nullingrels-when-looking-up-.patch (text/plain, 8.0K) download From eb12c61a4872f5ac735ba85f89fea5b9e543edb4 Mon Sep 17 00:00:00 2001 From: Matheus Alcantara <mths.dev@pm.me> Date: Tue, 20 Oct 2026 16:14:50 -0300 Subject: [PATCH v2] Ignore grouping-step nullingrels when looking up statistics Since commit f5050f795, a grouping expression that does not appear in every grouping set is marked nullable by the grouping step: the RT index of the GROUP RTE is added to the nullingrels of the Vars it contains. When looking up statistics, selfuncs.c strips nullingrels belonging to outer joins (commit e28033fe1), but not the grouping step's RT index. As a result, estimate_num_groups could create two GroupVarInfos for the same Var: one for a plain grouping column that is present in all grouping sets, and one for the same column pulled out of an expression that is not. When an ndistinct extended statistics object covers that column, estimate_multivariate_ndistinct counts the duplicate as two matching attributes, then fails to find an item for what is really a single attribute, and reports "corrupt MVNDistinct entry". The extra nullingrels bit also kept examine_variable from matching such expressions to expression indexes or to expressions in extended statistics, since pull_varnos reports the GROUP RTE as a second relation and equal() sees the differing nullingrels. To fix, also strip the grouping step's RT index in add_unique_group_var and examine_variable. The other place that strips outer-join nullingrels, estimate_multivariate_bucketsize, only sees hash join keys, which are computed below the grouping step and so never carry its RT index. Back-patch to v18, where the GROUP RTE was introduced. Reported-by: alpkeles99@gmail.com <alpkeles99@gmail.com> Reviewed-by: Aleksander Alekseev <aleksander@tigerdata.com> Discussion: https://www.postgresql.org/message-id/19772-d8c886b4db230132@postgresql.org Backpatch-through: 18 --- src/backend/utils/adt/selfuncs.c | 25 ++++++++++--- src/test/regress/expected/stats_ext.out | 47 +++++++++++++++++++++++++ src/test/regress/sql/stats_ext.sql | 39 ++++++++++++++++++++ 3 files changed, 107 insertions(+), 4 deletions(-) diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c index 5ee377595bd..05f0a2a69c6 100644 --- a/src/backend/utils/adt/selfuncs.c +++ b/src/backend/utils/adt/selfuncs.c @@ -3656,6 +3656,21 @@ matchingjoinsel(PG_FUNCTION_ARGS) } +/* + * Get the set of nulling relids that should be ignored when looking up + * statistical data about an expression (the outer joins, plus the grouping + * step if there is one). + */ +static Relids +get_stats_nulling_relids(PlannerInfo *root) +{ + if (root->group_rtindex == 0) + return root->outer_join_rels; + + return bms_add_member(bms_copy(root->outer_join_rels), + root->group_rtindex); +} + /* * Helper routine for estimate_num_groups: add an item to a list of * GroupVarInfos, but only if it's not known equal to any of the existing @@ -3687,7 +3702,7 @@ add_unique_group_var(PlannerInfo *root, List *varinfos, * extended statistics (see estimate_multivariate_ndistinct). So strip * them out first. */ - var = remove_nulling_relids(var, root->outer_join_rels, NULL); + var = remove_nulling_relids(var, get_stats_nulling_relids(root), NULL); foreach(lc, varinfos) { @@ -5653,6 +5668,7 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid, Node *basenode; Relids varnos; Relids basevarnos; + Relids nullingrels; RelOptInfo *onerel; /* Make sure we don't return dangling pointers in vardata */ @@ -5703,7 +5719,8 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid, * relation are considered "real" vars. */ varnos = pull_varnos(root, basenode); - basevarnos = bms_difference(varnos, root->outer_join_rels); + nullingrels = get_stats_nulling_relids(root); + basevarnos = bms_difference(varnos, nullingrels); onerel = NULL; @@ -5775,8 +5792,8 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid, * matching it to expressional index columns or to the expressions in * extended statistics. So strip them out first. */ - if (bms_overlap(varnos, root->outer_join_rels)) - node = remove_nulling_relids(node, root->outer_join_rels, NULL); + if (bms_overlap(varnos, nullingrels)) + node = remove_nulling_relids(node, nullingrels, NULL); foreach(ilist, onerel->indexlist) { diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out index 42d6c2777ea..c1d2948291b 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -1104,6 +1104,53 @@ SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, ( DROP STATISTICS s11; DROP STATISTICS s12; +-- grouping sets where an expression on a column is nullable by grouping but +-- the plain column is not, so the column's Var gets different nullingrels +CREATE STATISTICS s11 (ndistinct) ON a, b FROM ndistinct; +ANALYZE ndistinct; +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, ROLLUP ((a+1))'); + estimated | actual +-----------+-------- + 6 | 6 +(1 row) + +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY GROUPING SETS ((a, (a+1)), (a))'); + estimated | actual +-----------+-------- + 6 | 6 +(1 row) + +DROP STATISTICS s11; +-- same, with statistics on the expression +CREATE STATISTICS s11 (ndistinct) ON a, (a+1) FROM ndistinct; +ANALYZE ndistinct; +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, ROLLUP ((a+1))'); + estimated | actual +-----------+-------- + 6 | 6 +(1 row) + +DROP STATISTICS s11; +-- a grouping expression that is nullable by grouping should still be +-- matched to expression statistics and expression indexes +CREATE STATISTICS s11 ON (d % 2) FROM ndistinct; +ANALYZE ndistinct; +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY ROLLUP ((d % 2))'); + estimated | actual +-----------+-------- + 3 | 3 +(1 row) + +DROP STATISTICS s11; +CREATE INDEX ndistinct_d_mod_idx ON ndistinct ((d % 2)); +ANALYZE ndistinct; +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY ROLLUP ((d % 2))'); + estimated | actual +-----------+-------- + 3 | 3 +(1 row) + +DROP INDEX ndistinct_d_mod_idx; -- functional dependencies tests CREATE TABLE functional_dependencies ( filler1 TEXT, diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql index 6045cfa51e8..146e4ed558c 100644 --- a/src/test/regress/sql/stats_ext.sql +++ b/src/test/regress/sql/stats_ext.sql @@ -596,6 +596,45 @@ SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, ( DROP STATISTICS s11; DROP STATISTICS s12; +-- grouping sets where an expression on a column is nullable by grouping but +-- the plain column is not, so the column's Var gets different nullingrels +CREATE STATISTICS s11 (ndistinct) ON a, b FROM ndistinct; + +ANALYZE ndistinct; + +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, ROLLUP ((a+1))'); + +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY GROUPING SETS ((a, (a+1)), (a))'); + +DROP STATISTICS s11; + +-- same, with statistics on the expression +CREATE STATISTICS s11 (ndistinct) ON a, (a+1) FROM ndistinct; + +ANALYZE ndistinct; + +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY a, ROLLUP ((a+1))'); + +DROP STATISTICS s11; + +-- a grouping expression that is nullable by grouping should still be +-- matched to expression statistics and expression indexes +CREATE STATISTICS s11 ON (d % 2) FROM ndistinct; + +ANALYZE ndistinct; + +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY ROLLUP ((d % 2))'); + +DROP STATISTICS s11; + +CREATE INDEX ndistinct_d_mod_idx ON ndistinct ((d % 2)); + +ANALYZE ndistinct; + +SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct GROUP BY ROLLUP ((d % 2))'); + +DROP INDEX ndistinct_d_mod_idx; + -- functional dependencies tests CREATE TABLE functional_dependencies ( filler1 TEXT, -- 2.50.1 (Apple Git-155)

view thread (4+ messages)

Message-ID: <DMWRJIXJZICW.35POJKKJA72UM@gmail.com>
Permalink:  ../DMWRJIXJZICW.35POJKKJA72UM@gmail.com/
Also on:    postgresql.org/message-id/DMWRJIXJZICW.35POJKKJA72UM@gmail.com

 ·  · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: matheusssilv97@gmail.com, aleksander@tigerdata.com, alpkeles99@gmail.com, guofenglinux@gmail.com
  Subject: Re: BUG #19772: ERROR: corrupt MVNDistinct entry for GROUP BY c0, ROLLUP (abs(c0)) with ndistinct statistics
  In-Reply-To: <DMWRJIXJZICW.35POJKKJA72UM@gmail.com>
  References: <19772-d8c886b4db230132@postgresql.org> <CAFY6G8e0CxzGoD7wAukjzeroP7XyBi_sTM_ePLtMFycWRht1LA@mail.gmail.com> <CAJ7c6TNSVFhZtiNfztW3qiiis=AGYu+v0-eobFreTCRZPny6ug@mail.gmail.com> <DMWRJIXJZICW.35POJKKJA72UM@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