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
