Re: Push predicate down in view containing window function

On Fri, Nov 15, 2013 at 12:43 AM, Philippe Girolami
<philippe(dot)girolami(at)sensorly(dot)com> wrote:
> Ok so is there a way i can do something similar ? Would a function returning rows and taking the extra predicate 'values' as parameters be as optimized as the 'good' query in my first email ?

There is only one way I know of to do it (force a qual into a view
wrapped into a subquery). It's tweaky, particularly with pre-9.3
LATERAL. The basic MO is to put the window function into SQL function
in order to be able to force the qual into the inner query with a
function parameter. Then, you make a view that cross products the
possible arguments to the function and LATERALS them into the set
returning function (it's possible, but difficult, to do it without
lateral). As long as the 'arguments' expressed in the view are always
specified in the query that hits the view performance should be good.

If this sounds like something you'd like to tackle, maybe I can work
up an example.

merlin