Re: window function induces full table scan

Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> On Thu, Jan 2, 2014 at 1:52 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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.

If the restriction clause must give the same answer for any two rows of
the same partition, then yeah, we could in principle push it down without
knowing anything about the specific window function. It'd be a less than
trivial test to make, I think. In any case, it's not a "bug" that the
optimizer doesn't do this currently.

regards, tom lane