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".

user1822 · Accepted Answer · 2015-09-28 18:03:59Z

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.

answered Sep 28, 2015 at 18:03

user1822

Add a comment |

Stack Exchange Network

PostgreSQL SQL: updating summaries

1 Answer 1

Your Answer

Hot Network Questions

PostgreSQL SQL: updating summaries

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions