Too many IO?

From: Tatsuo Ishii <ishii(at)postgresql(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Too many IO?
Date: 2012-03-14 02:29:44
Message-ID: 20120314.112944.1482213036138313960.t-ishii@sraoss.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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::double precision))::integer
))
Buffers: shared hit=1255 read=1250
Total 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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Daniel Farina 2012-03-14 02:39:24 Re: Chronic performance issue with Replication Failover and FSM.
Previous Message Robert Haas 2012-03-14 02:21:54 Re: wal_buffers, redux