Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 285CD1131ED1 for ; Thu, 22 Dec 2011 14:06:16 -0400 (AST) Received: from mailgate.iii.com ([205.227.88.3]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Rdn20-0000gk-PV for pgsql-admin@postgresql.org; Thu, 22 Dec 2011 18:06:17 +0000 Received: from mailhub.iii.com (mailhub [10.1.2.3]) by mailgate.iii.com (8.13.6/8.13.6) with ESMTP id pBMI606G005621 for ; Thu, 22 Dec 2011 10:06:00 -0800 (PST) Received: from [10.1.3.160] ([10.1.3.160]) by mailhub.iii.com (8.13.6/8.13.6) with ESMTP id pBMIAYRx030430 for ; Thu, 22 Dec 2011 10:10:35 -0800 (PST) Message-ID: <4EF37188.1000208@iii.com> Date: Thu, 22 Dec 2011 10:06:00 -0800 From: Dinesh Bhandary Reply-To: dbhandary@iii.com Organization: Innovative Interfaces User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:8.0) Gecko/20111105 Thunderbird/8.0 MIME-Version: 1.0 To: pgsql-admin Subject: impact of truncate table on indexes Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -4.5 (----) X-Archive-Number: 201112/131 X-Sequence-Number: 38889 Hi All - We have been having some performance issues with indexes right after truncating a table. 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. 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. What is the best procedure in scenarios like this? Recreating indexes seems to solve the problem, but I am trying to find out if there are other alternatives out there. Thanks. Dinesh