[Someone]

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)

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]

BETWEEN 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...

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]

That’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]

> 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]

Sorry 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)