Works like a charm with PostgreSQL 17.5!

Materialized views are obviously useful.

create materialized view site_comment_count as 
  select site_id, 
  count(1) as count 
from content 
where prototype = 'Comment' 
group by site_id;

select count from site_comment_count where site_id = 1;
 count
-------
  1435
(1 row)