Re: [PERFORM] Simple query: how to optimize

[Roger Hand]

On October 28, 2005 2:54 PM
Collin Peters wrote:
> I have two tables, one is called 'users' the other is 'user_activity'.
...
> I am trying to write a simple query that returns the last time each
> user logged into the system.  This is how the query looks at the
> moment:
> 
> SELECT u.user_id, MAX(ua.activity_date)
> FROM pp_users u
> LEFT OUTER JOIN user_activity ua ON (u.user_id = ua.user_id AND
> ua.user_activity_type_id = 7)
> WHERE u.userstatus_id <> 4
> AND age(u.joined_date) < interval '30 days'
> GROUP BY u.user_id

You're first joining against the entire user table, then filtering out the users
you don't need.

Instead, filter out the users you don't need first, then do the join:

SELECT users.user_id, MAX(ua.activity_date)
FROM 
(SELECT u.user_id 
FROM pp_users u
WHERE u.userstatus_id <> 4
AND age(u.joined_date) < interval '30 days'
) users
LEFT OUTER JOIN user_activity ua 
  ON (users.user_id = ua.user_id 
  AND ua.user_activity_type_id = 7)
GROUP BY users.user_id

(disclaimer: I haven't actually tried this sql)

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