Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-general-owner+M217398=pgsql-general-arkari@postgresql.org>)
 id 1a09vG-0003tW-79
 for pgsql-general@arkaria.postgresql.org; Sat, 21 Nov 2015 15:17:54 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.84) (envelope-from
 <pgsql-general-owner+M217398=pgsql-general-arkari@postgresql.org>)
 id 1a09vF-0007CF-PL
 for pgsql-general@arkaria.postgresql.org; Sat, 21 Nov 2015 15:17:53 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
 (Exim 4.84) (envelope-from <adrian.klaver@aklaver.com>)
 id 1a09vE-0007Bw-QB
 for pgsql-general@postgresql.org; Sat, 21 Nov 2015 15:17:53 +0000
Received: from out5-smtp.messagingengine.com ([66.111.4.29])
 by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
 (Exim 4.84) (envelope-from <adrian.klaver@aklaver.com>)
 id 1a09vB-0004Qe-8B
 for pgsql-general@postgresql.org; Sat, 21 Nov 2015 15:17:51 +0000
Received: from compute6.internal (compute6.nyi.internal [10.202.2.46])
 by mailout.nyi.internal (Postfix) with ESMTP id 081AE202C4
 for <pgsql-general@postgresql.org>; Sat, 21 Nov 2015 10:17:47 -0500 (EST)
Received: from frontend2 ([10.202.2.161])
 by compute6.internal (MEProxy); Sat, 21 Nov 2015 10:17:47 -0500
DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d=aklaver.com; h=cc
 :content-transfer-encoding:content-type:date:from:in-reply-to
 :message-id:mime-version:references:subject:to:x-sasl-enc
 :x-sasl-enc; s=mesmtp; bh=XD9rtVQK9zbK/19WnOj1ltG5wDM=; b=fbrRPk
 OMiH6jKlSBfaIrJWCs2k3NRM0BlDuz3fFM86Qsw/m9e91n+Z2m6d1+mI4YYHqjF6
 SE6/zGkthN/u+yvvJOaNFxHYfywEfwUPubCPyedA5h1InqlYaQI4PazZnVgTyCjN
 vjy3Msr8p3k0O4ndmgUFUo8SZ6VTu3Yl/9Sc4=
DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d=
 messagingengine.com; h=cc:content-transfer-encoding:content-type
 :date:from:in-reply-to:message-id:mime-version:references
 :subject:to:x-sasl-enc:x-sasl-enc; s=smtpout; bh=XD9rtVQK9zbK/19
 WnOj1ltG5wDM=; b=Bhw1FDOWP0EdUbsxqLiH2ZKvztVqdoEAnW/witKfYRcipME
 /OLmKAbBDXbjcnaqmShjDaVi80LUdsuQ2xUDYR3PiTgYFG9sprYJ0d+PhVEjGYh2
 mj4lItP7eqeynrupoArSUDfYISEq8vU3M0SaKZsJaGdwgsabhi03B/fCUI5c=
X-Sasl-enc: kcPR+vi+N0GuvFG8oeU2sbTYxRoBAfOWoH1n7TyM8edH 1448119066
Received: from [192.168.1.2] (65-102-182-223.tukw.qwest.net [65.102.182.223])
 by mail.messagingengine.com (Postfix) with ESMTPA id 45AE0680179;
 Sat, 21 Nov 2015 10:17:46 -0500 (EST)
Subject: Re: [ADMIN] How to drop stats on table
To: Sridhar N Bamandlapally <sridhar.bn1@gmail.com>,
 Albe Laurenz <laurenz.albe@wien.gv.at>
References: 
 <CAGuFTBUC-G4wtwL-gQVj9CkRsxwAYeFCpkmRnzxLaVUFJ3+6OQ@mail.gmail.com>
 <A737B7A37273E048B164557ADEF4A58B50FE8202@ntex2010i.host.magwien.gv.at>
 <CAGuFTBXZr+Ab3Qnw9M-MZu=k8qh_p23UqhrnZW8vVYZmhC6bLw@mail.gmail.com>
Cc: PG-General Mailing List <pgsql-general@postgresql.org>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <56508B05.9000008@aklaver.com>
Date: Sat, 21 Nov 2015 07:17:25 -0800
User-Agent: Mozilla/5.0 (X11; Linux i686; rv:38.0) Gecko/20100101
 Thunderbird/38.3.0
MIME-Version: 1.0
In-Reply-To: 
 <CAGuFTBXZr+Ab3Qnw9M-MZu=k8qh_p23UqhrnZW8vVYZmhC6bLw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -2.7 (--)
List-Archive: <http://archives.postgresql.org/pgsql-general>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-general.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@postgresql.org>
List-Post: <mailto:pgsql-general@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-general>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
X-Mailing-List: pgsql-general
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org

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