Re: window function induces full table scan

On Thu, Jan 2, 2014 at 1:52 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Thomas Mayer <thomas(dot)mayer(at)student(dot)kit(dot)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