Re: Why is indexonlyscan so darned slow?

On Mon, May 21, 2012 at 9:37 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>>> This is exactly what we do for VACUUM and it works faster there.
>>
>> The reason that's okay for vacuum is that vacuum doesn't care if it
>> visits the same index tuple multiple times.  It will not work for real
>> queries, unless you would like to lock out all concurrent inserts.

Even if you didn't care about seeing duplicates (such as for instance
if you keep a hash table of seen tids to dedup) Vacuum's approach
wouldn't work because the technique it uses to detect concurrent page
splits to know that it has to go back and look for resulting child
pages only works if there's only one scanner at a time. Vacuum
actually marks each page with a vacuum generation number -- that
doesn't work if you want to allow multiple concurrent *scans*. Locking
out concurrent inserts just might even be conceivably tolerable for
some use cases but locking out concurrent read-only scans would really
be beyond the pale.

> I checked a little more and Oracle supports something called a Fast
> Index Scan. Maybe there is a way.

Oracle maintains their indexes differently. Since they do
transactional consistency at the block level and it applies to all
relations -- even indexes -- they see a consistent view of the entire
index. This is engineering. There's always a way but there's no free
lunch. They incur some overhead when they find a block in the index
and have to look up the old version of the block in the rollback
segment. In Postgres I suspect the kind of change that would be needed
would cost concurrency on inserts/updates in exchange for more
flexibility scanning the index.

--
greg