Re: [ADMIN] How to drop stats on table

[Alexander Shereshevsky <shereshevsky@xxxxxxxxx>]

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.bn1@xxxxxxxxx> 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.albe@xxxxxxxxxx> 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