X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5C3BF534FB for ; Thu, 21 Apr 2005 14:39:15 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 46490-08 for ; Thu, 21 Apr 2005 17:38:57 +0000 (GMT) Received: from web51410.mail.yahoo.com (web51410.mail.yahoo.com [206.190.38.189]) by svr1.postgresql.org (Postfix) with SMTP id 8A68053981 for ; Thu, 21 Apr 2005 14:38:55 -0300 (ADT) Received: (qmail 76288 invoked by uid 60001); 21 Apr 2005 17:38:55 -0000 Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; b=ZjrvB8avDdSSp0j+E6fmD5g7pG4Lcyks4ZOcm2hXZERjy5OwOl3fpYEQOsVI+Npm8Imhe1OXbtJVLmD7iJ58ndrFLPX/jTY6p6tSRxp2E4hhZEGHVC8UJF6qm7DlzjJVzongB5KmzKmbUBwfy0kePyldzk9+EbGWDVNW/Jc6dio= ; Message-ID: <20050421173855.76286.qmail@web51410.mail.yahoo.com> Received: from [192.88.67.254] by web51410.mail.yahoo.com via HTTP; Thu, 21 Apr 2005 10:38:55 PDT Date: Thu, 21 Apr 2005 10:38:55 -0700 (PDT) From: Bill Chandler Subject: Re: Index bloat problem? To: Josh Berkus Cc: pgsql-perform In-Reply-To: 6667 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.898 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, FROM_ENDS_IN_NUMS X-Spam-Level: X-Archive-Number: 200504/576 X-Sequence-Number: 11973 --- Josh Berkus wrote: > Bill, > > > What about if an out-of-the-ordinary number of > rows > > were deleted (say 75% of rows in the table, as > opposed > > to normal 5%) followed by a 'VACUUM ANALYZE'? >  Could > > things get out of whack because of that situation? > > Yes. You'd want to run REINDEX after and event like > that. As you should now. > > -- > Josh Berkus > Aglio Database Solutions > San Francisco > Thank you. Though I must say, that is very discouraging. REINDEX is a costly operation, timewise and due to the fact that it locks out other processes from proceeding. Updates are constantly coming in and queries are occurring continuously. A REINDEX could potentially bring the whole thing to a halt. Honestly, this seems like an inordinate amount of babysitting for a production application. I'm not sure if the client will be willing to accept it. Admittedly my knowledge of the inner workings of an RDBMS is limited, but could somebody explain to me why this would be so? If you delete a bunch of rows why doesn't the index get updated at the same time? Is this a common issue among all RDBMSs or is it something that is PostgreSQL specific? Is there any way around it? thanks, Bill __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com