X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id D0E50DB1F5
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 28 Oct 2005 20:56:38 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 00237-10
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 28 Oct 2005 23:56:35 +0000 (GMT)
Received: from xproxy.gmail.com (xproxy.gmail.com [66.249.82.204])
	by svr1.postgresql.org (Postfix) with ESMTP id 87DBDDB14B
	for <pgsql-performance@postgresql.org>;
	Fri, 28 Oct 2005 20:56:37 -0300 (ADT)
Received: by xproxy.gmail.com with SMTP id s14so382512wxc
	for <pgsql-performance@postgresql.org>;
	Fri, 28 Oct 2005 16:56:41 -0700 (PDT)
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=usAtfz/w8ly5eFeiSzn1m4dhte4KdiAlcCkBAozsQgnGpf5jUkebcTgZTARxrLvGGrlz0WQtd0g1ceNnrWozOo0DIRI5xD/sLcNFEc6HaT5qLlCa9qKoit6Hfi1fHXU+lDt8Gew2lqDZ67FBECerkb7MF7kn3DvZjsJb0BN7b5E=
Received: by 10.65.239.16 with SMTP id q16mr498895qbr;
	Fri, 28 Oct 2005 16:56:40 -0700 (PDT)
Received: by 10.65.157.7 with HTTP; Fri, 28 Oct 2005 16:56:40 -0700 (PDT)
Message-ID: <df01c91b0510281656gcae9b79r31fd096f0b31eefa@mail.gmail.com>
Date: Fri, 28 Oct 2005 16:56:40 -0700
From: Collin Peters <cadiolis@gmail.com>
Subject: Re: Simple query: how to optimize
Cc: pgsql-performance@postgresql.org
In-Reply-To: 
 <DB28E9B548192448A4E8C8A3C1B1E475611DAF@sj1-exch-01.us.corp.kailea.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>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.761 required=5 tests=[AWL=-0.760,
	MISSING_HEADERS=0.189, RCVD_IN_BL_SPAMCOP_NET=1.332]
X-Spam-Score: 0.761
X-Spam-Level: 
X-Archive-Number: 200510/446
X-Sequence-Number: 15214

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.=20
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 th=
e 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)
>