From: Bruno Wolff III <bruno@wolff.to>
To: Chris Linstruth <cjl@QNET.COM>
Cc: pgsql-sql@postgresql.org
Subject: Re: sub select performance
Date: Sun, 11 May 2003 12:51:52 -0500
Message-ID: <20030511175152.GC710@wolff.to> (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>
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.
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: bruno@wolff.to, cjl@QNET.COM
Subject: Re: sub select performance
In-Reply-To: <20030511175152.GC710@wolff.to>
* 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