Re: Why is indexonlyscan so darned slow?

On Mon, May 21, 2012 at 1:42 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
>> Earlier you said that this should be an ideal setup for IOS.  But it
>> isn't really--the ideal set up is one in which the alternative to an
>> IOS is a regular index scan which makes many uncached scattered reads
>> into the heap.  I don't think that that situation can't really be
>> engineered with a where-less query.
>
> Can you give me some suggested comparisons which *would* be ideal, then?

Are you looking for vaguely real-life examples, or highly contrived
examples used to dissect the server?

For vaguely real life, take your example of pgbench -i -s200 -F 50,
and I have 2Gig RAM, which seems to be the same as you do.

With select only work load (pgbench -S -M prepared -T 30), I get

tps = 193

But now enable index-only scans:

psql -c "create index on pgbench_accounts(aid, abalance);"

and it goes up to.

tps = 10137

Cheers,

Jeff