Why is indexonlyscan so darned slow?

So, I set up a test which should have been ideal setup for index-only scan. The index was 1/10 the size of the table, and fit in RAM (1G) which the table does not:

bench2=# select pg_size_pretty(pg_relation_size('pgbench_accounts_pkey'));
pg_size_pretty
----------------
428 MB
(1 row)

bench2=# select pg_size_pretty(pg_relation_size('pgbench_accounts'));
pg_size_pretty
----------------
5768 MB
(1 row)

The table was just VACUUM ANALYZED and had no subsequent updates. So, what's going on here?

bench2=# explain ( analyze on, buffers on ) select count(*) from pgbench_accounts;
QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------
------------
Aggregate (cost=855069.99..855070.00 rows=1 width=0) (actual time=64014.573..64014.574 rows=1 loops=1)
Buffers: shared hit=33 read=738289
I/O Timings: read=27691.314
-> Seq Scan on pgbench_accounts (cost=0.00..831720.39 rows=9339839 width=0) (actual time=6790.669..46530.408 rows=200000
00 loops=1)
Buffers: shared hit=33 read=738289
I/O Timings: read=27691.314
Total runtime: 64014.626 ms
(7 rows)

bench2=# explain ( analyze on, buffers on ) select count(*) from pgbench_accounts;
QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------
---------------------------------------------
Aggregate (cost=382829.37..382829.38 rows=1 width=0) (actual time=38325.026..38325.027 rows=1 loops=1)
Buffers: shared hit=1 read=54653
I/O Timings: read=907.202
-> Index Only Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.00..359479.77 rows=9339839 width=0) (actual t
ime=33.459..20110.908 rows=20000000 loops=1)
Heap Fetches: 0
Buffers: shared hit=1 read=54653
I/O Timings: read=907.202
Total runtime: 38333.536 ms

As you can see, the indexonlyscan version of the query spends 5% as much time reading the data as the seq scan version, and doesn't have to read the heap at all. Yet it spends 20 seconds doing ... what, exactly?

BTW, kudos on the new explain analyze reporting ... works great!

--Josh

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
San Francisco