Skip site navigation (1) Skip section navigation (2)

Re: wholly / partially table read into shared buffer

From: michal(dot)zaborowski(at)gmail(dot)com
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: wholly / partially table read into shared buffer
Date: 2007-09-28 11:37:07
Message-ID: 1190979427.877871.198750@50g2000hsm.googlegroups.com (view raw or flat)
Thread:
Lists: pgsql-admin
On 24 Wrz, 23:59, si(dot)(dot)(dot)(at)2ndquadrant(dot)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)


In response to

pgsql-admin by date

Next:From: Bruce MomjianDate: 2007-09-29 10:59:01
Subject: Re: log_duration / log_min_duration_statement differences between 8.1.8 and 8.2.4
Previous:From: =?shift-jis?B?c2F0b21p?=Date: 2007-09-27 16:59:14
Subject: 金山です。

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group