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?

Thanks,
Mike Tefft

Reply via email to