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