PostgreSQL: create index in timestamp :: DATE

Question

I create a pivot table that summarizes all the events for 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 is executed for a rather long time, even if it requests only two days of data with several events (January 1-2, 2014).

EXPLAIN in the query is rather gloomy - it starts a sequential scan in the event table, without using 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 that this is because we are comparing the cast value - creation_time::DATE , not creation_time . I tried indexing the cast:

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

But the error turned out:

 ERROR: syntax error at or near "::" 

Is there a way to use PostgreSQL indexes for a timezone column cast from DATE?

Answer 1

The expression in the index declaration must be enclosed in additional brackets, try:

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