Thanks very much for the reply. I tried setting alter table ctrg.xxx alter column p_id set (n_distinct_inherited=-0.033 ); alter table ctrg.xxx alter column pi_id set (n_distinct_inherited=-0.045 ); alter table ctrg.xxx alter column i_id set (n_distinct_inherited=-0.0002 ); I then ran analyze xxx; but saw no change in pg_stats.n_distinct for xxx. Reviewing the values for pg_stats.n_distinct in the partitions of xxx, - values for i_id are all positive - values for p_id are a mix - mostly positive, a few negative, but none carry the value I attempted to set - values for pi_id are all negative, but none carry the value I attempted to set Thanks, Mike Tefft -----Original Message----- From: Laurenz Albe <laurenz.albe@xxxxxxxxxxx> Sent: Monday, March 11, 2024 9:21 AM To: Tefft, Michael J <Michael.J.Tefft@xxxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxxxxxxxx Subject: Re: alter table xxx alter column yyy set (n_distinct=nnnn ); On Mon, 2024-03-11 at 12:36 +0000, Tefft, Michael J wrote: > I have been struggling to set n_distinct on a few table columns, and confirm that my changes have actually been accepted. > > I have a 400-million row table with 81 partitions. PostgreSQL version is 14.11. > Column p_id has 13 million distinct values but pg_stats says n_distinct is only 82k. > Column pi_id has 18 million distinct values but pg_stats says n_distinct is only 29k. > Column i_id has 70k distinct values but pg_stats says n_distinct is only 1800. > > I tried: > alter table xxx alter column p_id set (n_distinct=-0.033 ); alter > table xxx alter column pi_id set (n_distinct=-0.045 ); alter table xxx > alter column i_id set (n_distinct=-0.0002 ); > > All ran without error, but pg_stats shows no changes. I tried: > > alter table xxx alter column p_id set (n_distinct=13118955 ); alter > table xxx alter column pi_id set (n_distinct=18059179 ); alter table > xxx alter column i_id set (n_distinct=69911 ); > > Again, all ran without error, but pg_stats shows no changes. > > I saw somewhere (but can’t locate the reference today) that setting n_distinct takes effect after the next ANALYZE. I tried ANALYZE xxx and VACUUM ANALYZE xxx but the settings never appeared to take effect. > > I tried increasing “statistics” on the columns to 1000 and running ANALYZE; this produced somewhat-higher n_distinct values but still far from accurate and my manually-set values still did not appear. > > How can I get these values to take effect? If it is a partitioned table, set "n_distinct_inherited" on the column, then run ANALYZE. Yours, Laurenz Albe
