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 48CCAD1B52E for ; Mon, 6 Oct 2003 16:56:32 +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 03408-06 for ; Mon, 6 Oct 2003 13:55:43 -0300 (ADT) Received: from jamesr.best.vwh.net (jamesr.best.vwh.net [192.220.76.165]) by svr1.postgresql.org (Postfix) with SMTP id E3A5ED1B4EC for ; Mon, 6 Oct 2003 13:55:41 -0300 (ADT) Received: (qmail 43202 invoked by uid 19621); 6 Oct 2003 16:55:41 -0000 Received: from unknown (HELO ?10.0.0.210?) ([65.87.16.98]) (envelope-sender ) by 192.220.76.165 (qmail-ldap-1.03) with SMTP for ; 6 Oct 2003 16:55:41 -0000 Subject: Seqscan buffer promotion (was: reindex/vacuum locking/performance?) From: James Rogers To: Andrew Sullivan Cc: PostgreSQL Performance In-Reply-To: <20031006121459.GC4971@libertyrms.info> References: <1065397835.471.17.camel@tokyo> <20031006121459.GC4971@libertyrms.info> Content-Type: text/plain Organization: Message-Id: <1065459338.26229.26.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.2 (1.2.2-5) Date: 06 Oct 2003 09:55:38 -0700 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/181 X-Sequence-Number: 3929 On Mon, 2003-10-06 at 05:15, Andrew Sullivan wrote: > There's plenty of academic work which purports to show that LRU is > far from the best choice. Just in principle, it seems obvious that a > single-case seqscan-type operation (such as vacuum does) is a good > way to lose your cache for no real gain. Traditionally, seqscan type operations are accommodated in LRU type managers by having multiple buffer promotion policies, primarily because it is simple to implement. For example, if you are doing a seqscan, a buffer loaded from disk is never promoted to the top of the LRU. Instead it is only partially promoted (say, halfway for example) toward the top of the buffer list. A page that is already in the buffer is promoted either to the halfway point or top depending on where it was found. There are numerous variations on the idea, some being more clever and complex than others. The point of this being that a pathological or rare sequential scan can never trash more than a certain percentage of the cache, while not significantly impacting the performance of a sequential scan. The primary nuisance is that it slightly increases the API complexity. I'll add that I don't know what PostgreSQL actually does in this regard, but from the thread it appears as though seqscans are handled like the default case. Cheers, -James Rogers jamesr@best.com