Re: Why is indexonlyscan so darned slow?

On Mon, May 21, 2012 at 10:44 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
> Right.  So what I'm trying to figure out is why counting an index which
> fits in ram (and I've confirmed via EXPLAIN ( buffers on ) ) is not
> being heap-fetched or read from disk would take 25% as long as counting
> a table which is 80% on disk.

Sequential disk reads are fast. Parsing the data after it has been
read from disk is also fast, but not infinitely so. If you can get
your IO system to be about 4 times faster, then you would start being
limited by CPU even on disk-based sequential scans.

Earlier you said that this should be an ideal setup for IOS. But it
isn't really--the ideal set up is one in which the alternative to an
IOS is a regular index scan which makes many uncached scattered reads
into the heap. I don't think that that situation can't really be
engineered with a where-less query.

Iterating over any non-trivial data structure with 20,000,000 entries
is going to take some time. As way of comparison, iterating over a
Perl hash doing nothing but a counter increment takes several times
longer than a same-sized IOS count does. (Of course you don't need to
iterate over a Perl hash to get the size, but just directly fetching
the size would not be a fair comparison)

Cheers,

Jeff