Re: range intervals in window function frames

Vincent Veyron writes:
> > What I would LIKE to do is this:
> >
> >   select *, avg(ts) over(order by ts range (interval '5 min') preceding)
> >          from sample order by ts;
> >
>
> This?
>
>
> select t1.ts, t1.value, (select avg(t2.value) from (select value from
> sample where (t1.ts-ts)::INTERVAL <= interval '5 minutes' and ts<t1.ts)
> as t2) from sample t1;

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!  On a table with 53,622 rows:

   EXPLAIN ANALYZE [the above select statement];

     ...
     Total runtime: 481235.867 ms

But this:

   EXPLAIN ANALYZE select ts, value,
                          avg_over_interval(ts, interval '5 min')
                          from sample order by ts;

     ...
     Total runtime: 3934.755 ms

8 minutes vs 4 seconds!

I'm imagining window functions would be even more efficient.  Yes?
Can anyone answer when range intervals will be implemented for window
functions, as in the quoted select at the top of this message?


Cheers,

Dan