Horton

Thread

Commits

  1. vacuumdb: Fix --missing-stats-only for partitioned indexes.

  1. Fix --missing-stats-only false positive for partitioned expression indexes

    Baji Shaik <baji.pgdev@gmail.com> — 2026-06-16T15:25:52Z

    Hi,

I tested "vacuumdb --missing-stats-only"  and found that it flags
partitioned
tables that have expression indexes, even after a full ANALYZE.

Steps to reproduce:

  CREATE TABLE parent (a int, b int) PARTITION BY RANGE (a);
  CREATE TABLE child1 PARTITION OF parent FOR VALUES FROM (1) TO (100);
  CREATE TABLE child2 PARTITION OF parent FOR VALUES FROM (100) TO (200);
  CREATE INDEX ON parent ((a + b));
  INSERT INTO parent SELECT i, i FROM generate_series(1, 199) i;
  ANALYZE;

  $ vacuumdb --missing-stats-only --analyze-only -v postgres 2>&1 | grep
"public\."
  INFO:  analyzing "public.parent" inheritance tree   -- should not appear
  INFO:  analyzing "public.child1"                    -- should not appear
  INFO:  analyzing "public.child2"                    -- should not appear

Running ANALYZE again doesn't help. The table is always flagged.

This is because expression index stats check looks for stainherit=true rows
on
the partitioned index, but those never exist (only leaf indexes get
stats).  Fix is one line to skip the check when p.inherited is true.

Patch attached.

Thanks,
Baji Shaik.

  2. Re: Fix --missing-stats-only false positive for partitioned expression indexes

    Nathan Bossart <nathandbossart@gmail.com> — 2026-06-16T16:14:10Z

    On Tue, Jun 16, 2026 at 10:25:52AM -0500, Baji Shaik wrote:
> I tested "vacuumdb --missing-stats-only"  and found that it flags
> partitioned tables that have expression indexes, even after a full
> ANALYZE.

Thanks for reporting.

-							 " OR EXISTS (SELECT NULL FROM pg_catalog.pg_attribute a\n"
+							 " OR (NOT p.inherited"
+							 " AND EXISTS (SELECT NULL FROM pg_catalog.pg_attribute a\n"

I'm curious why you added this check to the beginning and surrounded the
rest with parentheses.  Wouldn't it be better to follow the example of the
surrounding clauses and an "AND NOT p.inherited" somewhere in the middle?

-- 
nathan

  3. Re: Fix --missing-stats-only false positive for partitioned expression indexes

    Corey Huinker <corey.huinker@gmail.com> — 2026-06-16T16:23:17Z

    On Tue, Jun 16, 2026 at 12:14 PM Nathan Bossart <nathandbossart@gmail.com>
wrote:

> On Tue, Jun 16, 2026 at 10:25:52AM -0500, Baji Shaik wrote:
> > I tested "vacuumdb --missing-stats-only"  and found that it flags
> > partitioned tables that have expression indexes, even after a full
> > ANALYZE.
>
> Thanks for reporting.
>
> -                                                        " OR EXISTS
> (SELECT NULL FROM pg_catalog.pg_attribute a\n"
> +                                                        " OR (NOT
> p.inherited"
> +                                                        " AND EXISTS
> (SELECT NULL FROM pg_catalog.pg_attribute a\n"
>
> I'm curious why you added this check to the beginning and surrounded the
> rest with parentheses.  Wouldn't it be better to follow the example of the
> surrounding clauses and an "AND NOT p.inherited" somewhere in the middle?
>
> --
> nathan


I had a similar question and am toying around with refactoring it now.

The rest of the patch checks out aside from that.

  4. Re: Fix --missing-stats-only false positive for partitioned expression indexes

    Baji Shaik <baji.pgdev@gmail.com> — 2026-06-16T16:26:28Z

    On Tue, Jun 16, 2026 at 11:14 AM Nathan Bossart <nathandbossart@gmail.com>
wrote:

> I'm curious why you added this check to the beginning and surrounded the
> rest with parentheses.  Wouldn't it be better to follow the example of the
> surrounding clauses and an "AND NOT p.inherited" somewhere in the middle?


Good point. v2 moves the check inside the EXISTS as
"AND NOT p.inherited", consistent with how the inheritance
sections below handle it.

v2 attached.

Thanks,
Baji Shaik.

  5. Re: Fix --missing-stats-only false positive for partitioned expression indexes

    Corey Huinker <corey.huinker@gmail.com> — 2026-06-16T16:40:11Z

    On Tue, Jun 16, 2026 at 12:26 PM Baji Shaik <baji.pgdev@gmail.com> wrote:

> On Tue, Jun 16, 2026 at 11:14 AM Nathan Bossart <nathandbossart@gmail.com>
> wrote:
>
>> I'm curious why you added this check to the beginning and surrounded the
>> rest with parentheses.  Wouldn't it be better to follow the example of the
>> surrounding clauses and an "AND NOT p.inherited" somewhere in the middle?
>
>
> Good point. v2 moves the check inside the EXISTS as
> "AND NOT p.inherited", consistent with how the inheritance
> sections below handle it.
>
> v2 attached.
>
> Thanks,
> Baji Shaik.
>

+1

The refactor I was testing differed only in having the new qual one line
higher (above the attstattarget check vs below it). Looks good to me.

  6. Re: Fix --missing-stats-only false positive for partitioned expression indexes

    Nathan Bossart <nathandbossart@gmail.com> — 2026-06-16T16:46:40Z

    On Tue, Jun 16, 2026 at 12:40:11PM -0400, Corey Huinker wrote:
> On Tue, Jun 16, 2026 at 12:26 PM Baji Shaik <baji.pgdev@gmail.com> wrote:
>> Good point. v2 moves the check inside the EXISTS as
>> "AND NOT p.inherited", consistent with how the inheritance
>> sections below handle it.
> 
> The refactor I was testing differed only in having the new qual one line
> higher (above the attstattarget check vs below it). Looks good to me.

We could remove the final "AND s.stainherit = p.inherited" in this part of
the clause, too, right?

-- 
nathan

  7. Re: Fix --missing-stats-only false positive for partitioned expression indexes

    Baji Shaik <baji.pgdev@gmail.com> — 2026-06-16T17:09:00Z

    On Tue, Jun 16, 2026 at 11:46 AM Nathan Bossart <nathandbossart@gmail.com>
wrote:

> We could remove the final "AND s.stainherit = p.inherited" in this part of
> the clause, too, right?
>

Right. With NOT p.inherited already restricting this clause to the
non-inherited case, the stainherit filter is redundant since index stats
never have stainherit = true (only leaf indexes accumulate stats,
always with stainherit = false). I verified this holds across
partitioned, inherited, and mixed scenarios. Removed in v3.

v3 attached.

Thanks,
Baji Shaik.

  8. Re: Fix --missing-stats-only false positive for partitioned expression indexes

    Nathan Bossart <nathandbossart@gmail.com> — 2026-06-16T17:15:27Z

    On Tue, Jun 16, 2026 at 12:09:00PM -0500, Baji Shaik wrote:
> v3 attached.

Here is a v4 with an updated commit message and a test case.

-- 
nathan

  9. Re: Fix --missing-stats-only false positive for partitioned expression indexes

    Baji Shaik <baji.pgdev@gmail.com> — 2026-06-16T17:23:35Z

    On Tue, Jun 16, 2026 at 12:15 PM Nathan Bossart <nathandbossart@gmail.com>
wrote:

> Here is a v4 with an updated commit message and a test case.
>

Thanks for adding the test case. v4 looks good to me. Tested and verified.

Thanks,
Baji Shaik.

  10. Re: Fix --missing-stats-only false positive for partitioned expression indexes

    Corey Huinker <corey.huinker@gmail.com> — 2026-06-16T17:25:27Z

    On Tue, Jun 16, 2026 at 1:15 PM Nathan Bossart <nathandbossart@gmail.com>
wrote:

> On Tue, Jun 16, 2026 at 12:09:00PM -0500, Baji Shaik wrote:
> > v3 attached.
>
> Here is a v4 with an updated commit message and a test case.
>
> --
> nathan
>

I know this is a corner-case of a corner-case, but if  " AND s.stainherit
OPERATOR(pg_catalog.=) p.inherited", we might then get a false negative
from a situation like this:

t2 inherits t1
t1 has ineritance stats but somehow not regular stats
t2 dis-inherits from t1, t1 is no longer p.inherited = true, but inherited
stats remain

vacuumdb goes looking for matches, sees that t1 is p.inherited = false,
find the old inherited stat row, not realizing it should have been looking
for a non-inherits row.

  11. Re: Fix --missing-stats-only false positive for partitioned expression indexes

    Nathan Bossart <nathandbossart@gmail.com> — 2026-06-16T17:51:28Z

    On Tue, Jun 16, 2026 at 01:25:27PM -0400, Corey Huinker wrote:
> I know this is a corner-case of a corner-case, but if  " AND s.stainherit
> OPERATOR(pg_catalog.=) p.inherited", we might then get a false negative
> from a situation like this:
> 
> t2 inherits t1
> t1 has ineritance stats but somehow not regular stats
> t2 dis-inherits from t1, t1 is no longer p.inherited = true, but inherited
> stats remain
> 
> vacuumdb goes looking for matches, sees that t1 is p.inherited = false,
> find the old inherited stat row, not realizing it should have been looking
> for a non-inherits row.

Since p.inherited is set based on the relkind, it could only change if the
table was converted from partitioned to not partitioned.  IIRC that's not
currently possible.

-- 
nathan

  12. Re: Fix --missing-stats-only false positive for partitioned expression indexes

    Nathan Bossart <nathandbossart@gmail.com> — 2026-06-16T17:54:49Z

    On Tue, Jun 16, 2026 at 12:51:28PM -0500, Nathan Bossart wrote:
> On Tue, Jun 16, 2026 at 01:25:27PM -0400, Corey Huinker wrote:
>> I know this is a corner-case of a corner-case, but if  " AND s.stainherit
>> OPERATOR(pg_catalog.=) p.inherited", we might then get a false negative
>> from a situation like this:
>> 
>> t2 inherits t1
>> t1 has ineritance stats but somehow not regular stats
>> t2 dis-inherits from t1, t1 is no longer p.inherited = true, but inherited
>> stats remain
>> 
>> vacuumdb goes looking for matches, sees that t1 is p.inherited = false,
>> find the old inherited stat row, not realizing it should have been looking
>> for a non-inherits row.
> 
> Since p.inherited is set based on the relkind, it could only change if the
> table was converted from partitioned to not partitioned.  IIRC that's not
> currently possible.

(Also, expression index stats are documented as always having stainherit =
false...)

-- 
nathan

  13. Re: Fix --missing-stats-only false positive for partitioned expression indexes

    Corey Huinker <corey.huinker@gmail.com> — 2026-06-16T18:28:20Z

    On Tue, Jun 16, 2026 at 1:54 PM Nathan Bossart <nathandbossart@gmail.com>
wrote:

> On Tue, Jun 16, 2026 at 12:51:28PM -0500, Nathan Bossart wrote:
> > On Tue, Jun 16, 2026 at 01:25:27PM -0400, Corey Huinker wrote:
> >> I know this is a corner-case of a corner-case, but if  " AND
> s.stainherit
> >> OPERATOR(pg_catalog.=) p.inherited", we might then get a false negative
> >> from a situation like this:
> >>
> >> t2 inherits t1
> >> t1 has ineritance stats but somehow not regular stats
> >> t2 dis-inherits from t1, t1 is no longer p.inherited = true, but
> inherited
> >> stats remain
> >>
> >> vacuumdb goes looking for matches, sees that t1 is p.inherited = false,
> >> find the old inherited stat row, not realizing it should have been
> looking
> >> for a non-inherits row.
> >
> > Since p.inherited is set based on the relkind, it could only change if
> the
> > table was converted from partitioned to not partitioned.  IIRC that's not
> > currently possible.
>
> (Also, expression index stats are documented as always having stainherit =
> false...)


Oh good. I like it when things are not problems.

  14. Re: Fix --missing-stats-only false positive for partitioned expression indexes

    Nathan Bossart <nathandbossart@gmail.com> — 2026-06-17T14:22:05Z

    Committed.

-- 
nathan