Re: performance of count(*)

On Fri, May 06, 2011 at 12:45:23PM -0600, Scott Ribe wrote:

> I need to optimize queries that deal with some aggregates regarding
resource availability. My specific problem is, I think, very closely
analogous to select count(*)... where...

If the WHERE clause is fairly selective and indexed, that should be
fast. Not as fast as estimates based on trigger-written values in
another table, of course, but reasonably fast. So the first order of
business is usually to find or create indexes that will make SELECT on
the same criteria fast.

It's only unqualified "SELECT count(*)" that is slow. Generally, the
system table is good enough for that, I find. (Someone: "How long
will this take?" Me: "There are about 400 million rows to go
through." Even if you're off by 50 million at that point, it doesn't
matter.)

A

--
Andrew Sullivan
ajs(at)crankycanuck(dot)ca