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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
