X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id DED7A52E8B for ; Tue, 26 Jul 2005 15:04:11 -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 00529-10 for ; Tue, 26 Jul 2005 18:04:04 +0000 (GMT) Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by svr1.postgresql.org (Postfix) with ESMTP id 8DD7052EB1 for ; Tue, 26 Jul 2005 15:04:02 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 726BD30B42; Tue, 26 Jul 2005 20:11:19 +0200 (MET DST) From: Chris Browne X-Newsgroups: pgsql.sql Subject: Re: REINDEX DATABASE Date: Tue, 26 Jul 2005 13:14:42 -0400 Organization: cbbrowne Computing Inc Lines: 55 Message-ID: <60pst51ocd.fsf@dba2.int.libertyrms.com> References: Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-7 Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.17 (Jumbo Shrimp, linux) Cancel-Lock: sha1:nSgmlGmXbit9dus/4thFCSc14Fk= To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.124 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200507/229 X-Sequence-Number: 22207 achill@matrix.gatewaynet.com (Achilleus Mantzios) writes: > O Christopher Browne έγραψε στις Jul 26, 2005 : > >> > Hello >> > >> > Would you like to advice to use REINDEX DATABASE on regular basis ? >> > >> > if (yes) >> > how it should be connected with VACUUM FULL ANALYZE which is run >> > regularly ? (reindex before vacuum or vacuum before reindex?) >> > >> > else >> > haw to determine _when_ to run REINDEX ? >> >> If you are doing ordinary VACUUM ANALYZE frequently enough, it >> shouldn't be necessary to either VACUUM FULL or REINDEX. >> >> Back in the 7.2 days, there were sorts of update patterns that would >> mandate reindexing every so often, as you could get cases where index >> pages would be very sparsely populated. That was alleviated in >> version 7.3, I believe, and was clearly evident in 7.4. >> >> You know you need to REINDEX if analysis of an index shows that it is >> sparsely populated. This generally shows up if you do an analyze on >> the table and find an index has more pages than tuples. >> >> But if you run VACUUM reasonably frequently, this shouldn't be >> necessary... > > I vacuum analyze every hour, however after reindexdb > my (currently) 2.47 GB db is reduced to 2.37 GB, > thus helping both db-wise and freebsd_cache-wise boost performance. > > All the above in 7.4.6. > > I am not arguing that i have done detailed analysis of the > situation, i'm just saying that i have witnessed a performance gain after > running contrib/reindexdb every month (or so). That doesn't strike me as being a material improvement, and it comes at a pretty high cost. You can get a savings of about 4% of the space, but at the cost of taking an appreciable outage during which the database is not usable. I wouldn't expect the 4% savings in space to lead to a particularly measurable improvement in performance, certainly not one worth the outage. -- (format nil "~S@~S" "cbbrowne" "acm.org") http://www.ntlug.org/~cbbrowne/sap.html Rules of the Evil Overlord #78. "I will not tell my Legions of Terror "And he must be taken alive!" The command will be: ``And try to take him alive if it is reasonably practical.''"