How to drop stats on table

pg_stat_reset_single_table_counters(oid) ?

Best Regards,
Alexander Shereshevsky

On Fri, Nov 20, 2015 at 1:54 PM, Sridhar N Bamandlapally <
sridhar.bn1@gmail.com> 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general