result.sql

Here query

EXPLAIN (ANALYZE, BUFFERS) SELECT
  date_trunc('day', install_time) AS d,
  COUNT (*) AS agg
FROM
  application_events
WHERE
  application_id = 9
AND event_type = 'install'
AND install_time >= '2015-10-01 0:0+0'
AND install_time < '2015-10-15 0:0+0'
GROUP BY
  1;

and result

HashAggregate  (cost=228427.86..228437.15 rows=743 width=8) (actual time=57436.103..57436.120 rows=14 loops=1)
  Buffers: shared hit=171 read=140741 dirtied=44 written=125
  ->  Result  (cost=27269.18..228424.14 rows=743 width=8) (actual time=4538.367..57427.408 rows=2308 loops=1)
        Buffers: shared hit=171 read=140741 dirtied=44 written=125
        ->  Append  (cost=27269.18..228422.29 rows=743 width=8) (actual time=4520.617..57390.617 rows=2308 loops=1)
              Buffers: shared hit=171 read=140741 dirtied=44 written=125
              ->  Bitmap Heap Scan on application_events  (cost=27269.18..101307.37 rows=734 width=8) (actual time=4520.615..33895.824 rows=2308 loops=1)
                    Recheck Cond: ((application_id = 9) AND (install_time >= '2015-10-01 00:00:00+00'::timestamp with time zone) AND (install_time < '2015-10-15 00:00:00+00'::timestamp with time zone))
                    Rows Removed by Index Recheck: 1397097
                    Filter: (event_type = 'install'::text)
                    Rows Removed by Filter: 40876
                    Buffers: shared hit=171 read=78046 dirtied=44 written=125
                    ->  BitmapAnd  (cost=27269.18..27269.18 rows=25915 width=0) (actual time=4341.661..4341.661 rows=0 loops=1)
                          Buffers: shared hit=171 read=3703 written=125
                          ->  Bitmap Index Scan on application_events_application_id  (cost=0.00..3105.59 rows=163887 width=0) (actual time=3579.972..3579.972 rows=176908 loops=1)
                                Index Cond: (application_id = 9)
                                Buffers: shared hit=6 read=502
                          ->  Bitmap Index Scan on application_events_install_time  (cost=0.00..24162.98 rows=1158655 width=0) (actual time=734.576..734.576 rows=1229616 loops=1)
                                Index Cond: ((install_time >= '2015-10-01 00:00:00+00'::timestamp with time zone) AND (install_time < '2015-10-15 00:00:00+00'::timestamp with time zone))
                                Buffers: shared hit=165 read=3201 written=125
              ->  Bitmap Heap Scan on application_events_y2015m09  (cost=1702.64..127114.92 rows=9 width=8) (actual time=23491.760..23491.760 rows=0 loops=1)
                    Recheck Cond: (application_id = 9)
                    Filter: ((install_time >= '2015-10-01 00:00:00+00'::timestamp with time zone) AND (install_time < '2015-10-15 00:00:00+00'::timestamp with time zone) AND (event_type = 'install'::text))
                    Rows Removed by Filter: 99474
                    Buffers: shared read=62695
                    ->  Bitmap Index Scan on application_events_y2015m09_application_id_idx  (cost=0.00..1702.64 rows=101095 width=0) (actual time=359.214..359.214 rows=99474 loops=1)
                          Index Cond: (application_id = 9)
                          Buffers: shared read=232
Total runtime: 57438.392 ms