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
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
