On Thursday, December 20, 2012, Robert Haas wrote:
IMHO, it's probably fairly hopeless to make a pure pgbench workload
show a benefit from index-only scans. A large table under a very
heavy, completely random write workload is just about the worst
possible case for index-only scans. Index-only scans are a way of
avoiding unnecessary visibility checks when the target data hasn't
changed recently, not a magic bullet to escape all heap access. If
the target data has changed, you're going to have to touch the heap.
And while I agree that we aren't aggressive enough in setting the VM
bits right now, I also think it wouldn't be too hard to go too far in
the opposite direction: we could easily spend more effort trying to
make index-only scans effective than we could ever hope to recoup from
the scans themselves.
Now, if you set up N threads of which 10% are doing regular pgbench
and the other 90% are doing pgbench -S, or something like that, then
you might start to hope for some benefit from index-only scans.
I set this up before, by dropping the primary key and instead building an index on (aid,abalance) and then just running pgbench with a mixture of -f flags that corresponded to some -S-like and some default-like transactions.
On a freshly vacuumed table, I saw a hint of a performance boost at even a 50:50 ratio, and clear boost at 3 -S to 1 default
I ran this at a size where not even all the index fit in RAM, to maximize the benefit of not having to visit the table.
However, the boost started going away due to vm clearance long before autovacuum kicked in at default settings.
Cheers,
Jeff
