From: | "Tefft, Michael J" <Michael(dot)J(dot)Tefft(at)snapon(dot)com> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | alter table xxx alter column yyy set (n_distinct=nnnn ); |
Date: | 2024-03-11 12:36:41 |
Message-ID: | BN8PR04MB6289FBCC41CD0CBA2549F510D0242@BN8PR04MB6289.namprd04.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Artur Zakirov | 2024-03-11 13:08:24 | Re: Pgxs - How to reference another extension |
Previous Message | normandavis1990 | 2024-03-11 12:33:42 | Re: Create a standby server |