Re: index-only scans

On 08/11/2011 04:06 PM, Robert Haas wrote:
> On my laptop, the first query executes in about 555 ms, while the
> second one takes about 1125 ms...I expect that you could get
> an even larger benefit from this type of query if you could avoid
> actual disk I/O, rather than just buffer cache thrashing, but I
> haven't come up with a suitable test cases for that yet (volunteers?).
>    

That part I can help with, using a Linux test that kills almost every 
cache. I get somewhat faster times on my desktop here running the cached 
version like you were doing (albeit with debugging options on, so I 
wouldn't read too much into this set of numbers):

select sum(aid) from sample_data a1 where exists (select * from
pgbench_accounts a where a.aid = a1.aid and a.aid != 1234567);     sum
-------------- 250279412983
(1 row)

Time: 472.778 ms
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Aggregate (cost=133325.00..133325.01 rows=1 width=4)   ->  Nested Loop Semi Join  (cost=0.00..133075.00 rows=100000
width=4)        ->  Seq Scan on sample_data a1  (cost=0.00..15286.00 
 
rows=100000 width=4)         ->  Index Only Scan using pgbench_accounts_pkey on 
pgbench_accounts a  (cost=0.00..1.17 rows=1 width=4)               Index Cond: (aid = a1.aid)               Filter:
(aid<> 1234567)
 

select sum(aid) from sample_data a1 where exists (select * from
pgbench_accounts a where a.aid = a1.aid and a.bid != 1234567);     sum
-------------- 250279412983

Time: 677.902 ms
explain select sum(aid) from sample_data a1 where exists (select * from
pgbench_accounts a where a.aid = a1.aid and a.bid != 1234567);                                                 QUERY
PLAN
------------------------------------------------------------------------------------------------------------ Aggregate
(cost=133325.00..133325.01rows=1 width=4)   ->  Nested Loop Semi Join  (cost=0.00..133075.00 rows=100000 width=4)
 ->  Seq Scan on sample_data a1  (cost=0.00..15286.00 
 
rows=100000 width=4)         ->  Index Scan using pgbench_accounts_pkey on pgbench_accounts 
a  (cost=0.00..1.17 rows=1 width=4)               Index Cond: (aid = a1.aid)               Filter: (bid <> 1234567)

If I setup my gsmith account to be able to start and stop the server 
with pg_ctl and a valid PGDATA, and drop these two scripts in that home 
directory:

== index-only-1.sql ==

\timing
select sum(aid) from sample_data a1 where exists (select * from
pgbench_accounts a where a.aid = a1.aid and a.aid != 1234567);

explain select sum(aid) from sample_data a1 where exists (select * from
pgbench_accounts a where a.aid = a1.aid and a.aid != 1234567);

== index-only-2.sql ==

\timing
select sum(aid) from sample_data a1 where exists (select * from
pgbench_accounts a where a.aid = a1.aid and a.bid != 1234567);

explain select sum(aid) from sample_data a1 where exists (select * from
pgbench_accounts a where a.aid = a1.aid and a.bid != 1234567);

I can then run this script as root:

#!/bin/bash
ME="gsmith"
su - $ME -c "pg_ctl stop -w"
echo 3 > /proc/sys/vm/drop_caches
su - $ME -c "pg_ctl start -w"
su - $ME -c "psql -ef index-only-1.sql"
su - $ME -c "pg_ctl stop -w"
echo 3 > /proc/sys/vm/drop_caches
su - $ME -c "pg_ctl start -w"
su - $ME -c "psql -ef index-only-2.sql"

And get results that start with zero information cached in RAM, showing 
a much more dramatic difference.  Including some snippets of interesting 
vmstat too, the index-only one gets faster as it runs while the regular 
one is pretty flat:

select sum(aid) from sample_data a1 where exists (select * from
pgbench_accounts a where a.aid = a1.aid and a.aid != 1234567);
Time: 31677.683 ms

$ vmstat 5
procs -----------memory---------- ---swap-- -----io---- -system-- 
----cpu---- 0  1      0 15807288   4388 126440    0    0  4681   118 1407 2432  1  
1 89 10 1  1      0 15779388   4396 154448    0    0  3587    17 1135 2058  1  
0 86 13 0  1      0 15739956   4396 193672    0    0  5800     0 1195 2056  1  
0 87 12 0  1      0 15691844   4396 241832    0    0  7053     3 1299 2044  1  
0 86 13 0  1      0 15629736   4396 304096    0    0  7995    37 1391 2053  1  
0 87 12 0  1      0 15519244   4400 414268    0    0 11639    14 1448 2189  1  
0 87 12

select sum(aid) from sample_data a1 where exists (select * from
pgbench_accounts a where a.aid = a1.aid and a.bid != 1234567);
Time: 172381.235 ms

$ vmstat 5
procs -----------memory---------- ---swap-- -----io---- -system-- 
----cpu---- 0  1      0 15736500   4848 196444    0    0  3142    22 1092 1989  1  
0 86 13 0  1      0 15711948   4852 221072    0    0  3411     1 1039 1943  0  
0 88 12 0  1      0 15685412   4852 247496    0    0  3618    34 1111 1997  0  
0 86 13
[this is the middle part, rate doesn't vary too much]

That's 5.4X as fast; not too shabby!  Kind of interesting how much 
different the I/O pattern is on the index-only version.  I ran this test 
against a 3-disk RAID0 set with a 256MB BBWC, so there's some 
possibility of caching here.  But given that each query blows away a 
large chunk of the other's data, I wouldn't expect that to be a huge 
factor here:

gsmith=# select pg_size_pretty(pg_relation_size('pgbench_accounts')); pg_size_pretty
---------------- 640 MB

gsmith=# select pg_size_pretty(pg_relation_size('pgbench_accounts_pkey')); pg_size_pretty
---------------- 107 MB

gsmith=# select pg_size_pretty(pg_relation_size('sample_data')); pg_size_pretty
---------------- 112 MB

And with the large difference in response time, things appear to be 
working as hoped even in this situation.  If you try this on your 
laptop, where drive cache size and random I/O are likely to be even 
slower, you might see an ever larger difference.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us