From bwillits@cox.net Tue Jun 19 17:43:22 2007 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 72A799FB55A for ; Tue, 19 Jun 2007 14:43:22 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 45401-08 for ; Tue, 19 Jun 2007 14:43:17 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from fed1rmmtao103.cox.net (fed1rmmtao103.cox.net [68.230.241.43]) by postgresql.org (Postfix) with ESMTP id 7F1C89FB526 for ; Tue, 19 Jun 2007 14:43:18 -0300 (ADT) Received: from fed1rmimpo01.cox.net ([70.169.32.71]) by fed1rmmtao103.cox.net (InterMail vM.7.08.02.01 201-2186-121-102-20070209) with ESMTP id <20070619174316.ERWL1594.fed1rmmtao103.cox.net@fed1rmimpo01.cox.net> for ; Tue, 19 Jun 2007 13:43:16 -0400 Received: from bwlaptop ([68.101.98.29]) by fed1rmimpo01.cox.net with bizsmtp id DVjD1X0030e2M1w0000000; Tue, 19 Jun 2007 13:43:17 -0400 Message-ID: <004201c7b299$4ceb7bf0$b601a8c0@bwlaptop> From: "Bill Willits" To: Subject: reindex in v8.2 Date: Tue, 19 Jun 2007 10:43:07 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_003F_01C7B25E.9F9BDB50" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.3138 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.339 tagged_above=0 required=5 tests=AWL=0.203, BAYES_50=0.001, HTML_50_60=0.134, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200706/185 X-Sequence-Number: 25853 This is a multi-part message in MIME format. ------=_NextPart_000_003F_01C7B25E.9F9BDB50 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello: We are in the process of upgrading posgres from v7.3.9 to v8.2.4 In our = current environment (v7.3), we run a reindex operation on all tables to = recover space and improve performance (we have several tables with high = insert/update load - no deletes). Is it likely that the reindex will = still be beneficial after the upgrade? Or has the pg code changed such = that this operation is obsolete? Thanks in advance. Bill Willits ------=_NextPart_000_003F_01C7B25E.9F9BDB50 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hello:
 
We are in the process of upgrading = posgres from=20 v7.3.9 to v8.2.4  In our current environment (v7.3), we run a = reindex=20 operation on all tables to recover space and improve performance (we = have=20 several tables with high insert/update load - no deletes).  Is it = likely=20 that the reindex will still be beneficial after the = upgrade?  Or=20 has the pg code changed such that this operation is = obsolete?
 
Thanks in advance.
Bill Willits
------=_NextPart_000_003F_01C7B25E.9F9BDB50-- From alvherre@commandprompt.com Tue Jun 19 17:53:01 2007 Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 4C9609FBCD0 for ; Tue, 19 Jun 2007 14:53:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 13657-07 for ; Tue, 19 Jun 2007 14:52:54 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from lists.commandprompt.com (host-254.commandprompt.net [207.173.203.254]) by postgresql.org (Postfix) with ESMTP id DF5399FBC7F for ; Tue, 19 Jun 2007 14:52:53 -0300 (ADT) Received: from perhan.alvh.no-ip.org (190-13-143-52.bk13-dsl.surnet.cl [190.13.143.52]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l5JHqqwO000717; Tue, 19 Jun 2007 10:52:53 -0700 Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id 0DD8032A23; Tue, 19 Jun 2007 13:52:50 -0400 (CLT) Date: Tue, 19 Jun 2007 13:52:50 -0400 From: Alvaro Herrera To: Bill Willits Cc: pgsql-admin@postgresql.org Subject: Re: reindex in v8.2 Message-ID: <20070619175250.GB21268@alvh.no-ip.org> References: <004201c7b299$4ceb7bf0$b601a8c0@bwlaptop> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <004201c7b299$4ceb7bf0$b601a8c0@bwlaptop> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: ClamAV version 0.88.5, clamav-milter version 0.88.5 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Tue, 19 Jun 2007 10:52:53 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/186 X-Sequence-Number: 25854 Bill Willits wrote: > Hello: > > We are in the process of upgrading posgres from v7.3.9 to v8.2.4 In > our current environment (v7.3), we run a reindex operation on all > tables to recover space and improve performance (we have several > tables with high insert/update load - no deletes). Is it likely that > the reindex will still be beneficial after the upgrade? Or has the pg > code changed such that this operation is obsolete? The index code was improved in 7.4, but some reindexing is still needed in (rarer) corner cases. Try getting rid of the reindex and track your indexes sizes for a while. If you don't notice a problem you can forget it entirely. Most people gets away without it. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. From fbsd@drivefaster.net Tue Jun 19 17:55:11 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 3FAD19FBE3C for ; Tue, 19 Jun 2007 14:55:11 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 29211-03 for ; Tue, 19 Jun 2007 14:55:00 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from crestone.coronasolutions.com (crestone.coronasolutions.com [66.45.104.24]) by postgresql.org (Postfix) with ESMTP id 20E449FBE7F for ; Tue, 19 Jun 2007 14:55:02 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by crestone.coronasolutions.com (Postfix) with ESMTP id B047E6447F5; Tue, 19 Jun 2007 11:54:59 -0600 (MDT) Received: from crestone.coronasolutions.com ([127.0.0.1]) by localhost (crestone.coronasolutions.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 06216-06; Tue, 19 Jun 2007 11:54:50 -0600 (MDT) Received: from mac.local (c-76-25-77-161.hsd1.co.comcast.net [76.25.77.161]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by crestone.coronasolutions.com (Postfix) with ESMTP id B4420644809 for ; Tue, 19 Jun 2007 11:54:48 -0600 (MDT) Message-ID: <46781868.5040108@drivefaster.net> Date: Tue, 19 Jun 2007 11:54:48 -0600 From: Dan Harris User-Agent: Thunderbird 2.0.0.4 (Macintosh/20070604) MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: reindex in v8.2 References: <004201c7b299$4ceb7bf0$b601a8c0@bwlaptop> In-Reply-To: <004201c7b299$4ceb7bf0$b601a8c0@bwlaptop> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at coronasolutions.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/187 X-Sequence-Number: 25855 Bill Willits wrote: > Hello: > > We are in the process of upgrading posgres from v7.3.9 to v8.2.4 In our > current environment (v7.3), we run a reindex operation on all tables to > recover space and improve performance (we have several tables with high > insert/update load - no deletes). Is it likely that the reindex will > still be beneficial after the upgrade? Or has the pg code changed such > that this operation is obsolete? > > Thanks in advance. > Bill Willits When you reload your dump file into 8.2, the indexes should be created fresh and AFAIK, a reindex at that point would not gain anything for you.