X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3A027535DC for ; Fri, 8 Apr 2005 19:54:42 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 11777-03 for ; Fri, 8 Apr 2005 18:54:36 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.198]) by svr1.postgresql.org (Postfix) with ESMTP id 1A2B95337D for ; Fri, 8 Apr 2005 19:54:35 +0100 (BST) Received: by wproxy.gmail.com with SMTP id 68so1035791wri for ; Fri, 08 Apr 2005 11:54:36 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references; b=V9S9/iNAB4OvWLvaZTs4aA/1Z10q7g/O+D5u3geChBcHUfzXs93ipH75XNZMu7sdsKWfDyacSmyw7RxIZDwIgdZsrwxPT4qJzDQVhNeb1yB21o23dK7UXO+4m3N2vHl7JjIawmHj8cbvhDxNK4oFMt8X4wsp/mxg8Sc73upcJGQ= Received: by 10.54.10.59 with SMTP id 59mr944284wrj; Fri, 08 Apr 2005 11:54:36 -0700 (PDT) Received: by 10.54.70.11 with HTTP; Fri, 8 Apr 2005 11:54:35 -0700 (PDT) Message-ID: <762e5c050408115451e67c70@mail.gmail.com> Date: Fri, 8 Apr 2005 13:54:35 -0500 From: Bob Henkel Reply-To: Bob Henkel To: Scott Marlowe Subject: Re: getting count for a specific querry Cc: Joel Fradkin , Andrew Sullivan , pgsql-sql@postgresql.org In-Reply-To: <1112983840.20921.77.camel@state.g2switchworks.com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_25859_17288088.1112986475491" References: <000001c53c5d$9f07f0d0$797ba8c0@jfradkin> <1112983840.20921.77.camel@state.g2switchworks.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.186 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_10_20, HTML_MESSAGE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200504/110 X-Sequence-Number: 21003 ------=_Part_25859_17288088.1112986475491 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On Apr 8, 2005 1:10 PM, Scott Marlowe wrote:= =20 >=20 > 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= =20 > 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= =20 > 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. >=20 > 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? >=20 > 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. >=20 > 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. >=20 > 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. >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings >=20 From a simple/high level perspective why is this? That is why can't=20 PostgreSQL do aggregates as well across large chunks of data. I'm assuming= =20 it extremely complicated. Otherwise the folks around here would have churne= d=20 out a fix in a month or less and made this issue a past story. ------=_Part_25859_17288088.1112986475491 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
On Apr 8, 2005 1:10 PM, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
On Fri, 2005-04-08 at 12:08, Joe= l Fradkin wrote:
> Thanks all.
> I might have to add a button t= o do the count on command so they don't get
> the hit.
> I woul= d want it to return the count of the condition, not the currently
> d= isplayed number of rows.
>
> Is there any other database engine= s that provide better performance?
> (We just 2 moths moving to postg= res and it is not live yet, but if I am
> going to get results back s= lower then my 2 proc box running MSSQL in 2 gig
> and 2 processor I c= ant 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 ou= r config to optimize.

Judging postgresql on one single data point (c= ount(*) 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 grea= t fit for certain loads, and a poor fit for others.
Are you going to hav= e lots of people updating the database WHILE the
select count(*) queries= are running?  Are you going to be doing other,
more interesti= ng things than simply counting?  If so, you really should
buil= d a test case that emulates what you're really going to be doing
with th= e system.

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

Note that the basic de= sign 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 chu= nks of your data.

---------------------------(end of broadcast)-----= ----------------------
TIP 7: don't forget to increase your free space m= ap settings
From a simple/high level perspective why = is this?  That is why can't PostgreSQL do aggregates as well across la= rge chunks of data. I'm assuming it extremely complicated. Otherwise t= he folks around here would have churned out a fix in a month or less and ma= de this issue a past story.
------=_Part_25859_17288088.1112986475491--