RE: alter table xxx alter column yyy set (n_distinct=nnnn );

["Tefft, Michael J" <Michael.J.Tefft@xxxxxxxxxx>]

OK, thanks to both of you, it is now working for me. Many thanks.

 

This is what I determined was the missing ingredient from the secret sauce…

 

One of the things I had tried (before posting here) was

Alter table xxx alter column p_id set statistics [varying values];

 

Including zero.

 

When I first tried the suggestion of also setting n_distinct_inherited, these columns were still set to “statistics 0”.

 

When I altered them back to the default value i.e.

Alter table xxx alter column p_id set statistics -1;

(or, in fact, any nonzero value), the desired n_distinct values appeared after ANALYZE.

 

So, to set n_distinct for column col1 in a partitioned table sss.xxx:

1. Make sure column col1 has “statistics” != 0
2. alter table sss.xxx alter column p_id set (n_distinct_inherited=[desired_value] );
3. analyze sss.xxx;
4. verify with: select attname, n_distinct from pg_stats where schemaname='sss' and tablename='xxx' and attname = ‘col1’;

 

So setting n_distinct on the column at the parent/partitioned-table level is not relevant for this.

 

Thanks again.

 

Mike Tefft

 

 

 

 

From: Greg Sabino Mullane <htamfids@xxxxxxxxx>
Sent: Monday, March 11, 2024 12:23 PM
To: Laurenz Albe <laurenz.albe@xxxxxxxxxxx>
Cc: Tefft, Michael J <Michael.J.Tefft@xxxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxxxxxxxx
Subject: Re: alter table xxx alter column yyy set (n_distinct=nnnn );

 

Works for me on Postgres 14 as well. Maybe you are looking at the wrong table? Might help to show the exact steps you typed in. ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍

Works for me on Postgres 14 as well. Maybe you are looking at the wrong table? Might help to show the exact steps you typed in.