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 "::"

user330315 · Accepted Answer · 2015-12-15 15:40:44Z

28

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

answered Dec 15, 2015 at 15:40

user330315

Sign up to request clarification or add additional context in comments.

5 Comments

Adam Matan Over a year ago

Thanks. Now I get ERROR: functions in index expression must be marked IMMUTABLE. Funny, because the day part of a date is as immutable as it gets.

user330315 Over a year ago

@AdamMatan: ah, I overlooked the data type. The conversion of a timestamp with time zone to a date is not "immutable" (because it depends on the time zone). This would only work if you were using timestamp without time zone

Adam Matan Over a year ago

Any way to make the conversion in the index?

user330315 Over a year ago

@AdamMatan: this has been asked before. See e.g. here: stackoverflow.com/q/5973030/330315

Adam Matan Over a year ago

Thanks, will refer to that one.

Mark · Accepted Answer · 2018-11-20 19:04:51Z

0

This worked, but I noticed that the index using an expression to cast a timestamp to a date used more disk space (~15%-20%) than an index on the timestamp.

I hoped for a disk space reduction in building an index on a 4 byte date over a 8 byte timestamp, but it seems that's not the case because 8 bytes seems to be the lowest common denominator for an element in the index. So, the disk use was worse, and the query performance was about the same, so I abandoned this approach.

answered Nov 20, 2018 at 19:04

Mark

5386 silver badges18 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: Index the day part of a timestamp

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related