Query optimization or missing indexes?
Simplify query


Remove useless user_id from subquery.
Remove coalesce around count(). I quote the manual on aggregate functions:


  It should be noted that except for count, these functions return a
  null value when no rows are selected.


Meaning, count() never returns NULL.
Remove the redundant LEFT JOIN from the subquery (Update: not if you want to set columns to 0 where no rows are found in t2).




UPDATE t1
SET    col1 = t2.col1_count
      ,col2 = t2.col2_sum
      ,col3 = t2.col3_sum
FROM  (
   SELECT t1_id
         ,count(*) AS col1_count  -- if id is NOT NULL, count(*) is a bit faster
         ,sum(CASE WHEN col5 = true  THEN col2 ELSE 0 END) AS col2_sum -- might be simpler
         ,sum(CASE WHEN col5 = false THEN col3 ELSE 0 END) AS col3_sum -- missing info
   FROM   t2
   WHERE  user_id = 1
   GROUP  BY t1_id
   ) t2
WHERE  t2.t1_id = t1.id;


To reset rows in t1 without any matches in t2:

UPDATE t1
SET    col1 = 0, col2 = 0, col3 = 0
WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.t1_id = t1.id);


To do both at once, the version with LEFT JOIN the subquery like you had is probably faster, depends on your data distribution.

Avoid empty updates

If there is a chance that values in t1 are already up to date, add conditions to the WHERE clause that prevent empty updates (applies to both queries):

...
AND (col1 IS DISTINCT FROM t2.col1_count OR -- again: might be simpler
     col2 IS DISTINCT FROM t2.col2_sum   OR -- missing info
     col3 IS DISTINCT FROM t2.col3_sum)


For columns defined NOT NULL you can use &lt;&gt; instead of IS DISTINCT FROM.

This can make a big difference, updates are expensive.

Index

The only index you need for this (in addition to the primary key on t1.id) is:

CREATE INDEX ix_t2_user_id ON t2(user_id);