Re: [ADMIN] How to drop stats on table

of cause it's mvcc and can be vacuumed by autovacuum or manually.
but 10's of GB sounds wrong for me, not sure how many tables you have, but
it's just too much.
anyway you can vacuum tables more aggressively on system level, or manually
vacuum the stats table.

see my test with 1000 tables 10K rows each.
select pg_size_pretty(pg_total_relation_size('pg_statistic'::regclass));
-- 504 kB

do $$ declare i int4;
begin
for i in 1..1000
loop
execute 'create table t'||i||' as select generate_series(1, 10000)';
execute 'analyze t'||i;
execute 'drop table t'||i;
end loop;
end $$;

select pg_size_pretty(pg_total_relation_size('pg_statistic'::regclass));
-- 888 kB

vacuum pg_statistic;

select pg_size_pretty(pg_total_relation_size('pg_statistic'::regclass));
-- 504 kB

Best Regards,
Alexander Shereshevsky
+972-52-7460635

On Sat, Nov 21, 2015 at 9:24 AM, Sridhar N Bamandlapally <
sridhar(dot)bn1(at)gmail(dot)com> wrote:

> the actual issue is, when
>
> 1. temp table <say tmp1>is created with rows
> 2. stats/analyze on table (tmp1)
> 3. table dropped (tmp1)
>
> but in stats related catalog tables a blot is created
>
> In this scenario, thousands of temp tables created per day, blots are
> increasing and stats related tables are growing to 10's of GB
>
> however, we schedule vacuum on catalog tables to control size
>
> the worry is, catalog tables also undergo MVCC concept
>
> I think when table is dropped, should have option to remove or reuse
> related blot-space on catalog tables
>
> -Sridhar
>
>
>
>
> On Fri, Nov 20, 2015 at 5:54 PM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
> wrote:
>
>> Sridhar N Bamandlapally wrote:
>> > is there any feature available in postgres to drop stats on table?
>>
>> What about
>>
>> DELETE FROM pg_catalog.pg_statistic WHERE starelid = <table oid>
>>
>> Yours,
>> Laurenz Albe
>>
>
>