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 <RHand@kailea.com> 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)
>