Re: Missing optimization when filters are applied after window functions

On Wed, May 16, 2012 at 12:50 AM, Volker Grabsch <vog(at)notjusthosting(dot)com> wrote:
> I propose the following general optimization: If all window
> functions are partitioned by the same first field (here: id),
> then any filter on that field should be executed before
> WindowAgg. So a query like this:

I think that's possible. Currently the planner doesn't think any
qualification from the upper query can be pushed down to a query that
has a window function. It would be possible to let it push down if
the expression matches PARTITION BY expression. However, the
challenge is that a query may have a number of window functions that
have different PARTITION BY expressions. At the time of pushing down
in the planning, it is not obvious which window function comes first.
One idea is to restrict such optimization in only case of single
window function, and the other is to make it generalize and cover a
lot of cases.

That said, our planner on window functions has a lot of improvement to
be done. Every kind of optimization I see is what I raised above;
they can be done easily by hacking in a small case, or they can be
done by generalizing for the most of cases. My understanding is our
project tends to like the latter and it takes a little time but covers
more use cases.

Thanks,
--
Hitoshi Harada