PostgreSQL: index the daily portion of a timestamp

Question

Consider the following table:

Column | Type | --------------------+--------------------------+ id | bigint | creation_time | timestamp with time zone | ... 

Queries like the following (not to mention the more complex JOINs) take a lot of time because they need to calculate the create_time :: DATE value for each element:

 SELECT creation_time::DATE, COUNT(*) FROM items GROUP BY 1; 

How to create an index in the daytime part of a timestamp - creation_time::DATE ?

I tried:

• CREATE INDEX items_day_of_creation_idx ON items (creation_time)::date;
• CREATE INDEX items_day_of_creation_idx ON items (creation_time::date);

But both failed:

 ERROR: syntax error at or near "::" 

Answer 1

When you create an index in an expression, the expression should be placed between the parentheses (in addition to the parentheses that surround the list of columns / expressions:

 CREATE INDEX items_day_of_creation_idx ON items ( (creation_time::date) );