Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id D75D2133655C for ; Thu, 14 Apr 2011 05:23:34 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 98250-02 for ; Thu, 14 Apr 2011 08:23:27 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from elizabeth.gransy.com (elizabeth.gransy.com [89.187.132.199]) by mail.postgresql.org (Postfix) with ESMTP id C5CAA13364DE for ; Thu, 14 Apr 2011 05:23:27 -0300 (ADT) Received: from sq.gransy.com (localhost [127.0.0.1]) by elizabeth.gransy.com (Postfix) with ESMTP id 5DB1115E410A; Thu, 14 Apr 2011 10:23:26 +0200 (CEST) Received: from 85.160.164.52 (SquirrelMail authenticated user tv@fuzzy.cz) by sq.gransy.com with HTTP; Thu, 14 Apr 2011 10:23:26 +0200 Message-ID: <5c6c67e9f0c4abed2b7ac84e83fe1f32.squirrel@sq.gransy.com> In-Reply-To: References: <20110412171855.GA14292@tux> <4DA496FA.3070908@fuzzy.cz> <8F22D592-23C1-4A3C-94A5-48363332ADD3@darkstatic.com> <4DA4BFA2.5060601@fuzzy.cz> <4DA4D40A.4010200@fuzzy.cz> <4DA56DA8020000250003C783@gw.wicourts.gov> <4DA6216E.9020907@fuzzy.cz> <4DA63125.5070106@fuzzy.cz> Date: Thu, 14 Apr 2011 10:23:26 +0200 Subject: Re: Performance From: tv@fuzzy.cz To: "Claudio Freire" Cc: "Tomas Vondra" , pgsql-performance@postgresql.org User-Agent: SquirrelMail/1.4.21 MIME-Version: 1.0 Content-Type: text/plain;charset=utf-8 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201104/211 X-Sequence-Number: 43273 > On Thu, Apr 14, 2011 at 1:26 AM, Tomas Vondra wrote: >> Workload A: Touches just a very small portion of the database, to the >> 'active' part actually fits into the memory. In this case the cache hit >> ratio can easily be close to 99%. >> >> Workload B: Touches large portion of the database, so it hits the drive >> very often. In this case the cache hit ratio is usually around RAM/(size >> of the database). > > You've answered it yourself without even realized it. > > This particular factor is not about an abstract and opaque "Workload" > the server can't know about. It's about cache hit rate, and the server > can indeed measure that. OK, so it's not a matter of tuning random_page_cost/seq_page_cost? Because tuning based on cache hit ratio is something completely different (IMHO). Anyway I'm not an expert in this field, but AFAIK something like this already happens - btw that's the purpose of effective_cache_size. But I'm afraid there might be serious fail cases where the current model works better, e.g. what if you ask for data that's completely uncached (was inactive for a long time). But if you have an idea on how to improve this, great - start a discussion in the hackers list and let's see. regards Tomas