Re: [ADMIN] How to drop stats on table

Поиск
Список
Период
Сортировка
От Alexander Shereshevsky
Тема Re: [ADMIN] How to drop stats on table
Дата
Msg-id CAJMMYvpevvvn-dgQ8hfmN_3SGQ1DF7rAJnjbrybAo8f1evi-FQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [ADMIN] How to drop stats on table  (Sridhar N Bamandlapally <sridhar.bn1@gmail.com>)
Список pgsql-general
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@gmail.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.albe@wien.gv.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


В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: remote connection error:could not connect to server: Connection refused
Следующее
От: Jeff Janes
Дата:
Сообщение: Re: [ADMIN] How to drop stats on table