Re: 9.2 and index only scans

On Sun, Aug 26, 2012 at 11:01:31PM +0200, Thomas Kellerer wrote:
> I was inspired by this question on StackOverflow:
> http://stackoverflow.com/questions/12128501/fastest-way-to-count-the-rows-in-any-database-table/12128545#12128545
>
> Which shows Oracle's behaviour with an index scan for the count(*) operation.

Interesting, It shows indeed Oracle uses the index to do the operation.

For postgres it's not so simple for a few reasons, I'm not sure how
oracle avoids the same issues:

- The index has no visibility information, so you can't tell if an
index entry refers to a row you can actually see in your session.
The visibility map might help here in the future.

- Different versions of the same row (after an UPDATE for example) may
both be in the index, Now if you're counting a primary key column you
can work around that.

But frankly, counting all the rows in a table is something I never do.
The system tables carry estimates which have proved good enough for
statistical purposes when I need them.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
-- Arthur Schopenhauer