Re: the big picture for index-only scans

On 10.05.2011 20:15, Simon Riggs wrote:
> On Tue, May 10, 2011 at 5:17 PM, Kevin Grittner
> <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> 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.
>
> I can picture that. Regrettably, I can also picture the accesses to
> the visibility map, the maintenance operations on the VM that are
> needed for this and the contention that both of those will cause.

Note that we already have the visibility map, and the accesses needed to
update it are already there. Granted, we'll have to change the logic
slightly to make it crash safe, but I don't expect that to add any
meaningful overhead - the changes are going to be where the bits are
set, ie. vacuum, not when the bits are cleared. Granted, we might also
want to set the bits more aggressively once they're used by
index-only-scans. But done correctly, just taking advantage of the VM
that's already there shouldn't add overhead to other operations.

I agree that we need to do tests to demonstrate that there's a gain from
the patch, once we have a patch to test. I would be very surprised if
there isn't, but that just means the testing is going to be easy.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com