Re: sub select performance

On Sun, May 11, 2003 at 09:48:05 -0700, Chris Linstruth <cjl@QNET.COM> 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.