Too many IO?
От | Tatsuo Ishii |
---|---|
Тема | Too many IO? |
Дата | |
Msg-id | 20120314.112944.1482213036138313960.t-ishii@sraoss.co.jp обсуждение исходный текст |
Ответы |
Re: Too many IO?
Re: Too many IO? Re: Too many IO? |
Список | 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::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
В списке pgsql-hackers по дате отправления: