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
