Thomas Mayer <thomas.mayer@student.kit.edu> writes: > When querying a view with a WHERE condition, postgresql normally is able > to perform an index scan which reduces time for evaluation dramatically.
> However, if a window function is evaluated in the view, postgresql is > evaluating the window function before the WHERE condition is applied. > This induces a full table scan.
You haven't exactly provided full details, but it looks like you are thinking that WHERE clauses applied above a window function should be pushed to below it. A moment's thought about the semantics should convince you that such an optimization would be incorrect: the window function would see fewer input rows than it should, and therefore would (in general) return the wrong values for the selected rows.
It's possible that in the specific case you exhibit here, pushing down the clause wouldn't result in changes in the window function's output for the selected rows, but the optimizer doesn't have enough knowledge about window functions to determine that.
A restriction in the WHERE clause which corresponds to the PARTITION BY should be pushable, no? I think it doesn't need to understand the internal semantics of the window function itself, just of the PARTITION BY, which should be doable, at least in principle.
