pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsub select performance
4+ messages / 3 participants
[nested] [flat]
* sub select performance
@ 2003-05-11 16:48 Chris Linstruth <cjl@QNET.COM>
0 siblings, 2 replies; 4+ messages in thread
From: Chris Linstruth @ 2003-05-11 16:48 UTC (permalink / raw)
To: 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 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)".
Thanks.
--
Chris Linstruth <cjl@qnet.com>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: sub select performance
@ 2003-05-11 17:51 Bruno Wolff III <bruno@wolff.to>
parent: Chris Linstruth <cjl@QNET.COM>
1 sibling, 0 replies; 4+ messages in thread
From: Bruno Wolff III @ 2003-05-11 17:51 UTC (permalink / raw)
To: Chris Linstruth <cjl@QNET.COM>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: sub select performance
@ 2003-05-11 18:53 Mathieu Arnold <mat@mat.cc>
parent: Chris Linstruth <cjl@QNET.COM>
1 sibling, 1 reply; 4+ messages in thread
From: Mathieu Arnold @ 2003-05-11 18:53 UTC (permalink / raw)
To: Chris Linstruth <cjl@QNET.COM>; pgsql-sql
--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);
--
Mathieu Arnold
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: sub select performance
@ 2003-05-12 01:05 Chris Linstruth <cjl@QNET.COM>
parent: Mathieu Arnold <mat@mat.cc>
0 siblings, 0 replies; 4+ messages in thread
From: Chris Linstruth @ 2003-05-12 01:05 UTC (permalink / raw)
To: Mathieu Arnold <mat@mat.cc>; +Cc: pgsql-sql
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@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);
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-05-12 01:05 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-11 16:48 sub select performance Chris Linstruth <cjl@QNET.COM>
2003-05-11 17:51 ` Bruno Wolff III <bruno@wolff.to>
2003-05-11 18:53 ` Mathieu Arnold <mat@mat.cc>
2003-05-12 01:05 ` Chris Linstruth <cjl@QNET.COM>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox