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 12D505340C for ; Thu, 24 Mar 2005 19:24:13 +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 37586-03 for ; Thu, 24 Mar 2005 19:24:12 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.192]) by svr1.postgresql.org (Postfix) with ESMTP id EFEBD53205 for ; Thu, 24 Mar 2005 19:24:11 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 36so683605wra for ; Thu, 24 Mar 2005 11:24:12 -0800 (PST) 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:content-transfer-encoding:references; b=ulBBqPoDdvN+pDkVnoT3X+uiK6YsMd4SfMZW9l3Zvh2Tnyz8tnJP/qzVfzIxmS2mv/GELwBlLqv9GDbHbYTv1q14hZsnoYW0zrCEG/yKW/jFDAbyFI6qEdB6nY1+OO0sL19beeZWO2HPinOfDeXgG/OVTntnZNjZcyGrwG9nu8g= Received: by 10.54.27.51 with SMTP id a51mr440448wra; Thu, 24 Mar 2005 11:24:12 -0800 (PST) Received: by 10.54.11.61 with HTTP; Thu, 24 Mar 2005 11:24:12 -0800 (PST) Message-ID: <37d451f7050324112414d7c9b5@mail.gmail.com> Date: Thu, 24 Mar 2005 13:24:12 -0600 From: Rosser Schwarz Reply-To: Rosser Schwarz To: matt@followers.net Subject: Re: Preventing query from hogging server Cc: pgsql-performance@postgresql.org In-Reply-To: <42431001.36c6643c.1b4b.ffffd505@mx.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit References: <42431001.36c6643c.1b4b.ffffd505@mx.gmail.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.211 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200503/514 X-Sequence-Number: 11219 while you weren't looking, Matthew Nuzum wrote: > select accountid, min(atime) as atime, sessionid from usage_access > group by accountid,sessionid; Try something along the lines of: select ua.accountid , (select atime from usage_access where sessionid = ua.sessionid and accountid = ua.accountid order by atime asc limit 1 ) as atime , ua.sessionid from usage_access ua group by accountid , sessionid min() and max() currently do table scans, which, on large tables, or even moderately sized tables with large numbers of accounts/sessions, can add up. You'll need to replace asc with desc in the subquery for the max() version. This form cheats a bit and uses the index to find the highest and lowest values, provided you've created the appropriate indices. This is, IIRC, in the FAQ. /rls -- :wq