PostgreSQL: Create an index on timestamp::DATE
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));
Adam Matan
Team leader, developer, and public speaker. I build end-to-end apps using modern cloud infrastructure, especially serverless tools. My current position is R&D Manager at Corvid by Wix.com, a serverless platform for rapid web app generation. My CV and contact details are available on my Github README.
Updated on January 12, 2020Comments
-
Adam Matan over 4 years
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 theevent
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
, notcreation_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?