Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group