gistfile1.txt

trucoin_rails_development=#  explain analyze select to_timestamp(start_time * 86400) start_time_tz, to_timestamp((start_time + 1) * 86400) end_time_tz, sum(amount) as volume, min(price) as low, max(price) as high, coalesce(lag(max(last_price)) over (order by start_time), max(first_price)) as open, max(last_price) as close
from (
  select date/86400 as start_time, amount, price, first_value(price) over w as first_price, last_value(price) over w as last_price
  from trades
  where date/86400 >= ((1369176488/86400) - 120)
  and exchange_id = 1
  window w as (partition by date/86400 order by date rows between unbounded preceding and unbounded following)
) s
group by start_time
order by start_time;
                                                                                  QUERY PLAN                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 WindowAgg  (cost=411663.46..501777.17 rows=200 width=85) (actual time=2919.871..12270.549 rows=121 loops=1)
   ->  GroupAggregate  (cost=411663.46..501770.67 rows=200 width=85) (actual time=2919.734..12266.962 rows=121 loops=1)
         ->  WindowAgg  (cost=411663.46..454569.03 rows=1716223 width=21) (actual time=2908.839..10708.375 rows=1870032 loops=1)
               ->  Sort  (cost=411663.46..415954.02 rows=1716223 width=21) (actual time=2906.511..3244.771 rows=1870032 loops=1)
                     Sort Key: ((trades.date / 86400)), trades.date
                     Sort Method: external merge  Disk: 77832kB
                     ->  Bitmap Heap Scan on trades  (cost=39769.75..128353.66 rows=1716223 width=21) (actual time=163.298..786.039 rows=1870032 loops=1)
                           Recheck Cond: ((date / 86400) >= 15726)
                           Filter: (exchange_id = 1)
                           ->  Bitmap Index Scan on trades_partition_by_86400  (cost=0.00..39340.70 rows=1716223 width=0) (actual time=161.649..161.649 rows=1870032 loops=1)
                                 Index Cond: ((date / 86400) >= 15726)
 Total runtime: 12285.613 ms
(12 rows)