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