PostgreSQL: Create an index on timestamp::DATE [duplicate]

Question

I am creating a summary table that sums up all events in a given day.

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; 

The creation_time column is indexed:

CREATE INDEX event_creation_time_date_idx ON event USING BTREE(creation_time); 

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).

The EXPLAIN on the query is pretty grim - it runs a sequential scan on the event table, not utilizing the index at all:

->  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)))) 

I assume this is because we compare a casted value - creation_time::DATE, not creation_time. I have tried indexing the cast:

CREATE INDEX event_creation_time_date_idx ON event USING BTREE(creation_time::DATE); 

But got an error:

ERROR: syntax error at or near "::" 

Is there a way to utilize PostgreSQL indices on a timezone column casted to DATE?

Answer 1

An expression in an index declaration should be enclosed in additional brackets, try:

CREATE INDEX event_creation_time_date_idx ON event ((creation_time::DATE));