title: PostgreSQL aggregate query is very slow
body: <p>I have a table, which contains a <code>timestamp</code> column and a <code>source</code> column <code>varchar(20)</code>. I insert a couple thousand entries into this table every hour and I would like to show an aggregate on this data. My query looks like this:</p>
  
  <pre><code>EXPLAIN (analyze, buffers) SELECT
      count(*) AS count
  FROM frontend_car c
  WHERE date_created at time zone 'cet' &gt; now() at time zone 'cet' - interval '1 week'
  GROUP BY source, date_trunc('hour', c.date_created at time zone 'CET')
  ORDER BY source ASC, date_trunc('hour', c.date_created at time zone 'CET') DESC
  </code></pre>
  
  <p>I have already created an index like so:</p>
  
  <pre><code>create index source_date_created on
  table_name(
      (date_created AT TIME ZONE 'CET') DESC,
      source ASC,
      date_trunc('hour', date_created at time zone 'CET') DESC
  );
  </code></pre>
  
  <p>And the output of my <code>ANALYZE</code> is:</p>
  
  <pre><code>    QUERY PLAN
  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
   Sort  (cost=142888.08..142889.32 rows=495 width=16) (actual time=10242.141..10242.188 rows=494 loops=1)
     Sort Key: source, (date_trunc('hour'::text, timezone('CET'::text, date_created)))
     Sort Method: quicksort  Memory: 63kB
     Buffers: shared hit=27575 read=28482
     -&gt;  HashAggregate  (cost=142858.50..142865.93 rows=495 width=16) (actual time=10236.393..10236.516 rows=494 loops=1)
           Group Key: source, date_trunc('hour'::text, timezone('CET'::text, date_created))
           Buffers: shared hit=27575 read=28482
           -&gt;  Bitmap Heap Scan on frontend_car c  (cost=7654.61..141002.20 rows=247507 width=16) (actual time=427.894..10122.438 rows=249056 loops=1)
                 Recheck Cond: (timezone('cet'::text, date_created) &gt; (timezone('cet'::text, now()) - '7 days'::interval))
                 Rows Removed by Index Recheck: 141143
                 Heap Blocks: exact=27878 lossy=26713
                 Buffers: shared hit=27575 read=28482
                 -&gt;  Bitmap Index Scan on frontend_car_source_date_created  (cost=0.00..7592.74 rows=247507 width=0) (actual time=420.415..420.415 rows=249056 loops=1)
                       Index Cond: (timezone('cet'::text, date_created) &gt; (timezone('cet'::text, now()) - '7 days'::interval))
                       Buffers: shared hit=3 read=1463
   Planning time: 2.430 ms
   Execution time: 10242.379 ms
  (17 rows)
  </code></pre>
  
  <p>Clearly this is too slow and in my mind it should be able to be computed only using indexes, if I use only either time or source for aggregation, it is reasonably fast, but together somehow its slow.</p>
  
  <p>This is on a rather small VPS with only 512MB of ram and the database presently contains about 700k rows. </p>
  
  <p>From what I read it seems that the majority of time is spent on recheck, which means that the index did not fit in memory?</p>