DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=pXbGhsXhNt5oV/jCe3VcL7rePZSdUMwgKT1mjzAXdtATr/yw1s3R0y+DzmN1aJEfNHTUfQgXk64BNaQHw/aE9FCLjVs+bOOKF2ZsDsEVibPR7d7CHzM0uFZwgsScTjXZrwVd/fkfHDG9eo0w3dE3fnor8cyuATDfze518cVjgkA=
Message-ID: <dcc563d10803101521v64db0d99k4da5cc129665ab74@mail.gmail.com>
Date: Mon, 10 Mar 2008 15:21:42 -0700
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "Joe Mirabal" <jmmirabal@gmail.com>
Subject: Re: count * performance issue
Cc: "Gregory Stark" <stark@enterprisedb.com>, "Tom Lane" <tgl@sss.pgh.pa.us>,
	josh@agliodbs.com, pgsql-performance@postgresql.org,
	"Craig James" <craig_james@emolecules.com>
In-Reply-To: <12ba3bf00803101354w15b023e1o237f564a1b01105f@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <f966c2ee0803052142l6a105678jfe2da92a0d9371@mail.gmail.com>
	<47D00DB2.7040007@emolecules.com> <18451.1204824562@sss.pgh.pa.us>
	<200803071727.36146.josh@agliodbs.com>
	<13345.1204940295@sss.pgh.pa.us> <87r6eia9tz.fsf@oxford.xeocode.com>
	<12ba3bf00803101354w15b023e1o237f564a1b01105f@mail.gmail.com>

On Mon, Mar 10, 2008 at 1:54 PM, Joe Mirabal <jmmirabal@gmail.com> wrote:
> Gregory,
>
> I just joined this listserv and was happy to see this posting.  I have a
> 400GB table that I have indexed (building the index took 27 hours) , Loading
> the table with 10 threads took 9 hours.  I run queries on the data nad get
> immediate max and min as well as other aggrgate functions very quickly,
> however a select count(*) of the table takes forever usually nearly an hour
> or more.
>
> Do you have any tuning recommendations.  We in our warehouse use the
> count(*) as our verification of counts by day/month's etc and in Netezza its
> immediate.  I tried by adding oids. BUT the situation I learned was that
> adding the oids in the table adds a significasnt amount of space to the data
> AND the index.

Yeah, this is a typical problem people run into with MVCC databases to
one extent or another.  PostgreSQL has no native way to just make it
faster.  However, if it's a table with wide rows, you can use a lookup
table to help a bit.  Have a FK with cascading deletes from the master
table to a table that just holds the PK for it, and do count(*) on
that table.

Otherwise, you have the trigger solution mentioned previously.

Also, if you only need an approximate count, then you can use the
system tables to get that with something like

select reltuples from pg_class where relname='tablename';

after an analyze.  It won't be 100% accurate, but it will be pretty
close most the time.