燃烧到：

SELECT count(*)
FROM  (
   SELECT 1
   FROM  (
      SELECT client_id
      FROM   test_clients
      GROUP  BY 1
      HAVING count(dog_id) = 2
      ) a1
   JOIN   test_clients USING (client_id)
   GROUP  BY filial
   HAVING count(*) > 10
   ) a2;

假设filial定义为NOT NULL。
使用窗口函数并去掉自连接可能会更快：

SELECT count(*)
FROM  (
   SELECT 1
   FROM  (
      SELECT filial
           , count(dog_id) OVER (PARTITION BY client_id) AS dog_ct
      FROM   test_clients
      ) a1
   WHERE  dog_ct = 2
   GROUP  BY filial
   HAVING count(*) > 10
   ) a2;

根据您确切的表定义，我们可能能够进一步优化...

欧文建议的一个小小的折射器，只是给你玩玩...
外部查询起作用的原因是...

• 内部查询首先发生
• 接下来发生WHERE子句
• 然后是GROUP BY和HAVING子句
• 然后SELECT子句 （因此COUNT() OVER ()）
• 最后的区别

SELECT
  DISTINCT
  COUNT(filial) OVER ()
FROM
(
  SELECT
    filial,
    client_id,
    COUNT(dog_id) OVER (PARTITION BY client_id) AS client_dog_ct
  FROM
    test_clients
)
  count_dogs
WHERE
  client_dog_ct = 2
GROUP BY
  filial
HAVING
  COUNT(DISTINCT client_id) > 10

你可能想要也可能不想要COUNT(DISTINCT client_id)，这还不清楚。所以，也玩一下那个。
我不是说它更好，只是说它不同，可能有助于你的学习。