Skip site navigation (1) Skip section navigation (2)

Re: count(*) slow on large tables

From: Jeff <threshar(at)torgo(dot)978(dot)org>
To: Christopher Browne <cbbrowne(at)acm(dot)org>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: count(*) slow on large tables
Date: 2003-10-03 12:36:42
Message-ID: Pine.BSF.4.44.0310030832280.34439-100000@torgo.978.org (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
On Thu, 2 Oct 2003, Christopher Browne wrote:

> I can't imagine why the raw number of tuples in a relation would be
> expected to necessarily be terribly useful.
>

We use stuff like that for reporting queries.

example:
On our message boards each post is a row.  The powers that be like to know
how many posts there are total (In addition to 'today')-
select count(*) from posts is how it has been
done on our informix db.  With our port to PG I instead select reltuples
pg_class.

I know when I login to a new db (or unknown to me db) the first thing I do
is look at tables and see what sort of data there is.. but in code I'd
rarely do that.

I know some monitoring things around here also do a select count(*) on
sometable to ensure it is growing, but like you said, this is easily done
with the number of pages as well.

yes. Informix caches this data. I believe Oracle does too.

Mysql with InnoDB does the same thing PG does. (MyISAM caches it)

--
Jeff Trout <jeff(at)jefftrout(dot)com>
http://www.jefftrout.com/
http://www.stuarthamm.net/



In response to

Responses

pgsql-performance by date

Next:From: StefDate: 2003-10-03 14:30:40
Subject: Postgres low end processing.
Previous:From: Christopher BrowneDate: 2003-10-03 11:37:07
Subject: Re: count(*) slow on large tables

pgsql-hackers by date

Next:From: achillDate: 2003-10-03 12:56:07
Subject: HeapTuple->t_tableOid==0 after SPI_exec
Previous:From: Max JacobDate: 2003-10-03 11:51:59
Subject: calling functions through a "pointer"

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group