Re: the big picture for index-only scans

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.

ISTM quite likely that we'll slow down writes to some extent in order
to improve this use case.

So I'm interested in knowing how broad the use case is and what the
overheads are, rather than have an "aw crap!" moment in the future
where we finish the code and only then realise its benefit footprint
is not useful. Best to start out with a clear benefit analysis other
than "other DBMS do it".

For example, will this be an index-specific tuning option
(manual/automatic), per table or an always-on feature?

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services