Subject: How to delete column level Stats/Histogram

From: "Wong, Kam Fook (TR Technology)" <[email-masked]>
Date: Thu, 30 May 2024 21:27:56 +0000

1.  Is there a way to delete a specific column level stats/histogram.  The following approach does not work.

alter table abc alter column bg_org_partner set statistics 0;

analyze   abc;

select *

FROM pg_stats where tablename in ('abc' ) and attname = 'bg_org_partner';

the most_common_vals remain and most_common_freqs remain the same.

  1.  Any other ways or workaround such as wiping out a specific table level stat, then restore the stats but minus 1 column.  And the future vacuum analyze/analyze will not be updating that specific column stat.

Thank you
This e-mail is for the sole use of the intended recipient and contains information that may be privileged and/or confidential. If you are not an intended recipient, please notify the sender by return e-mail and delete this e-mail and any attachments. Certain required legal entity disclosures can be accessed on our website: https://www.thomsonreuters.com/en/resources/disclosures.html

---

From: David Rowley <[email-masked]>
Date: Fri, 31 May 2024 10:45:24 +1200

On Fri, 31 May 2024 at 09:28, Wong, Kam Fook (TR Technology)
<[email-masked]> wrote:
> Is there a way to delete a specific column level stats/histogram.  The following approach does not work.
>

You'd have to:

DELETE FROM pg_statistic WHERE starelid = 'abc'::regclass and
staattnum = (SELECT attnum FROM pg_attribute WHERE attrelid =
'abc'::regclass AND attname = 'bg_org_partner');

to get rid of it.

David

---

From: David Rowley <[email-masked]>
Date: Fri, 31 May 2024 13:55:38 +1200

(please keep communication on the list)

On Fri, 31 May 2024 at 13:43, Wong, Kam Fook (TR Technology)
<[email-masked]> wrote:
>
> Silly question why did I run into this problem below?  Will the autovacuum analyze abc reset it back which I don't want it to.
>
> DELETE FROM pg_statistic WHERE starelid = 'abc'::regclass and staattnum = (SELECT attnum FROM pg_attribute WHERE attrelid = 'abc'::regclass AND attname = 'bg_org_partner');
>
> SQL Error [42P01]: ERROR: relation "abc" does not exist
>   Position: 52

The schema for the abc table will need to be in your search_path.
You'll need to be connected to the correct database too.

David