From: Mathieu Arnold <mat@mat.cc>
To: Chris Linstruth <cjl@QNET.COM>
To: pgsql-sql@postgresql.org
Subject: Re: sub select performance
Date: Sun, 11 May 2003 20:53:46 +0200
Message-ID: <3465828.1052686426@sauron.in.mat.cc> (raw)
In-Reply-To: <Pine.BSI.4.33.0305110914360.21305-100000@cello.qnet.com>
References: <Pine.BSI.4.33.0305110914360.21305-100000@cello.qnet.com>
--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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mat@mat.cc, cjl@QNET.COM
Subject: Re: sub select performance
In-Reply-To: <3465828.1052686426@sauron.in.mat.cc>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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