Re: sub select performance

Wow. Now to dig into the docs to see what you did. Thanks
a bunch.

$ time psql radius < mailinglist.sql
sessions | connecttime
----------+-------------
7 | 01:47:25
(1 row)

0.04 real 0.00 user 0.00 sys
$

Out of almost a million records, too.

--
Chris Linstruth <cjl(at)qnet(dot)com>

On Sun, 11 May 2003, Mathieu Arnold wrote:

>
>
> --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);
>
>