Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 99B1F2E0091 for ; Mon, 10 Mar 2008 20:01:49 -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 73752-07 for ; Mon, 10 Mar 2008 20:01:40 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from westnet.com (westnet.com [216.187.52.2]) by postgresql.org (Postfix) with ESMTP id 2323A2E0064 for ; Mon, 10 Mar 2008 20:01:46 -0300 (ADT) Received: from westnet.com (localhost [127.0.0.1]) by westnet.com (8.14.0/8.14.0) with ESMTP id m2AN1i10000571; Mon, 10 Mar 2008 19:01:44 -0400 (EDT) Received: from localhost (gsmith@localhost) by westnet.com (8.14.0/8.13.2/Submit) with ESMTP id m2AN1irS000567; Mon, 10 Mar 2008 19:01:44 -0400 (EDT) X-Authentication-Warning: westnet.com: gsmith owned process doing -bs Date: Mon, 10 Mar 2008 19:01:43 -0400 (EDT) From: Greg Smith X-X-Sender: gsmith@westnet.com To: Joe Mirabal cc: pgsql-performance@postgresql.org Subject: Re: count * performance issue In-Reply-To: <12ba3bf00803101354w15b023e1o237f564a1b01105f@mail.gmail.com> Message-ID: 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> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/211 X-Sequence-Number: 29165 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