From: | "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | "Adam Rich" <adam(dot)r(at)sbcglobal(dot)net> |
Cc: | rihad(at)mail(dot)ru, pgsql-general(at)postgresql(dot)org |
Subject: | Re: help optimizing query |
Date: | 2008-02-10 06:42:16 |
Message-ID: | dcc563d10802092242v7309c860he337f4dcd4476927@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Feb 9, 2008 8:04 PM, Adam Rich <adam(dot)r(at)sbcglobal(dot)net> wrote:
> > It seems to do the job, but how good is it in the long run? Any way I
> > could tweak it?
>
>
> I think this form will work the best:
>
>
> SELECT u.login, MAX(s.stop_time) AS last_use_time
> FROM users u, stats s
> WHERE u.id=s.user_id
> AND u.status='3' AND u.next_plan_id IS NULL
If only ba small number of fields have next_plan as null, an they
correlate to the status normally, then an index on state where
next_plan_id is null might help here.
> GROUP BY u.login
> HAVING MAX(s.stop_time) < (now() - interval '1 month')
> ORDER BY last_use_time;
From | Date | Subject | |
---|---|---|---|
Next Message | Lew | 2008-02-10 08:08:08 | Re: "advanced" database design (long) |
Previous Message | Tom Lane | 2008-02-10 06:12:39 | Re: Lockless pg_buffercache |