X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C955D8BA123 for ; Tue, 15 Feb 2005 07:36:13 +0000 (GMT) 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 94442-02 for ; Tue, 15 Feb 2005 07:36:11 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 267668B9F30 for ; Tue, 15 Feb 2005 07:36:10 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j1F7a2aA009327; Tue, 15 Feb 2005 02:36:03 -0500 (EST) To: Greg Stark Cc: Mark Aufflick , pgsql-performance@postgresql.org Subject: Re: seq scan cache vs. index cache smackdown In-reply-to: <87d5v247z8.fsf@stark.xeocode.com> References: <87d5v247z8.fsf@stark.xeocode.com> Comments: In-reply-to Greg Stark message dated "15 Feb 2005 02:07:07 -0500" Date: Tue, 15 Feb 2005 02:36:02 -0500 Message-ID: <9326.1108452962@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/233 X-Sequence-Number: 10562 Greg Stark writes: > 8.0, on the other hand, has a new algorithm that specifically tries to > protect against the shared buffers being blown out by a sequential > scan. But that will only help if it's the shared buffers being > thrashed that's hurting you, not the entire OS file system cache. Something we ought to think about sometime: what are the performance implications of the real-world situation that we have another level of caching sitting underneath us? AFAIK all the theoretical studies we've looked at consider only a single level of caching. But for example, if our buffer management algorithm recognizes an index page as being heavily hit and therefore keeps it in cache for a long time, then when it does fall out of cache you can be sure it's going to need to be read from disk when it's next used, because the OS-level buffer cache has not seen a call for that page in a long time. Contrariwise a page that we think is only on the fringe of usefulness is going to stay in the OS cache because we repeatedly drop it and then have to ask for it again. I have no idea how to model this situation, but it seems like it needs some careful thought. regards, tom lane