3 ms·
As thomaswang says, that assumes that the query planner recognizes that it can hoist that query part out of the loop (https://en.wikipedia.org/wiki/Loop-invaria
by Someone 8y ago
As thomaswang says, that assumes that the query planner recognizes that it can hoist that query part out of the loop (https://en.wikipedia.org/wiki/Loop-invariant_code_motion https://en.wikipedia.org/wiki/Loop-invariant_code_motion)
If it doesn’t, and runs it again and again, time will add up (possibly by less than 5 seconds for each time it gets run because the data could stay in memory)
That theory was only disproved at “Move the sub-query into a CTE” section.
Also, I’m not familiar with Postgres, so I don’t know whether it could significantly affect timing, but the query benchmarked at 5231.765 ms isn’t identical to that in the larger query. The latter has ::text[] added.
I also would try and replace
AND r_time > to_timestamp(1547585600)
AND r_time < to_timestamp(1549177599)
by
AND r_time BETWEEN to_timestamp(1547585600+1) AND timestamp(1549177599-1)
because the query optimizer might not detect that in this query.
- manish_gill 8y agoBETWEEN is soemthing that is on the "Don't do this" page of Postgres. :) https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_BETWEEN_.28especially_with_timestamps.29 https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use... the ::text[] addition had no impact on perf. I actually constructed this post from my notes at the time so might missed have a few things here and there. The bit about query planner recognizing things is exactly what the challenge is to be honest. Like I said at the end, the good mental model of SQL helps a lot, and mine is decent-ish I hope but can still learn a lot. :)
- Someone 8y agoThat’s why I added the +1 and -1 fragments :-) I thought those computed the smallest larger/largest smaller time stamp. I didn’t realize that time stamps weren’t integers in PostgreSQL, though, so that is a bug. Are you sure your query, which uses an interval that is open at both ends, is correct?
- zimpenfish 8y ago> As thomaswang says, that assumes that [...] In a subsequent comment, yes; but in the original comment I responded to, there was nothing about the surrounding context, only (basically) "left % is slow".
- thomaswang 8y agoSorry to say, but I stand my ground. I believe it is the left % that is the root issue. As said without further explanation in my initial comment. (it's the same issue on almost every database, and I have never seen it not, being the main culprit, and the solution that never seme to fail is always index)