Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id E7BF12E00D3 for ; Mon, 10 Mar 2008 17:54:34 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 38692-01 for ; Mon, 10 Mar 2008 17:54:26 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from po-out-1718.google.com (po-out-1718.google.com [72.14.252.157]) by postgresql.org (Postfix) with ESMTP id 16C952E00EF for ; Mon, 10 Mar 2008 17:54:27 -0300 (ADT) Received: by po-out-1718.google.com with SMTP id y22so3466893pof.3 for ; Mon, 10 Mar 2008 13:54:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=aely4/WfbVe8DxqLDlzSsiDfJ8iPrePvcDK3OafIEGQ=; b=ije2/OyDl7Q7aXCB6ZiBpg8CwqRDCuiZqMNVZeMySD+H2OT2GNy20nvylQEm/dRYLyiUGYiGiilIQHrd0CH1SIex+xYm3n96jjPUV6j/9eSw3eO4Ft2PlEJu4xjN1K+UohGPiSw8LoKtERBqGSy1nb+wsmWpASxYZ4U1bDnRofQ= 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:references; b=HUziT2yv06cErfcLazrcAZ+qBFqZcdp+TGy6OFSp1Agk8yJ7W5WPuR8ZMkdayxxeJiBA3JrZdl+IE4CVmaLigTV3fsCEZjIgVupeyfSzHufuURxvkBYND9MNQzDn3T9qSLWcvbutGeSxu6jedcTHMoAH0bVGTJf7ExpsxXP3tHQ= Received: by 10.141.69.1 with SMTP id w1mr3587516rvk.185.1205182463096; Mon, 10 Mar 2008 13:54:23 -0700 (PDT) Received: by 10.141.69.5 with HTTP; Mon, 10 Mar 2008 13:54:23 -0700 (PDT) Message-ID: <12ba3bf00803101354w15b023e1o237f564a1b01105f@mail.gmail.com> Date: Mon, 10 Mar 2008 16:54:23 -0400 From: "Joe Mirabal" To: "Gregory Stark" Subject: Re: count * performance issue Cc: "Tom Lane" , josh@agliodbs.com, pgsql-performance@postgresql.org, "Craig James" In-Reply-To: <87r6eia9tz.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_189_1172153.1205182463079" References: <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> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200803/206 X-Sequence-Number: 29160 ------=_Part_189_1172153.1205182463079 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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. As you may gather from this we are relatively new on Postgres. Any suggestions you can give me would be most helpful. Cheers, Joe On Mon, Mar 10, 2008 at 11:16 AM, Gregory Stark wrote: > "Tom Lane" writes: > > > Well, scanning an index to get a count might be significantly faster > > than scanning the main table, but it's hardly "instantaneous". It's > > still going to take time proportional to the table size. > > Hm, Mark's comment about bitmap indexes makes that not entirely true. A > bitmap > index can do RLE compression which makes the relationship between the size > of > the table and the time taken to scan the index more complex. In the > degenerate > case where there are no concurrent updates (assuming you can determine > that > quickly) it might actually be constant time. > > > Unless they keep a central counter of the number of index entries; > > which would have all the same serialization penalties we've talked > > about before... > > Bitmap indexes do in fact have concurrency issues -- arguably they're just > a > baroque version of this central counter in this case. > > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > Ask me about EnterpriseDB's Slony Replication support! > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Mirabili et Veritas Joe Mirabal ------=_Part_189_1172153.1205182463079 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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.

As you may gather from this we are relatively new on Postgres.

Any suggestions you can give me would be most helpful.

Cheers,
Joe

On Mon, Mar 10, 2008 at 11:16 AM, Gregory Stark <stark@enterprisedb.com> wrote:
"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> Well, scanning an index to get a count might be significantly faster
> than scanning the main table, but it's hardly "instantaneous".  It's
> still going to take time proportional to the table size.

Hm, Mark's comment about bitmap indexes makes that not entirely true. A bitmap
index can do RLE compression which makes the relationship between the size of
the table and the time taken to scan the index more complex. In the degenerate
case where there are no concurrent updates (assuming you can determine that
quickly) it might actually be constant time.

> Unless they keep a central counter of the number of index entries;
> which would have all the same serialization penalties we've talked
> about before...

Bitmap indexes do in fact have concurrency issues -- arguably they're just a
baroque version of this central counter in this case.

--
 Gregory Stark
 EnterpriseDB          http://www.enterprisedb.com
 Ask me about EnterpriseDB's Slony Replication support!

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



--
Mirabili et Veritas
Joe Mirabal ------=_Part_189_1172153.1205182463079--