CODEWITHSUNDEEP
postgresql
Samuel Bodin
Samuel Bodin

Reputation: 15

Postgres timestamp index performance

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

  1. Does Postgres handle work well with indexing timestamps
    (e.g: 2018-10-09T12:41:20.276846Z)?

  2. Will it improve performance if I truncate the date to the seconds if I don't care about that much precision when selecting?

Upvotes: 0

Views: 1354

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246308

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 >=.

Upvotes: 3

Related Questions