Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id CC2C01131ED1 for ; Thu, 22 Dec 2011 14:40:39 -0400 (AST) Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RdnZD-0001KM-M3 for pgsql-admin@postgresql.org; Thu, 22 Dec 2011 18:40:39 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id pBMIeM9b017583; Thu, 22 Dec 2011 13:40:22 -0500 (EST) To: dbhandary@iii.com cc: pgsql-admin Subject: Re: impact of truncate table on indexes In-reply-to: <4EF37188.1000208@iii.com> References: <4EF37188.1000208@iii.com> Comments: In-reply-to Dinesh Bhandary message dated "Thu, 22 Dec 2011 10:06:00 -0800" Date: Thu, 22 Dec 2011 13:40:22 -0500 Message-ID: <17582.1324579222@sss.pgh.pa.us> From: Tom Lane X-Pg-Spam-Score: -4.5 (----) X-Archive-Number: 201112/133 X-Sequence-Number: 38891 Dinesh Bhandary writes: > We have been having some performance issues with indexes right after > truncating a table. You'd need to be a lot more specific than that if you want useful help. Also, pgsql-performance is a better list for discussing performance issues. http://wiki.postgresql.org/wiki/SlowQueryQuestions > My understanding is truncate removes all the > contents of the table and the space get reclaimed immediately. > In such cases what happens to its indexes. They get rebuilt as empty indexes. > It does not seem like they > are removed, but merely marked for deletion by a vacuum job and it > creates huge bloats and performance problems. On what do you base this clearly-contrary-to-reality conclusion? regards, tom lane