pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chris Linstruth <cjl@QNET.COM>
To: Mathieu Arnold <mat@mat.cc>
Cc: pgsql-sql@postgresql.org
Subject: Re: sub select performance
Date: Sun, 11 May 2003 18:05:06 -0700 (PDT)
Message-ID: <Pine.BSI.4.33.0305111802140.5852-100000@cello.qnet.com> (raw)
In-Reply-To: <3465828.1052686426@sauron.in.mat.cc>

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




view thread (4+ messages)

Message-ID: <Pine.BSI.4.33.0305111802140.5852-100000@cello.qnet.com>
Permalink:  ../Pine.BSI.4.33.0305111802140.5852-100000@cello.qnet.com/
Also on:    postgresql.org/message-id/Pine.BSI.4.33.0305111802140.5852-100000@cello.qnet.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: cjl@QNET.COM, mat@mat.cc
  Subject: Re: sub select performance
  In-Reply-To: <Pine.BSI.4.33.0305111802140.5852-100000@cello.qnet.com>
  References: <3465828.1052686426@sauron.in.mat.cc> <Pine.BSI.4.33.0305111802140.5852-100000@cello.qnet.com>

* 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