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 9B58553CBF for ; Fri, 8 Apr 2005 20:39:05 +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 22387-07 for ; Fri, 8 Apr 2005 19:39:02 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.205]) by svr1.postgresql.org (Postfix) with ESMTP id 87AD65363E for ; Fri, 8 Apr 2005 20:39:01 +0100 (BST) Received: by wproxy.gmail.com with SMTP id 68so1047652wri for ; Fri, 08 Apr 2005 12:39:03 -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=i9s7hnsWB95Ve89F9VCgUfZVt9T/ePI/G8jkojyBp7PQDQ9EIuV4HhbZaW3V6KBB58LwDORnoxuzj58fQJ8rHDgYQldfTUR/kdMIqTgWe2PcZJLcItYOp0wrvY0SlUChrxR1DGd2B90GMZyDeyZFj/QNH37oa4YzpQu43e0Xd3o= Received: by 10.54.26.57 with SMTP id 57mr20637wrz; Fri, 08 Apr 2005 12:39:02 -0700 (PDT) Received: by 10.54.70.11 with HTTP; Fri, 8 Apr 2005 12:39:02 -0700 (PDT) Message-ID: <762e5c05040812392480b796@mail.gmail.com> Date: Fri, 8 Apr 2005 14:39:02 -0500 From: Bob Henkel Reply-To: Bob Henkel To: Joel Fradkin Subject: Re: getting count for a specific querry Cc: Scott Marlowe , Andrew Sullivan , pgsql-sql@postgresql.org In-Reply-To: <003b01c53c70$6ff184b0$797ba8c0@jfradkin> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_26021_17245336.1112989142788" References: <1112983840.20921.77.camel@state.g2switchworks.com> <003b01c53c70$6ff184b0$797ba8c0@jfradkin> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.238 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/115 X-Sequence-Number: 21008 ------=_Part_26021_17245336.1112989142788 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On Apr 8, 2005 2:23 PM, Joel Fradkin wrote:=20 >=20 > Believe me I just spent two months converting our app, I do not wish to= =20 > give > up on that work. We do a great deal more then count. Specifically many of > our queries run much slower on postgres. As mentioned I purchased a 4 pro= c > box with 8 gigs of memory for this upgrade (Dell may have been a poor=20 > choice > based on comments I have received). Even when I could see a query like > select * from tblassoc where clientnum =3D 'WAZ' using indexed joins on > location and jobtitle it is still taking 22 seconds to run compared to th= e=20 > 9 > seconds on MSSQL on a 2 proc 2 gig box. I got one of my querries to run > faster using a page cost of .2 but then the assoc query was running 50 > seconds, so I adjusted to a cost of 2 (tried 1.2, 2, 3, and 4 and did not > see hug changes in the assoc except it did not like .2). >=20 > I have placed a call to commandprompt.com and= =20 > am going to pay for some > support to see if they have anything meaningful to add. >=20 > It could be something with my hardware, my hardware config, my postgres > config. I am just not sure. I know I have worked diligently to try to=20 > learn > all I can and I used to think I was kinda smart. >=20 > I set up the data on 4 10k scsi drives in a powervault and my wal on 2 15= k > drives. I am using links to those from the install directory. It starts= =20 > and > stops ok this way, but maybe it should be different. >=20 > I can tell you I am very happy to have this forum as I could not have=20 > gotten > to the point I am without the many usefull comments from folks on the=20 > list. > I greatly appreciate everyone who has helped. But truth is if I cant get= =20 > to > work better then I have I may have to ditch the effort and bite the 70K > bullet. Its compounded by using 3 developers time for two months to yield= =20 > an > answer that my boss may just fire me for. I figured since my first test > showed I could get data faster on the postgres box that I could with=20 > enough > study get all our data to go faster, but I am afraid I have not been very > successful. >=20 > My failure is not a reflection postgres as you mentioned it is definatley > great at some things. I have 90 some views not to mention as many stored > procedures that have been converted. I wrote an app to move the data and= =20 > it > works great. But if it too slow I just will not be able to use for > production. >=20 > Joel >=20 >=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 9: the planner will ignore your desire to choose an index scan if you= r > joining column's datatypes do not match >=20 Have you posted your postgresql config files for the folks here to review? = I=20 can't say I can help you with that because I too can only read the docs and= =20 go from there. But for specific situations you need specific configs. I=20 would think you can get more out of postgresql with a some time and help=20 from the people around here. Though count(*) looks like it may be slow. ------=_Part_26021_17245336.1112989142788 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
On Apr 8, 2005 2:23 PM, Joel Fradkin <jf= radkin@wazagua.com> wrote:
Believe me I just spent two mont= hs converting our app, I do not wish to give
up on that work. We do a gr= eat deal more then count. Specifically many of
our queries run much slow= er on postgres. As mentioned I purchased a 4 proc
box with 8 gigs of mem= ory for this upgrade (Dell may have been a poor choice
based on comments= I have received). Even when I could see a query like
select * from tbla= ssoc where clientnum =3D 'WAZ' using indexed joins on
location and jobti= tle it is still taking 22 seconds to run compared to the 9
seconds on MS= SQL on a 2 proc 2 gig box. I got one of my querries to run
faster using = a page cost of .2 but then the assoc query was running 50
seconds, so I = adjusted to a cost of 2 (tried 1.2, 2, 3, and 4 and did not
see hug chan= ges in the assoc except it did not like .2).

I have placed a call to= commandprompt.com and am going to= pay for some
support to see if they have anything meaningful to add.
It could be something with my hardware, my hardware config, my postgre= s
config. I am just not sure. I know I have worked diligently to try to = learn
all I can and I used to think I was kinda smart.

I set up t= he data on 4 10k scsi drives in a powervault and my wal on 2 15k
drives.= I am using links to those from the install directory. It starts and
sto= ps ok this way, but maybe it should be different.

I can tell you I a= m very happy to have this forum as I could not have gotten
to the point = I am without the many usefull comments from folks on the list.
I greatly= appreciate everyone who has helped. But truth is if I cant get to
work = better then I have I may have to ditch the effort and bite the 70K
bulle= t. Its compounded by using 3 developers time for two months to yield an
= answer that my boss may just fire me for. I figured since my first test
= showed I could get data faster on the postgres box that I could with enough=
study get all our data to go faster, but I am afraid I have not been ve= ry
successful.

My failure is not a reflection postgres as you men= tioned it is definatley
great at some things. I have 90 some views not t= o mention as many stored
procedures that have been converted. I wrote an= app to move the data and it
works great. But if it too slow I just will= not be able to use for
production.

Joel


Judging postg= resql on one single data point (count(*) performance) is
quite unfair.&n= bsp; 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 data= base?

PostgreSQL is a great fit for certain loads, and a poor fit fo= r others.
Are you going to have lots of people updating the database WHI= LE 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 re= ally going to be doing
with the system.

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

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

-----------------= ----------(end of broadcast)---------------------------
TIP 9: the plann= er will ignore your desire to choose an index scan if your
  &= nbsp;  joining column's datatypes do not match
<= br>Have you posted your postgresql config files for the folks here to revie= w? I can't say I can help you with that because I too can only read the doc= s and go from there. But for specific situations you need specific configs.=   I would think you can get more out of postgresql with a some time an= d help from the people around here.  Though count(*) looks like it may= be slow. ------=_Part_26021_17245336.1112989142788--