How can I speed up my count queries?
You should construct an UPDATE statement that updates many rows at once, along the lines of:

UPDATE table1
SET population_count_hour_1 = sub.pop_count_1,
    population_count_hour_2 = sub.pop_count_2,
    ...
FROM (SELECT state,
             count(*) FILTER (WHERE start_time &lt; 3600 AND end_time &lt; 0)    AS pop_count_1,
             count(*) FILTER (WHERE start_time &lt; 7200 AND end_time &lt; 3600) AS pop_count_2,
             ...
      FROM table2
      GROUP BY state) AS sub
WHERE p.state = sub.state;


That should be considerably faster.