DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=subject:mime-version:content-type:from:in-reply-to:date:cc
	:content-transfer-encoding:message-id:references:to:x-mailer;
	b=rw6AqOUGVFW31Kfs/wURpiM2Cjc8Rp3kDZuyIwGQOb/ddkUIkHoXqq5dlgp81oe3az
	NNvsF6d4dEnsolBuDFagD+htl5BCyYQIRvmGzRpQ8Nw32/4WcSGGsMFR/W8cuJIJ/6Oy
	/yT0dUK51JhfW8E/3pt6es1AcMCgvB3lUOk7Y=
Subject: Re: equivalent queries lead to different query plans for self-joins
	with group by?
Mime-Version: 1.0 (Apple Message framework v1081)
Content-Type: text/plain; charset=us-ascii
From: Ben <midfield@gmail.com>
In-Reply-To: <23147.1289515069@sss.pgh.pa.us>
Date: Thu, 11 Nov 2010 15:56:31 -0800
Cc: pgsql-performance@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <5E705CB3-DF3D-4804-8F87-984D2041C9AC@gmail.com>
References: <5D061DAC-31E2-4C47-AE3B-7799118C57D3@gmail.com>
	<23147.1289515069@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>

appreciate the instant response.

> Well, arguably it's not doing the right thing either way --- you'd =
sort
> of like the inequalities to get pushed down into both of the join
> inputs, not just one of them.  PG doesn't make that deduction though;
> it can make such inferences for equalities, but inequalities are not
> optimized as much.

in my work i have replaced the query with a sql function + window :

create or replace function bar(timestamp, timestamp) returns setof foo
language 'sql' as $$
  select ts,
         id,
         val -
         (avg(val) over (partition by ts)) as val
  from foo
  where ts > $1
  and ts < $2
$$;

i was forced to use a sql function as opposed to a view because the =
query planner was unable to push down restrictions on ts inside the view =
subquery, which i've manually done in the function.  indeed,

explain select ts, id, val - (avg(val) over (partition by ts)) as val =
from foo where ts > '2009-10-20' and ts < '2009-10-21';

and

explain select * from (select ts, id, val - (avg(val) over (partition by =
ts)) as val from foo) as f where ts > '2009-10-20' and ts < =
'2009-10-21';

give different answers, despite being equivalent, but i understand it is =
hard to push things into subqueries in general.  in this case it is only =
legal because we partition by ts.

thanks again for the explanations!

best, ben