candlestick data function

explain

                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Limit  (cost=2669841.24..4708620.21 rows=120 width=37)
   CTE five_minute_intervals
     ->  Function Scan on generate_series n  (cost=4901.85..4944.35 rows=1000 width=4)
           InitPlan 1 (returns $0)
             ->  Aggregate  (cost=1584.69..1584.70 rows=1 width=8)
                   ->  Seq Scan on trades  (cost=0.00..1436.95 rows=59095 width=8)
           InitPlan 2 (returns $1)
             ->  Aggregate  (cost=1584.69..1584.70 rows=1 width=8)
                   ->  Seq Scan on trades  (cost=0.00..1436.95 rows=59095 width=8)
           InitPlan 3 (returns $2)
             ->  Aggregate  (cost=1732.43..1732.44 rows=1 width=8)
                   ->  Seq Scan on trades  (cost=0.00..1436.95 rows=59095 width=8)
   ->  GroupAggregate  (cost=2664896.89..6062861.83 rows=200 width=37)
         Filter: (sum(t.amount) > 0::numeric)
         ->  Sort  (cost=2664896.89..2681312.16 rows=6566111 width=37)
               Sort Key: i.start_time, i.end_time
               ->  Nested Loop Left Join  (cost=0.00..1382767.19 rows=6566111 width=37)
                     Join Filter: ((t.date_as_datetime >= i.start_time) AND (t.date_as_datetime < i.end_time))
                     ->  CTE Scan on five_minute_intervals i  (cost=0.00..20.00 rows=1000 width=16)
                     ->  Materialize  (cost=0.00..2079.43 rows=59095 width=21)
                           ->  Seq Scan on trades t  (cost=0.00..1436.95 rows=59095 width=21)
(21 rows)

gistfile1.sql

with five_minute_intervals as (
  select 
    (select min(date_as_datetime)::date from trades) + ( n    || ' minutes')::interval start_time,
    (select min(date_as_datetime)::date from trades) + ((n+5) || ' minutes')::interval end_time
  from generate_series(0, ((select max(date_as_datetime)::date - min(date_as_datetime)::date from trades) + 1)*24*60, 5) n
)
select i.start_time, i.end_time, sum(t.amount) volume, min(t.price) low, max(t.price) high, first(t.price order by date_as_datetime), last(t.price order by date_as_datetime)
from trades t
right join five_minute_intervals i
             on t.date_as_datetime >= i.start_time and t.date_as_datetime < i.end_time
group by i.start_time, i.end_time
having sum(t.amount) > 0
order by i.start_time DESC
limit 120;