Re: count(*) slow on large tables - Mailing list pgsql-performance

I can tell you that this is one of the first thing applications' programmers and IT managers notice. It can slightly
tarnishpostgres' 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
canbe locked out by a user with an exclusive lock so its not entirely independant of the table (like a stored value in
oneof the system catalog tables). 

This is not to say Informix is "right" and Postgres is "wrong" ... but it  is something that virtually any newcomer
willrun 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,
dependingon what one is really after with this info. 

Greg Williamson
DBA
GlobeXplorer LLC

-----Original Message-----
From:    Dror Matalon [mailto:dror@zapatec.com]
Sent:    Thu 10/2/2003 9:27 PM
To:    pgsql-performance@postgresql.org
Cc:
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
table.

Dror

On Thu, Oct 02, 2003 at 10:08:18PM -0400, Christopher Browne wrote:
> The world rejoiced as dror@zapatec.com (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
http://www.zapatec.com

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