performances of distinct/group by
select count(author) from message where room=12;


Because author can not be null just do SELECT count(*). Doing so may result in this being an index-only scan on either message_room_created or message_room_id. If you need the unique authors, which you don't have you'll have to use count( DISTINCT author ).

Also this being planned as a parallel query. That's relatively new functionality. You may want to try SET max_parallel_workers_per_gather = 0 to disable parallel query functionality and reposting the results.

Either way


  this one is slow (same duration, about 45ms)


If you've got millions rows an index scan and heap fetches, it's not really fair to call 45ms "slow."