PostgreSQL SQL: updating summaries

Question

I'm trying to optimize a query like:

UPDATE master SET count =
 (SELECT COUNT(*) FROM detail WHERE detail.master_id=master.id)

Where the master table is large, so running this results in a huge sequential scan. On the other hand, this count doesn't change so often, so my idea is to rewrite it like this:

UPDATE master SET count =
 (SELECT COUNT(*) FROM detail WHERE detail.master_id=master.id)
 WHERE count !=
 (SELECT COUNT(*) FROM detail WHERE detail.master_id=master.id)

EXPLAIN tells me this results in two subplans, which I'd expect. Is there a way to avoid the two executed subplans?

An attempt to rewrite the above as

UPDATE master SET count =
 (SELECT COUNT(*) FROM detail WHERE detail.master_id=master.id) AS cnt
 WHERE count != cnt

fails with syntax error at "AS".

Answer 1

This should be faster:

UPDATE master 
 SET "count" = t.cnt
FROM (
 SELECT master_id, COUNT(*) as cnt 
 FROM detail 
 group by master_id
) as t
where t.detail.master_id = master.id
 and t.cnt <> master."count";

This assumes that master.id is unique.