Search code examples
sqlpostgresqltimestampquery-optimizationinner-join

PostgreSQL - Better to join with a TIMESTAMP field, or should I convert to a unix_timestamp (int) to join?


I am creating a join, and as part of this join I am using a timestamp field to join with. Performance wise, would I be better converting both timestamps to a unix_timestamp, so that the join is using a integer? Or would I be better leaving as is.


Solution

  • I would not expect any noticeable performance difference depending on whether the datatype of the column is timestamp or integer. As commented by a_horse_with_no_name, a timestamp is internally stored as an integer anyway.

    An important thing to note: performance-wise, you want to use the raw data directly, without applying any transformation.

    A predicate that uses functions makes the query non-SARGable, meaning that the database cannot take advantage of an index, even if there is one available:

    extract(epoch from t1.timestamp) = extract(epoch from t2.timestamp)
    

    Instead, you do want to use the column values directly:

    t1.timestamp = t2.timestamp