Re: Too many IO?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Tatsuo Ishii <ishii(at)postgresql(dot)org>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Too many IO?
Date: 2012-03-14 14:46:26
Message-ID: 381.1331736386@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Tatsuo Ishii <ishii(at)postgresql(dot)org> writes:
> I have created a 29GB test database by using standard pgbnech -i -s
> 2000. Then I executed:

That means 200 million accounts rows. With integer keys you could
expect to get 200 to 300 keys per index page. Taking the number as 200
for simplicity, we expect 1 million leaf pages, 5000 level-1 inner
pages, 25 level-2 inner pages, and a level-3 root page. Even if the
tree were packed completely full, it'd still be depth 3.

> As you can see, this query generated 1255+1250 = 2505 times block read
> either from the buffer or the disk. In my understanding the query
> accesses an index tuple, which will need access to root page and
> several number of meta pages (I mean index pages they are not either
> root or leaf pages) and 1 leaf page, then access 1 heap block. So I
> expected total number of IO would be somewhat:

> 500 index leaf pages + 500 heap blocks = 1000

The way I count it, each probe will touch the root page, a level-2 inner
page, a level-1 inner page, a leaf page, and a heap page, so five buffer
touches per cycle, which is almost exactly what you've got. Only the
first two of those are very likely to benefit from caching from previous
searches, so the fact that you got 1255 hits and not only 1000 is
actually a bit better than expected. Probably this query was not done
from a standing start, and so some of the level-1 pages were already in
buffer cache.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Pavel Stehule 2012-03-14 14:51:00 Re: Syntax error and reserved keywords
Previous Message Euler Taveira 2012-03-14 14:42:34 Re: Syntax error and reserved keywords