Re: the big picture for index-only scans

On Tue, May 10, 2011 at 12:53 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "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.

Well, we're not in the habit of committing performance patches without
performance numbers, so I doubt we'll reverse that trend now, and
certainly I had no intention of doing so.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company