Re: the big picture for index-only scans

On Tue, May 10, 2011 at 5:53 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> It's already the case that we'll flip over to a bitmap indexscan,
> and thus get rid of most/all of the "random" page accesses, in
> situations where this is likely to be a big win.  Pointing to the
> performance difference in databases that don't do that is therefore
> not too convincing.

The other major effect is row size. Many databases have very wide
rows, perhaps on the order of 1kB. So the table with a million rows
might be 8GB but the index on a few key columns might only be a few
megabytes. Even if you have to read the entire index in random order
it'll likely all be cached and scan faster than the table itself.

One problem with hanging on benchmarks is that database schema design
can actually change based on what performs well. People get in the
habit of creating indexes in Oracle that are only logical when you
realize they allow the database to do an index-only scan because they
contain extra columns that aren't actually used in where clauses but
are typically in the select list.

--
greg