Date: Sun, 11 May 2003 20:53:46 +0200
From: Mathieu Arnold <mat@mat.cc>
To: Chris Linstruth <cjl@QNET.COM>, pgsql-sql@postgresql.org
Subject: Re: sub select performance
Message-ID: <3465828.1052686426@sauron.in.mat.cc>
In-Reply-To: <Pine.BSI.4.33.0305110914360.21305-100000@cello.qnet.com>
References: <Pine.BSI.4.33.0305110914360.21305-100000@cello.qnet.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline


--Le 11/05/2003 09:48 -0700, Chris Linstruth =E9crivait :

|
| But this can take 15-30 seconds:
|=20
| SELECT count(radacctid) AS sessions,
|         sum(acctsessiontime) AS connecttime
|         FROM radacct
|         WHERE radacctid IN
|         (SELECT DISTINCT ON (acctsessionid) radacctid FROM radacct
|           WHERE username=3D'cjl'
|           AND acctstoptime IS NOT NULL
|           AND date_trunc('month', now())=3Ddate_trunc('month',
| acctstoptime));
|=20
| 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)".

try this :
SELECT count(radacctid) AS sessions,
        sum(acctsessiontime) AS connecttime
        FROM radacct
             JOIN (SELECT DISTINCT ON (acctsessionid) radacctid FROM radacct
                   WHERE username=3D'cjl'
                   AND acctstoptime IS NOT NULL
                   AND date_trunc('month', now())=3Ddate_trunc('month',
acctstoptime)) AS subselect USING (radacctid);

--=20
Mathieu Arnold