Josh Berkus <josh@agliodbs.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