public inbox for [email protected]
help / color / mirror / Atom feedHow to delete column level Stats/Histogram
3+ messages / 2 participants
[nested] [flat]
* How to delete column level Stats/Histogram
@ 2024-05-30 21:27 Wong, Kam Fook (TR Technology) <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Wong, Kam Fook (TR Technology) @ 2024-05-30 21:27 UTC (permalink / raw)
To: pgsql-general
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to delete column level Stats/Histogram
@ 2024-05-30 22:45 David Rowley <[email protected]>
parent: Wong, Kam Fook (TR Technology) <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: David Rowley @ 2024-05-30 22:45 UTC (permalink / raw)
To: Wong, Kam Fook (TR Technology) <[email protected]>; +Cc: pgsql-general
On Fri, 31 May 2024 at 09:28, Wong, Kam Fook (TR Technology)
<[email protected]> wrote:
> 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;
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [EXT] Re: How to delete column level Stats/Histogram
@ 2024-05-31 01:55 David Rowley <[email protected]>
parent: David Rowley <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: David Rowley @ 2024-05-31 01:55 UTC (permalink / raw)
To: Wong, Kam Fook (TR Technology) <[email protected]>; pgsql-general
(please keep communication on the list)
On Fri, 31 May 2024 at 13:43, Wong, Kam Fook (TR Technology)
<[email protected]> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-05-31 01:55 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-30 21:27 How to delete column level Stats/Histogram Wong, Kam Fook (TR Technology) <[email protected]>
2024-05-30 22:45 ` David Rowley <[email protected]>
2024-05-31 01:55 ` David Rowley <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox