I will add more example links in the top-level comment, but here is the thread for that bug. [0]. tl;dr timestamp vs timestamptz comparisons aren't consistently ordered in a pathological edge case, which means that index scans can return the wrong answer. There's no way to fix it without either redefining the operators in a surprising way or disabling index scans in this case.
[0] https://www.postgresql.org/message-id/flat/c22286ad6268347c6...
> or disabling index scans in this case
This seems rather extreme. The pathological case only seems to make a given timestamp ~1h bigger, so you could still use an index scan, just with a bigger input followed by a filter that removes the timestamps that shouldn't be in the query.
OT: Crazy that TZ-less are default in so many RDBMS. TZ included should be the default, and no-TZ opt in like TIMESTAMP NO TIME ZONE.
Drives me crazy that the engineers I work with are perfectly happy writing TZ-less datetime strings to the hundreds of tables we manage. Every user has to guess, or be informed, that they’re UTC.
In cases where the data itself doesn't carry the unit (or TZ in this case), I usually ask that the unit-less columns at least include the unit in the name. A TZ-less column is not that bad if it is suffixed "UTC", e.g. `ExecutionTimeUtc`.
There are probably real world cases where dropping the TZ/offset provides meaningful performance/storage savings, but it hasn't been the typical case in my current sector.
> Every user has to guess, or be informed, that they’re UTC.
At least they are UTC and not EST or PST...
They must yearn for the before times when text could be in an encoding specified only as: "your guess is as good mine!"