Subject: Re: getting count for a specific querry
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Joel Fradkin <jfradkin@wazagua.com>
Cc: 'Andrew Sullivan' <ajs@crankycanuck.ca>, pgsql-sql@postgresql.org
In-Reply-To: <000001c53c5d$9f07f0d0$797ba8c0@jfradkin>
References: <000001c53c5d$9f07f0d0$797ba8c0@jfradkin>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1112983840.20921.77.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Fri, 08 Apr 2005 13:10:40 -0500

On Fri, 2005-04-08 at 12:08, Joel Fradkin wrote:
> Thanks all.
> I might have to add a button to do the count on command so they don't get
> the hit.
> I would want it to return the count of the condition, not the currently
> displayed number of rows.
> 
> Is there any other database engines that provide better performance?
> (We just 2 moths moving to postgres and it is not live yet, but if I am
> going to get results back slower then my 2 proc box running MSSQL in 2 gig
> and 2 processor I cant see any reason to move to it)
> The Postgres is on a 4 proc Dell with 8 gigs of memory.
> I thought I could analyze our queries and our config to optimize.

Judging postgresql on one single data point (count(*) performance) is
quite unfair.  Unless your system only operates on static data and is
used to mostly do things like counting, in which case, why are you using
a database?

PostgreSQL is a great fit for certain loads, and a poor fit for others. 
Are you going to have lots of people updating the database WHILE the
select count(*) queries are running?  Are you going to be doing other,
more interesting things than simply counting?  If so, you really should
build a test case that emulates what you're really going to be doing
with the system.

I've found that the poor performance of aggregates in PostgreSQL is
generally more than made up for by the outstanding behaviour it exhibits
when under heavy parallel load.  

Note that the basic design of PostgreSQL's MVCC system is such that
without using some kind of trigger to maintain pre-calculated aggregate
information, it will NEVER be as fast as most other databases at doing
aggregates across large chunks of your data.