Re: equivalent queries lead to different query plans for self-joins with group by?

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
onts 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