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.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 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(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us