Re: sub select performance

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