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,
andfit 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=9339839width=0) (actual time=6790.669..46530.408 rows=200000
00 loops=1) Buffers: shared hit=33 read=738289 I/O Timings: read=27691.314Total 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.027rows=1 loops=1) Buffers: shared hit=1 read=54653 I/O Timings: read=907.202 -> Index Only
Scanusing 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.202Total 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,
anddoesn'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