On 10.05.2011 20:15, Simon Riggs wrote:
On Tue, May 10, 2011 at 5:17 PM, Kevin Grittner
Simon Riggs<si...@2ndquadrant.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.
Sent via pgsql-hackers mailing list (email@example.com)
To make changes to your subscription: