title: PostgreSQL: Create an index on timestamp::DATE
body: <p>I am creating a summary table that sums up all events in a given day.</p>
  
  <pre><code>INSERT INTO graph_6(
    day,
    event_type,
    (SELECT COUNT(*) FROM event e 
                    WHERE event_type = e.event_type 
                    AND creation_time::DATE = sq.day)
  FROM event_type
  CROSS JOIN
      (SELECT generate_series(
                  (SELECT '2014-01-01'::DATE),
                  (SELECT '2014-01-02'::DATE),
                  '1 day') as day) sq;
  </code></pre>
  
  <p>The <code>creation_time</code> column is indexed:</p>
  
  <pre><code>CREATE INDEX event_creation_time_date_idx ON event USING BTREE(creation_time);
  </code></pre>
  
  <p>However, the query runs a pretty long time even when only querying two days of data with a handful of events (January 1-2 2014).</p>
  
  <p>The <code>EXPLAIN</code> on the query is pretty grim - it runs a sequential scan on the <code>event</code> table, not utilizing the index at all:</p>
  
  <pre><code>-&gt;  Seq Scan on event e_1  (cost=0.00..12557.39 rows=531 width=38)
  Filter: ... AND ((creation_time)::date = (generate_series(($12)::timestamp with time zone, ($13)::timestamp with time zone, '1 day'::interval))))
  </code></pre>
  
  <p>I assume this is because we compare a casted value - <code>creation_time::DATE</code>, not <code>creation_time</code>. I have tried indexing the cast:</p>
  
  <pre><code>CREATE INDEX event_creation_time_date_idx ON event USING BTREE(creation_time::DATE);
  </code></pre>
  
  <p>But got an error:</p>
  
  <pre><code>ERROR: syntax error at or near "::"
  </code></pre>
  
  <p><strong>Is there a way to utilize PostgreSQL indices on a timezone column casted to DATE?</strong></p>