Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 3407A2E1A50 for ; Mon, 10 Mar 2008 23:57:11 -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 50591-07 for ; Mon, 10 Mar 2008 23:56:58 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.156]) by postgresql.org (Postfix) with ESMTP id 800FE2E1988 for ; Mon, 10 Mar 2008 23:57:07 -0300 (ADT) Received: by fg-out-1718.google.com with SMTP id 22so1790180fge.23 for ; Mon, 10 Mar 2008 19:57:06 -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:in-reply-to:mime-version:content-type:references; bh=OagHHZMAxQrXKoF1D1DAkbu/wIXdCs84Ssn4hLlu1qs=; b=o4c2Ndb5oVLAvsyjFPyduUCKM8j+mu4wl3GZQDSVP+nciL0M37KoA5UALebvmcaJfXiDCCzVVRZYJZLqIxGhFWQPSwdcv+VCRVOh7vOos7eyoTEqHQJ6x1KHjCgWgCws3Iy6YiTi8Q4ccxszFyelZFrQHDAzjnGDY5JdFrTdDCA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=gFFBVctOPNmKNFGVIuakhifhyD2VA/iJU+8naKjd/Y8gel1Nm4bveh0XVQunaZQ6vL6C8pOYI9ozqO4HxT4uuhO+kWd2t/eQycrnMT/U6TPrnp/vSTXDEaR9FiTBED+vR3GiKMTSKtJ8UbxEPx4PYjy97RBUb2a/dCXGdigi4jI= Received: by 10.82.187.2 with SMTP id k2mr14927137buf.26.1205204225599; Mon, 10 Mar 2008 19:57:05 -0700 (PDT) Received: by 10.82.113.9 with HTTP; Mon, 10 Mar 2008 19:57:05 -0700 (PDT) Message-ID: <36af4bed0803101957u6078127fq78dc3384fe78de5d@mail.gmail.com> Date: Tue, 11 Mar 2008 08:27:05 +0530 From: "Robins Tharakan" To: pgsql-performance@postgresql.org Subject: Re: count * performance issue In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_13628_71472.1205204225588" 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> <12ba3bf00803101354w15b023e1o237f564a1b01105f@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/218 X-Sequence-Number: 29172 ------=_Part_13628_71472.1205204225588 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi, I have been reading this conversation for a few days now and I just wanted to ask this. From the release notes, one of the new additions in 8.3 is (Allow col IS NULL to use an index (Teodor)). Sorry, if I am missing something here, but shouldn't something like this allow us to get a (fast) accurate count ? SELECT COUNT(*) from table WHERE indexed_field IS NULL + SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL *Robins Tharakan* ---------- Forwarded message ---------- From: Greg Smith Date: Tue, Mar 11, 2008 at 4:31 AM Subject: Re: [PERFORM] count * performance issue To: Joe Mirabal Cc: pgsql-performance@postgresql.org On Mon, 10 Mar 2008, Joe Mirabal wrote: > 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. Are you sure the form of "select count(*)" you're using is actually utilizing the index to find a useful subset? What do you get out of EXPLAIN ANALZYE on the query? In order for indexes to be helpful a couple of things need to happen: 1) They have to be structured correctly to be useful 2) There needs to be large enough settings for shared_buffes and effective_cache_size that the database things it can use them efficiently 3) The tables involved need to be ANALYZEd to keep their statistics up to date. The parameters to run a 400GB *table* are very different from the defaults; if you want tuning suggestions you should post the non-default entries in your postgresql.conf file from what you've already adjusted along with basic information about your server (PostgreSQL version, OS, memory, disk setup). > We in our warehouse use the count(*) as our verification of counts by > day/month's etc If you've got a database that size and you're doing that sort of thing on it, you really should be considering partitioning as well. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ------=_Part_13628_71472.1205204225588 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi,

I have been reading this conversation for a few days now and I just wanted to ask this. From the release notes, one of the new additions in 8.3 is (Allow col IS NULL to use an index (Teodor)).

Sorry, if I am missing something here, but shouldn't something like this allow us to get a (fast) accurate count ?

SELECT COUNT(*) from table WHERE indexed_field IS NULL
+
SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL

Robins Tharakan

---------- Forwarded message ----------
From: Greg Smith <gsmith@gregsmith.com>
Date: Tue, Mar 11, 2008 at 4:31 AM
Subject: Re: [PERFORM] count * performance issue
To: Joe Mirabal <jmmirabal@gmail.com>
Cc: pgsql-performance@postgresql.org


On Mon, 10 Mar 2008, Joe Mirabal wrote:

> 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.

Are you sure the form of "select count(*)" you're using is actually
utilizing the index to find a useful subset?  What do you get out of
EXPLAIN ANALZYE on the query?

In order for indexes to be helpful a couple of things need to happen:
1) They have to be structured correctly to be useful
2) There needs to be large enough settings for shared_buffes and
effective_cache_size that the database things it can use them efficiently
3) The tables involved need to be ANALYZEd to keep their statistics up to
date.

The parameters to run a 400GB *table* are very different from the
defaults; if you want tuning suggestions you should post the non-default
entries in your postgresql.conf file from what you've already adjusted
along with basic information about your server (PostgreSQL version, OS,
memory, disk setup).

> We in our warehouse use the count(*) as our verification of counts by
> day/month's etc

If you've got a database that size and you're doing that sort of thing on
it, you really should be considering partitioning as well.

 --
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

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

------=_Part_13628_71472.1205204225588--