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