4 ms·
They are not equivalent since `created_at + INTERVAL '90 DAY'` can overflow for every single row whereas `now() - INTERVAL '90 DAY'` is a constant for the purpo
by mahkoh 5y ago
They are not equivalent since `created_at + INTERVAL '90 DAY'` can overflow for every single row whereas `now() - INTERVAL '90 DAY'` is a constant for the purpose of the query execution.
- smt88 5y agoYeah this seems very logical to me. I wouldn't call it a "gotcha".
- CWuestefeld 5y agoYes - this is a common restriction in any DB I've used, certainly in MS SQL Server. The idea is that your queries need to be "SARGable": https://en.wikipedia.org/wiki/Sargable https://en.wikipedia.org/wiki/Sargable
- OJFord 5y agoBut that would never be desirable, so it's just another reason to do the other?
- magicalhippo 5y agoThe DB we use (SQLAnywhere) doesn't consider now() a constant either, so no indexes considered just due to that alone #thingsilearnedinproduction
- Hjfrf 5y agoWow, that's a dealbreaker for me.
- qwertox 5y agoWhat does "can overflow for every single row" mean in this context?
- dec0dedab0de 5y agoI was wondering the same thing, but after staring at it a bit I think the problem is that one of them is doing math on the values from every row and the other is doing the math once. created_at + INTERVAL '90 DAY' < now() says that for every row take the created_at column, add 90 days to it, and then see if it is less than now() created_at < now() - INTERVAL '90 DAY' says take now() subtract 90 days, and then see which rows are less than the result. Atleast, thats my guess. I rarely do any db stuff directly.
- qwertox 5y agoIs "overflow" a term used to express "computed for every row"? I can see where the optimization would come from, when comparing `created_at` with a fixed value `now() - INTERVAL...` (assuming PostgreSQL is smart enough to evaluate it only once and reuse it for all the index comparisons), but the word "overflow" throws me out of the lane.
- mahkoh 5y agoThe maximum value in a postgres timestamp is `294276-12-31 23:59:59.999999`. Overflow means that `created_at + interval '90 days'` exceeds this value. This causes an error.
- qwertox 5y agoAh, so a traditional overflow. It's just that it didn't come to my mind that dates this huge would actually be used, so I was wondering if it was some other thing being referred to. Thanks.