[gaha]

Have you tried unnesting the subquery? From the images posted in your blog it's not clear to me how often the subquery is run. Maybe unnesting it somehow like this should work well:

    SELECT COUNT(*)        as "count",
         COALESCE(MAX(EXTRACT(EPOCH FROM age(now(), runs.created_at)))::bigint, 0) AS "max_age"
    FROM runs
      JOIN stacks ON runs.stack_id = stacks.id
      JOIN worker_pools ON worker_pools.id = stacks.worker_pool_id
      JOIN accounts ON stacks.account_id = accounts.id
      /*unnesting*/
      join (SELECT accounts_other.id,  COUNT(*) as cnt
       FROM accounts accounts_other
         JOIN stacks stacks_other ON accounts_other.id = stacks_other.account_id
         JOIN runs runs_other ON stacks_other.id = runs_other.stack_id
       WHERE accounts_other.id
         AND (stacks_other.worker_pool_id IS NULL OR
          stacks_other.worker_pool_id = worker_pools.id)
         AND runs_other.worker_id IS NOT NULL
        group by accounts_other.id) as acc_cnt 
      on acc_cnt.id = accounts.id and  accounts.max_public_parallelism / 2 > acc_cnt.cnt
     /*end*/
    WHERE worker_pools.is_public = true
      AND runs.type IN (1, 4)
      AND runs.state = 1
      AND runs.worker_id IS NULL
      /* maybe also copy these filter predicates inside the unnested query above */

Edit: format and typo