Daniel Popowich <danielpopowich@gmail.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
