pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
wholly / partially table read into shared buffer
3+ messages / 3 participants
[nested] [flat]

* wholly / partially table read into shared buffer
@ 2007-09-20 15:07  Григорий Титов <nav73@narod.ru>
  0 siblings, 1 reply; 3+ messages in thread

From: Григорий Титов @ 2007-09-20 15:07 UTC (permalink / raw)
  To: pgsql-admin

Hello!
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?
WBW&R, Grigory Titov.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: wholly / partially table read into shared buffer
@ 2007-09-24 21:59  Simon Riggs <simon@2ndquadrant.com>
  parent: Григорий Титов <nav73@narod.ru>
  0 siblings, 1 reply; 3+ messages in thread

From: Simon Riggs @ 2007-09-24 21:59 UTC (permalink / raw)
  To: Григорий Титов <nav73@narod.ru>; +Cc: pgsql-admin

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.

BTQ, Oracle does that too...

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: wholly / partially table read into shared buffer
@ 2007-09-28 11:37  michal.zaborowski@gmail.com
  parent: Simon Riggs <simon@2ndquadrant.com>
  0 siblings, 0 replies; 3+ messages in thread

From: michal.zaborowski@gmail.com @ 2007-09-28 11:37 UTC (permalink / raw)
  To: pgsql-admin

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)




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2007-09-28 11:37 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-09-20 15:07 wholly / partially table read into shared buffer Григорий Титов <nav73@narod.ru>
2007-09-24 21:59 ` Simon Riggs <simon@2ndquadrant.com>
2007-09-28 11:37   ` michal.zaborowski@gmail.com

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox