I can tell you that this is one of the first thing applications' programmers and IT 
managers notice. It can slightly tarnish postgres' image when it takes it many long 
seconds to do what other databases can do in a snap. The "whys and wherefores" can be 
hard to get across once they see the comparative numbers.

When I use Informix "dbaccess" it has a "status" which will tell me the row count of a 
table virtually instantly -- it can be locked out by a user with an exclusive lock so 
its not entirely independant of the table (like a stored value in one of the system 
catalog tables).

This is not to say Informix is "right" and Postgres is "wrong" ... but it  is 
something that virtually any newcomer will run into head long, with resulting bruises 
and contusions, not to mention confusion.

At the very least this needs to be VERY clearly explained right up front, along with 
some of the possible work-arounds, depending on what one is really after with this 

Greg Williamson
GlobeXplorer LLC

-----Original Message-----
From:   Dror Matalon [mailto:[EMAIL PROTECTED]
Sent:   Thu 10/2/2003 9:27 PM
Subject:        Re: [PERFORM] count(*) slow on large tables

I smell a religious war in the aii:-). 
Can you go several days in a row without doing select count(*) on any
of your tables? 

I suspect that this is somewhat a domain specific issue. In some areas
you don't need to know the total number of rows in your tables, in
others you do. 

I also suspect that you're right, that end user applications don't use
this information as often as DBAs would. On the other hand, it seems
whenever you want to optimize your app (something relevant to this list),
one of the things you do need to know is the number of rows in your


On Thu, Oct 02, 2003 at 10:08:18PM -0400, Christopher Browne wrote:
> The world rejoiced as [EMAIL PROTECTED] (Dror Matalon) wrote:
> > I don't have an opinion on how hard it would be to implement the
> > tracking in the indexes, but "select count(*) from some table" is, in my
> > experience, a query that people tend to run quite often. 
> > One of the databases that I've used, I believe it was Informix, had that
> > info cached so that it always new how many rows there were in any
> > table. It was quite useful.
> I can't imagine why the raw number of tuples in a relation would be
> expected to necessarily be terribly useful.
> I'm involved with managing Internet domains, and it's only when people
> are being pretty clueless that anyone imagines that "select count(*)
> from domains;" would be of any use to anyone.  There are enough "test
> domains" and "inactive domains" and other such things that the raw
> number of "things in the table" aren't really of much use.
> - I _do_ care how many pages a table occupies, to some extent, as that
> determines whether it will fit in my disk space or not, but that's not
> COUNT(*).
> - I might care about auditing the exact numbers of records in order to
> be assured that a data conversion process was done correctly.  But in
> that case, I want to do something a whole *lot* more detailed than
> mere COUNT(*).
> I'm playing "devil's advocate" here, to some extent.  But
> realistically, there is good reason to be skeptical of the merits of
> using SELECT COUNT(*) FROM TABLE for much of anything.
> Furthermore, the relation that you query mightn't be a physical
> "table."  It might be a more virtual VIEW, and if that's the case,
> bets are even MORE off.  If you go with the common dictum of "good
> design" that users don't directly access tables, but go through VIEWs,
> users may have no way to get at SELECT COUNT(*) FROM TABLE.
> -- 
> output = reverse("ac.notelrac.teneerf" "@" "454aa")
> http://www.ntlug.org/~cbbrowne/finances.html
> Rules  of  the  Evil  Overlord  #74.   "When  I  create  a  multimedia
> presentation of my plan designed  so that my five-year-old advisor can
> easily  understand the  details, I  will not  label the  disk "Project
> Overlord" and leave it lying on top of my desk."
> <http://www.eviloverlord.com/>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Dror Matalon, President
Zapatec Inc 
1700 MLK Way
Berkeley, CA 94709

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Reply via email to