title: Speeding up a group by date query on a big table in postgres
body: <p>I've got a table with around 20 million rows. For arguments sake, lets say there are two columns in the table - an id and a timestamp. I'm trying to get a count of the number of items per day. Here's what I have at the moment.</p>
  
  <pre><code>  SELECT DATE(timestamp) AS day, COUNT(*)
      FROM actions
     WHERE DATE(timestamp) &gt;= '20100101'
       AND DATE(timestamp) &lt;  '20110101'
  GROUP BY day;
  </code></pre>
  
  <p>Without any indices, this takes about a 30s to run on my machine. Here's the explain analyze output:</p>
  
  <pre><code> GroupAggregate  (cost=675462.78..676813.42 rows=46532 width=8) (actual time=24467.404..32417.643 rows=346 loops=1)
     -&gt;  Sort  (cost=675462.78..675680.34 rows=87021 width=8) (actual time=24466.730..29071.438 rows=17321121 loops=1)
           Sort Key: (date("timestamp"))
           Sort Method:  external merge  Disk: 372496kB
           -&gt;  Seq Scan on actions  (cost=0.00..667133.11 rows=87021 width=8) (actual time=1.981..12368.186 rows=17321121 loops=1)
                 Filter: ((date("timestamp") &gt;= '2010-01-01'::date) AND (date("timestamp") &lt; '2011-01-01'::date))
   Total runtime: 32447.762 ms
  </code></pre>
  
  <p>Since I'm seeing a sequential scan, I tried to index on the date aggregate</p>
  
  <pre><code>CREATE INDEX ON actions (DATE(timestamp));
  </code></pre>
  
  <p>Which cuts the speed by about 50%.</p>
  
  <pre><code> HashAggregate  (cost=796710.64..796716.19 rows=370 width=8) (actual time=17038.503..17038.590 rows=346 loops=1)
     -&gt;  Seq Scan on actions  (cost=0.00..710202.27 rows=17301674 width=8) (actual time=1.745..12080.877 rows=17321121 loops=1)
           Filter: ((date("timestamp") &gt;= '2010-01-01'::date) AND (date("timestamp") &lt; '2011-01-01'::date))
   Total runtime: 17038.663 ms
  </code></pre>
  
  <p>I'm new to this whole query-optimization business, and I have no idea what to do next. Any clues how I could get this query running faster?</p>
  
  <p><strong>--edit--</strong></p>
  
  <p>It looks like I'm hitting the limits of indices. This is pretty much the only query that gets run on this table (though the values of the dates change). Is there a way to partition up the table? Or create a cache table with all the count values? Or any other options?</p>