[EMAIL PROTECTED] (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 "[EMAIL PROTECTED]" "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.''" <http://www.eviloverlord.com/> ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq