Re: Why is indexonlyscan so darned slow?

On Thu, May 17, 2012 at 5:22 AM, Joshua Berkus <josh(at)agliodbs(dot)com> wrote:
> Ants,
>
> Well, that's somewhat better, but again hardly the gain in performance I'd expect to see ... especially since this is ideal circumstances for index-only scan.
>
> bench2=# select count(*) from pgbench_accounts;
>  count
> ----------
>  20000000
> (1 row)
>
> Time: 3827.508 ms
>
> bench2=# set enable_indexonlyscan=off;
> SET
> Time: 0.241 ms
> bench2=# select count(*) from pgbench_accounts;
>  count
> ----------
>  20000000
> (1 row)
>
> Time: 16012.444 ms
>
> For some reason counting tuples in an index takes 5X as long (per tuple) as counting them in a table.  Why?
>

It looks like the IOS is taking 4x less time, not more time.

Anyway, the IOS follows the index logical structure, not the physical
structure, so if the index is not in RAM it will really be hurt by the
lack of sequential reads.

Cheers,

Jeff