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