From: Scott Marlowe <scott.marlowe@gmail.com>
To: Joe Mirabal <jmmirabal@gmail.com>
Cc: Gregory Stark <stark@enterprisedb.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: josh@agliodbs.com, pgsql-performance@postgresql.org, "Craig James" <craig_james@emolecules.com>
Subject: Re: count * performance issue
Date: Mon, 10 Mar 2008 15:21:42 -0700
Message-ID: <dcc563d10803101521v64db0d99k4da5cc129665ab74@mail.gmail.com> (raw)
In-Reply-To: <12ba3bf00803101354w15b023e1o237f564a1b01105f@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scott.marlowe@gmail.com, jmmirabal@gmail.com, stark@enterprisedb.com, tgl@sss.pgh.pa.us, craig_james@emolecules.com
Subject: Re: count * performance issue
In-Reply-To: <dcc563d10803101521v64db0d99k4da5cc129665ab74@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox