On Tue, Jan 23, 2007 at 02:15:23PM -0500, Jeremy Haile wrote:
> But there are ways that we could optimize count(*) queries for specific
> circumstances right? Obviously this isn't trivial, but I think it would
> be nice if we could maintain a number of rows count that could be used
> when performing a count(*) on the whole table (no where clause).
Not really. SQL has fairly strict specifications to the answer to that
query and anything that would optimise it comes at a not inconsiderable
cost.
If you don't care about an exact answer, you can find a number of
methods in the archives.
> I don't know if the overhead of keeping track of that number is worth
> the benefits - but I know that querying for the number of rows in a
> table is a common need and other RDBMSs do optimize for that special
> case.
It's not just keeping track of the number of rows. It keeping track of the
number of rows for each currently executing transaction, since each
transaction could get a different answer. So any accurate method is
going to be tracking the number of tuples even for transactions that don't
want to know. For people who really want to spend the overhead, you can
make a working system. But most people can live with estimates...
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.