Re: Why is indexonlyscan so darned slow?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, Ants Aasma <ants(at)cybertec(dot)at>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Why is indexonlyscan so darned slow?
Date: 2012-05-21 17:41:02
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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.

regards, tom lane

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Josh Berkus 2012-05-21 17:44:59 Re: Why is indexonlyscan so darned slow?
Previous Message Tom Lane 2012-05-21 17:33:50 Re: transformations between types and languages