Re: range intervals in window function frames

Daniel Popowich <danielpopowich(at)gmail(dot)com> writes:
> Close. Your where clause needed to have (ts<=t1.ts). It can also be
> simplified to this:

> select t1.ts, t1.value, (select avg(t2.value)
> from sample t2
> where (t1.ts - t2.ts) <= interval '5 min'
> and t2.ts <= t1.ts)
> from sample t1 order by t1.ts;

> HOWEVER, the performance is horrible compared to using the
> avg_over_interval() function!

The reason for that is the WHERE clause got rewritten into a form that
can't be used efficiently with the index on t2. Phrase it the same way
as in the function, ie

where (t1.ts - interval '5 min') <= t2.ts
and t2.ts <= t1.ts

and you'll probably get similar results. Of course, since this isn't
anything except inlining the function into the query, it's probably not
all that exciting to you.

> Can anyone answer when range intervals will be implemented for window
> functions, as in the quoted select at the top of this message?

Nope. There was a patch submitted, it was rejected on a couple of
grounds, and I don't know if anyone is actively working on the problem
or not.

regards, tom lane