Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 69FDD9F9BE3 for ; Fri, 28 Sep 2007 08:52:34 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 14941-07 for ; Fri, 28 Sep 2007 08:52:33 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by postgresql.org (Postfix) with ESMTP id 1CAD29F9BBF for ; Fri, 28 Sep 2007 08:52:34 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.14.1/8.14.1) with ESMTP id l8SBqTIw082252 for ; Fri, 28 Sep 2007 08:52:29 -0300 (ADT) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.14.1/8.14.1/Submit) id l8SBb4M4074025 for pgsql-admin@postgresql.org; Fri, 28 Sep 2007 08:37:04 -0300 (ADT) (envelope-from news) From: michal.zaborowski@gmail.com X-Newsgroups: pgsql.admin Subject: Re: wholly / partially table read into shared buffer Date: Fri, 28 Sep 2007 11:37:07 -0000 Organization: http://groups.google.com Lines: 29 Message-ID: <1190979427.877871.198750@50g2000hsm.googlegroups.com> References: <42071190300869@webmail27.yandex.ru> <1190671185.4181.236.camel@ebony.site> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Complaints-To: groups-abuse@google.com In-Reply-To: <1190671185.4181.236.camel@ebony.site> User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.8.1.6) Gecko/20061201 Firefox/2.0.0.6 (Ubuntu-feisty),gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: 50g2000hsm.googlegroups.com; posting-host=193.33.58.14; posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0 To: pgsql-admin@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/152 X-Sequence-Number: 26913 On 24 Wrz, 23:59, si...@2ndquadrant.com (Simon Riggs) wrote: > On Thu, 2007-09-20 at 19:07 +0400, wrote: > > I am newbie in Postgres, but previously worked with Oracle. Now I am > > puzzled with heap_blks_hit/heap_blks_read ratio. I make select from > > table (whose size is larger than shared_buffer) and saw that it was > > wholly read from disk after second, third and so on runs. Isn't is > > wrong that RDBMS tries to cache table data blocks at least PARTIALLY > > in the case of insufficient cache buffer? > > It does cache partially, but its always the wrong part of the table. > Let's say - you have very big table. Even if you are querying data with index - table read will be done... If your queries are quite randomly hitting table pages - you *will* see that effect. You can think about partitions - idea looks good - you can save some IO bandwidth paying with processor time. Partitions can help if you do seq-scans, but... with index scans and heap_blks_hit - does not. Instead you can prepare 'index cluster' a specially when there are not too many writes... -- Regards, Micha Zaborowski (TeXXaS)