Re: [ADMIN] How to drop stats on table

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(dot)albe(at)wien(dot)gv(dot)at
> <mailto: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
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com