Postgres timestamp index performance

Question

I was wondering, when dealing with large tables (> 100 million rows)

Does Postgres handle work well with indexing timestamps
(e.g: 2018-10-09T12:41:20.276846Z)?
Will it improve performance if I truncate the date to the seconds if I don't care about that much precision when selecting?

Are you looking for an exact value match, or for a range of values? Can you give some examples of the queries you will be using? — David Aldridge
– David Aldridge, Commented Oct 9, 2018 at 15:23
Welcome to Stack Overflow! At StackOverflow, we are helping developers to get stronger. However, we won't never let company get rid of them by requesting free jobs on this forum. So in order to distinct yourself from these sharks, show us some effort, pieces of code, some bugs, or other... And then we will help you! Please take the tour and read "What types of questions should I avoid asking?" help center, "What topics can I ask about here?" help center and minimal reproducible example! — Jonathan Gagne
– Jonathan Gagne, Commented Oct 9, 2018 at 15:35

Laurenz Albe · Accepted Answer · 2018-10-09 15:35:25Z

3

Indexes on timestamps work very well in PostgreSQL, because timestamps have a total ordering.

No matter what precision your timestamp value has, it will always occupy 4 bytes, so the precision has no influence on the index size or performance.

You have to make sure that your index can be used with the condition in your queries. For that, the condition must be of the form

<indexed expression> <operator> <constant>

where <constant> need not really be a constant, but have the same value for the duration of the index scan.

<operator> can be =, <, >, <= or >=.

answered Oct 9, 2018 at 15:35

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

More info on date/time storage requirements: postgresql.org/docs/9.2/static/…

a common rewrite I find myself doing is using ts < now() - '30 days' instead of ts + interval '30 days' < now()