Re: PostgreSQL Gotchas

On Thu, Oct 06, 2005 at 04:18:03PM -0400, Stephen Frost wrote:
> COUNT(*) very slow
>   As someone else has pointed out, it's only slow if you've got a large
>   dataset.  There's certainly workarounds for this issue (generally
>   involving a couple of functions for keeping track of the number of
>   rows).  In this way, Postgres actually gives the user the flexibility
>   to choose the performance loss on insert/deletes which is necessary to
>   track the number of rows seperately, or not to.

Actually, I think the real issue here is that unlike every other
database I've used, PostgreSQL can't do covering index scans, where only
the index is read and not the base table. This is due to not having MVCC
visibility info in the index. But there has been discussion on -hackers
about a way to get close to this behavior; something along the lines of
setting a bit in the index once a tuple is visible to all running
transactions. This would be a win on larger indexes that don't have a
lot of insert/update/delete activity.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461