Why is indexonlyscan so darned slow? - Mailing list pgsql-hackers

From Joshua Berkus
Subject Why is indexonlyscan so darned slow?
Date
Msg-id 1645421906.291004.1337224103503.JavaMail.root@mail-1.01.com
Whole thread Raw
Responses Re: Why is indexonlyscan so darned slow?
List pgsql-hackers
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


pgsql-hackers by date:

Previous
From: Joshua Berkus
Date:
Subject: Re: Strange issues with 9.2 pg_basebackup & replication
Next
From: Alvaro Herrera
Date:
Subject: Re: "could not open relation with OID" errors after promoting the standby to master