sub select performance - Mailing list pgsql-sql

I'm trying to use a subselect and am not sure why performance suffers.

This returns instantly:

SELECT DISTINCT ON (acctsessionid) radacctid from radacct         WHERE username='cjl'         AND acctstoptime IS NOT
NULL        AND date_trunc('month', now())=date_trunc('month', acctstoptime);
 

radacctid
-----------   244983   606131   720282   365422  1152892   949219  1125943
(7 rows)

This returns instantly as well:

SELECT count(radacctid) AS sessions,       sum(acctsessiontime) AS connecttime       FROM radacct       WHERE radacctid
IN(244983,606131, 720282, 365422, 1152892, 949219, 1125943);
 

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
acctstoptimeIS 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)".

Thanks.

-- 
Chris Linstruth <cjl@qnet.com>