DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=PQZ29jsfgywZ4X/WX5jTImfChkcatvU+oyjTBXfeetMG0rulFtMRkfLUiVV8wP9SlA7oOgDDlLfEwJhrwJKJNZTAJlPIr73seKaC6qOZSp031QUg8qOMD+x91tjXu7cKkFd1gnly1asEsZXLAMfM5wn3RgnJIP8Wttv5WdLuY9s=
Message-ID: <df01c91b0510281704u552a6b2ree60eec6b371ef74@mail.gmail.com>
Date: Fri, 28 Oct 2005 17:04:32 -0700
From: Collin Peters <cadiolis@gmail.com>
Subject: Re: Simple query: how to optimize
Cc: pgsql-performance@postgresql.org
In-Reply-To: <df01c91b0510281656gcae9b79r31fd096f0b31eefa@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: 
 <DB28E9B548192448A4E8C8A3C1B1E475611DAF@sj1-exch-01.us.corp.kailea.com>
	<df01c91b0510281656gcae9b79r31fd096f0b31eefa@mail.gmail.com>

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@gmail.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@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 =3D ua.user_id AND
> > > ua.user_activity_type_id =3D 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 =3D ua.user_id
> >   AND ua.user_activity_type_id =3D 7)
> > GROUP BY users.user_id
> >
> > (disclaimer: I haven't actually tried this sql)
> >
>