I have created a 29GB test database by using standard pgbnech -i -s
2000. Then I executed:
explain (analyze, buffers) select * from pgbench_accounts where aid in
(select cast(random()*200000000 as int) from generate_series(1,500));
Nested Loop (cost=30.00..6075.07 rows=100000000 width=97) (actual time=23.051.
.13570.739 rows=500 loops=1) Buffers: shared hit=1255 read=1250 -> HashAggregate (cost=30.00..32.00 rows=200
width=4)(actual time=0.474..0
.723 rows=500 loops=1) -> Function Scan on generate_series (cost=0.00..17.50 rows=1000 width
=0) (actual time=0.097..0.264 rows=500 loops=1) -> Index Scan using pgbench_accounts_pkey on pgbench_accounts
(cost=0.00..3
0.20 rows=1 width=97) (actual time=27.085..27.138 rows=1 loops=500) Index Cond: (aid = (((random() *
200000000::doubleprecision))::integer
)) Buffers: shared hit=1255 read=1250Total runtime: 13571.020 ms
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
However I saw 1505 more accesses in total. My guess is this number
mainly comes from index meta page access. So my guess is we need 3
page accesses (to traverse b tree index tree) before reaching the leaf
page in average. Am I correct or the number is execessive?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp