Обсуждение: sub select performance

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>

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.

--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
(SELECTDISTINCT ON (acctsessionid) radacctid FROM radacct                  WHERE username='cjl'                  AND
acctstoptimeIS NOT NULL                  AND date_trunc('month', now())=date_trunc('month', 
acctstoptime)) AS subselect USING (radacctid);

--
Mathieu Arnold

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

$ time psql radius < mailinglist.sqlsessions | 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@qnet.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);
>
>