How can I speed up my count queries? Right now I am creating counts for each hour for the whole week. I am using python to connect to the database, and I am looping through each hour so str(i) is the current hour that is being updated.

UPDATE table1 p SET population_count_hour_' + str(i) + '=sub.popcount 
FROM (SELECT state, count(*) as pop_count 
     FROM table2 
     WHERE start_time &lt; ' + str(i*3600) + ' 
     AND end_time &lt; ' + str((i-1)*3600) + ' GROUP BY state) sub 
WHERE p.state=sub.state AND p.population_count_hour_' + str(i) + ' IS DISTINCT 
FROM sub.pop_count;'


I have to do several queries in a similar fashion with different variables. Is there way to do this faster because right now it is a very slow process?
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.