Re: the big picture for index-only scans

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> wrote:
>> This topic has been discussed many times, yet I have never seen an
>> assessment that explains WHY we would want to do index-only scans.

> In databases with this feature, it's not too unusual for a query
> which uses just an index to run one or more orders of magnitude
> faster than a query which has to randomly access the heap for each
> index entry. That seems like enough evidence of its possible value
> in PostgreSQL to proceed to the point where benchmarks become
> possible. I'm assuming that, like all other features added as
> performance optimizations, it won't be committed until there are
> benchmarks showing the net benefit.

> As a thought experiment, picture the relative costs of scanning a
> portion of an index in index sequence, and being done, versus
> scanning a portion of an index in index sequence and jumping to a
> random heap access for each index entry as you go.

It's already the case that we'll flip over to a bitmap indexscan,
and thus get rid of most/all of the "random" page accesses, in
situations where this is likely to be a big win. Pointing to the
performance difference in databases that don't do that is therefore
not too convincing.

I'm inclined to agree that index-only scans might be worth the amount
of work that's involved ... but I share Simon's desire to see some proof
before anything gets committed.

regards, tom lane