Re: sub select performance

--Le 11/05/2003 09:48 -0700, Chris Linstruth écrivait :

|
| But this can take 15-30 seconds:
| 
| 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)".

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

-- 
Mathieu Arnold