[PERFORM] Simple query: how to optimize

[Collin Peters]

I have two tables, one is called 'users' the other is 'user_activity'.
 The 'users' table simply contains the users in the system there is
about 30,000 rows.  The 'user_activity' table stores the activities
the user has taken.  This table has about 430,000 rows and also
(notably) has a column which tracks the type of activity.  90% of the
table is type 7 which indicates the user logged into the system.

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

The above query takes about 5 seconds but I'm wondering how it can be
optimized.  When the query is formatted as above it does use an index
on the user_id column of the user_activity table... but the cost is
huge (cost=0.00..1396700.80).

I have tried formatting it another way with a sub-query but it takes
about the same amount to completed:

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

Can anybody offer any pointers on this scenario?

Regards,
Collin

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PERFORM] Simple query: how to optimize

[Collin Peters]

These two queries execute at exactly the same speed.  When I run run
EXPLAIN on them both they return the *exact* same query plan as well. 
I find this strange... but it is also kind of what I expected from
reading up on various things.  I am under the impression the
postgresql will break up your query and run it as it sees best.   So
in the case of these two queries... it seems it is actually almost
converting one into the other.  Maybe I am wrong.

Is there a good resource list somewhere for postgresql query
optimization?  There are entire books devoted to the subject for
oracle but I can't find more than a few small articles on postgresql
query optimizations on the web.

Regards,
Collin

On 10/28/05, Roger Hand [EMAIL PROTECTED] wrote:
  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

Re: [PERFORM] Simple query: how to optimize

[Collin Peters]

A little bit more on my last post that I forget to mention.  The two
queries run at the same speed and have the same plan only if I have an
index on the user_activity.user_id column.  Otherwise they run at
different speeds.  The query you gave me actually runs slower without
the index.  All  this is making my head spin!! :O

On 10/28/05, Collin Peters [EMAIL PROTECTED] wrote:
 These two queries execute at exactly the same speed.  When I run run
 EXPLAIN on them both they return the *exact* same query plan as well.
 I find this strange... but it is also kind of what I expected from
 reading up on various things.  I am under the impression the
 postgresql will break up your query and run it as it sees best.   So
 in the case of these two queries... it seems it is actually almost
 converting one into the other.  Maybe I am wrong.

 Is there a good resource list somewhere for postgresql query
 optimization?  There are entire books devoted to the subject for
 oracle but I can't find more than a few small articles on postgresql
 query optimizations on the web.

 Regards,
 Collin

 On 10/28/05, Roger Hand [EMAIL PROTECTED] wrote:
   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 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly