Search Postgresql Archives

Re: need help optimizing query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Here's one without a subquery, so may be master:

select u.login, s.stop_time
  from users as a
    inner join stats as s
      on s.user_id = u.user_id
  where status = '3' 
    and next_plan_id is null
    and stop_time < now() - interval '1 month'
  group by u.user_id, u.login, s.stop_time
  order by s.stop_time

On 2/2/08, rihad <rihad@xxxxxxx> wrote:
Hi all,

The situation: there are users in one table, and their access statistics
in the other. Now I want to find users whose last access time was more
than one month ago. As I've only had to write quite simple queries
involving no sub-selects so far, I'd like to ask your opinion if this
one scales at all or not.

SELECT u.login,last_use_time
FROM users u
JOIN (SELECT user_id, MAX(stop_time) AS last_use_time
       FROM stats
       GROUP BY user_id) AS s ON (u.id=s.user_id)
WHERE status='3' AND next_plan_id IS NULL
   AND last_use_time < now() - interval '1 month'
ORDER BY last_use_time;

It seems to do the job, but how good is it in the long run? Any way I
could tweak it?

Thanks.


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend



--
Sincerely yours,
Olexandr Melnyk <><
http://omelnyk.net/

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux