X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id AD641D1B4EF for ; Sun, 5 Oct 2003 22:20:55 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 17862-06 for ; Sun, 5 Oct 2003 19:20:12 -0300 (ADT) Received: from news.hub.org (unknown [64.117.224.194]) by svr1.postgresql.org (Postfix) with ESMTP id 034D1D1B520 for ; Sun, 5 Oct 2003 19:20:10 -0300 (ADT) Received: from news.hub.org (host-64-117-224-194.altec1.com [64.117.224.194] (may be forged)) by news.hub.org (8.12.9/8.12.9) with ESMTP id h95MK5Qh082405 for ; Sun, 5 Oct 2003 22:20:05 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id h95M4YQw080756 for pgsql-performance@postgresql.org; Sun, 5 Oct 2003 22:04:34 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.performance Subject: Re: reindex/vacuum locking/performance? Date: Sun, 05 Oct 2003 17:57:42 -0400 Organization: cbbrowne Computing Inc Lines: 23 Message-ID: References: <1368.1065373870@sss.pgh.pa.us> 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.1003 (Gnus v5.10.3) XEmacs/21.4 (Reasonable Discussion, linux) Cancel-Lock: sha1:NvJnCRv57A/AFMW7U2gKB7DEgsY= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/162 X-Sequence-Number: 3910 After a long battle with technology,matt@ymogen.net ("Matt Clark"), an earthling, wrote: >> The point is that a big seqscan (either VACUUM or a plain table scan) >> hits a lot of pages, and thereby tends to fill your cache with pages >> that aren't actually likely to get hit again soon, perhaps pushing out >> pages that will be needed again soon. This happens at both the >> shared-buffer and kernel-disk-cache levels of caching. > > OK, I had thought (wrongly it seems, as usual, but this is how we learn!) > that a plain VACUUM did not incur a read of all pages. I still don't > understand *why* it does, but I'll take your word for it. How does it know what to do on any given page if it does not read it in? It has to evaluate whether tuples can be thrown away or not, and that requires looking at the tuples. It may only be looking at a small portion of the page, but that still requires reading each page. No free lunch, unfortunately... -- wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','cbbrowne.com'). http://www3.sympatico.ca/cbbrowne/sgml.html "End users are just test loads for verifying that the system works, kind of like resistors in an electrical circuit." -- Kaz Kylheku in c.o.l.d.s