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 AFE9354F47 for ; Fri, 4 Mar 2005 15:56:45 +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 04004-05 for ; Fri, 4 Mar 2005 15:56:44 +0000 (GMT) Received: from anchor-post-32.mail.demon.net (anchor-post-32.mail.demon.net [194.217.242.90]) by svr1.postgresql.org (Postfix) with ESMTP id 4FDAF54E95 for ; Fri, 4 Mar 2005 15:56:43 +0000 (GMT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-32.mail.demon.net with esmtp (Exim 4.42) id 1D7FAR-000Kma-7i; Fri, 04 Mar 2005 15:56:46 +0000 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 2FCCB162ED; Fri, 4 Mar 2005 15:56:26 +0000 (GMT) Message-ID: <42288529.7000803@archonet.com> Date: Fri, 04 Mar 2005 15:56:25 +0000 From: Richard Huxton User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Ken Egervari Cc: John Arbash Meinel , pgsql-performance@postgresql.org Subject: Re: Help with tuning this query (with explain analyze finally) References: <001001c51ef4$3849eeb0$6a01a8c0@KEN> <200503022152.13211.josh@agliodbs.com> <004601c51fbe$828bbb50$6a01a8c0@KEN> <200503030935.14537.josh@agliodbs.com> <001d01c5204a$b440edf0$6a01a8c0@KEN> <4227AA36.2080700@arbash-meinel.com> <000e01c5207a$1f668ac0$6a01a8c0@KEN> In-Reply-To: <000e01c5207a$1f668ac0$6a01a8c0@KEN> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.061 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/73 X-Sequence-Number: 10778 Ken Egervari wrote: > Let's say we have 200 users signed into the application at the same > time. The application refreshes their shipment information automatically > to make sure it's up to date on the user's screen. The application will > execute the query we are trying to tune every 60 seconds for most of > these users. Users can set the refresh time to be higher, but 60 is the > lowest amount so I'm just assuming everyone has it at 60. > > Anyway, if you have 200 users logged in, that's 200 queries in the 60 > second period, which is about 3-4 queries every second. Can you turn the problem around? Calculate what you want for all users (once every 60 seconds) and stuff those results into a summary table. Then let the users query the summary table as often as they like (with the understanding that the figures aren't going to update any faster than once a minute) -- Richard Huxton Archonet Ltd