Re: range intervals in window function frames

Tom Lane writes:
> 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.

Thanks, Tom, that explains it. EXPLAIN ANALYZE with the re-written
WHERE brings the inline version down to 8.5 seconds, still twice as
slow, but that's a heck of a lot better than 122 times as slow! :)

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

Not terribly, but it's good to discover the function version is twice
as fast. (not to mention that the function is much easier to read.)

> > 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.

Bummer. I may go ask in hackers.

Thanks,

Dan