Re: Simple query: how to optimize

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 <cadiolis(at)gmail(dot)com> 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 <RHand(at)kailea(dot)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)
> >
>