X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 69056D1D287 for ; Thu, 5 Feb 2004 13:44:49 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 85568-02 for ; Thu, 5 Feb 2004 09:44:56 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 25AA5D1D1CD for ; Thu, 5 Feb 2004 09:44:47 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i15Diq1Y034328 for ; Thu, 5 Feb 2004 13:44:52 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i15DKZfW027766 for pgsql-admin@postgresql.org; Thu, 5 Feb 2004 13:20:35 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.admin Subject: Re: R: slow seqscan after vacuum analize Date: Thu, 05 Feb 2004 08:15:24 -0500 Organization: cbbrowne Computing Inc Lines: 35 Message-ID: References: <2s28b9$kqpmo@mailr-1.tiscali.it> <006401c3ebb1$d4237c60$7201a8c0@mst1x5r347kymb> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1006 (Gnus v5.10.6) XEmacs/21.4 (Reasonable Discussion, linux) Cancel-Lock: sha1:fFuhfIk2lW9mb/UeLxqpOKi4+d8= To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/56 X-Sequence-Number: 12230 Centuries ago, Nostradamus foresaw when iain@mst.co.jp ("Iain") would write: > I'd like to know more about the possibility of plain vacuums harming > performance. This is the first I've heard of it. Vacuum full is not always > an option in a production environment. There certainly are known cases where systems where the I/O bus is already fairly much saturated will suffer BADLY when a big vacuum is thrown at them. The problem in such cases is that the vacuum draws the pages that it is working on into the buffer cache, pushing out data that is actually useful to cache. There are experimental patches for 7.4, 7.3, and even, I believe, 7.2, for a "sleeping vacuum" that tries to limit the damage by sleeping every so often so that the vacuum does not dominate, and so that "ordinary traffic" gets a chance to reclaim cache. And there are efforts in 7.5 to improve cache management, so that pages brought in by VACUUM would be put at the opposite end of the "LRU" queue. That way, instead of them being treated as Most Recently Used, pushing everything the least bit older towards being dropped from the buffer cache, the vacuumed pages would be treated as if they were LRU, so they would get evicted FIRST. But if the Original Poster is encountering that the database is doing Seq Scans when it would be better to do an Index Scan, that is a separate problem, and focusing on the VACUUM may distract from the _real_ problem... -- let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];; http://www.ntlug.org/~cbbrowne/advocacy.html Rules of the Evil Overlord #195. "I will not use hostages as bait in a trap. Unless you're going to use them for negotiation or as human shields, there's no point in taking them."