Обсуждение: How to drop stats on table
Hi
is there any feature available in postgres to drop stats on table?
thanks
Sridhar
pg_stat_reset_single_table_counters(oid) ?
Best Regards,
Alexander Shereshevsky
Alexander Shereshevsky
On Fri, Nov 20, 2015 at 1:54 PM, Sridhar N Bamandlapally <sridhar.bn1@gmail.com> wrote:
Hiis there any feature available in postgres to drop stats on table?thanksSridhar
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
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
On 11/20/2015 11:24 PM, Sridhar N Bamandlapally 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 What is a blot? > > 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 You will need to be more specific, which catalog tables are growing? Also what Postgres version are you using? I am not seeing the behavior you describe, when using 9.4: production=> create temp table p1 as select plant1.* from plant1; SELECT 4535 production=> analyze p1; ANALYZE production=> select relname, n_tup_ins from pg_stat_user_tables where relname='p1'; relname | n_tup_ins ---------+----------- p1 | 4535 (1 row) production=> drop table p1; DROP TABLE production=> select relname, n_tup_ins from pg_stat_user_tables where relname='p1'; relname | n_tup_ins ---------+----------- (0 rows) > > -Sridhar > > > > On Fri, Nov 20, 2015 at 5:54 PM, Albe Laurenz <laurenz.albe@wien.gv.at > <mailto: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 > > -- Adrian Klaver adrian.klaver@aklaver.com
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
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, when1. temp table <say tmp1>is created with rows2. stats/analyze on table (tmp1)3. table dropped (tmp1)but in stats related catalog tables a blot is createdIn this scenario, thousands of temp tables created per day, blots are increasing and stats related tables are growing to 10's of GBhowever, we schedule vacuum on catalog tables to control sizethe worry is, catalog tables also undergo MVCC conceptI think when table is dropped, should have option to remove or reuse related blot-space on catalog tables-SridharOn 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
On Fri, Nov 20, 2015 at 11:24 PM, 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 That should happen automatically, unless your server crashes often while the tables are in place, or you have encountered a bug. How reliably can you trigger the problem? And what version are you on? Cheers, Jeff
Sridhar N Bamandlapally 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 What is a blot in this context? Are you talking about "bloat"? > 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 It must be "bloat" you're talking about. Do you have "autovacuum = on"? If yes, it should take care of the problem if there are only thousands of temporary tables per day. What do you get for SELECT last_autovacuum, last_autoanalyze FROM pg_stat_sys_tables WHERE schemaname = 'pg_catalog' AND relname = 'pg_statistic'; Yours, Laurenz Albe