3 ms·
What does "can overflow for every single row" mean in this context?
by qwertox 5y ago
What 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.