On Thu, Jan 2, 2014 at 1:52 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Thomas Mayer <thomas.ma...@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. Cheers, Jeff
