X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 53FED52A6A for ; Thu, 3 Mar 2005 20:47:52 +0000 (GMT) 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 91267-08 for ; Thu, 3 Mar 2005 20:47:42 +0000 (GMT) Received: from penguin.goodinassociates.com (penguin.goodinassociates.com [206.80.71.229]) by svr1.postgresql.org (Postfix) with ESMTP id 433E55514D for ; Thu, 3 Mar 2005 20:47:41 +0000 (GMT) Received: from [206.80.71.242] ([206.80.71.242]) by penguin.goodinassociates.com (8.12.11/8.12.11) with ESMTP id j23KlTIM011117; Thu, 3 Mar 2005 14:47:29 -0600 Subject: Re: name search query speed From: Jeremiah Jahn To: Dave Held Cc: John A Meinel , postgres performance In-Reply-To: <49E94D0CFCD4DB43AFBA928DDD20C8F90261845D@asg002.asg.local> References: <49E94D0CFCD4DB43AFBA928DDD20C8F90261845D@asg002.asg.local> Content-Type: text/plain Date: Thu, 03 Mar 2005 14:47:29 -0600 Message-Id: <1109882849.16326.99.camel@bluejay.goodinassociates.com> Mime-Version: 1.0 X-Mailer: Evolution 2.0.4 (2.0.4-1) Content-Transfer-Encoding: 7bit X-RAVMilter-Version: 8.4.4(snapshot 20030410) (penguin.goodinassociates.com) Received-SPF: none (penguin.goodinassociates.com: 206.80.71.242 is neither permitted nor denied by domain of cs.earlham.edu) client-ip=206.80.71.242; envelope-from=jeremiah@cs.earlham.edu; helo=[206.80.71.242]; X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/64 X-Sequence-Number: 10769 doesn't that cause two queries? I used to do it that way and cut my time substantially by counting in-line. Even though the results were cached it still took more time. Also since the tables is constantly be updated the returned total would not always match the number of results on the second query. On Thu, 2005-03-03 at 14:26 -0600, Dave Held wrote: > > -----Original Message----- > > From: Jeremiah Jahn [mailto:jeremiah@cs.earlham.edu] > > Sent: Thursday, March 03, 2005 2:15 PM > > To: John A Meinel > > Cc: postgres performance > > Subject: Re: [PERFORM] name search query speed > > > > [...] > > So the count for this is generated on the fly needed for > > pagination in the app which expresses the total number of > > finds, but only displays 40 of them. If any one knows a way > > to determine the total number of matches without needing to > > iterate through them using jdbc, I'm all ears as this would > > save me huge amounts of time and limit/offset would become > > an option. > > Is there a reason you can't do a count(field) query first? If > so, you can get the number of records returned by setting > absolute(-1) and getting the row number. > > __ > David B. Held > Software Engineer/Array Services Group > 200 14th Ave. East, Sartell, MN 56377 > 320.534.3637 320.253.7800 800.752.8129 -- "A power so great, it can only be used for Good or Evil!" -- Firesign Theatre, "The Giant Rat of Summatra"