Re: 9.2 and index only scans

Martijn van Oosterhout, 28.08.2012 10:02:
> 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.

In Oracle an index (entry) has the information about transactional visibility.

> - 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.

This also works fine in Oracle due to the visibility information inside the index.
I did a test where I deleted and inserted a bunch of rows in the test table (in a different transaction).
The execution plan - even the real one - still used the index.

> But frankly, counting all the rows in a table is something I never do.

I agree, but I thought it was a nice example to test out this new PostgreSQL feature after seeing the SO question.

Regards
Thomas