Re: Why is indexonlyscan so darned slow?

On Tue, May 22, 2012 at 11:33 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> On Mon, May 21, 2012 at 2:29 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> See here: http://www.devheads.net/database/postgresql/performance/index-all-necessary-columns-postgres-vs-mssql.htm
>> for a 'in the wild' gripe about about not having index scans.
>
> But without scripts to recreate the data with the right selectivities
> and correlations, and to generate a long stream of appropriate query
> parameterizations so that they don't become cached, that is just a
> gripe and not an example.
>
> I tried to reproduce the problem as stated, and couldn't make IOS be
> useful because I couldn't make it be slow even without them.
> Presumably I'm doing something wrong, but how could I tell what?  Have
> we heard back on whether IOS was tried and proved useful to the
> originator of that thread?

nope. but the damning evidence was that non-IOS on sql server
performed on par with postgres on the OP's data. (i also tried to
reproduce with similar results as you).

I bet i/o bound IOS will do better than 50% most of the time because
the 'tuples' are packed better than on a typical heap page unless the
heap is well clustered around that particular index resulting in less
random I/O. This will directly translate to cpu efficiencies as
storage gets faster. It's just an all around fabulous feature and
like HOT is something to really consider carefully when laying out
schema.

merlin