Re: Improving count(*)

On Thu, Nov 17, 2005 at 02:55:09PM -0500, Rod Taylor wrote:
> On Thu, 2005-11-17 at 20:38 +0100, Martijn van Oosterhout wrote:
> > It's an interesting idea, but you still run into the issue of
> > visibility. If two people start a transaction, one of them inserts a
> > row and then both run a select count(*), they should get different
> > answers. I just don't see a way that your suggestion could possibly
> > lead to that result...
>
> The instant someone touches a block it would no longer be marked as
> frozen (vacuum or analyze or other is not required) and count(*) would
> visit the tuples in the block making the correct decision at that time.

Hmm, so the idea would be that if a block no longer contained any
tuples hidden from any active transaction, you could store the count
and skip reading that page. Ofcourse, as soon as someone UPDATEs a
tuple, that block comes into play again because it would be visible
from some but not other transactions. Then again, for count(*) UPDATEs
are irrelevent.

The other way, storing visibility in the index seems awfully expensive,
since any changes to the tuple would require updating the index. Still,
people have thought about this already, I'm sure the issues are
known...

Have a niceday,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.