For some reason, Postgres (15.3) isn't using a multicolumn index on (an_id, start_ts, end_ts) when OVERLAPS operator is used in a WHERE predicate:
WHERE (start_ts, end_ts) OVERLAPS (input_ts_start, input_ts_end)
With the following rewrite though, the planner can use the above multicolumn index. But, is above OVERLAPS predicate equivalent to this?
WHERE input_ts_start < end_ts AND input_ts_end > start_ts
Presuming that both _ts pairs are internally ordered (start_ts < end_ts and input_ts_start < input_ts_end) is the 2nd version equivalent? (Postgres' source is more elaborate since it handles cases where any two _ts pairs might have to be swapped. It also takes care of NULLs.)
Having input_ts_end as 'infinity'::timestamp also works with the index. Am I missing anything else?
From the documentation on OVERLAPS:
In addition to these functions, the SQL OVERLAPS operator is supported:
(start1, end1) OVERLAPS (start2, end2) (start1, length1) OVERLAPS (start2, length2)This expression yields true when two time periods (defined by their endpoints) overlap, false when they do not overlap. The endpoints can be specified as pairs of dates, times, or time stamps; or as a date, time, or time stamp followed by an interval. When a pair of values is provided, either the start or the end can be written first; OVERLAPS automatically takes the earlier value of the pair as the start. Each time period is considered to represent the half-open interval start <= time < end, unless start and end are equal in which case it represents that single time instant. This means for instance that two time periods with only an endpoint in common do not overlap.
With your additional assumptions, your rewrite should be identical to OVERLAPS. PostgreSQL does not support index scans for OVERLAPS; one of the reasons must be that OVERLAPS swaps end points.
You might consider using range types and the && operator, which can be supported by a GiST index.