PostgreSQL: Index the day part of a timestamp

Question

Consider the following table:

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

Queries like the following (let alone more complicated JOINs) takes quite a while, because they needs to calculate creation_time::DATE for each item:

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

How do I create an index on the day part of the timestamp - creation_time::DATE?

I have 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 with:

ERROR:  syntax error at or near "::"

Answer 1

When you create an index on an expression that expression must be put between parentheses (in addition to the parentheses that surround the column/expression list:

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