From jfradkin@wazagua.com Fri Apr 8 13:29:14 2005 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 8A7B553CA3 for ; Fri, 8 Apr 2005 14:29:14 +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 12840-07 for ; Fri, 8 Apr 2005 13:29:10 +0000 (GMT) Received: from vms040pub.verizon.net (vms040pub.verizon.net [206.46.252.40]) by svr1.postgresql.org (Postfix) with ESMTP id 8CF9553CBB for ; Fri, 8 Apr 2005 14:29:09 +0100 (BST) Received: from jfradkin ([66.15.127.235]) by vms040.mailsrvcs.net (Sun Java System Messaging Server 6.2 HotFix 0.04 (built Dec 24 2004)) with ESMTPA id <0IEM00HQRQSOQQQ6@vms040.mailsrvcs.net> for pgsql-sql@postgresql.org; Fri, 08 Apr 2005 08:29:13 -0500 (CDT) Date: Fri, 08 Apr 2005 09:29:13 -0400 From: "Joel Fradkin" Subject: getting count for a specific querry To: Message-id: <000001c53c3e$f4f5b730$797ba8c0@jfradkin> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Mailer: Microsoft Outlook, Build 10.0.6626 Content-type: multipart/alternative; boundary="----=_NextPart_000_0001_01C53C1D.6DE41730" Importance: Normal X-Priority: 3 (Normal) X-MSMail-priority: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.382 tagged_above=0 required=5 tests=AWL, HTML_60_70, HTML_MESSAGE X-Spam-Level: * X-Archive-Number: 200504/86 X-Sequence-Number: 20979 This is a multi-part message in MIME format. ------=_NextPart_000_0001_01C53C1D.6DE41730 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Per a thread a while back the discussion was along the lines of serving = data up to the web quick. Our app currently pulls a bunch of data to several query pages. I have tried and not gotten the queries to return as fast as they do now which is a huge disappointment as the hardware is twice as powerful as = our current production. I can get it pretty close on some thing but others = are just plain slower. =20 My idea is to use the limit and offset to return just the first 50 = records, if they hit next I can set the offset. My understanding was this gets slower as you move further into the data, = but we have several options to modify the search, and I do not believe our clients will page very far intro a dataset. =20 One problem I think I will have though is they currently have count of = the records matching their request and I would like to keep that as a = display field =20 So given a table of associates my default query will be something like Select * from tblassoc where clientnum =3D 'WAZ' and isdeleted is false The user could add stuff like and where first name like 'Joel' =20 Currently it returns all records with a count and a display of the = records your viewing like 1-50 of 470, next page is 51-100 etc. Is there a fast way to get the count? Will this concept fly? Also I am getting heat that my search is now case sensitive. What is the best way = to get a case insensitive search? I could use ~* or perhaps do an UPPER(firstname) in the select etc?=20 =20 Thanks for any ideas here. I have tried playing with various settings = and have not seen my times change much, I will persue this on the = performance mailing list. =20 =20 =20 Joel Fradkin =20 =20 =20 ------=_NextPart_000_0001_01C53C1D.6DE41730 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Per a thread a while back the discussion was along = the lines of serving data up to the web quick.

Our app currently pulls a bunch of data to several = query pages.

I have tried and not gotten the queries to return as = fast as they do now which is a huge disappointment as the hardware is twice as = powerful as our current production. I can get it pretty close on some thing but = others are just plain slower.

 

My idea is to use the limit and offset to return just = the first 50 records, if they hit next I can set the = offset.

My understanding was this gets slower as you move = further into the data, but we have several options to modify the search, and I = do not believe our clients will page very far intro a = dataset.

 

One problem I think I will have though is they = currently have count of the records matching their request and I would like to = keep that as a display field

 

So given a table of associates my default query will = be something like

Select * from tblassoc where clientnum =3D = ‘WAZ’ and isdeleted is false

The user could add stuff like and where first name = like ‘Joel’

 

Currently it returns all records with a count and a = display of the records your viewing like 1-50 of 470, next page is 51-100 = etc.

Is there a fast way to get the count? Will this = concept fly? Also I am getting heat that my search is now case sensitive. What is the = best way to get a case insensitive search? I could use ~* or perhaps do an = UPPER(firstname) in the select etc?

 

Thanks for any ideas here. I have tried playing with = various settings and have not seen my times change much, I will persue this on = the performance mailing list.

 

 

 

Joel Fradkin

 


 

 

------=_NextPart_000_0001_01C53C1D.6DE41730-- From ajs@crankycanuck.ca Fri Apr 8 15:07:38 2005 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 965FD53377 for ; Fri, 8 Apr 2005 16:07:38 +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 43700-08 for ; Fri, 8 Apr 2005 15:07:34 +0000 (GMT) Received: from smtp100.rog.mail.re2.yahoo.com (smtp100.rog.mail.re2.yahoo.com [206.190.36.78]) by svr1.postgresql.org (Postfix) with SMTP id 0C6E553234 for ; Fri, 8 Apr 2005 16:07:33 +0100 (BST) Received: from unknown (HELO phlogiston.dydns.org) (a.sullivan@rogers.com@216.75.167.195 with login) by smtp100.rog.mail.re2.yahoo.com with SMTP; 8 Apr 2005 15:07:32 -0000 Received: by phlogiston.dydns.org (Postfix, from userid 1000) id 682333D2F; Fri, 8 Apr 2005 11:07:31 -0400 (EDT) Date: Fri, 8 Apr 2005 11:07:31 -0400 From: Andrew Sullivan To: pgsql-sql@postgresql.org Subject: Re: getting count for a specific querry Message-ID: <20050408150731.GB27718@phlogiston.dyndns.org> References: <000001c53c3e$f4f5b730$797ba8c0@jfradkin> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <000001c53c3e$f4f5b730$797ba8c0@jfradkin> User-Agent: Mutt/1.5.6+20040907i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200504/91 X-Sequence-Number: 20984 On Fri, Apr 08, 2005 at 09:29:13AM -0400, Joel Fradkin wrote: > My understanding was this gets slower as you move further into the data, but > we have several options to modify the search, and I do not believe our > clients will page very far intro a dataset. > It gets slower because when you do an offset of 50, you have to pass through the first 50 before picking up the ones you want. If you offset 100, you scan through the first 100. &c. If you don't want to pay that, you need to use a cursor, but this causes the problem that you have to keep your cursor open across page views, which is a tricky issue on the Web. > Currently it returns all records with a count and a display of the records > your viewing like 1-50 of 470, next page is 51-100 etc. > > Is there a fast way to get the count? Not really, no. You have to perform a count() to get it, which is possibly expensive. One way to do it, though, is to do SELECT count(*) FROM tablename WHERE condition LIMIT n; or something like that. Assuming the condition is reasonably limited (i.e. it's not going to cost you a fortune to run this), you'll get the right number back if the number is < n or else you'll get n. If you have n, your application can say "viewing 1-50 of at least n records". This is something you see from time to time in this sort of application. > getting heat that my search is now case sensitive. What is the best way to > get a case insensitive search? I could use ~* or perhaps do an > UPPER(firstname) in the select etc? The upper() (or lower() -- whatever) stragegy is what I'd use. In any case, you want to make sure you put functional indexes on all such columns, because otherwise you'll never get an index scan. A -- Andrew Sullivan | ajs@crankycanuck.ca In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland From gnari@simnet.is Fri Apr 8 16:16:20 2005 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 DC2945365F for ; Fri, 8 Apr 2005 17:16:20 +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 65956-09 for ; Fri, 8 Apr 2005 16:16:16 +0000 (GMT) Received: from quasar.skima.is (quasar.skima.is [212.30.200.205]) by svr1.postgresql.org (Postfix) with ESMTP id B61DA53635 for ; Fri, 8 Apr 2005 17:16:14 +0100 (BST) Received: from gnari ([157.157.159.208] [157.157.159.208]) by quasar.skima.is with ESMTP; Fri, 8 Apr 2005 16:16:13 Z Subject: Re: getting count for a specific querry From: Ragnar =?ISO-8859-1?Q?Hafsta=F0?= To: Andrew Sullivan Cc: pgsql-sql@postgresql.org In-Reply-To: <20050408150731.GB27718@phlogiston.dyndns.org> References: <000001c53c3e$f4f5b730$797ba8c0@jfradkin> <20050408150731.GB27718@phlogiston.dyndns.org> Content-Type: text/plain Date: Fri, 08 Apr 2005 16:17:45 +0000 Message-Id: <1112977065.7444.30.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/96 X-Sequence-Number: 20989 On Fri, 2005-04-08 at 11:07 -0400, Andrew Sullivan wrote: > On Fri, Apr 08, 2005 at 09:29:13AM -0400, Joel Fradkin wrote: > > > > Is there a fast way to get the count? > > Not really, no. You have to perform a count() to get it, which is > possibly expensive. One way to do it, though, is to do > > SELECT count(*) FROM tablename WHERE condition LIMIT n; > > or something like that. Assuming the condition is reasonably limited > (i.e. it's not going to cost you a fortune to run this), you'll get > the right number back if the number is < n or else you'll get > n. come again ? test=# select count(*) from a; count ------- 3 (1 row) test=# select count(*) from a limit 2; count ------- 3 (1 row) the LIMIT clause limits the number of rows returned by the select, in this case 1 row. maybe you mean something like: test=# select count(*) from (select * from a limit 2) as foo; count ------- 2 (1 row) gnari From gnari@simnet.is Fri Apr 8 16:24:20 2005 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 4145153635 for ; Fri, 8 Apr 2005 17:24:20 +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 69955-01 for ; Fri, 8 Apr 2005 16:24:15 +0000 (GMT) Received: from quasar.skima.is (quasar.skima.is [212.30.200.205]) by svr1.postgresql.org (Postfix) with ESMTP id 9E2D453671 for ; Fri, 8 Apr 2005 17:24:14 +0100 (BST) Received: from gnari ([157.157.159.208] [157.157.159.208]) by quasar.skima.is with ESMTP; Fri, 8 Apr 2005 16:24:14 Z Subject: Re: getting count for a specific querry From: Ragnar =?ISO-8859-1?Q?Hafsta=F0?= To: Joel Fradkin Cc: pgsql-sql@postgresql.org In-Reply-To: <000001c53c3e$f4f5b730$797ba8c0@jfradkin> References: <000001c53c3e$f4f5b730$797ba8c0@jfradkin> Content-Type: text/plain Date: Fri, 08 Apr 2005 16:25:46 +0000 Message-Id: <1112977546.7447.39.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/97 X-Sequence-Number: 20990 On Fri, 2005-04-08 at 09:29 -0400, Joel Fradkin wrote: > Our app currently pulls a bunch of data to several query pages. > > My idea is to use the limit and offset to return just the first 50 > records, if they hit next I can set the offset. > > My understanding was this gets slower as you move further into the > data, but we have several options to modify the search, and I do not > believe our clients will page very far intro a dataset. you might reduce the performance loss if your dataset is ordered by a UNIQUE index. select * from mytable where somecondition ORDER by uniquecol limit 50; and next: select * from mytable where somecondition AND uniquecol>? ORDER by uniquecol limit 50 OFFSET 50; where the ? is placeholder for last value returned by last query. if your unique index is a multi-column one, the method is slightly more complicated, but the same idea. gnari From ajs@crankycanuck.ca Fri Apr 8 16:30:49 2005 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 3D06253677 for ; Fri, 8 Apr 2005 17:30:49 +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 71461-08 for ; Fri, 8 Apr 2005 16:30:42 +0000 (GMT) Received: from smtp100.rog.mail.re2.yahoo.com (smtp100.rog.mail.re2.yahoo.com [206.190.36.78]) by svr1.postgresql.org (Postfix) with SMTP id CFE6353666 for ; Fri, 8 Apr 2005 17:30:40 +0100 (BST) Received: from unknown (HELO phlogiston.dydns.org) (a.sullivan@rogers.com@216.75.167.195 with login) by smtp100.rog.mail.re2.yahoo.com with SMTP; 8 Apr 2005 16:30:40 -0000 Received: by phlogiston.dydns.org (Postfix, from userid 1000) id 8C1B53D2E; Fri, 8 Apr 2005 12:30:39 -0400 (EDT) Date: Fri, 8 Apr 2005 12:30:39 -0400 From: Andrew Sullivan To: pgsql-sql@postgresql.org Subject: Re: getting count for a specific querry Message-ID: <20050408163039.GE27718@phlogiston.dyndns.org> References: <000001c53c3e$f4f5b730$797ba8c0@jfradkin> <20050408150731.GB27718@phlogiston.dyndns.org> <1112977065.7444.30.camel@localhost.localdomain> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <1112977065.7444.30.camel@localhost.localdomain> User-Agent: Mutt/1.5.6+20040907i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.025 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/98 X-Sequence-Number: 20991 On Fri, Apr 08, 2005 at 04:17:45PM +0000, Ragnar Hafstaš wrote: > On Fri, 2005-04-08 at 11:07 -0400, Andrew Sullivan wrote: > > > > SELECT count(*) FROM tablename WHERE condition LIMIT n; > the LIMIT clause limits the number of rows returned by the select, > in this case 1 row. > > maybe you mean something like: > > test=# select count(*) from (select * from a limit 2) as foo; Yes, that was stupid of me. That's what I meant, though. A -- Andrew Sullivan | ajs@crankycanuck.ca When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes From tgl@sss.pgh.pa.us Fri Apr 8 16:32:10 2005 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 CE371536DB for ; Fri, 8 Apr 2005 17:32:10 +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 71342-06 for ; Fri, 8 Apr 2005 16:32:05 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id D291953671 for ; Fri, 8 Apr 2005 17:32:03 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j38GW3Hh015085; Fri, 8 Apr 2005 12:32:03 -0400 (EDT) To: Ragnar =?ISO-8859-1?Q?Hafsta=F0?= Cc: Joel Fradkin , pgsql-sql@postgresql.org Subject: Re: getting count for a specific querry In-reply-to: <1112977546.7447.39.camel@localhost.localdomain> References: <000001c53c3e$f4f5b730$797ba8c0@jfradkin> <1112977546.7447.39.camel@localhost.localdomain> Comments: In-reply-to Ragnar =?ISO-8859-1?Q?Hafsta=F0?= message dated "Fri, 08 Apr 2005 16:25:46 -0000" Date: Fri, 08 Apr 2005 12:32:03 -0400 Message-ID: <15084.1112977923@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/99 X-Sequence-Number: 20992 Ragnar =?ISO-8859-1?Q?Hafsta=F0?= writes: > you might reduce the performance loss if your dataset is ordered by > a UNIQUE index. > select * from mytable where somecondition > ORDER by uniquecol limit 50; > and next: > select * from mytable where somecondition AND uniquecol>? > ORDER by uniquecol limit 50 OFFSET 50; > where the ? is placeholder for last value returned by last query. Uh, you don't want the OFFSET there do you? But otherwise, yeah, this is a popular solution for paging through some rows. Doesn't really help with the problem of counting the total dataset though ... regards, tom lane From pg@rbt.ca Fri Apr 8 16:51:43 2005 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 3989F53745 for ; Fri, 8 Apr 2005 17:51:43 +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 76821-06 for ; Fri, 8 Apr 2005 16:51:38 +0000 (GMT) Received: from tht.net (vista.tht.net [216.126.88.2]) by svr1.postgresql.org (Postfix) with ESMTP id 393A853743 for ; Fri, 8 Apr 2005 17:51:37 +0100 (BST) Received: from dyn-70-66.tor.dsl.tht.net (dyn-70-66.tor.dsl.tht.net [134.22.70.66]) by tht.net (Postfix) with ESMTP id 0F2A976A27; Fri, 8 Apr 2005 12:51:37 -0400 (EDT) Subject: Re: getting count for a specific querry From: Rod Taylor To: Tom Lane Cc: pgsql-sql@postgresql.org, Joel Fradkin , Ragnar =?ISO-8859-1?Q?Hafsta=F0?= In-Reply-To: <15084.1112977923@sss.pgh.pa.us> References: <000001c53c3e$f4f5b730$797ba8c0@jfradkin> <1112977546.7447.39.camel@localhost.localdomain> <15084.1112977923@sss.pgh.pa.us> Content-Type: text/plain Date: Fri, 08 Apr 2005 12:51:32 -0400 Message-Id: <1112979092.92363.250.camel@home> Mime-Version: 1.0 X-Mailer: Evolution 2.2.1.1 FreeBSD GNOME Team Port Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.008 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/101 X-Sequence-Number: 20994 > > select * from mytable where somecondition AND uniquecol>? > > ORDER by uniquecol limit 50 OFFSET 50; > > > where the ? is placeholder for last value returned by last query. > > Uh, you don't want the OFFSET there do you? But otherwise, yeah, > this is a popular solution for paging through some rows. Doesn't really > help with the problem of counting the total dataset though ... In the past I've done an EXPLAIN and parsed the plan to see what PostgreSQL estimated for the number of . If less than $threshhold, I would do a count(*). If more than $threshhold I would display to the user "approx N records". This seemed to be good enough for most cases. -- From gnari@simnet.is Fri Apr 8 16:54:29 2005 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 211BF534B5 for ; Fri, 8 Apr 2005 17:54:29 +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 75676-10 for ; Fri, 8 Apr 2005 16:54:24 +0000 (GMT) Received: from quasar.skima.is (quasar.skima.is [212.30.200.205]) by svr1.postgresql.org (Postfix) with ESMTP id 8E002531C7 for ; Fri, 8 Apr 2005 17:54:23 +0100 (BST) Received: from gnari ([157.157.159.208] [157.157.159.208]) by quasar.skima.is with ESMTP; Fri, 8 Apr 2005 16:54:21 Z Subject: Re: getting count for a specific querry From: Ragnar =?ISO-8859-1?Q?Hafsta=F0?= To: Tom Lane Cc: Joel Fradkin , pgsql-sql@postgresql.org In-Reply-To: <15084.1112977923@sss.pgh.pa.us> References: <000001c53c3e$f4f5b730$797ba8c0@jfradkin> <1112977546.7447.39.camel@localhost.localdomain> <15084.1112977923@sss.pgh.pa.us> Content-Type: text/plain Date: Fri, 08 Apr 2005 16:55:49 +0000 Message-Id: <1112979349.7447.42.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/102 X-Sequence-Number: 20995 On Fri, 2005-04-08 at 12:32 -0400, Tom Lane wrote: > Ragnar =?ISO-8859-1?Q?Hafsta=F0?= writes: > > you might reduce the performance loss if your dataset is ordered by > > a UNIQUE index. > > > select * from mytable where somecondition > > ORDER by uniquecol limit 50; > > > and next: > > > select * from mytable where somecondition AND uniquecol>? > > ORDER by uniquecol limit 50 OFFSET 50; > > > where the ? is placeholder for last value returned by last query. > > Uh, you don't want the OFFSET there do you? ooops! of course not. the uniquecol>? is meant to REPLACE the OFFSET. gnari From jfradkin@wazagua.com Fri Apr 8 17:08:48 2005 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 BF93B53608 for ; Fri, 8 Apr 2005 18:08:48 +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 82112-04 for ; Fri, 8 Apr 2005 17:08:43 +0000 (GMT) Received: from vms040pub.verizon.net (vms040pub.verizon.net [206.46.252.40]) by svr1.postgresql.org (Postfix) with ESMTP id 82477535F0 for ; Fri, 8 Apr 2005 18:08:42 +0100 (BST) Received: from jfradkin ([66.15.127.235]) by vms040.mailsrvcs.net (Sun Java System Messaging Server 6.2 HotFix 0.04 (built Dec 24 2004)) with ESMTPA id <0IEN004090YIQIIC@vms040.mailsrvcs.net> for pgsql-sql@postgresql.org; Fri, 08 Apr 2005 12:08:43 -0500 (CDT) Date: Fri, 08 Apr 2005 13:08:44 -0400 From: "Joel Fradkin" Subject: Re: getting count for a specific querry In-reply-to: <20050408163039.GE27718@phlogiston.dyndns.org> To: "'Andrew Sullivan'" , Message-id: <000001c53c5d$9f07f0d0$797ba8c0@jfradkin> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Mailer: Microsoft Outlook, Build 10.0.6626 Content-type: text/plain; charset=us-ascii Content-transfer-encoding: 7bit Importance: Normal X-Priority: 3 (Normal) X-MSMail-priority: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.34 tagged_above=0 required=5 tests=AWL X-Spam-Level: * X-Archive-Number: 200504/103 X-Sequence-Number: 20996 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. Joel Fradkin From smarlowe@g2switchworks.com Fri Apr 8 18:11:17 2005 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 5114B5360C for ; Fri, 8 Apr 2005 19:11:17 +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 98326-08 for ; Fri, 8 Apr 2005 18:11:16 +0000 (GMT) Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by svr1.postgresql.org (Postfix) with ESMTP id 8950853CBE for ; Fri, 8 Apr 2005 19:10:49 +0100 (BST) Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP with MS-WebStorage 6.5.6944 Received: from state.g2switchworks.com by mail.g2switchworks.com; 08 Apr 2005 13:10:40 -0500 Subject: Re: getting count for a specific querry From: Scott Marlowe To: Joel Fradkin Cc: 'Andrew Sullivan' , 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 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Fri, 08 Apr 2005 13:10:40 -0500 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/105 X-Sequence-Number: 20998 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. From luckyratfoot@gmail.com Fri Apr 8 18:54:42 2005 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-- From jfradkin@wazagua.com Fri Apr 8 19:23:32 2005 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 0DCFD53C8E for ; Fri, 8 Apr 2005 20:23:32 +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 18316-07 for ; Fri, 8 Apr 2005 19:23:29 +0000 (GMT) Received: from vms048pub.verizon.net (vms048pub.verizon.net [206.46.252.48]) by svr1.postgresql.org (Postfix) with ESMTP id 0055C53CC1 for ; Fri, 8 Apr 2005 20:23:23 +0100 (BST) Received: from jfradkin ([66.15.127.235]) by vms048.mailsrvcs.net (Sun Java System Messaging Server 6.2 HotFix 0.04 (built Dec 24 2004)) with ESMTPA id <0IEN00D3K76ZX7I2@vms048.mailsrvcs.net> for pgsql-sql@postgresql.org; Fri, 08 Apr 2005 14:23:24 -0500 (CDT) Date: Fri, 08 Apr 2005 15:23:25 -0400 From: "Joel Fradkin" Subject: Re: getting count for a specific querry In-reply-to: <1112983840.20921.77.camel@state.g2switchworks.com> To: "'Scott Marlowe'" Cc: "'Andrew Sullivan'" , Message-id: <003b01c53c70$6ff184b0$797ba8c0@jfradkin> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Mailer: Microsoft Outlook, Build 10.0.6626 Content-type: text/plain; charset=us-ascii Content-transfer-encoding: quoted-printable Importance: Normal X-Priority: 3 (Normal) X-MSMail-priority: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.326 tagged_above=0 required=5 tests=AWL X-Spam-Level: * X-Archive-Number: 200504/111 X-Sequence-Number: 21004 Believe me I just spent two months converting our app, I do not wish to = 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 = proc box with 8 gigs of memory 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 tblassoc where clientnum =3D 'WAZ' using indexed joins on location and jobtitle it is still taking 22 seconds to run compared to = the 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). 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 postgres 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 the 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 stops ok this way, but maybe it should be different. I can tell you I am 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 bullet. 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 = very successful. 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 = it works great. But if it too slow I just will not be able to use for production. Joel 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.=20 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. =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. From tgl@sss.pgh.pa.us Fri Apr 8 19:28:09 2005 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 8951853C92 for ; Fri, 8 Apr 2005 20:28:09 +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 19720-05 for ; Fri, 8 Apr 2005 19:28:01 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 29EC353758 for ; Fri, 8 Apr 2005 20:27:59 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j38JRsD2016324; Fri, 8 Apr 2005 15:27:54 -0400 (EDT) To: Bob Henkel Cc: Scott Marlowe , Joel Fradkin , Andrew Sullivan , pgsql-sql@postgresql.org Subject: Re: getting count for a specific querry In-reply-to: <762e5c050408115451e67c70@mail.gmail.com> References: <000001c53c5d$9f07f0d0$797ba8c0@jfradkin> <1112983840.20921.77.camel@state.g2switchworks.com> <762e5c050408115451e67c70@mail.gmail.com> Comments: In-reply-to Bob Henkel message dated "Fri, 08 Apr 2005 13:54:35 -0500" Date: Fri, 08 Apr 2005 15:27:53 -0400 Message-ID: <16323.1112988473@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/112 X-Sequence-Number: 21005 Bob Henkel writes: > From a simple/high level perspective why is this? That is why can't > PostgreSQL do aggregates as well across large chunks of data. I'm > assuming it extremely complicated. Otherwise the folks around here > would have churned out a fix in a month or less and made this issue a > past story. You can find very detailed discussions of this in the archives, but the basic reason is that we have a very general/extensible view of aggregates (which is how come we can support custom aggregates). An aggregate is a function that you feed all the input rows to, one at a time, and then it produces the answer. Nice, general, extensible, and not at all optimizable :-( Now in general that is the only way to do it, and so Scott's implication that we always suck compared to other databases is really an overstatement. Ask another database to do a standard deviation calculation, for instance, and it'll be just as slow. However there are special cases that other DBs can optimize that we don't even try to. The big ones are: * COUNT(*) across a whole table --- most non-MVCC databases keep tabs of the physical number of the rows in the table, and so they can answer this very quickly. Postgres doesn't keep such a count, and under MVCC rules it wouldn't necessarily be the right answer if we had it. (BTW, count of rows satisfying a particular condition is a different ballgame entirely; in most cases that can't be optimized at all, AFAIK.) If you are willing to accept approximate answers there are various tricks you can use --- see the archives --- but we don't get to fudge on COUNT(*) itself because it's in the SQL standard. * MIN or MAX of an indexed column --- most DBs can use an index scan to find such a row relatively quickly, although whether this trick works or not depends a whole lot on whether you have WHERE or GROUP BY and just what those conditions look like. You can fake the min/max answer in Postgres by doing the transformstion to an indexable query by hand, for instance instead of MAX(col) do SELECT col FROM tab ORDER BY col DESC LIMIT 1; There are periodic discussions in the hackers list about teaching the planner to do that automatically, and it will probably happen someday; but it's a complicated task and not exceedingly high on the priority list. regards, tom lane From ajs@crankycanuck.ca Fri Apr 8 19:33:39 2005 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 72A695363E for ; Fri, 8 Apr 2005 20:33:39 +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 20395-08 for ; Fri, 8 Apr 2005 19:33:34 +0000 (GMT) Received: from smtp102.rog.mail.re2.yahoo.com (smtp102.rog.mail.re2.yahoo.com [206.190.36.80]) by svr1.postgresql.org (Postfix) with SMTP id A751C535CA for ; Fri, 8 Apr 2005 20:33:32 +0100 (BST) Received: from unknown (HELO phlogiston.dydns.org) (a.sullivan@rogers.com@216.75.167.195 with login) by smtp102.rog.mail.re2.yahoo.com with SMTP; 8 Apr 2005 19:33:34 -0000 Received: by phlogiston.dydns.org (Postfix, from userid 1000) id 122843F9C; Fri, 8 Apr 2005 15:33:33 -0400 (EDT) Date: Fri, 8 Apr 2005 15:33:33 -0400 From: Andrew Sullivan To: pgsql-sql@postgresql.org Subject: Re: getting count for a specific querry Message-ID: <20050408193333.GA28225@phlogiston.dyndns.org> References: <1112983840.20921.77.camel@state.g2switchworks.com> <003b01c53c70$6ff184b0$797ba8c0@jfradkin> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <003b01c53c70$6ff184b0$797ba8c0@jfradkin> User-Agent: Mutt/1.5.6+20040907i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200504/113 X-Sequence-Number: 21006 On Fri, Apr 08, 2005 at 03:23:25PM -0400, Joel Fradkin wrote: > Believe me I just spent two months converting our app, I do not wish to give > our queries run much slower on postgres. As mentioned I purchased a 4 proc I suspect you want the -performance list. And it'd be real handy to get some EXPLAIN ANALYSE results for the offending queries in order to help you (where "handy" is read as "necessary"). A -- Andrew Sullivan | ajs@crankycanuck.ca Information security isn't a technological problem. It's an economics problem. --Bruce Schneier From jfradkin@wazagua.com Fri Apr 8 19:37:22 2005 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 43FBF53CCA for ; Fri, 8 Apr 2005 20:37:22 +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 22724-03 for ; Fri, 8 Apr 2005 19:37:14 +0000 (GMT) Received: from vms046pub.verizon.net (vms046pub.verizon.net [206.46.252.46]) by svr1.postgresql.org (Postfix) with ESMTP id 14CF953C92 for ; Fri, 8 Apr 2005 20:37:13 +0100 (BST) Received: from jfradkin ([66.15.127.235]) by vms046.mailsrvcs.net (Sun Java System Messaging Server 6.2 HotFix 0.04 (built Dec 24 2004)) with ESMTPA id <0IEN009GO7U1BYR0@vms046.mailsrvcs.net> for pgsql-sql@postgresql.org; Fri, 08 Apr 2005 14:37:14 -0500 (CDT) Date: Fri, 08 Apr 2005 15:37:14 -0400 From: "Joel Fradkin" Subject: Re: getting count for a specific querry In-reply-to: <16323.1112988473@sss.pgh.pa.us> To: "'Tom Lane'" , "'Bob Henkel'" Cc: "'Scott Marlowe'" , "'Andrew Sullivan'" , Message-id: <004b01c53c72$5e4c6390$797ba8c0@jfradkin> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Mailer: Microsoft Outlook, Build 10.0.6626 Content-type: text/plain; charset=us-ascii Content-transfer-encoding: quoted-printable Importance: Normal X-Priority: 3 (Normal) X-MSMail-priority: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.313 tagged_above=0 required=5 tests=AWL X-Spam-Level: * X-Archive-Number: 200504/114 X-Sequence-Number: 21007 As always thanks Tom, I will definitely look at what I can do. Since it is a count of matched condition records I may not have a way around. I don't think my clients would like me to aprox as it is a count of = their records. What I plan on doing assuming I can get all my other problems = fixed (as mentioned I am going to try and get paid help to see if I goofed it = up some where) is make the count a button, so they don't wait everytime, = but can choose to wait if need be, maybe I can store the last count with a = count on day for the generic search it defaults to, and just have them do a = count on demand if they have a specific query. Our screens have several = criteria fields in each application. Joel Fradkin =20 -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20 Sent: Friday, April 08, 2005 2:28 PM To: Bob Henkel Cc: Scott Marlowe; Joel Fradkin; Andrew Sullivan; = pgsql-sql@postgresql.org Subject: Re: [SQL] getting count for a specific querry=20 Bob Henkel writes: > From a simple/high level perspective why is this? That is why can't > PostgreSQL do aggregates as well across large chunks of data. I'm > assuming it extremely complicated. Otherwise the folks around here > would have churned out a fix in a month or less and made this issue a > past story. You can find very detailed discussions of this in the archives, but the basic reason is that we have a very general/extensible view of aggregates (which is how come we can support custom aggregates). An aggregate is a function that you feed all the input rows to, one at a time, and then it produces the answer. Nice, general, extensible, and not at all optimizable :-( Now in general that is the only way to do it, and so Scott's implication that we always suck compared to other databases is really an overstatement. Ask another database to do a standard deviation calculation, for instance, and it'll be just as slow. However there are special cases that other DBs can optimize that we don't even try to. The big ones are: * COUNT(*) across a whole table --- most non-MVCC databases keep tabs of the physical number of the rows in the table, and so they can answer this very quickly. Postgres doesn't keep such a count, and under MVCC rules it wouldn't necessarily be the right answer if we had it. (BTW, count of rows satisfying a particular condition is a different ballgame entirely; in most cases that can't be optimized at all, AFAIK.) If you are willing to accept approximate answers there are various tricks you can use --- see the archives --- but we don't get to fudge on COUNT(*) itself because it's in the SQL standard. * MIN or MAX of an indexed column --- most DBs can use an index scan to find such a row relatively quickly, although whether this trick works or not depends a whole lot on whether you have WHERE or GROUP BY and just what those conditions look like. You can fake the min/max answer in Postgres by doing the transformstion to an indexable query by hand, for instance instead of MAX(col) do SELECT col FROM tab ORDER BY col DESC LIMIT 1; There are periodic discussions in the hackers list about teaching the planner to do that automatically, and it will probably happen someday; but it's a complicated task and not exceedingly high on the priority = list. regards, tom lane From luckyratfoot@gmail.com Fri Apr 8 19:39:05 2005 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-- From mischa.sandberg@telus.net Fri Apr 8 19:39:55 2005 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 591D053CCE for ; Fri, 8 Apr 2005 20:39:55 +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 23018-05 for ; Fri, 8 Apr 2005 19:39:52 +0000 (GMT) Received: from priv-edtnes51.telusplanet.net (outbound04.telus.net [199.185.220.223]) by svr1.postgresql.org (Postfix) with ESMTP id F01A153BF4 for ; Fri, 8 Apr 2005 20:39:49 +0100 (BST) Received: from localhost ([199.185.220.240]) by priv-edtnes51.telusplanet.net (InterMail vM.6.01.04.00 201-2131-118-20041027) with ESMTP id <20050408193949.WNOI4275.priv-edtnes51.telusplanet.net@localhost>; Fri, 8 Apr 2005 13:39:49 -0600 Received: from 209.17.183.249 ( [209.17.183.249]) as user a3a18850@192.168.200.1 by webmail.telus.net with HTTP; Fri, 8 Apr 2005 12:39:49 -0700 Message-ID: <1112989189.4256de0583b33@webmail.telus.net> Date: Fri, 8 Apr 2005 12:39:49 -0700 From: Mischa Sandberg To: Scott Marlowe Cc: pgsql-sql@postgresql.org Subject: Re: getting count for a specific querry References: <000001c53c5d$9f07f0d0$797ba8c0@jfradkin> <1112983840.20921.77.camel@state.g2switchworks.com> In-Reply-To: <1112983840.20921.77.camel@state.g2switchworks.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit User-Agent: Internet Messaging Program (IMP) 3.1-cvs X-Originating-IP: 209.17.183.249 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.082 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_POST, FORGED_RCVD_HELO X-Spam-Level: * X-Archive-Number: 200504/116 X-Sequence-Number: 21009 Quoting Scott Marlowe : > On Fri, 2005-04-08 at 12:08, Joel Fradkin wrote: > > 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. > > 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? For the general discussion of slowness of count(*), and given no entry on the subject in http://www.postgresql.org/docs/faqs.FAQ.html ... I guess everyone has to be pointed at: http://archives.postgresql.org/pgsql-hackers/2005-01/msg00247.php However, the gist of this person's problem is that an adhoc query, NOT just a 'select count(*) from table', can take remarkably long. Again, the problem is that PG can't just scan an index. ---------------------- One workaround for this is to use EXPLAIN. THIS APPLIES TO CASES WHERE THE USER HAS WIDE LIBERTY IN QUERIES. It's pointless overhead, otherwise. default_statistics_target is cranked up to 200 on all such tables, and pg_autovacuum is running. (If there were anything to improve, it would be refining the thresholds on this). If the "(cost...rows=nnnn" string returns a number higher than the QUERY row limit, the user is derailed ("That's not specific enough to answer immediately; do you want an emailed report?"). Otherwise, it runs EXPLAIN ANALYZE, which is still faster than the query itself. If the "(actual...rows=nnnn...)" is higher than the RESULT row limit (PAGE limit). It then runs the query, with the PAGE rows offset and limit --- and happily, practically everything that query needs is now in shared_buffers. The count from the EXPLAIN analyze is displayed in the web page. -- "Dreams come true, not free." -- S.Sondheim, ITW From jfradkin@wazagua.com Fri Apr 8 19:47:44 2005 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 8E630535FD for ; Fri, 8 Apr 2005 20:47:44 +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 23957-10 for ; Fri, 8 Apr 2005 19:47:34 +0000 (GMT) Received: from vms042pub.verizon.net (vms042pub.verizon.net [206.46.252.42]) by svr1.postgresql.org (Postfix) with ESMTP id 4FEC95360B for ; Fri, 8 Apr 2005 20:47:33 +0100 (BST) Received: from jfradkin ([66.15.127.235]) by vms042.mailsrvcs.net (Sun Java System Messaging Server 6.2 HotFix 0.04 (built Dec 24 2004)) with ESMTPA id <0IEN00F9B8B93AZ0@vms042.mailsrvcs.net> for pgsql-sql@postgresql.org; Fri, 08 Apr 2005 14:47:35 -0500 (CDT) Date: Fri, 08 Apr 2005 15:47:35 -0400 From: "Joel Fradkin" Subject: Re: getting count for a specific querry In-reply-to: <762e5c05040812392480b796@mail.gmail.com> To: "'Bob Henkel'" Cc: "'Scott Marlowe'" , "'Andrew Sullivan'" , Message-id: <005301c53c73$d01bb6f0$797ba8c0@jfradkin> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Mailer: Microsoft Outlook, Build 10.0.6626 Content-type: multipart/mixed; boundary="----=_NextPart_000_0054_01C53C52.490A16F0" Importance: Normal X-Priority: 3 (Normal) X-MSMail-priority: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.3 tagged_above=0 required=5 tests=AWL, HTML_MESSAGE X-Spam-Level: * X-Archive-Number: 200504/117 X-Sequence-Number: 21010 This is a multi-part message in MIME format. ------=_NextPart_000_0054_01C53C52.490A16F0 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0055_01C53C52.490A16F0" ------=_NextPart_001_0055_01C53C52.490A16F0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit I have asked specific questions and paid attention to the various threads on configuration. I will take my config files and post on the performance thread that is a good suggestion (personnaly I have more faith in this forum then a paid consultant, but at this point I am willing to try both). Thanks again. The count thing I can get around using stored results and on demand counting, but some of my statistical reporting is just a must have. I enclosed one of my views, I realize to get help I should also include tables and indexes etc, and maybe I will do that. It is just there are so many of them. This one in particular did not run at all when I first got my data loaded. I ended up adding a few indexes and not sure what else and got it to run faster on postgres. Now it is running horrid, so I am back to the drawing board I change one thing and something else breaks. I am just frustrated, maybe Monday I will have better strength to figure it all out. Joel Fradkin ------=_NextPart_001_0055_01C53C52.490A16F0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

I have asked specific questions and = paid attention to the various threads on configuration.

I will take my config files and = post on the performance thread that is a good suggestion (personnaly I have more = faith in this forum then a paid consultant, but at this point I am willing to = try both).

 

Thanks again.

 

The count thing I can get around = using stored results and on demand counting, but some of my statistical = reporting is just a must have.

I enclosed one of my views, I = realize to get help I should also include tables and indexes etc, and maybe I will = do that.

It is just there are so many of = them. This one in particular did not run at all when I first got my data = loaded.

I ended up adding a few indexes and = not sure what else and got it to run faster on postgres.

Now it is running horrid, so I am = back to the drawing board I change one thing and something else = breaks.

I am just frustrated, maybe Monday = I will have better strength to figure it all out.

 

Joel Fradkin

 

 

------=_NextPart_001_0055_01C53C52.490A16F0-- ------=_NextPart_000_0054_01C53C52.490A16F0 Content-Type: text/plain; name="viewcasecube.txt" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="viewcasecube.txt" CREATE OR REPLACE VIEW viwcasecube as /*customer 1*/ select c.clientnum,c.casenum,c.casereferencenum, coalesce ((select sum(quantity * amount)=20 as merchandiseamount from tblmerchandise m where m.caseid =3D c.caseid and m.clientnum=3Dc.clientnum), 0)=20 || coalesce(c.totaladmitted, 0) as casevalue,=20 coalesce(c.totaladmitted, 0) as admitted,=20 coalesce(c.totalrecovery, 0) as recovered,=20 coalesce(c.ageatcase, 0) as ageatcase, 1 as numberofcase, coalesce(ct.value,'na') as casetype,=20 s.value as status, c.opendate, c.closedate,=20 a.value as action, u2.completename as closebyuser,=20 cs.value as casesource, m.value as method, m2.value as method2,=20 c.reportingagentfirstinitial, c.reportingagentlastname,=20 case when c.monthsemployedatcase is null then 'na' else cast(c.monthsemployedatcase as varchar(3)) end as monthsemployedatcase, u1.completename as createdby, st.value as subjecttype, ot.value as offensetype,=20 /*cust*/ custpt.value as patrontype,=20 'na' as jobtitle, 0 as testscore, coalesce(cust.firstname,'na') as firstname, coalesce(cust.lastname,'na') as lastname, coalesce(cust.address,'na') as address, coalesce(cust.city,'na') as city, coalesce(cust.state,'na') as state, coalesce(cust.zip,'na') as zip, coalesce(crtt.value,'na') as restitutiontype, /* type of restitution tracking */ coalesce(tblsex.value,'na') as gender, coalesce(eth.value,'na') as ethnicity, custmbt.value as militarybranch, custmst.value as militarystatus, coalesce(secagentnum,'not recorded') as secagentnum, l.locationnum, l.name as store, coalesce(l.address,'na') as locationaddress, coalesce(l.city,'na') as locationcity, coalesce(l.state,'na') as locationstate, coalesce(l.zip,'na') as locationzip, d .districtnum,=20 d .districtname as district, r.regionnum,=20 r.regionname as region, dv.divisionnum,=20 dv.divisionname as division,=20 case when c.apprehdate is null then c.opendate else c.apprehdate end as apprehdate, to_char( coalesce(c.apprehdate,c.opendate),'yyyy') as year, to_char( coalesce(c.apprehdate, c.opendate),'q') = as quarter, to_char( coalesce(c.apprehdate, c.opendate),'MM') = as month, to_char( coalesce(c.apprehdate, c.opendate),'D') = as weekday, to_char( coalesce(c.apprehdate, c.opendate),'WW') = as week, to_char( coalesce(c.apprehdate, = c.opendate),'HH24:MI') as time, coalesce(c.sourcedocnum,'none') as sourcedocnum, case coalesce(c.sourcemodule,'n')=20 when 'n' then 'none' when 'i' then 'incident' when 'g' then 'general investigation' when 'e' then 'employee investigation' else 'none' end as sourcemodule, case coalesce(tblcase1.clientnum, 'no') || = coalesce(cdacase.clicasenumber, 'no') when 'nono' then 'no' else 'yes' end as civilcase, coalesce(lpr.lpregionnum,'na')as = lpregionnum,coalesce(lpr.managername,'na') as lpmanager from tblcase c left outer join tblaction a on c.actionid =3D a.id and c.clientnum =3D a.clientnum = and 1=3D a.presentationid left outer join=20 tblmethod m on c.methodid =3D m.id and c.clientnum =3D m.clientnum = and 1=3D m.presentationid left outer join tblmethod m2 on c.methodid2 =3D m2.id and c.clientnum =3D = m2.clientnum and 1=3D m2.presentationid left outer join tblcasesource cs on c.casesourceid =3D cs.id and c.clientnum =3D = cs.clientnum and 1=3D cs.presentationid inner join tbllocation l=20 left outer join tbllpregion lpr on l.lpregionid =3D lpr.lpregionid = and l.clientnum =3D lpr.clientnum and 1=3D lpr.presentationid=20 on c.clientnum =3D l.clientnum and=20 c.locationid =3D l.locationid=20 inner join tbldistrict d on=20 c.clientnum =3D d.clientnum and=20 l.districtid =3D d.districtid and l.regionid =3D d.regionid and l.divisionid =3D d.divisionid inner join tblregion r on=20 c.clientnum =3D r.clientnum and=20 l.regionid =3D r.regionid and l.divisionid =3D r.divisionid inner join tbldivision dv on=20 c.clientnum =3D dv.clientnum and=20 l.divisionid =3D dv.divisionid left outer join tblcaseresttracktype crtt on c.resttracktypeid =3D crtt.id and = c.clientnum =3D crtt.clientnum and 1=3D crtt.presentationid left outer = join tblstatus s on c.statusid =3D s.id and 1=3D s.presentationid left = outer join tblsubjecttype st on c.subjecttypeid =3D st.id and 1=3D = st.presentationid left outer join tblcasetype ct on c.casetypeid =3D ct.id and c.clientnum =3D = ct.clientnum and 1=3D ct.presentationid left outer join tbluser u1 on c.createdbyid =3D u1.userid and c.clientnum =3D = u1.clientnum and 1=3D u1.presentationid left outer join tbluser u2 on c.closedby =3D u2.userid and c.clientnum =3D = u2.clientnum and 1=3D u2.presentationid left outer join tbloffensetype ot on c.offensetypeid =3D ot.id and c.clientnum =3D = ot.clientnum and 1=3D ot.presentationid left outer join tblcustomer cust on c.subjectid =3D cust.customerid and c.clientnum =3D = cust.clientnum left outer join tblethnicity eth on cust.ethnicityid =3D = eth.id and cust.clientnum =3D eth.clientnum and 1=3D eth.presentationid left outer join tblsex on cust.sexid =3D tblsex.id and 1=3D = tblsex.presentationid=20 left outer join tblmilitarybranch custmbt on = cust.militarybranchid =3D custmbt.id and cust.clientnum =3D = custmbt.clientnum and 1=3D custmbt.presentationid left outer join tblmilitarystatus custmst on = cust.militarystatusid =3D custmst.id and cust.clientnum =3D = custmst.clientnum and 1=3D custmst.presentationid left outer join tblpatrontype custpt on cust.patrontypeid =3D = custpt.id and cust.clientnum =3D custpt.clientnum and 1=3D = custpt.presentationid left join tblexportmarkedrecords tblcase1 on c.clientnum =3D = tblcase1.clientnum and=20 c.caseid =3D tblcase1.fieldvalue and 'cda'=3D = tblcase1.exportentitynum left join tblcdacases cdacase on c.clientnum =3D cdacase.clientnum and c.casenum =3D = cdacase.clicasenumber=20 where c.isdeleted =3D false and c.subjecttypeid =3D 1/* and = c.clientnum =3D 'waz'*/ union /*assoc 6*/ select c.clientnum,c.casenum, c.casereferencenum,coalesce ((select sum(quantity * amount)=20 as merchandiseamount from tblmerchandise m where m.caseid =3D c.caseid and m.clientnum=3Dc.clientnum), 0)=20 || coalesce(c.totaladmitted, 0) as casevalue,=20 coalesce(c.totaladmitted, 0) as admitted,=20 coalesce(c.totalrecovery, 0) as recovered,=20 coalesce(c.ageatcase, 0) as ageatcase, 1 as numberofcase, coalesce(ct.value,'na') as casetype,=20 s.value as status, c.opendate, c.closedate,=20 a.value as action, u2.completename as closebyuser,=20 cs.value as casesource, m.value as method, m2.value as method2,=20 c.reportingagentfirstinitial, c.reportingagentlastname,=20 case when c.monthsemployedatcase is null then 'na' else cast(c.monthsemployedatcase as varchar(3))=20 end as monthsemployedatcase, u1.completename as createdby, st.value as subjecttype, ot.value as offensetype,=20 pt.value as patrontype, jt.value as jobtitle, coalesce(ac.testscore,0) as testscore, coalesce(ac.firstname,'na') as firstname, coalesce(ac.lastname,'na') as lastname, coalesce(ac.address,'na') as address, coalesce(ac.city,'na') as city, coalesce(ac.state,'na') as state, coalesce(ac.zip,'na') as zip, coalesce(crtt.value,'na') as restitutiontype, coalesce(tblsex.value,'na') as gender, coalesce(eth.value,'na') as ethnicity, mbt.value as militarybranch, mst.value as militarystatus, coalesce(secagentnum,'not recorded') as secagentnum, l.locationnum, l.name as store,=20 coalesce(l.address,'na') as locationaddress, coalesce(l.city,'na') as locationcity, coalesce(l.state,'na') as locationstate, coalesce(l.zip,'na') as locationzip, d .districtnum,=20 d .districtname as district, r.regionnum,=20 r.regionname as region, dv.divisionnum,=20 dv.divisionname as division,=20 case when c.apprehdate is null then c.opendate else c.apprehdate end as apprehdate, to_char( coalesce(c.apprehdate,c.opendate),'yyyy') as year, to_char( coalesce(c.apprehdate, c.opendate),'q') = as quarter, to_char( coalesce(c.apprehdate, c.opendate),'MM') = as month, to_char( coalesce(c.apprehdate, c.opendate),'D') = as weekday, to_char( coalesce(c.apprehdate, c.opendate),'WW') = as week, to_char( coalesce(c.apprehdate, = c.opendate),'HH24:MI') as time, coalesce(c.sourcedocnum,'none') as sourcedocnum, case coalesce(c.sourcemodule,'n')=20 when 'n' then 'none' when 'i' then 'incident' when 'g' then 'general investigation' when 'e' then 'employee investigation' else 'none' end as sourcemodule, case coalesce(tblcase1.clientnum, 'no') || = coalesce(cdacase.clicasenumber, 'no') when 'nono' then 'no' else 'yes' end as civilcase, coalesce(lpr.lpregionnum,'na')as = lpregionnum,coalesce(lpr.managername,'na') as lpmanager from tblcase c left outer join tblaction a on c.actionid =3D a.id and c.clientnum =3D a.clientnum = and 1=3D a.presentationid left outer join=20 tblmethod m on c.methodid =3D m.id and c.clientnum =3D m.clientnum = and 1=3D m.presentationid left outer join tblmethod m2 on c.methodid2 =3D m2.id and c.clientnum =3D = m2.clientnum and 1=3D m2.presentationid left outer join tblcasesource cs on c.casesourceid =3D cs.id and c.clientnum =3D = cs.clientnum and 1=3D cs.presentationid inner join tbllocation l=20 left outer join tbllpregion lpr on l.lpregionid =3D lpr.lpregionid = and l.clientnum =3D lpr.clientnum and 1=3D lpr.presentationid=20 on c.clientnum =3D l.clientnum and=20 c.locationid =3D l.locationid=20 inner join tbldistrict d on=20 c.clientnum =3D d.clientnum and=20 l.districtid =3D d.districtid and l.regionid =3D d.regionid and l.divisionid =3D d.divisionid inner join tblregion r on=20 c.clientnum =3D r.clientnum and=20 l.regionid =3D r.regionid and l.divisionid =3D r.divisionid inner join tbldivision dv on=20 c.clientnum =3D dv.clientnum and=20 l.divisionid =3D dv.divisionid left outer join tblcaseresttracktype crtt on c.resttracktypeid =3D crtt.id and = c.clientnum =3D crtt.clientnum and 1=3D crtt.presentationid left outer = join tblstatus s on c.statusid =3D s.id and 1=3D s.presentationid left = outer join tblsubjecttype st on c.subjecttypeid =3D st.id and 1=3D = st.presentationid left outer join tblcasetype ct on c.casetypeid =3D ct.id and c.clientnum =3D = ct.clientnum and 1=3D ct.presentationid left outer join tbluser u1 on c.createdbyid =3D u1.userid and c.clientnum =3D = u1.clientnum and 1=3D u1.presentationid left outer join tbluser u2 on c.closedby =3D u2.userid and c.clientnum =3D = u2.clientnum and 1=3D u2.presentationid left outer join tbloffensetype ot on c.offensetypeid =3D ot.id and c.clientnum =3D = ot.clientnum and 1=3D ot.presentationid left outer join tblassociate ac on c.subjectid =3D ac.associateid and c.clientnum =3D ac.clientnum left outer join tblethnicity eth on ac.ethnicityid =3D eth.id = and ac.clientnum =3D eth.clientnum and 1=3D eth.presentationid left outer join tblsex on ac.sexid =3D tblsex.id and 1=3D = tblsex.presentationid =20 left outer join tblmilitarybranch mbt on ac.militarybranchid = =3D mbt.id and ac.clientnum =3D mbt.clientnum and 1=3D = mbt.presentationid left outer join tblmilitarystatus mst on ac.militarystatusid = =3D mst.id and ac.clientnum =3D mst.clientnum and 1=3D = mst.presentationid left outer join tblpatrontype pt on ac.patrontypeid =3D pt.id = and ac.clientnum =3D pt.clientnum and 1=3D pt.presentationid left outer join tbljobtitle jt on ac.jobtitleid =3D jt.id = and ac.clientnum =3D jt.clientnum and 1=3D jt.presentationid left join tblexportmarkedrecords tblcase1 on c.clientnum =3D = tblcase1.clientnum and=20 c.caseid =3D tblcase1.fieldvalue and 'cda'=3D = tblcase1.exportentitynum left join tblcdacases cdacase on c.clientnum =3D cdacase.clientnum and c.casenum =3D = cdacase.clicasenumber=20 where c.isdeleted =3D false and c.subjecttypeid =3D 6/* and = c.clientnum =3D 'waz'*/ /*other 7*/ union select c.clientnum,c.casenum, c.casereferencenum,coalesce ((select sum(quantity * amount)=20 as merchandiseamount from tblmerchandise m where m.caseid =3D c.caseid and m.clientnum=3Dc.clientnum), 0)=20 || coalesce(c.totaladmitted, 0) as casevalue,=20 coalesce(c.totaladmitted, 0) as admitted,=20 coalesce(c.totalrecovery, 0) as recovered,=20 coalesce(c.ageatcase, 0) as ageatcase, 1 as numberofcase, coalesce(ct.value,'na') as casetype,=20 s.value as status, c.opendate, c.closedate,=20 a.value as action, u2.completename as closebyuser,=20 cs.value as casesource, m.value as method, m2.value as method2,=20 c.reportingagentfirstinitial, c.reportingagentlastname,=20 case when c.monthsemployedatcase is null then 'na' else cast(c.monthsemployedatcase as varchar(3)) end as monthsemployedatcase, u1.completename as createdby, st.value as subjecttype, ot.value as offensetype,=20 otherpt.value as patrontype, 'na' as jobtitle, 0 as testscore, coalesce(other.firstname,'na') as firstname, coalesce(other.lastname,'na') as lastname, coalesce(other.address,'na') as address, coalesce(other.city,'na') as city, coalesce(other.state,'na') as state, coalesce(other.zip,'na') as zip, coalesce(crtt.value,'na') as restitutiontype, coalesce(tblsex.value,'na') as gender, coalesce(eth.value,'na') as ethnicity, othermbt.value as militarybranch, othermst.value as militarystatus, coalesce(secagentnum,'not recorded') as secagentnum, l.locationnum, l.name as store, coalesce(l.address,'na') as locationaddress, coalesce(l.city,'na') as locationcity, coalesce(l.state,'na') as locationstate, coalesce(l.zip,'na') as locationzip, d .districtnum,=20 d .districtname as district, r.regionnum,=20 r.regionname as region, dv.divisionnum,=20 dv.divisionname as division,=20 case when c.apprehdate is null then c.opendate else c.apprehdate end as apprehdate, to_char( coalesce(c.apprehdate,c.opendate),'yyyy') as year, to_char( coalesce(c.apprehdate, c.opendate),'q') = as quarter, to_char( coalesce(c.apprehdate, c.opendate),'MM') = as month, to_char( coalesce(c.apprehdate, c.opendate),'D') = as weekday, to_char( coalesce(c.apprehdate, c.opendate),'WW') = as week, to_char( coalesce(c.apprehdate, = c.opendate),'HH24:MI') as time, coalesce(c.sourcedocnum,'none') as sourcedocnum, case coalesce(c.sourcemodule,'n')=20 when 'n' then 'none' when 'i' then 'incident' when 'g' then 'general investigation' when 'e' then 'employee investigation' else 'none' end as sourcemodule, case coalesce(tblcase1.clientnum, 'no') || = coalesce(cdacase.clicasenumber, 'no') when 'nono' then 'no' else 'yes' end as civilcase, coalesce(lpr.lpregionnum,'na')as = lpregionnum,coalesce(lpr.managername,'na') as lpmanager from tblcase c left outer join tblaction a on c.actionid =3D a.id and c.clientnum =3D a.clientnum = and 1=3D a.presentationid left outer join=20 tblmethod m on c.methodid =3D m.id and c.clientnum =3D m.clientnum = and 1=3D m.presentationid left outer join tblmethod m2 on c.methodid2 =3D m2.id and c.clientnum =3D = m2.clientnum and 1=3D m2.presentationid left outer join tblcasesource cs on c.casesourceid =3D cs.id and c.clientnum =3D = cs.clientnum and 1=3D cs.presentationid inner join tbllocation l=20 left outer join tbllpregion lpr on l.lpregionid =3D lpr.lpregionid = and l.clientnum =3D lpr.clientnum and 1=3D lpr.presentationid=20 on c.clientnum =3D l.clientnum and=20 c.locationid =3D l.locationid=20 inner join tbldistrict d on=20 c.clientnum =3D d.clientnum and=20 l.districtid =3D d.districtid and l.regionid =3D d.regionid and l.divisionid =3D d.divisionid inner join tblregion r on=20 c.clientnum =3D r.clientnum and=20 l.regionid =3D r.regionid and l.divisionid =3D r.divisionid inner join tbldivision dv on=20 c.clientnum =3D dv.clientnum and=20 l.divisionid =3D dv.divisionid /* left outer join tbllocation l left outer join tbldistrict d left outer join tblregion r left outer join tbldivision dv on r.divisionid =3D dv.divisionid and r.clientnum = =3D dv.clientnum on=20 d .regionid =3D r.regionid and d.clientnum =3D r.clientnum on = l.districtid =3D d .districtid and l.clientnum =3D d.clientnum on=20 c.locationid =3D l.locationid and c.clientnum =3D l.clientnum =20 */ left outer join tblcaseresttracktype crtt on c.resttracktypeid =3D crtt.id and = c.clientnum =3D crtt.clientnum and 1=3D crtt.presentationid left outer = join tblstatus s on c.statusid =3D s.id and 1=3D s.presentationid left = outer join tblsubjecttype st on c.subjecttypeid =3D st.id and 1=3D = st.presentationid left outer join tblcasetype ct on c.casetypeid =3D ct.id and c.clientnum =3D = ct.clientnum and 1=3D ct.presentationid left outer join tbluser u1 on c.createdbyid =3D u1.userid and c.clientnum =3D = u1.clientnum and 1=3D u1.presentationid left outer join tbluser u2 on c.closedby =3D u2.userid and c.clientnum =3D = u2.clientnum and 1=3D u2.presentationid left outer join tbloffensetype ot on c.offensetypeid =3D ot.id and c.clientnum =3D = ot.clientnum and 1=3D ot.presentationid left outer join tblotherperson other=20 on c.subjectid =3D other.otherpersonid and c.clientnum =3D = other.clientnum left outer join tblethnicity eth on other.ethnicityid =3D = eth.id and other.clientnum =3D eth.clientnum and 1=3D eth.presentationid left outer join tblsex on other.sexid =3D tblsex.id and 1=3D = tblsex.presentationid=20 left outer join tblmilitarybranch othermbt on = other.militarybranchid =3D othermbt.id and other.clientnum =3D = othermbt.clientnum and 1=3D othermbt.presentationid left outer join tblmilitarystatus othermst on = other.militarystatusid =3D othermst.id and other.clientnum =3D = othermst.clientnum and 1=3D othermst.presentationid left outer join tblpatrontype otherpt on other.patrontypeid = =3D otherpt.id and other.clientnum =3D otherpt.clientnum and 1=3D = otherpt.presentationid left join tblexportmarkedrecords tblcase1 on c.clientnum =3D = tblcase1.clientnum and=20 c.caseid =3D tblcase1.fieldvalue and 'cda'=3D = tblcase1.exportentitynum left join tblcdacases cdacase on c.clientnum =3D cdacase.clientnum and c.casenum =3D = cdacase.clicasenumber=20 where c.isdeleted =3D false and c.subjecttypeid =3D 7 /*and = c.clientnum =3D 'waz'*/ ; ------=_NextPart_000_0054_01C53C52.490A16F0-- From jfradkin@wazagua.com Fri Apr 8 19:51:01 2005 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 C968253C85 for ; Fri, 8 Apr 2005 20:51:01 +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 24599-09 for ; Fri, 8 Apr 2005 19:51:00 +0000 (GMT) Received: from vms042pub.verizon.net (vms042pub.verizon.net [206.46.252.42]) by svr1.postgresql.org (Postfix) with ESMTP id 072BB535F4 for ; Fri, 8 Apr 2005 20:50:59 +0100 (BST) Received: from jfradkin ([66.15.127.235]) by vms042.mailsrvcs.net (Sun Java System Messaging Server 6.2 HotFix 0.04 (built Dec 24 2004)) with ESMTPA id <0IEN00MP18H0ATM2@vms042.mailsrvcs.net> for pgsql-sql@postgresql.org; Fri, 08 Apr 2005 14:51:01 -0500 (CDT) Date: Fri, 08 Apr 2005 15:51:02 -0400 From: "Joel Fradkin" Subject: Re: getting count for a specific querry In-reply-to: <1112989189.4256de0583b33@webmail.telus.net> To: "'Mischa Sandberg'" , "'Scott Marlowe'" Cc: Message-id: <005901c53c74$4ba5d800$797ba8c0@jfradkin> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Mailer: Microsoft Outlook, Build 10.0.6626 Content-type: text/plain; charset=us-ascii Content-transfer-encoding: quoted-printable Importance: Normal X-Priority: 3 (Normal) X-MSMail-priority: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.293 tagged_above=0 required=5 tests=AWL X-Spam-Level: * X-Archive-Number: 200504/118 X-Sequence-Number: 21011 I will also look at doing it the way you describe, they do have wide liberty. Thanks so much for the ideas. Sorry I did not do a perusal of = the archives first (I normally try that, but think I am brain dead today). Joel Fradkin =20 Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 =20 jfradkin@wazagua.com www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized = review, use, disclosure or distribution is prohibited. If you are not the = intended recipient, please contact the sender by reply email and delete and = destroy all copies of the original message, including attachments. =20 =20 -----Original Message----- From: pgsql-sql-owner@postgresql.org = [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Mischa Sandberg Sent: Friday, April 08, 2005 2:40 PM To: Scott Marlowe Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] getting count for a specific querry Quoting Scott Marlowe : > On Fri, 2005-04-08 at 12:08, Joel Fradkin wrote: > > 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. >=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? For the general discussion of slowness of count(*), and given no entry on the subject in=20 http://www.postgresql.org/docs/faqs.FAQ.html ... I guess everyone has to be pointed at: http://archives.postgresql.org/pgsql-hackers/2005-01/msg00247.php However, the gist of this person's problem is that an adhoc query, NOT just a 'select count(*) from table', can take remarkably long. Again, the problem is that PG can't just scan an index. ---------------------- One workaround for this is to use EXPLAIN. THIS APPLIES TO CASES WHERE THE USER HAS WIDE LIBERTY IN QUERIES. It's pointless overhead, otherwise. default_statistics_target is cranked up to 200 on all such tables, and pg_autovacuum is running. (If there were anything to improve, it would be refining the thresholds on this). If the "(cost...rows=3Dnnnn" string returns a number higher than the=20 QUERY row limit, the user is derailed ("That's not specific enough to = answer immediately; do you want an emailed report?"). Otherwise, it runs EXPLAIN ANALYZE, which is still faster than the query itself. If the "(actual...rows=3Dnnnn...)" is higher than the RESULT row limit = (PAGE limit). It then runs the query, with the PAGE rows offset and limit --- and = happily, practically everything that query needs is now in shared_buffers. The count from the EXPLAIN analyze is displayed in the web page. --=20 "Dreams come true, not free." -- S.Sondheim, ITW ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org From vivek@khera.org Fri Apr 8 20:23:13 2005 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 24F6C53350 for ; Fri, 8 Apr 2005 21:23:13 +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 33086-03 for ; Fri, 8 Apr 2005 20:23:04 +0000 (GMT) Received: from yertle.kcilink.com (yertle.kcilink.com [65.205.34.180]) by svr1.postgresql.org (Postfix) with ESMTP id C89C852A82 for ; Fri, 8 Apr 2005 21:23:03 +0100 (BST) Received: from [192.168.7.103] (host-103.int.kcilink.com [192.168.7.103]) by yertle.kcilink.com (Postfix) with ESMTP id 366CDB850 for ; Fri, 8 Apr 2005 16:23:05 -0400 (EDT) Mime-Version: 1.0 (Apple Message framework v619.2) In-Reply-To: <003b01c53c70$6ff184b0$797ba8c0@jfradkin> References: <003b01c53c70$6ff184b0$797ba8c0@jfradkin> Content-Type: multipart/signed; micalg=sha1; boundary=Apple-Mail-8--606885194; protocol="application/pkcs7-signature" Message-Id: From: Vivek Khera Subject: Re: getting count for a specific querry Date: Fri, 8 Apr 2005 16:23:04 -0400 To: X-Mailer: Apple Mail (2.619.2) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.105 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/119 X-Sequence-Number: 21012 --Apple-Mail-8--606885194 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; format=flowed On Apr 8, 2005, at 3:23 PM, Joel Fradkin wrote: > I set up the 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 > stops ok this way, but maybe it should be different. > Your problem might just be the choice of using a Dell RAID controller. I have a 1 year old box connected to a 14 disk powervault (PowerEdge 2650) and it is dog slow compared to a dual opteron with 8 disks that is replacing it. It is all I/O for me, and the dell's just are not known for speedy I/O. Vivek Khera, Ph.D. +1-301-869-4449 x806 --Apple-Mail-8--606885194 Content-Transfer-Encoding: base64 Content-Type: application/pkcs7-signature; name=smime.p7s Content-Disposition: attachment; filename=smime.p7s MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGhzCCAz8w ggKooAMCAQICAQ0wDQYJKoZIhvcNAQEFBQAwgdExCzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0 ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEaMBgGA1UEChMRVGhhd3RlIENvbnN1bHRpbmcx KDAmBgNVBAsTH0NlcnRpZmljYXRpb24gU2VydmljZXMgRGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0 ZSBQZXJzb25hbCBGcmVlbWFpbCBDQTErMCkGCSqGSIb3DQEJARYccGVyc29uYWwtZnJlZW1haWxA dGhhd3RlLmNvbTAeFw0wMzA3MTcwMDAwMDBaFw0xMzA3MTYyMzU5NTlaMGIxCzAJBgNVBAYTAlpB MSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUg UGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBDQTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEA xKY8VXNV+065yplaHmjAdQRwnd/p/6Me7L3N9VvyGna9fww6YfK/Uc4B1OVQCjDXAmNaLIkVcI7d yfArhVqqP3FWy688Cwfn8R+RNiQqE88r1fOCdz0Dviv+uxg+B79AgAJk16emu59l0cUqVIUPSAR/ p7bRPGEEQB5kGXJgt/sCAwEAAaOBlDCBkTASBgNVHRMBAf8ECDAGAQH/AgEAMEMGA1UdHwQ8MDow OKA2oDSGMmh0dHA6Ly9jcmwudGhhd3RlLmNvbS9UaGF3dGVQZXJzb25hbEZyZWVtYWlsQ0EuY3Js MAsGA1UdDwQEAwIBBjApBgNVHREEIjAgpB4wHDEaMBgGA1UEAxMRUHJpdmF0ZUxhYmVsMi0xMzgw DQYJKoZIhvcNAQEFBQADgYEASIzRUIPqCy7MDaNmrGcPf6+svsIXoUOWlJ1/TCG4+DYfqi2fNi/A 9BxQIJNwPP2t4WFiw9k6GX6EsZkbAMUaC4J0niVQlGLH2ydxVyWN3amcOY6MIE9lX5Xa9/eH1sYI Tq726jTlEBpbNU1341YheILcIRk13iSx0x1G/11fZU8wggNAMIICqaADAgECAgMOah8wDQYJKoZI hvcNAQEEBQAwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkp IEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA1 MDQwNTIwMzEzMloXDTA2MDQwNTIwMzEzMlowgYoxHzAdBgNVBAMTFlRoYXd0ZSBGcmVlbWFpbCBN ZW1iZXIxHjAcBgkqhkiG9w0BCQEWD3ZpdmVrQGtoZXJhLm9yZzEgMB4GCSqGSIb3DQEJARYRa2hl cmFAa2NpbGluay5jb20xJTAjBgkqhkiG9w0BCQEWFnZpdmVrQG1haWxlcm1haWxlci5jb20wggEi MA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDdo7hywGcY0VvK6WqqXXV77MS/t/4X3WkCaCXo RSl2W58GP4P21hodPn7hlIxUoDOW7x9O+FbqTgE2Ejqr6yA00Mm90tGPFgjFjqPGAqg7xk6IDcv9 uTyMia/FKEHSIynM6zqokXY8JklvdbJOiByE/8VeyEXOANWiflo8o4+GHnhMKpA9982YTXUqeKU6 mMQVaLCBRjTDc7j2XkMC/UNcp2HMyDQdTqYVnhLxbvbLX8CNDBY/7OWFlB9evru46SpGWhe4lhv5 DSgE2RdCKvDytzxRDvP49L8V0TnFjAVeC1C1Pj0/KQsoL/AP4APplROiD4QaUhshQl28pXxJtfbl AgMBAAGjVzBVMEUGA1UdEQQ+MDyBD3ZpdmVrQGtoZXJhLm9yZ4ERa2hlcmFAa2NpbGluay5jb22B FnZpdmVrQG1haWxlcm1haWxlci5jb20wDAYDVR0TAQH/BAIwADANBgkqhkiG9w0BAQQFAAOBgQAr CWop3h28qPwofzLrkoT410J4d7Bqk6FLeVlKZfg/wXlS1MTqYMNcCm4x+JsJbjwsO0fb2elFIuGq 1razoSzPpgi89itydvUT0U0U/u+AkZA5rW4AptTpMZ70YW5u9wzkcvmifqZmcfbaaeGdZfruzUXZ 6qvdXDpNb3ZHeQw6PjGCAucwggLjAgEBMGkwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0 ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFp bCBJc3N1aW5nIENBAgMOah8wCQYFKw4DAhoFAKCCAVMwGAYJKoZIhvcNAQkDMQsGCSqGSIb3DQEH ATAcBgkqhkiG9w0BCQUxDxcNMDUwNDA4MjAyMzA0WjAjBgkqhkiG9w0BCQQxFgQUjbIbdlHxt38D 70xzQXBKbk6d8moweAYJKwYBBAGCNxAEMWswaTBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhh d3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVt YWlsIElzc3VpbmcgQ0ECAw5qHzB6BgsqhkiG9w0BCRACCzFroGkwYjELMAkGA1UEBhMCWkExJTAj BgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJz b25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAgMOah8wDQYJKoZIhvcNAQEBBQAEggEAQPFzIhBGebzy Du0uRL2I2lqKVPrJ6oZCngDVDr7kupms6ircMMLbHWhUfX6gxayMgcQhMgG6SjlmNWBdX+lvVllE wv4z/WcOxL/gL5X0QN0Nw4QfMladc6D0Nz33se+d7FwBpvqOvy4Dyh8zgV4KZXzKNfvpYap9AU83 fRzDe2T2cQ4TcyX52K+M0Z7FcRtaSxvvvikkFtWtJUaTC5/SDilin++MZImoQz5qo5454uGZAY/O XlmenF6jmIcuQDpXl2yp3JkzEYQvGn53uRbjCi+DMiovSJT27Xklgaj2/UkgcPiRAZvjMiXyD9E3 OyGqVURzVBjP7ntweQbm6ZzHSwAAAAAAAA== --Apple-Mail-8--606885194-- From smarlowe@g2switchworks.com Fri Apr 8 20:31:08 2005 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 293545359C for ; Fri, 8 Apr 2005 21:31:08 +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 34825-10 for ; Fri, 8 Apr 2005 20:31:02 +0000 (GMT) Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by svr1.postgresql.org (Postfix) with ESMTP id 7434253572 for ; Fri, 8 Apr 2005 21:31:01 +0100 (BST) Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP with MS-WebStorage 6.5.6944 Received: from state.g2switchworks.com by mail.g2switchworks.com; 08 Apr 2005 15:31:02 -0500 Subject: Re: getting count for a specific querry From: Scott Marlowe To: Vivek Khera Cc: pgsql-sql@postgresql.org In-Reply-To: References: <003b01c53c70$6ff184b0$797ba8c0@jfradkin> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1112992262.20921.81.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Fri, 08 Apr 2005 15:31:02 -0500 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/120 X-Sequence-Number: 21013 On Fri, 2005-04-08 at 15:23, Vivek Khera wrote: > On Apr 8, 2005, at 3:23 PM, Joel Fradkin wrote: > > > I set up the 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 > > stops ok this way, but maybe it should be different. > > > > Your problem might just be the choice of using a Dell RAID controller. > I have a 1 year old box connected to a 14 disk powervault (PowerEdge > 2650) and it is dog slow compared to a dual opteron with 8 disks that > is replacing it. It is all I/O for me, and the dell's just are not > known for speedy I/O. Note that there are several different RAID controllers you can get with a DELL. I had good luck with the PERC 4C (AMI MegaRAID based) at my last job. In a dual 2400Mz machine with 2 gigs ram, it handily outran a 4 way (about 1200 MHz CPUs) windows / MSSQL box with 4 gigs of ram at most tasks. Especially inserts / updates. The windows machine had the more generic PERC 3I type controller in it. From luckyratfoot@gmail.com Fri Apr 8 20:35:33 2005 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 5E938535B9 for ; Fri, 8 Apr 2005 21:35:33 +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 35636-03 for ; Fri, 8 Apr 2005 20:35:31 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.196]) by svr1.postgresql.org (Postfix) with ESMTP id EFE11535AF for ; Fri, 8 Apr 2005 21:35:30 +0100 (BST) Received: by wproxy.gmail.com with SMTP id 68so1060818wri for ; Fri, 08 Apr 2005 13:35:32 -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=o2PmRqgN2kOjfuC+yTj7mgldhZN2y416SzbU7PX2LSCNYm3zZBTNHtkZiFQi0yfJxw7mMaLdjMMshU1xMvHkRms3EmveCcSMrEo/5odY+MwpO6WXhyVtTNt8SUtk2cSipsWzSfh7eFvTregZy2BzJn839wNsaXeRGY43e+0s4uc= Received: by 10.54.71.17 with SMTP id t17mr303632wra; Fri, 08 Apr 2005 13:35:32 -0700 (PDT) Received: by 10.54.70.11 with HTTP; Fri, 8 Apr 2005 13:35:32 -0700 (PDT) Message-ID: <762e5c05040813354436bd27@mail.gmail.com> Date: Fri, 8 Apr 2005 15:35:32 -0500 From: Bob Henkel Reply-To: Bob Henkel To: Vivek Khera Subject: Re: getting count for a specific querry Cc: pgsql-sql@postgresql.org In-Reply-To: Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_26188_3285544.1112992532620" References: <003b01c53c70$6ff184b0$797ba8c0@jfradkin> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.383 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_20_30, HTML_MESSAGE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200504/121 X-Sequence-Number: 21014 ------=_Part_26188_3285544.1112992532620 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On Apr 8, 2005 3:23 PM, Vivek Khera wrote:=20 >=20 >=20 > On Apr 8, 2005, at 3:23 PM, Joel Fradkin wrote: >=20 > > I set up the 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 > > stops ok this way, but maybe it should be different. > > >=20 > Your problem might just be the choice of using a Dell RAID controller. > I have a 1 year old box connected to a 14 disk powervault (PowerEdge > 2650) and it is dog slow compared to a dual opteron with 8 disks that > is replacing it. It is all I/O for me, and the dell's just are not > known for speedy I/O. >=20 > Vivek Khera, Ph.D. > +1-301-869-4449 x806 >=20 >=20 > But that is relative I would think. Is the Dell RAID much faster than my= =20 desktop SATA drive with no RAID? I'm by any means as knowledgeable about I/= O=20 setup as many of you are but my 2 cents wonders if the Dell RAID is really= =20 that much slower than a competitively priced/speced alternative? Would=20 Joel's problems just fade away if he wasn't using a Dell RAID? ------=_Part_26188_3285544.1112992532620 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
On Apr 8, 2005 3:23 PM, Vivek Khera <vivek@kh= era.org> wrote:

On Apr 8, 2005, at 3:23 PM, = Joel Fradkin wrote:

> I set up the 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
> stops ok t= his way, but maybe it should be different.
>

Your problem migh= t just be the choice of using a Dell RAID controller.
I have a 1 year ol= d box connected to a 14 disk powervault (PowerEdge
2650) and it is dog s= low compared to a dual opteron with 8 disks that
is replacing it. &= nbsp;It is all I/O for me, and the dell's just are not
known for speedy = I/O.

Vivek Khera, Ph.D.
+1-301-869-4449 x806


But that is relative I would think.  Is the Dell RAID much faster= than my desktop SATA drive with no RAID? I'm by any means as knowledg= eable about I/O setup as many of you are but my 2 cents wonders if the= Dell RAID is really that much slower than a competitively priced/speced al= ternative?  Would Joel's problems just fade away if he wasn't using a = Dell RAID? 
 

 
------=_Part_26188_3285544.1112992532620-- From vivek@khera.org Fri Apr 8 20:37:02 2005 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 1288E535AF for ; Fri, 8 Apr 2005 21:37:02 +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 37098-01 for ; Fri, 8 Apr 2005 20:36:53 +0000 (GMT) Received: from yertle.kcilink.com (yertle.kcilink.com [65.205.34.180]) by svr1.postgresql.org (Postfix) with ESMTP id 774A152A82 for ; Fri, 8 Apr 2005 21:36:53 +0100 (BST) Received: from [192.168.7.103] (host-103.int.kcilink.com [192.168.7.103]) by yertle.kcilink.com (Postfix) with ESMTP id 4C30DB80D for ; Fri, 8 Apr 2005 16:36:55 -0400 (EDT) Mime-Version: 1.0 (Apple Message framework v619.2) In-Reply-To: <1112992262.20921.81.camel@state.g2switchworks.com> References: <003b01c53c70$6ff184b0$797ba8c0@jfradkin> <1112992262.20921.81.camel@state.g2switchworks.com> Content-Type: multipart/signed; micalg=sha1; boundary=Apple-Mail-9--606055089; protocol="application/pkcs7-signature" Message-Id: <7501ca328bf49eaf7990583fa6f598bf@khera.org> From: Vivek Khera Subject: Re: getting count for a specific querry Date: Fri, 8 Apr 2005 16:36:54 -0400 To: pgsql-sql@postgresql.org X-Mailer: Apple Mail (2.619.2) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.092 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/122 X-Sequence-Number: 21015 --Apple-Mail-9--606055089 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; format=flowed On Apr 8, 2005, at 4:31 PM, Scott Marlowe wrote: > Note that there are several different RAID controllers you can get with > a DELL. I had good luck with the PERC 4C (AMI MegaRAID based) at my > I've had bad luck regarding speed with *all* of them, AMI MegaRAID and Adaptec based ones, under high load. Under moderate to low load they're acceptable. Vivek Khera, Ph.D. +1-301-869-4449 x806 --Apple-Mail-9--606055089 Content-Transfer-Encoding: base64 Content-Type: application/pkcs7-signature; name=smime.p7s Content-Disposition: attachment; filename=smime.p7s MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGhzCCAz8w ggKooAMCAQICAQ0wDQYJKoZIhvcNAQEFBQAwgdExCzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0 ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEaMBgGA1UEChMRVGhhd3RlIENvbnN1bHRpbmcx KDAmBgNVBAsTH0NlcnRpZmljYXRpb24gU2VydmljZXMgRGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0 ZSBQZXJzb25hbCBGcmVlbWFpbCBDQTErMCkGCSqGSIb3DQEJARYccGVyc29uYWwtZnJlZW1haWxA dGhhd3RlLmNvbTAeFw0wMzA3MTcwMDAwMDBaFw0xMzA3MTYyMzU5NTlaMGIxCzAJBgNVBAYTAlpB MSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUg UGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBDQTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEA xKY8VXNV+065yplaHmjAdQRwnd/p/6Me7L3N9VvyGna9fww6YfK/Uc4B1OVQCjDXAmNaLIkVcI7d yfArhVqqP3FWy688Cwfn8R+RNiQqE88r1fOCdz0Dviv+uxg+B79AgAJk16emu59l0cUqVIUPSAR/ p7bRPGEEQB5kGXJgt/sCAwEAAaOBlDCBkTASBgNVHRMBAf8ECDAGAQH/AgEAMEMGA1UdHwQ8MDow OKA2oDSGMmh0dHA6Ly9jcmwudGhhd3RlLmNvbS9UaGF3dGVQZXJzb25hbEZyZWVtYWlsQ0EuY3Js MAsGA1UdDwQEAwIBBjApBgNVHREEIjAgpB4wHDEaMBgGA1UEAxMRUHJpdmF0ZUxhYmVsMi0xMzgw DQYJKoZIhvcNAQEFBQADgYEASIzRUIPqCy7MDaNmrGcPf6+svsIXoUOWlJ1/TCG4+DYfqi2fNi/A 9BxQIJNwPP2t4WFiw9k6GX6EsZkbAMUaC4J0niVQlGLH2ydxVyWN3amcOY6MIE9lX5Xa9/eH1sYI Tq726jTlEBpbNU1341YheILcIRk13iSx0x1G/11fZU8wggNAMIICqaADAgECAgMOah8wDQYJKoZI hvcNAQEEBQAwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkp IEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA1 MDQwNTIwMzEzMloXDTA2MDQwNTIwMzEzMlowgYoxHzAdBgNVBAMTFlRoYXd0ZSBGcmVlbWFpbCBN ZW1iZXIxHjAcBgkqhkiG9w0BCQEWD3ZpdmVrQGtoZXJhLm9yZzEgMB4GCSqGSIb3DQEJARYRa2hl cmFAa2NpbGluay5jb20xJTAjBgkqhkiG9w0BCQEWFnZpdmVrQG1haWxlcm1haWxlci5jb20wggEi MA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDdo7hywGcY0VvK6WqqXXV77MS/t/4X3WkCaCXo RSl2W58GP4P21hodPn7hlIxUoDOW7x9O+FbqTgE2Ejqr6yA00Mm90tGPFgjFjqPGAqg7xk6IDcv9 uTyMia/FKEHSIynM6zqokXY8JklvdbJOiByE/8VeyEXOANWiflo8o4+GHnhMKpA9982YTXUqeKU6 mMQVaLCBRjTDc7j2XkMC/UNcp2HMyDQdTqYVnhLxbvbLX8CNDBY/7OWFlB9evru46SpGWhe4lhv5 DSgE2RdCKvDytzxRDvP49L8V0TnFjAVeC1C1Pj0/KQsoL/AP4APplROiD4QaUhshQl28pXxJtfbl AgMBAAGjVzBVMEUGA1UdEQQ+MDyBD3ZpdmVrQGtoZXJhLm9yZ4ERa2hlcmFAa2NpbGluay5jb22B FnZpdmVrQG1haWxlcm1haWxlci5jb20wDAYDVR0TAQH/BAIwADANBgkqhkiG9w0BAQQFAAOBgQAr CWop3h28qPwofzLrkoT410J4d7Bqk6FLeVlKZfg/wXlS1MTqYMNcCm4x+JsJbjwsO0fb2elFIuGq 1razoSzPpgi89itydvUT0U0U/u+AkZA5rW4AptTpMZ70YW5u9wzkcvmifqZmcfbaaeGdZfruzUXZ 6qvdXDpNb3ZHeQw6PjGCAucwggLjAgEBMGkwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0 ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFp bCBJc3N1aW5nIENBAgMOah8wCQYFKw4DAhoFAKCCAVMwGAYJKoZIhvcNAQkDMQsGCSqGSIb3DQEH ATAcBgkqhkiG9w0BCQUxDxcNMDUwNDA4MjAzNjU1WjAjBgkqhkiG9w0BCQQxFgQUqjN06Wyq5Prj pmoIjgc8CdREsugweAYJKwYBBAGCNxAEMWswaTBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhh d3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVt YWlsIElzc3VpbmcgQ0ECAw5qHzB6BgsqhkiG9w0BCRACCzFroGkwYjELMAkGA1UEBhMCWkExJTAj BgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJz b25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAgMOah8wDQYJKoZIhvcNAQEBBQAEggEAoC+gq0nUqHjp sW6iOQYTL7n4MblmOFUQLiTn/OUzWAjbKPEh7raXq93ymePb6FP+XzBxXdaFgPlTb5Llosyrg03e gRO61jieGJXzd7soP09s/cNJrumHldvqOGYslt5z6TCPfh3SA/yKOeIo8vhzO3oAq2cWI5RQPSpS BgC+sQg4vHtDUoDVW8ZyT5KKL4mFENfE+07OmsHVR1HWIJwgPGT4MetAa119MMWagi+/nvqo0j98 VPkOTgBg4aKqfiHtgdtM6V/oJXUNGl457cBagE7+pfEJEwMXF/MMuiuQklG++1Sb9R4WR41xZyPX A76fHe6ej6z8R4Es4g6anyCxSwAAAAAAAA== --Apple-Mail-9--606055089-- From vivek@khera.org Fri Apr 8 20:41:27 2005 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 89E10535BA for ; Fri, 8 Apr 2005 21:41:27 +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 37407-09 for ; Fri, 8 Apr 2005 20:41:26 +0000 (GMT) Received: from yertle.kcilink.com (yertle.kcilink.com [65.205.34.180]) by svr1.postgresql.org (Postfix) with ESMTP id C35DD535BC for ; Fri, 8 Apr 2005 21:41:24 +0100 (BST) Received: from [192.168.7.103] (host-103.int.kcilink.com [192.168.7.103]) by yertle.kcilink.com (Postfix) with ESMTP id 3149BB850 for ; Fri, 8 Apr 2005 16:41:27 -0400 (EDT) Mime-Version: 1.0 (Apple Message framework v619.2) In-Reply-To: <762e5c05040813354436bd27@mail.gmail.com> References: <003b01c53c70$6ff184b0$797ba8c0@jfradkin> <762e5c05040813354436bd27@mail.gmail.com> Content-Type: multipart/signed; micalg=sha1; boundary=Apple-Mail-10--605782794; protocol="application/pkcs7-signature" Message-Id: <8ef5bfe4806dac39bab8e07837dbab38@khera.org> From: Vivek Khera Subject: Re: getting count for a specific querry Date: Fri, 8 Apr 2005 16:41:26 -0400 To: pgsql-sql@postgresql.org X-Mailer: Apple Mail (2.619.2) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.087 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/123 X-Sequence-Number: 21016 --Apple-Mail-10--605782794 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; format=flowed On Apr 8, 2005, at 4:35 PM, Bob Henkel wrote: > desktop SATA drive with no RAID? I'm by any means as knowledgeable > about I/O > setup as many of you are but my 2 cents wonders if the Dell RAID is > really > that much slower than a competitively priced/speced alternative? Would > Joel's problems just fade away if he wasn't using a Dell RAID? > "Dell RAID" is not one thing. They sell "altered" RAID cards from Adaptec and LSI. Whatever alteration they do to them tends to make them run not so fast. I have a Dell SATA RAID (adaptec based) on the office server and it is OK, though not something I'd buy again. I have various PERC 3 and PERC 4 RAID controllers on my servers (SCSI based) and they suck under heavy I/O load. I wonder why the name-brand LSI cards work so much faster... perhaps it is the motherboard? I don't know, and I don't care... :-) For me, high performance DB and Dell servers are mutually exclusive. Vivek Khera, Ph.D. +1-301-869-4449 x806 --Apple-Mail-10--605782794 Content-Transfer-Encoding: base64 Content-Type: application/pkcs7-signature; name=smime.p7s Content-Disposition: attachment; filename=smime.p7s MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGhzCCAz8w ggKooAMCAQICAQ0wDQYJKoZIhvcNAQEFBQAwgdExCzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0 ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEaMBgGA1UEChMRVGhhd3RlIENvbnN1bHRpbmcx KDAmBgNVBAsTH0NlcnRpZmljYXRpb24gU2VydmljZXMgRGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0 ZSBQZXJzb25hbCBGcmVlbWFpbCBDQTErMCkGCSqGSIb3DQEJARYccGVyc29uYWwtZnJlZW1haWxA dGhhd3RlLmNvbTAeFw0wMzA3MTcwMDAwMDBaFw0xMzA3MTYyMzU5NTlaMGIxCzAJBgNVBAYTAlpB MSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUg UGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBDQTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEA xKY8VXNV+065yplaHmjAdQRwnd/p/6Me7L3N9VvyGna9fww6YfK/Uc4B1OVQCjDXAmNaLIkVcI7d yfArhVqqP3FWy688Cwfn8R+RNiQqE88r1fOCdz0Dviv+uxg+B79AgAJk16emu59l0cUqVIUPSAR/ p7bRPGEEQB5kGXJgt/sCAwEAAaOBlDCBkTASBgNVHRMBAf8ECDAGAQH/AgEAMEMGA1UdHwQ8MDow OKA2oDSGMmh0dHA6Ly9jcmwudGhhd3RlLmNvbS9UaGF3dGVQZXJzb25hbEZyZWVtYWlsQ0EuY3Js MAsGA1UdDwQEAwIBBjApBgNVHREEIjAgpB4wHDEaMBgGA1UEAxMRUHJpdmF0ZUxhYmVsMi0xMzgw DQYJKoZIhvcNAQEFBQADgYEASIzRUIPqCy7MDaNmrGcPf6+svsIXoUOWlJ1/TCG4+DYfqi2fNi/A 9BxQIJNwPP2t4WFiw9k6GX6EsZkbAMUaC4J0niVQlGLH2ydxVyWN3amcOY6MIE9lX5Xa9/eH1sYI Tq726jTlEBpbNU1341YheILcIRk13iSx0x1G/11fZU8wggNAMIICqaADAgECAgMOah8wDQYJKoZI hvcNAQEEBQAwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkp IEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA1 MDQwNTIwMzEzMloXDTA2MDQwNTIwMzEzMlowgYoxHzAdBgNVBAMTFlRoYXd0ZSBGcmVlbWFpbCBN ZW1iZXIxHjAcBgkqhkiG9w0BCQEWD3ZpdmVrQGtoZXJhLm9yZzEgMB4GCSqGSIb3DQEJARYRa2hl cmFAa2NpbGluay5jb20xJTAjBgkqhkiG9w0BCQEWFnZpdmVrQG1haWxlcm1haWxlci5jb20wggEi MA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDdo7hywGcY0VvK6WqqXXV77MS/t/4X3WkCaCXo RSl2W58GP4P21hodPn7hlIxUoDOW7x9O+FbqTgE2Ejqr6yA00Mm90tGPFgjFjqPGAqg7xk6IDcv9 uTyMia/FKEHSIynM6zqokXY8JklvdbJOiByE/8VeyEXOANWiflo8o4+GHnhMKpA9982YTXUqeKU6 mMQVaLCBRjTDc7j2XkMC/UNcp2HMyDQdTqYVnhLxbvbLX8CNDBY/7OWFlB9evru46SpGWhe4lhv5 DSgE2RdCKvDytzxRDvP49L8V0TnFjAVeC1C1Pj0/KQsoL/AP4APplROiD4QaUhshQl28pXxJtfbl AgMBAAGjVzBVMEUGA1UdEQQ+MDyBD3ZpdmVrQGtoZXJhLm9yZ4ERa2hlcmFAa2NpbGluay5jb22B FnZpdmVrQG1haWxlcm1haWxlci5jb20wDAYDVR0TAQH/BAIwADANBgkqhkiG9w0BAQQFAAOBgQAr CWop3h28qPwofzLrkoT410J4d7Bqk6FLeVlKZfg/wXlS1MTqYMNcCm4x+JsJbjwsO0fb2elFIuGq 1razoSzPpgi89itydvUT0U0U/u+AkZA5rW4AptTpMZ70YW5u9wzkcvmifqZmcfbaaeGdZfruzUXZ 6qvdXDpNb3ZHeQw6PjGCAucwggLjAgEBMGkwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0 ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFp bCBJc3N1aW5nIENBAgMOah8wCQYFKw4DAhoFAKCCAVMwGAYJKoZIhvcNAQkDMQsGCSqGSIb3DQEH ATAcBgkqhkiG9w0BCQUxDxcNMDUwNDA4MjA0MTI3WjAjBgkqhkiG9w0BCQQxFgQUGlRVit1NmUoH 5qPqVbe9xVHf5JsweAYJKwYBBAGCNxAEMWswaTBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhh d3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVt YWlsIElzc3VpbmcgQ0ECAw5qHzB6BgsqhkiG9w0BCRACCzFroGkwYjELMAkGA1UEBhMCWkExJTAj BgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJz b25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAgMOah8wDQYJKoZIhvcNAQEBBQAEggEABv75PJR72vb5 Eg0dWJv930jBAD0UKEzWWUZxO27obRyEYl8ZKNC+TguGWiulR0j37FtP5visN1imFmi9Bct8bLSg SYymN/CcfS4WBC8tMzj3jo0nmTtV83bQ/iEchjRzSlfHf2O90/Rwau93DfoAD2KITRY94ZMREeWA /siKvKRSW18Lgp9qEKd6/tAN7MHBunG77/Vf01LS1RWRUlHIORINueBfIQfRVrypCgNxXbMoyAzk f5oKY93N7sY6DgfgZNkr2WAo1b3sXH+ZO6F4nvGZb5CXF03ZuXY/Srx2NloDwjyKiq3W/Hic4HoV pCbVMr6W3/llAMHNvbV/ku5wHAAAAAAAAA== --Apple-Mail-10--605782794-- From smarlowe@g2switchworks.com Fri Apr 8 20:42:03 2005 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 D2CF4533A8 for ; Fri, 8 Apr 2005 21:42:03 +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 37706-06 for ; Fri, 8 Apr 2005 20:42:00 +0000 (GMT) Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by svr1.postgresql.org (Postfix) with ESMTP id 414755321A for ; Fri, 8 Apr 2005 21:41:59 +0100 (BST) Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP with MS-WebStorage 6.5.6944 Received: from state.g2switchworks.com by mail.g2switchworks.com; 08 Apr 2005 15:42:01 -0500 Subject: Re: getting count for a specific querry From: Scott Marlowe To: Bob Henkel Cc: Vivek Khera , pgsql-sql@postgresql.org In-Reply-To: <762e5c05040813354436bd27@mail.gmail.com> References: <003b01c53c70$6ff184b0$797ba8c0@jfradkin> <762e5c05040813354436bd27@mail.gmail.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1112992921.20921.84.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Fri, 08 Apr 2005 15:42:01 -0500 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/124 X-Sequence-Number: 21017 On Fri, 2005-04-08 at 15:35, Bob Henkel wrote: > On Apr 8, 2005 3:23 PM, Vivek Khera wrote: > On Apr 8, 2005, at 3:23 PM, Joel Fradkin wrote: > > > I set up the 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 > > stops ok this way, but maybe it should be different. > > > > Your problem might just be the choice of using a Dell RAID > controller. > I have a 1 year old box connected to a 14 disk powervault > (PowerEdge > 2650) and it is dog slow compared to a dual opteron with 8 > disks that > is replacing it. It is all I/O for me, and the dell's just > are not > known for speedy I/O. > > Vivek Khera, Ph.D. > +1-301-869-4449 x806 > > > > But that is relative I would think. Is the Dell RAID much faster than > my desktop SATA drive with no RAID? I'm by any means as knowledgeable > about I/O setup as many of you are but my 2 cents wonders if the Dell > RAID is really that much slower than a competitively priced/speced > alternative? Would Joel's problems just fade away if he wasn't using > a Dell RAID? My experience with the 3i controllers (See my earlier post) was that my old Pentium Pro200x2 machine with 512 meg ram and a generic Ultra Wide SCSI card and a half dozen drives running software RAID 5 was faster. Seriously. So was my P-II-350 desktop with the same controller, and an older Dual P-III-750 with only UltraSCSI running in a RAID-1 mirror set. The 3I is REALLY slow (or at least WAS slow) under linux. From smarlowe@g2switchworks.com Fri Apr 8 20:43:02 2005 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 F0E865321A for ; Fri, 8 Apr 2005 21:43:02 +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 37296-10 for ; Fri, 8 Apr 2005 20:42:57 +0000 (GMT) Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by svr1.postgresql.org (Postfix) with ESMTP id A518D53622 for ; Fri, 8 Apr 2005 21:42:55 +0100 (BST) Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP with MS-WebStorage 6.5.6944 Received: from state.g2switchworks.com by mail.g2switchworks.com; 08 Apr 2005 15:42:57 -0500 Subject: Re: getting count for a specific querry From: Scott Marlowe To: Vivek Khera Cc: pgsql-sql@postgresql.org In-Reply-To: <7501ca328bf49eaf7990583fa6f598bf@khera.org> References: <003b01c53c70$6ff184b0$797ba8c0@jfradkin> <1112992262.20921.81.camel@state.g2switchworks.com> <7501ca328bf49eaf7990583fa6f598bf@khera.org> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1112992977.20921.86.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Fri, 08 Apr 2005 15:42:57 -0500 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/125 X-Sequence-Number: 21018 On Fri, 2005-04-08 at 15:36, Vivek Khera wrote: > On Apr 8, 2005, at 4:31 PM, Scott Marlowe wrote: > > > Note that there are several different RAID controllers you can get with > > a DELL. I had good luck with the PERC 4C (AMI MegaRAID based) at my > > > > I've had bad luck regarding speed with *all* of them, AMI MegaRAID and > Adaptec based ones, under high load. Under moderate to low load > they're acceptable. Were you using battery backed cache with write-back enabled on the 4C? The 3C is also megaraid based, but it a pretty old design and not very fast. From luckyratfoot@gmail.com Fri Apr 8 20:46:31 2005 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 9C280535BB for ; Fri, 8 Apr 2005 21:46:31 +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 39154-09 for ; Fri, 8 Apr 2005 20:46:30 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.202]) by svr1.postgresql.org (Postfix) with ESMTP id 1AA6C53396 for ; Fri, 8 Apr 2005 21:46:29 +0100 (BST) Received: by wproxy.gmail.com with SMTP id 68so1063219wri for ; Fri, 08 Apr 2005 13:46:32 -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=XB3XPSf1gftTjqygTCciM/uas3AgB0oi0ir3vwwNp16cMNjxdiEnkUJIF25o+4CWTNYnd9LMvzAoF1TDgjrMKWR63bCZDg1Jsybwt+A47lfO5SVEjuwNmn1ow/z4lMDgF6mv0OQKbAk+AgMQE0fAaMXWVx41zt/RQ4R4xG5IDOE= Received: by 10.54.15.51 with SMTP id 51mr27433wro; Fri, 08 Apr 2005 13:46:32 -0700 (PDT) Received: by 10.54.70.11 with HTTP; Fri, 8 Apr 2005 13:46:32 -0700 (PDT) Message-ID: <762e5c050408134658d594fa@mail.gmail.com> Date: Fri, 8 Apr 2005 15:46:32 -0500 From: Bob Henkel Reply-To: Bob Henkel To: Scott Marlowe Subject: Re: getting count for a specific querry Cc: Vivek Khera , pgsql-sql@postgresql.org In-Reply-To: <1112992921.20921.84.camel@state.g2switchworks.com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_26214_9692910.1112993192079" References: <003b01c53c70$6ff184b0$797ba8c0@jfradkin> <762e5c05040813354436bd27@mail.gmail.com> <1112992921.20921.84.camel@state.g2switchworks.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.216 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_40_50, HTML_MESSAGE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200504/126 X-Sequence-Number: 21019 ------=_Part_26214_9692910.1112993192079 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On Apr 8, 2005 3:42 PM, Scott Marlowe wrote:= =20 >=20 > On Fri, 2005-04-08 at 15:35, Bob Henkel wrote: > > On Apr 8, 2005 3:23 PM, Vivek Khera wrote: > > On Apr 8, 2005, at 3:23 PM, Joel Fradkin wrote: > > > > > I set up the 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 > > > stops ok this way, but maybe it should be different. > > > > > > > Your problem might just be the choice of using a Dell RAID > > controller. > > I have a 1 year old box connected to a 14 disk powervault > > (PowerEdge > > 2650) and it is dog slow compared to a dual opteron with 8 > > disks that > > is replacing it. It is all I/O for me, and the dell's just > > are not > > known for speedy I/O. > > > > Vivek Khera, Ph.D. > > +1-301-869-4449 x806 > > > > > > > > But that is relative I would think. Is the Dell RAID much faster than > > my desktop SATA drive with no RAID? I'm by any means as knowledgeable > > about I/O setup as many of you are but my 2 cents wonders if the Dell > > RAID is really that much slower than a competitively priced/speced > > alternative? Would Joel's problems just fade away if he wasn't using > > a Dell RAID? >=20 > My experience with the 3i controllers (See my earlier post) was that my > old Pentium Pro200x2 machine with 512 meg ram and a generic Ultra Wide > SCSI card and a half dozen drives running software RAID 5 was faster. >=20 > Seriously. So was my P-II-350 desktop with the same controller, and an > older Dual P-III-750 with only UltraSCSI running in a RAID-1 mirror set. >=20 > The 3I is REALLY slow (or at least WAS slow) under linux. >=20 Interesting... Maybe Joel after a weekend of rest can try it on a different= =20 setup even if that different setup is just a power users development machin= e=20 to see if he has same or worse timing results. Be wonderful if it magically= =20 sped up. ------=_Part_26214_9692910.1112993192079 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
On Apr 8, 2005 3:42 PM, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
On Fri, 2005-04-08 at 15:35, Bob= Henkel wrote:
> On Apr 8, 2005 3:23 PM, Vivek Khera <vivek@khera.org> wrote:
>  &n= bsp;      On Apr 8, 2005, at 3:23 PM, Joel Fradkin= wrote:
>
>         >= ; I set up the data on 4 10k scsi drives in a powervault and
> &= nbsp;       my wal on 2
>  &n= bsp;      > 15k
>    =      > drives. I am using links to those from the in= stall
>         directory. It=
>         > starts and>         > stops ok this wa= y, but maybe it should be different.
>     &= nbsp;   >
>
>      &= nbsp;  Your problem might just be the choice of using a Dell RAID
&= gt;         controller.
>&nbs= p;        I have a 1 year old box connec= ted to a 14 disk powervault
>      &nbs= p;  (PowerEdge
>        = 2650) and it is dog slow compared to a dual opteron with 8
> &n= bsp;       disks that
>  &nbs= p;      is replacing it.  It is all I/O = for me, and the dell's just
>      &nbs= p;  are not
>         kn= own for speedy I/O.
>
>      &nbs= p;  Vivek Khera, Ph.D.
>      &nbs= p;  +1-301-869-4449 x806
>
>
>
> But that is r= elative I would think.  Is the Dell RAID much faster than
>= my desktop SATA drive with no RAID? I'm by any means as knowledgeable
&= gt; about I/O setup as many of you are but my 2 cents wonders if the Dell> RAID is really that much slower than a competitively priced/speced> alternative?  Would Joel's problems just fade away if he w= asn't using
> a Dell RAID?

My experience with the 3i controlle= rs (See my earlier post) was that my
old Pentium Pro200x2 machine with 5= 12 meg ram and a generic Ultra Wide
SCSI card and a half dozen drives ru= nning software RAID 5 was faster.

Seriously.  So was my P-= II-350 desktop with the same controller, and an
older Dual P-III-750 wit= h only UltraSCSI running in a RAID-1 mirror set.

The 3I is REALLY sl= ow (or at least WAS slow) under linux.

Interesting...  Maybe Joel after a weekend of rest can t= ry it on a different setup even if that different setup is just a power use= rs development machine to see if he has same or worse timing results. = Be wonderful if it magically sped  up.
 
------=_Part_26214_9692910.1112993192079-- From smarlowe@g2switchworks.com Fri Apr 8 20:50:54 2005 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 ECEE153568 for ; Fri, 8 Apr 2005 21:50:54 +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 40425-04 for ; Fri, 8 Apr 2005 20:50:53 +0000 (GMT) Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by svr1.postgresql.org (Postfix) with ESMTP id 908BB53567 for ; Fri, 8 Apr 2005 21:50:53 +0100 (BST) Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP with MS-WebStorage 6.5.6944 Received: from state.g2switchworks.com by mail.g2switchworks.com; 08 Apr 2005 15:50:54 -0500 Subject: Re: getting count for a specific querry From: Scott Marlowe To: Vivek Khera Cc: pgsql-sql@postgresql.org In-Reply-To: <8ef5bfe4806dac39bab8e07837dbab38@khera.org> References: <003b01c53c70$6ff184b0$797ba8c0@jfradkin> <762e5c05040813354436bd27@mail.gmail.com> <8ef5bfe4806dac39bab8e07837dbab38@khera.org> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1112993454.20921.89.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Fri, 08 Apr 2005 15:50:54 -0500 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/127 X-Sequence-Number: 21020 On Fri, 2005-04-08 at 15:41, Vivek Khera wrote: > On Apr 8, 2005, at 4:35 PM, Bob Henkel wrote: > > > desktop SATA drive with no RAID? I'm by any means as knowledgeable > > about I/O > > setup as many of you are but my 2 cents wonders if the Dell RAID is > > really > > that much slower than a competitively priced/speced alternative? Would > > Joel's problems just fade away if he wasn't using a Dell RAID? > > > > "Dell RAID" is not one thing. They sell "altered" RAID cards from > Adaptec and LSI. Whatever alteration they do to them tends to make > them run not so fast. > > I have a Dell SATA RAID (adaptec based) on the office server and it is > OK, though not something I'd buy again. > > I have various PERC 3 and PERC 4 RAID controllers on my servers (SCSI > based) and they suck under heavy I/O load. > > I wonder why the name-brand LSI cards work so much faster... perhaps it > is the motherboard? I don't know, and I don't care... :-) For me, > high performance DB and Dell servers are mutually exclusive. It would be nice to be able to put a stock ami megaraid in one and see. Do you run your 2650s with hyperthreading on? I found that slowed mine down under load, but we never had more than a couple dozen users hitting the db at once, so we may well have had a different load profile than what you're seeing. From jfradkin@wazagua.com Fri Apr 8 21:09:27 2005 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 8D93552A82 for ; Fri, 8 Apr 2005 22:09:27 +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 42774-10 for ; Fri, 8 Apr 2005 21:09:18 +0000 (GMT) Received: from vms040pub.verizon.net (vms040pub.verizon.net [206.46.252.40]) by svr1.postgresql.org (Postfix) with ESMTP id 7FA2752A56 for ; Fri, 8 Apr 2005 22:09:17 +0100 (BST) Received: from jfradkin ([66.15.127.235]) by vms040.mailsrvcs.net (Sun Java System Messaging Server 6.2 HotFix 0.04 (built Dec 24 2004)) with ESMTPA id <0IEN00HJWC3JYFME@vms040.mailsrvcs.net> for pgsql-sql@postgresql.org; Fri, 08 Apr 2005 16:09:20 -0500 (CDT) Date: Fri, 08 Apr 2005 17:09:21 -0400 From: "Joel Fradkin" Subject: Re: getting count for a specific querry In-reply-to: <1112993454.20921.89.camel@state.g2switchworks.com> To: "'Scott Marlowe'" , "'Vivek Khera'" Cc: Message-id: <008001c53c7f$3c35a930$797ba8c0@jfradkin> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Mailer: Microsoft Outlook, Build 10.0.6626 Content-type: text/plain; charset=us-ascii Content-transfer-encoding: 7bit Importance: Normal X-Priority: 3 (Normal) X-MSMail-priority: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.273 tagged_above=0 required=5 tests=AWL X-Spam-Level: * X-Archive-Number: 200504/128 X-Sequence-Number: 21021 I turned off hyperthreading (I saw that on the list that it did not help on Linux). I am using a pretty lightweight windows box Optiplex with IDE 750-meg internal 2.4 mghz cpu. My desktop has 2 gig, so might not be bad idea to try it local (I have installed), but me thinks its not totally a hardware issue for us. Joel Fradkin Do you run your 2650s with hyperthreading on? I found that slowed mine down under load, but we never had more than a couple dozen users hitting the db at once, so we may well have had a different load profile than what you're seeing. ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster From lists@boutiquenumerique.com Fri Apr 8 21:38:04 2005 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 0E4785381E for ; Fri, 8 Apr 2005 22:38:04 +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 50969-06 for ; Fri, 8 Apr 2005 21:37:57 +0000 (GMT) Received: from boutiquenumerique.com (boutiquenumerique.com [82.67.9.10]) by svr1.postgresql.org (Postfix) with ESMTP id AB9C75384A for ; Fri, 8 Apr 2005 22:37:55 +0100 (BST) Received: (qmail 28216 invoked from network); 8 Apr 2005 23:38:19 +0200 Received: from unknown (HELO localhost) (boutiquenumerique-lists@192.168.0.4) by boutiquenumerique.com with SMTP; 8 Apr 2005 23:38:19 +0200 To: "Joel Fradkin" , "'Tom Lane'" , "'Bob Henkel'" Cc: "'Scott Marlowe'" , "'Andrew Sullivan'" , pgsql-sql@postgresql.org Subject: Re: getting count for a specific querry References: <004b01c53c72$5e4c6390$797ba8c0@jfradkin> Message-ID: Date: Fri, 08 Apr 2005 23:37:55 +0200 From: PFC Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?= Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit In-Reply-To: <004b01c53c72$5e4c6390$797ba8c0@jfradkin> User-Agent: Opera M2(BETA2)/8.0 (Linux, build 987) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/129 X-Sequence-Number: 21022 > Since it is a count of matched condition records I may not have a way > around. What you could do is cache the search results (just caching the id's of the rows to display is enough and uses little space) in a cache table, numbering them with your sort order using a temporary sequence, so that you can : SELECT ... FROM cache WHERE row_position BETWEEN page_no*per_page AND (page_no+1)*per_page-1 to get the count : SELECT row_position FROM CACHE ORDER BY row_position DESC LIMIT 1 Add a session_id referencing your sessions table with an ON DELETE CASCADE and the cache will be auto-purged when sessions expire. From lists@boutiquenumerique.com Fri Apr 8 22:15:57 2005 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 84A44535AC for ; Fri, 8 Apr 2005 23:15:57 +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 59045-04 for ; Fri, 8 Apr 2005 22:15:47 +0000 (GMT) Received: from boutiquenumerique.com (boutiquenumerique.com [82.67.9.10]) by svr1.postgresql.org (Postfix) with ESMTP id F2849531C7 for ; Fri, 8 Apr 2005 23:15:46 +0100 (BST) Received: (qmail 29932 invoked from network); 9 Apr 2005 00:16:09 +0200 Received: from unknown (HELO localhost) (boutiquenumerique-lists@192.168.0.4) by boutiquenumerique.com with SMTP; 9 Apr 2005 00:16:09 +0200 To: "Joel Fradkin" Cc: pgsql-sql@postgresql.org Subject: Re: getting count for a specific querry References: <005901c53c74$4ba5d800$797ba8c0@jfradkin> Message-ID: Date: Sat, 09 Apr 2005 00:15:46 +0200 From: PFC Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?= Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit In-Reply-To: <005901c53c74$4ba5d800$797ba8c0@jfradkin> User-Agent: Opera M2(BETA2)/8.0 (Linux, build 987) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/130 X-Sequence-Number: 21023 Please run this disk throughput test on your system : http://boutiquenumerique.com/pf/multi_io.py It just spawns N threads which will write a lot of data simultaneously to the disk, then measures the total time. Same for read. Modify the parameters in the source... it's set to generate 10G of files in the current directory and re-read them, all with 8 threads. How much I/O do you get ? Also hdparm -t /dev/hd? would be interesting. On Fri, 08 Apr 2005 21:51:02 +0200, Joel Fradkin wrote: > I will also look at doing it the way you describe, they do have wide > liberty. Thanks so much for the ideas. Sorry I did not do a perusal of > the > archives first (I normally try that, but think I am brain dead today). > > Joel Fradkin > Wazagua, Inc. > 2520 Trailmate Dr > Sarasota, Florida 34243 > Tel. 941-753-7111 ext 305 > jfradkin@wazagua.com > www.wazagua.com > Powered by Wazagua > Providing you with the latest Web-based technology & advanced tools. > C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc > This email message is for the use of the intended recipient(s) and may > contain confidential and privileged information. Any unauthorized > review, > use, disclosure or distribution is prohibited. If you are not the > intended > recipient, please contact the sender by reply email and delete and > destroy > all copies of the original message, including attachments. > > > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org] > On Behalf Of Mischa Sandberg > Sent: Friday, April 08, 2005 2:40 PM > To: Scott Marlowe > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] getting count for a specific querry > > Quoting Scott Marlowe : > >> On Fri, 2005-04-08 at 12:08, Joel Fradkin wrote: >> > 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. >> >> 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? > > For the general discussion of slowness of count(*), > and given no entry on the subject in > http://www.postgresql.org/docs/faqs.FAQ.html > ... I guess everyone has to be pointed at: > http://archives.postgresql.org/pgsql-hackers/2005-01/msg00247.php > > However, the gist of this person's problem is that an adhoc query, > NOT just a 'select count(*) from table', can take remarkably long. > Again, the problem is that PG can't just scan an index. > ---------------------- > One workaround for this is to use EXPLAIN. > THIS APPLIES TO CASES WHERE THE USER HAS WIDE LIBERTY IN QUERIES. > It's pointless overhead, otherwise. > > default_statistics_target is cranked up to 200 on all such tables, > and pg_autovacuum is running. (If there were anything to improve, > it would be refining the thresholds on this). > > If the "(cost...rows=nnnn" string returns a number higher than the > QUERY row limit, the user is derailed ("That's not specific enough to > answer > immediately; do you want an emailed report?"). > > Otherwise, it runs EXPLAIN ANALYZE, which is still faster than the query > itself. > If the "(actual...rows=nnnn...)" is higher than the RESULT row limit > (PAGE > limit). > > It then runs the query, with the PAGE rows offset and limit --- and > happily, > practically everything that query needs is now in shared_buffers. > The count from the EXPLAIN analyze is displayed in the web page. > From desoi@pgedit.com Sat Apr 9 15:00:31 2005 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 877435380B for ; Sat, 9 Apr 2005 16:00:31 +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 99285-06 for ; Sat, 9 Apr 2005 15:00:27 +0000 (GMT) Received: from a2s1.a2webhosting.com (host-180.a2webhosting.com [69.39.89.180]) by svr1.postgresql.org (Postfix) with ESMTP id 431395339E for ; Sat, 9 Apr 2005 16:00:26 +0100 (BST) Received: from user-11207sg.dsl.mindspring.com ([66.32.31.144] helo=[192.168.1.10]) by a2s1.a2webhosting.com with esmtpsa (TLSv1:RC4-SHA:128) (Exim 4.44) id 1DKHRe-0008UY-LG; Sat, 09 Apr 2005 11:00:22 -0400 In-Reply-To: <004b01c53c72$5e4c6390$797ba8c0@jfradkin> References: <004b01c53c72$5e4c6390$797ba8c0@jfradkin> Mime-Version: 1.0 (Apple Message framework v619) Content-Type: text/plain; charset=US-ASCII; format=flowed Message-Id: <177B93D9-A908-11D9-92FA-000A95B03262@pgedit.com> Content-Transfer-Encoding: 7bit Cc: "'Bob Henkel'" , "'Scott Marlowe'" , "'Andrew Sullivan'" , "'Tom Lane'" , From: John DeSoi Subject: Re: getting count for a specific querry Date: Sat, 9 Apr 2005 11:00:20 -0400 To: "Joel Fradkin" X-Mailer: Apple Mail (2.619) X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - a2s1.a2webhosting.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - pgedit.com X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.009 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/132 X-Sequence-Number: 21025 On Apr 8, 2005, at 3:37 PM, Joel Fradkin wrote: > I don't think my clients would like me to aprox as it is a count of > their > records. What I plan on doing assuming I can get all my other problems > fixed > (as mentioned I am going to try and get paid help to see if I goofed > it up > some where) is make the count a button, so they don't wait everytime, > but > can choose to wait if need be, maybe I can store the last count with a > count > on day for the generic search it defaults to, and just have them do a > count > on demand if they have a specific query. Our screens have several > criteria > fields in each application. Here is an interface idea I'm working on for displaying query results in PostgreSQL. Maybe it will work for you if your connection method does not prevent you from using cursors. I create a cursor an then fetch the first 1000 rows. The status display has 4 paging buttons, something like this: |< < rows 1 - 1000 of ? > >| The user can hit the "next" button to get the next 1000. If less than 1000 are fetched the ? is replaced with the actual count. They can press the "last" button to move to the end of the cursor and get the actual count if they need it. So here the initial query should be fast, the user can get the count if they need it, and you don't have to re-query using limit and offset. The problem I'm looking into now (which I just posted on the general list) is I don't see a way to get the table and column information from a cursor. If I fetch from a cursor, the table OID and column number values are 0 in the row description. If I execute the same query directly without a cursor, the row description has the correct values for table OID and column number. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL From luckyratfoot@gmail.com Sat Apr 9 15:43:13 2005 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 09C9052A56 for ; Sat, 9 Apr 2005 16:43:13 +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 13322-05 for ; Sat, 9 Apr 2005 15:43:09 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.201]) by svr1.postgresql.org (Postfix) with ESMTP id F0175537B8 for ; Sat, 9 Apr 2005 16:43:07 +0100 (BST) Received: by wproxy.gmail.com with SMTP id 68so1196919wri for ; Sat, 09 Apr 2005 08:43:07 -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=GpsRRBue9j1dXdWpO7hcuFmnbLAE+Vnd/djiPLvRQZogIczduv3oXJhOIj0aDK22REaR7yCaHwA1UaafGODbfPWKvdK0o/64BinCdAbjC1Oc1vxaJUTJZfPyubKaWf7M5a3J4WGgEXyQaZAGpNg0Z50dZxtKt4i2dT8fJwf4ZJE= Received: by 10.54.40.34 with SMTP id n34mr491165wrn; Sat, 09 Apr 2005 08:43:07 -0700 (PDT) Received: by 10.54.70.11 with HTTP; Sat, 9 Apr 2005 08:43:07 -0700 (PDT) Message-ID: <762e5c0504090843121e087c@mail.gmail.com> Date: Sat, 9 Apr 2005 10:43:07 -0500 From: Bob Henkel Reply-To: Bob Henkel To: John DeSoi Subject: Re: getting count for a specific querry Cc: Joel Fradkin , Scott Marlowe , Andrew Sullivan , Tom Lane , pgsql-sql@postgresql.org In-Reply-To: <177B93D9-A908-11D9-92FA-000A95B03262@pgedit.com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1022_33535892.1113061387437" References: <004b01c53c72$5e4c6390$797ba8c0@jfradkin> <177B93D9-A908-11D9-92FA-000A95B03262@pgedit.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.337 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/133 X-Sequence-Number: 21026 ------=_Part_1022_33535892.1113061387437 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On Apr 9, 2005 10:00 AM, John DeSoi wrote: >=20 >=20 > On Apr 8, 2005, at 3:37 PM, Joel Fradkin wrote: >=20 > > I don't think my clients would like me to aprox as it is a count of > > their > > records. What I plan on doing assuming I can get all my other problems > > fixed > > (as mentioned I am going to try and get paid help to see if I goofed > > it up > > some where) is make the count a button, so they don't wait everytime, > > but > > can choose to wait if need be, maybe I can store the last count with a > > count > > on day for the generic search it defaults to, and just have them do a > > count > > on demand if they have a specific query. Our screens have several > > criteria > > fields in each application. >=20 > Here is an interface idea I'm working on for displaying query results > in PostgreSQL. Maybe it will work for you if your connection method > does not prevent you from using cursors. I create a cursor an then > fetch the first 1000 rows. The status display has 4 paging buttons, > something like this: >=20 > |< < rows 1 - 1000 of ? > >| >=20 > The user can hit the "next" button to get the next 1000. If less than > 1000 are fetched the ? is replaced with the actual count. They can > press the "last" button to move to the end of the cursor and get the > actual count if they need it. So here the initial query should be fast, > the user can get the count if they need it, and you don't have to > re-query using limit and offset. >=20 > The problem I'm looking into now (which I just posted on the general > list) is I don't see a way to get the table and column information from > a cursor. If I fetch from a cursor, the table OID and column number > values are 0 in the row description. If I execute the same query > directly without a cursor, the row description has the correct values > for table OID and column number. >=20 > John DeSoi, Ph.D. > http://pgedit.com/ > Power Tools for PostgreSQL Oracle Forms uses a similar method as you described and it works just fine.= =20 It will say Record 1 of ?(But I think the developer can set the amount of= =20 records cached so that if you set it to 10 and queried 5 records it would= =20 say record 1 of 5 because it would be under the cache amount.) Forms also= =20 offers a button that say get hit count. So if you really need to know the= =20 record count you can get it without moving off the current record. ------=_Part_1022_33535892.1113061387437 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

On Apr 9, 2005 10:00 AM, John DeSoi <desoi@pgedit.com> wrote:

On Apr 8, 2005, at 3:37 PM, Joel Fradkin wrote:<= br>
> I don't think my clients would like me to aprox as it is a coun= t of
> their
> records. What I plan on doing assuming I can get= all my other problems
> fixed
> (as mentioned I am going to tr= y and get paid help to see if I goofed
> it up
> some where) is= make the count a button, so they don't wait everytime,
> but
>= can choose to wait if need be, maybe I can store the last count with a
= > count
> on day for the generic search it defaults to, and just h= ave them do a
> count
> on demand if they have a specific query= . Our screens have several
> criteria
> fields in each applicat= ion.

Here is an interface idea I'm working on for displaying query r= esults
in PostgreSQL. Maybe it will work for you if your connection meth= od
does not prevent you from using cursors. I create a cursor an thenfetch the first 1000 rows. The status display has 4 paging buttons,
som= ething like this:

|< < rows 1 - 1000 of ? > >|

Th= e user can hit the "next" button to get the next 1000. If less th= an
1000 are fetched the ? is replaced with the actual count. They canpress the "last" button to move to the end of the cursor and get= the
actual count if they need it. So here the initial query should be f= ast,
the user can get the count if they need it, and you don't have tore-query using limit and offset.

The problem I'm looking into now = (which I just posted on the general
list) is I don't see a way to get th= e table and column information from
a cursor. If I fetch from a cursor, = the table OID and column number
values are 0 in the row description. If = I execute the same query
directly without a cursor, the row description = has the correct values
for table OID and column number.

John DeSo= i, Ph.D.
http://pgedit.com/
Power = Tools for PostgreSQL


Oracle Forms uses a similar method as you described and it works just fine.  It will say Record 1 of ?(But I think the developer can set the amount of records cached so that if you set it to 10 and queried 5 records it would say record 1 of 5 because it would be under the cache amount.)  Forms also offers a button that say get hit count. So if you really need to know the record count you can get it without moving off the current record.

&= nbsp;

------=_Part_1022_33535892.1113061387437-- From desoi@pgedit.com Sat Apr 9 15:51:18 2005 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 7DF6753807 for ; Sat, 9 Apr 2005 16:51:18 +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 14617-08 for ; Sat, 9 Apr 2005 15:51:15 +0000 (GMT) Received: from a2s1.a2webhosting.com (host-180.a2webhosting.com [69.39.89.180]) by svr1.postgresql.org (Postfix) with ESMTP id 0F6E453761 for ; Sat, 9 Apr 2005 16:51:14 +0100 (BST) Received: from user-11207sg.dsl.mindspring.com ([66.32.31.144] helo=[192.168.1.10]) by a2s1.a2webhosting.com with esmtpsa (TLSv1:RC4-SHA:128) (Exim 4.44) id 1DKIEr-0005xv-Kn; Sat, 09 Apr 2005 11:51:13 -0400 In-Reply-To: <762e5c0504090843121e087c@mail.gmail.com> References: <004b01c53c72$5e4c6390$797ba8c0@jfradkin> <177B93D9-A908-11D9-92FA-000A95B03262@pgedit.com> <762e5c0504090843121e087c@mail.gmail.com> Mime-Version: 1.0 (Apple Message framework v619) Content-Type: text/plain; charset=US-ASCII; format=flowed Message-Id: <32472AF2-A90F-11D9-92FA-000A95B03262@pgedit.com> Content-Transfer-Encoding: 7bit Cc: Joel Fradkin , pgsql-sql@postgresql.org, Scott Marlowe , Andrew Sullivan , Tom Lane From: John DeSoi Subject: Re: getting count for a specific querry Date: Sat, 9 Apr 2005 11:51:11 -0400 To: Bob Henkel X-Mailer: Apple Mail (2.619) X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - a2s1.a2webhosting.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - pgedit.com X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.009 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/134 X-Sequence-Number: 21027 On Apr 9, 2005, at 11:43 AM, Bob Henkel wrote: > Forms also offers a button that say get hit count. So if you really > need to know the record count you can get it without moving off the > current record. That's a good idea too. Maybe in my interface you could click on the ? to get the count without changing the rows you are viewing. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL From vivek@khera.org Tue Apr 12 19:29:32 2005 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 DD4B153411 for ; Tue, 12 Apr 2005 16:29:32 -0300 (ADT) 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 11771-10 for ; Tue, 12 Apr 2005 19:29:26 +0000 (GMT) Received: from yertle.kcilink.com (yertle.kcilink.com [65.205.34.180]) by svr1.postgresql.org (Postfix) with ESMTP id 0C41E53420 for ; Tue, 12 Apr 2005 16:29:24 -0300 (ADT) Received: from [192.168.7.103] (host-103.int.kcilink.com [192.168.7.103]) by yertle.kcilink.com (Postfix) with ESMTP id EE559B80D for ; Tue, 12 Apr 2005 15:29:25 -0400 (EDT) Mime-Version: 1.0 (Apple Message framework v619.2) In-Reply-To: <1112993454.20921.89.camel@state.g2switchworks.com> References: <003b01c53c70$6ff184b0$797ba8c0@jfradkin> <762e5c05040813354436bd27@mail.gmail.com> <8ef5bfe4806dac39bab8e07837dbab38@khera.org> <1112993454.20921.89.camel@state.g2switchworks.com> Content-Type: multipart/signed; micalg=sha1; boundary=Apple-Mail-31--264505281; protocol="application/pkcs7-signature" Message-Id: From: Vivek Khera Subject: Re: getting count for a specific querry Date: Tue, 12 Apr 2005 15:29:24 -0400 To: pgsql-sql@postgresql.org X-Mailer: Apple Mail (2.619.2) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.075 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/167 X-Sequence-Number: 21060 --Apple-Mail-31--264505281 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; format=flowed On Apr 8, 2005, at 4:50 PM, Scott Marlowe wrote: > Do you run your 2650s with hyperthreading on? I found that slowed mine > down under load, but we never had more than a couple dozen users > hitting > the db at once, so we may well have had a different load profile than > what you're seeing. > Yep. Turned off as per various recommendations on this list. The RAID card on this box is a PERC 3/DC. It is a very big disappointment. The Opteron based generic system totally outperforms this Dell box. Vivek Khera, Ph.D. +1-301-869-4449 x806 --Apple-Mail-31--264505281 Content-Transfer-Encoding: base64 Content-Type: application/pkcs7-signature; name=smime.p7s Content-Disposition: attachment; filename=smime.p7s MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGhzCCAz8w ggKooAMCAQICAQ0wDQYJKoZIhvcNAQEFBQAwgdExCzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0 ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEaMBgGA1UEChMRVGhhd3RlIENvbnN1bHRpbmcx KDAmBgNVBAsTH0NlcnRpZmljYXRpb24gU2VydmljZXMgRGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0 ZSBQZXJzb25hbCBGcmVlbWFpbCBDQTErMCkGCSqGSIb3DQEJARYccGVyc29uYWwtZnJlZW1haWxA dGhhd3RlLmNvbTAeFw0wMzA3MTcwMDAwMDBaFw0xMzA3MTYyMzU5NTlaMGIxCzAJBgNVBAYTAlpB MSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUg UGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBDQTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEA xKY8VXNV+065yplaHmjAdQRwnd/p/6Me7L3N9VvyGna9fww6YfK/Uc4B1OVQCjDXAmNaLIkVcI7d yfArhVqqP3FWy688Cwfn8R+RNiQqE88r1fOCdz0Dviv+uxg+B79AgAJk16emu59l0cUqVIUPSAR/ p7bRPGEEQB5kGXJgt/sCAwEAAaOBlDCBkTASBgNVHRMBAf8ECDAGAQH/AgEAMEMGA1UdHwQ8MDow OKA2oDSGMmh0dHA6Ly9jcmwudGhhd3RlLmNvbS9UaGF3dGVQZXJzb25hbEZyZWVtYWlsQ0EuY3Js MAsGA1UdDwQEAwIBBjApBgNVHREEIjAgpB4wHDEaMBgGA1UEAxMRUHJpdmF0ZUxhYmVsMi0xMzgw DQYJKoZIhvcNAQEFBQADgYEASIzRUIPqCy7MDaNmrGcPf6+svsIXoUOWlJ1/TCG4+DYfqi2fNi/A 9BxQIJNwPP2t4WFiw9k6GX6EsZkbAMUaC4J0niVQlGLH2ydxVyWN3amcOY6MIE9lX5Xa9/eH1sYI Tq726jTlEBpbNU1341YheILcIRk13iSx0x1G/11fZU8wggNAMIICqaADAgECAgMOah8wDQYJKoZI hvcNAQEEBQAwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkp IEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA1 MDQwNTIwMzEzMloXDTA2MDQwNTIwMzEzMlowgYoxHzAdBgNVBAMTFlRoYXd0ZSBGcmVlbWFpbCBN ZW1iZXIxHjAcBgkqhkiG9w0BCQEWD3ZpdmVrQGtoZXJhLm9yZzEgMB4GCSqGSIb3DQEJARYRa2hl cmFAa2NpbGluay5jb20xJTAjBgkqhkiG9w0BCQEWFnZpdmVrQG1haWxlcm1haWxlci5jb20wggEi MA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDdo7hywGcY0VvK6WqqXXV77MS/t/4X3WkCaCXo RSl2W58GP4P21hodPn7hlIxUoDOW7x9O+FbqTgE2Ejqr6yA00Mm90tGPFgjFjqPGAqg7xk6IDcv9 uTyMia/FKEHSIynM6zqokXY8JklvdbJOiByE/8VeyEXOANWiflo8o4+GHnhMKpA9982YTXUqeKU6 mMQVaLCBRjTDc7j2XkMC/UNcp2HMyDQdTqYVnhLxbvbLX8CNDBY/7OWFlB9evru46SpGWhe4lhv5 DSgE2RdCKvDytzxRDvP49L8V0TnFjAVeC1C1Pj0/KQsoL/AP4APplROiD4QaUhshQl28pXxJtfbl AgMBAAGjVzBVMEUGA1UdEQQ+MDyBD3ZpdmVrQGtoZXJhLm9yZ4ERa2hlcmFAa2NpbGluay5jb22B FnZpdmVrQG1haWxlcm1haWxlci5jb20wDAYDVR0TAQH/BAIwADANBgkqhkiG9w0BAQQFAAOBgQAr CWop3h28qPwofzLrkoT410J4d7Bqk6FLeVlKZfg/wXlS1MTqYMNcCm4x+JsJbjwsO0fb2elFIuGq 1razoSzPpgi89itydvUT0U0U/u+AkZA5rW4AptTpMZ70YW5u9wzkcvmifqZmcfbaaeGdZfruzUXZ 6qvdXDpNb3ZHeQw6PjGCAucwggLjAgEBMGkwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0 ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFp bCBJc3N1aW5nIENBAgMOah8wCQYFKw4DAhoFAKCCAVMwGAYJKoZIhvcNAQkDMQsGCSqGSIb3DQEH ATAcBgkqhkiG9w0BCQUxDxcNMDUwNDEyMTkyOTI0WjAjBgkqhkiG9w0BCQQxFgQUmjMI1xFeBHab IeySmHRqyxHG2i0weAYJKwYBBAGCNxAEMWswaTBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhh d3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVt YWlsIElzc3VpbmcgQ0ECAw5qHzB6BgsqhkiG9w0BCRACCzFroGkwYjELMAkGA1UEBhMCWkExJTAj BgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJz b25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAgMOah8wDQYJKoZIhvcNAQEBBQAEggEANxQxFBMs6L3q keeF6/S9OCzKMrfPCHLhtiwglzr2z8tkD9zYihvtzAcZoR0t+CC+znk/8MRMaBg9tFCIhkHWwJtP hJsZBeXVtFO0CxadeRMZsOYh4dzWDC4mmM493rR2b3zui4Q+dIFSs//C5pn75h0Lj0SSBSJULzto 20sngCXU04EVIY97N2zIElSxqe5OWjOEUbPMKzfF9wUtkDiRKDRf4THpvbcyS1TtFe/99fJwvHEB 9OLIIR0oyiV6VEWuxY8P9k/P9v21UjR49digEYyPJvnvFYY0k+vpgFm4qhPzV9og2im9tpNi6KEX SYLOBfrkPLpvhqo0LxOrTARzEAAAAAAAAA== --Apple-Mail-31--264505281-- From smarlowe@g2switchworks.com Tue Apr 12 20:23:19 2005 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 0115C53D0B for ; Tue, 12 Apr 2005 17:23:19 -0300 (ADT) 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 30594-05 for ; Tue, 12 Apr 2005 20:23:10 +0000 (GMT) Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by svr1.postgresql.org (Postfix) with ESMTP id F265F53D0A for ; Tue, 12 Apr 2005 17:23:08 -0300 (ADT) Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP with MS-WebStorage 6.5.6944 Received: from state.g2switchworks.com by mail.g2switchworks.com; 12 Apr 2005 15:23:07 -0500 Subject: Re: getting count for a specific querry From: Scott Marlowe To: Vivek Khera Cc: pgsql-sql@postgresql.org In-Reply-To: References: <003b01c53c70$6ff184b0$797ba8c0@jfradkin> <762e5c05040813354436bd27@mail.gmail.com> <8ef5bfe4806dac39bab8e07837dbab38@khera.org> <1112993454.20921.89.camel@state.g2switchworks.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1113337387.20921.413.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Tue, 12 Apr 2005 15:23:07 -0500 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/169 X-Sequence-Number: 21062 On Tue, 2005-04-12 at 14:29, Vivek Khera wrote: > On Apr 8, 2005, at 4:50 PM, Scott Marlowe wrote: > > > Do you run your 2650s with hyperthreading on? I found that slowed mine > > down under load, but we never had more than a couple dozen users > > hitting > > the db at once, so we may well have had a different load profile than > > what you're seeing. > > > > Yep. Turned off as per various recommendations on this list. The RAID > card on this box is a PERC 3/DC. It is a very big disappointment. The > Opteron based generic system totally outperforms this Dell box. How much memory is in the box? I've heard horror stories about performance with >2 gigs of ram, which is why I made them order mine with 2 gigs. Does the 3/DC have battery backed cache set to write back? From vivek@khera.org Tue Apr 12 20:32:19 2005 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 EE9E853D50 for ; Tue, 12 Apr 2005 17:32:19 -0300 (ADT) 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 33882-08 for ; Tue, 12 Apr 2005 20:32:18 +0000 (GMT) Received: from yertle.kcilink.com (yertle.kcilink.com [65.205.34.180]) by svr1.postgresql.org (Postfix) with ESMTP id 77D9653D0A for ; Tue, 12 Apr 2005 17:32:17 -0300 (ADT) Received: from [192.168.7.103] (host-103.int.kcilink.com [192.168.7.103]) by yertle.kcilink.com (Postfix) with ESMTP id F043EB80D for ; Tue, 12 Apr 2005 16:32:19 -0400 (EDT) Mime-Version: 1.0 (Apple Message framework v619.2) In-Reply-To: <1113337387.20921.413.camel@state.g2switchworks.com> References: <003b01c53c70$6ff184b0$797ba8c0@jfradkin> <762e5c05040813354436bd27@mail.gmail.com> <8ef5bfe4806dac39bab8e07837dbab38@khera.org> <1112993454.20921.89.camel@state.g2switchworks.com> <1113337387.20921.413.camel@state.g2switchworks.com> Content-Type: multipart/signed; micalg=sha1; boundary=Apple-Mail-37--260731034; protocol="application/pkcs7-signature" Message-Id: <76f942bc142be849e6ab3a8018b090a6@khera.org> From: Vivek Khera Subject: Re: getting count for a specific querry Date: Tue, 12 Apr 2005 16:32:18 -0400 To: pgsql-sql@postgresql.org X-Mailer: Apple Mail (2.619.2) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.063 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/170 X-Sequence-Number: 21063 --Apple-Mail-37--260731034 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; format=flowed On Apr 12, 2005, at 4:23 PM, Scott Marlowe wrote: > How much memory is in the box? I've heard horror stories about > performance with >2 gigs of ram, which is why I made them order mine > with 2 gigs. Does the 3/DC have battery backed cache set to write > back? > 4GB RAM and battery backed cache set to write-back mode. FreeBSD 4.11. Vivek Khera, Ph.D. +1-301-869-4449 x806 --Apple-Mail-37--260731034 Content-Transfer-Encoding: base64 Content-Type: application/pkcs7-signature; name=smime.p7s Content-Disposition: attachment; filename=smime.p7s MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGhzCCAz8w ggKooAMCAQICAQ0wDQYJKoZIhvcNAQEFBQAwgdExCzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0 ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEaMBgGA1UEChMRVGhhd3RlIENvbnN1bHRpbmcx KDAmBgNVBAsTH0NlcnRpZmljYXRpb24gU2VydmljZXMgRGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0 ZSBQZXJzb25hbCBGcmVlbWFpbCBDQTErMCkGCSqGSIb3DQEJARYccGVyc29uYWwtZnJlZW1haWxA dGhhd3RlLmNvbTAeFw0wMzA3MTcwMDAwMDBaFw0xMzA3MTYyMzU5NTlaMGIxCzAJBgNVBAYTAlpB MSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUg UGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBDQTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEA xKY8VXNV+065yplaHmjAdQRwnd/p/6Me7L3N9VvyGna9fww6YfK/Uc4B1OVQCjDXAmNaLIkVcI7d yfArhVqqP3FWy688Cwfn8R+RNiQqE88r1fOCdz0Dviv+uxg+B79AgAJk16emu59l0cUqVIUPSAR/ p7bRPGEEQB5kGXJgt/sCAwEAAaOBlDCBkTASBgNVHRMBAf8ECDAGAQH/AgEAMEMGA1UdHwQ8MDow OKA2oDSGMmh0dHA6Ly9jcmwudGhhd3RlLmNvbS9UaGF3dGVQZXJzb25hbEZyZWVtYWlsQ0EuY3Js MAsGA1UdDwQEAwIBBjApBgNVHREEIjAgpB4wHDEaMBgGA1UEAxMRUHJpdmF0ZUxhYmVsMi0xMzgw DQYJKoZIhvcNAQEFBQADgYEASIzRUIPqCy7MDaNmrGcPf6+svsIXoUOWlJ1/TCG4+DYfqi2fNi/A 9BxQIJNwPP2t4WFiw9k6GX6EsZkbAMUaC4J0niVQlGLH2ydxVyWN3amcOY6MIE9lX5Xa9/eH1sYI Tq726jTlEBpbNU1341YheILcIRk13iSx0x1G/11fZU8wggNAMIICqaADAgECAgMOah8wDQYJKoZI hvcNAQEEBQAwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkp IEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA1 MDQwNTIwMzEzMloXDTA2MDQwNTIwMzEzMlowgYoxHzAdBgNVBAMTFlRoYXd0ZSBGcmVlbWFpbCBN ZW1iZXIxHjAcBgkqhkiG9w0BCQEWD3ZpdmVrQGtoZXJhLm9yZzEgMB4GCSqGSIb3DQEJARYRa2hl cmFAa2NpbGluay5jb20xJTAjBgkqhkiG9w0BCQEWFnZpdmVrQG1haWxlcm1haWxlci5jb20wggEi MA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDdo7hywGcY0VvK6WqqXXV77MS/t/4X3WkCaCXo RSl2W58GP4P21hodPn7hlIxUoDOW7x9O+FbqTgE2Ejqr6yA00Mm90tGPFgjFjqPGAqg7xk6IDcv9 uTyMia/FKEHSIynM6zqokXY8JklvdbJOiByE/8VeyEXOANWiflo8o4+GHnhMKpA9982YTXUqeKU6 mMQVaLCBRjTDc7j2XkMC/UNcp2HMyDQdTqYVnhLxbvbLX8CNDBY/7OWFlB9evru46SpGWhe4lhv5 DSgE2RdCKvDytzxRDvP49L8V0TnFjAVeC1C1Pj0/KQsoL/AP4APplROiD4QaUhshQl28pXxJtfbl AgMBAAGjVzBVMEUGA1UdEQQ+MDyBD3ZpdmVrQGtoZXJhLm9yZ4ERa2hlcmFAa2NpbGluay5jb22B FnZpdmVrQG1haWxlcm1haWxlci5jb20wDAYDVR0TAQH/BAIwADANBgkqhkiG9w0BAQQFAAOBgQAr CWop3h28qPwofzLrkoT410J4d7Bqk6FLeVlKZfg/wXlS1MTqYMNcCm4x+JsJbjwsO0fb2elFIuGq 1razoSzPpgi89itydvUT0U0U/u+AkZA5rW4AptTpMZ70YW5u9wzkcvmifqZmcfbaaeGdZfruzUXZ 6qvdXDpNb3ZHeQw6PjGCAucwggLjAgEBMGkwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0 ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFp bCBJc3N1aW5nIENBAgMOah8wCQYFKw4DAhoFAKCCAVMwGAYJKoZIhvcNAQkDMQsGCSqGSIb3DQEH ATAcBgkqhkiG9w0BCQUxDxcNMDUwNDEyMjAzMjE5WjAjBgkqhkiG9w0BCQQxFgQUoTwnDjeWsttB Awi3J+2wuUqQcf0weAYJKwYBBAGCNxAEMWswaTBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhh d3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVt YWlsIElzc3VpbmcgQ0ECAw5qHzB6BgsqhkiG9w0BCRACCzFroGkwYjELMAkGA1UEBhMCWkExJTAj BgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJz b25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAgMOah8wDQYJKoZIhvcNAQEBBQAEggEAmIxRCAoaaQ1J nhyku4wrvP5ORAP0/VIKlV4vsg30ofwThG+t99bTlHNwyaxEz2COGJx9UMJ2ZYIeOhkzsAB4ZNrl 6c4KtCN8OqFJuNL3EEB3dkCXDyBYIg3l2ZbpMH0loYgYo/iW0vF2X2O1D+DUcfQHgDj8x4Jkeyjq LOj1xIaaTv+amkgg0XIIG0Bb91A1qX/1bBTy1F9yo5EWK7HQvbDxUegE33f8A4PqdNk035utaelp BY5K1Hmgesx9w4uuQAwSgK3T/JkBU+QW/8RMwFGvNaEUQEWukuBOAQMXbiIHsWu39Dp6UD2HDdfx OmJK+c+ooH5Gz69IPCT+/Q2bkgAAAAAAAA== --Apple-Mail-37--260731034-- From smarlowe@g2switchworks.com Tue Apr 12 20:42:55 2005 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 7462D53D70 for ; Tue, 12 Apr 2005 17:42:55 -0300 (ADT) 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 36231-10 for ; Tue, 12 Apr 2005 20:42:54 +0000 (GMT) Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by svr1.postgresql.org (Postfix) with ESMTP id 1287253D5B for ; Tue, 12 Apr 2005 17:42:53 -0300 (ADT) Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP with MS-WebStorage 6.5.6944 Received: from state.g2switchworks.com by mail.g2switchworks.com; 12 Apr 2005 15:42:54 -0500 Subject: Re: getting count for a specific querry From: Scott Marlowe To: Vivek Khera Cc: pgsql-sql@postgresql.org In-Reply-To: <76f942bc142be849e6ab3a8018b090a6@khera.org> References: <003b01c53c70$6ff184b0$797ba8c0@jfradkin> <762e5c05040813354436bd27@mail.gmail.com> <8ef5bfe4806dac39bab8e07837dbab38@khera.org> <1112993454.20921.89.camel@state.g2switchworks.com> <1113337387.20921.413.camel@state.g2switchworks.com> <76f942bc142be849e6ab3a8018b090a6@khera.org> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1113338574.20921.436.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Tue, 12 Apr 2005 15:42:54 -0500 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/171 X-Sequence-Number: 21064 On Tue, 2005-04-12 at 15:32, Vivek Khera wrote: > On Apr 12, 2005, at 4:23 PM, Scott Marlowe wrote: > > > How much memory is in the box? I've heard horror stories about > > performance with >2 gigs of ram, which is why I made them order mine > > with 2 gigs. Does the 3/DC have battery backed cache set to write > > back? > > > > 4GB RAM and battery backed cache set to write-back mode. FreeBSD 4.11. If you've got the time, try running it with only 2 gigs and compare the speed. I never really got a chance to run mine with >2 gigs, but I know that I read plenty of posts at the time that the chipset in the 2650 was REALLY slow at using memory over 2 gig.