Postgres 14.7
To be fair, it's understandable that postgres doesn't know that extract(epoch from ts) does not change the order of m.ts. For postgres, it's just a random function which could do anything, I guess. So it can't use the index since it needs to calculate that value for every row first. To spot stuff like this is part of my job as a developer :)
reply
Ah, yeah, I guess "overwriting" with "AS ts" in this case would understandably cause such issues, missed that detail. Well done finding it. 🙂
reply
It would be the same on any implementation IMHO. At least it's the same with MySQL too.
reply
Yeah, overwriting field names should be avoided if possible.
reply