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