如果您获取 所有或大多数行， 通常最快：
SELECt pp.id , COALESCE(pt.a_dog_ct, 0) AS alive_dogs_count , COALESCE(pt.a_cat_ct, 0) AS alive_cats_countFROM people ppLEFT JOIN ( SELECt person_id , count(kind = 'dog' OR NULL) AS a_dog_ct , count(kind = 'cat' OR NULL) AS a_cat_ct FROM pets WHERe alive GROUP BY 1 ) pt ON pt.person_id = pp.id;
此处的索引无关紧要，全表扫描将是最快的。 除非 活着的宠物 很少见 ，否则
部分索引 应该有所帮助。喜欢：
CREATE INDEX pets_alive_idx ON pets (person_id, kind) WHERe alive;
我包括了查询所需的所有列，
(person_id, kind)以允许仅索引扫描。
SQL提琴。
通常对于 较小的子集或单行 最快：
SELECt pp.id , count(kind = 'dog' OR NULL) AS alive_dogs_count , count(kind = 'cat' OR NULL) AS alive_cats_countFROM people ppLEFT JOIN pets pt ON pt.person_id = pp.id AND pt.aliveWHERe <some condition to retrieve a small subset>GROUP BY 1;
您至少应该
pets.person_id为此指定一个索引（或上面的部分索引），并且可能还要更多，具体取决于
WHERe条件。
