X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 124F6475E14 for ; Sun, 11 May 2003 13:50:33 -0400 (EDT) Received: from wolff.to (wolff.to [66.93.249.74]) by postgresql.org (Postfix) with SMTP id 4A9624758F1 for ; Sun, 11 May 2003 13:50:32 -0400 (EDT) Received: (qmail 755 invoked by uid 500); 11 May 2003 17:51:52 -0000 Date: Sun, 11 May 2003 12:51:52 -0500 From: Bruno Wolff III To: Chris Linstruth Cc: pgsql-sql@postgresql.org Subject: Re: sub select performance Message-ID: <20030511175152.GC710@wolff.to> Mail-Followup-To: Chris Linstruth , pgsql-sql@postgresql.org References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.4i X-Spam-Status: No, hits=-38.0 required=5.0 tests=BAYES_10,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_MUTT autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/208 X-Sequence-Number: 13373 On Sun, May 11, 2003 at 09:48:05 -0700, Chris Linstruth wrote: > I'm trying to use a subselect and am not sure why performance suffers. > > SELECT count(radacctid) AS sessions, > sum(acctsessiontime) AS connecttime > FROM radacct > WHERE radacctid IN > (SELECT DISTINCT ON (acctsessionid) radacctid FROM radacct > WHERE username='cjl' > AND acctstoptime IS NOT NULL > AND date_trunc('month', now())=date_trunc('month', acctstoptime)); > > There are probably many different ways to perform this query. My > main problem is trying to overcome the fact that try as I might, > I can't stop the occasional duplicate accounting record from being > inserted so I have to weed them out, hence the "DISTINCT ON > (acctsessionid)". IN is slow. If you tried the development version it would probably be a lot faster. For 7.3 and below, try rewriting the query to use a join or a where clause.