Re: Why is indexonlyscan so darned slow?

On 21 May 2012 13:41, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> Well, if it's not CPU costs, then something else is eating the time,
>> since I'm seeing per-tuple COUNT counts on indexes being 400% more than
>> on heap.
>
> Well, I'm not: as I said, it looks like about 10% here.  Perhaps you're
> testing a cassert-enabled build?
>
>> In the airport you said something about index-only scan not scanning the
>> tuples in leaf page order.   Can you elaborate on that?
>
> If the index is too big to fit in RAM, you'd be looking at random
> fetches of the index pages in most cases (since logical ordering of the
> index pages is typically different from physical ordering), leading to
> it likely being a lot slower per page than a heapscan.  Not sure this
> has anything to do with your test case though, since you said you'd
> sized the index to fit in RAM.

As you point out, this is still an IndexScan even if the heap access is zero.

Surely the way to solve this is by having a new plan node that does a
physical SeqScan of the index relation. It means we wouldn't preserve
the sort order of the rows from the index, but that is just a plan
cost issue.

This is exactly what we do for VACUUM and it works faster there.

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