Tatsuo Ishii <ishii@postgresql.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