From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
To: Albe Laurenz <laurenz.albe@wien.gv.at>
Cc: PG-General Mailing List <pgsql-general@postgresql.org>
Subject: Re: [ADMIN] How to drop stats on table
Date: Sat, 21 Nov 2015 07:17:25 -0800
Message-ID: <56508B05.9000008@aklaver.com> (raw)
In-Reply-To: <CAGuFTBXZr+Ab3Qnw9M-MZu=k8qh_p23UqhrnZW8vVYZmhC6bLw@mail.gmail.com>
References: <CAGuFTBUC-G4wtwL-gQVj9CkRsxwAYeFCpkmRnzxLaVUFJ3+6OQ@mail.gmail.com>
<A737B7A37273E048B164557ADEF4A58B50FE8202@ntex2010i.host.magwien.gv.at>
<CAGuFTBXZr+Ab3Qnw9M-MZu=k8qh_p23UqhrnZW8vVYZmhC6bLw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: adrian.klaver@aklaver.com, sridhar.bn1@gmail.com, laurenz.albe@wien.gv.at, pgsql-general@postgresql.org
Subject: Re: [ADMIN] How to drop stats on table
In-Reply-To: <56508B05.9000008@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox