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.