带有SUM的Postgres LEFT JOIN，缺少记录

Question

我正在尝试获取相关表中某些记录类型的计数。我正在使用左联接。

因此，我有一个查询不太正确，并且正在返回正确的结果。正确的结果查询具有较高的执行成本。如果可以纠正结果，我想使用第一种方法。（请参阅http://sqlfiddle.com/#!15/7c20b/5/2）

CREATE TABLE people(
  id SERIAL,
  name varchar not null
);

CREATE TABLE pets(
  id SERIAL,
  name varchar not null, 
  kind varchar not null,
  alive boolean not null default false,
  person_id integer not null
);

INSERT INTO people(name) VALUES
('Chad'),
('Buck'); --can't keep pets alive

INSERT INTO pets(name, alive, kind, person_id) VALUES
('doggio', true, 'dog', 1),
('dog master flash', true, 'dog', 1),
('catio', true, 'cat', 1),
('lucky', false, 'cat', 2);

我的目标是与所有这些人和他们活着的宠物种类一起归还一张桌子：

| ID | ALIVE_DOGS_COUNT | ALIVE_CATS_COUNT |
|----|------------------|------------------|
|  1 |                2 |                1 |
|  2 |                0 |                0 |

我使这个例子变得微不足道了。在我们的生产应用程序中（不是真正的宠物），每人大约有100,000只死狗和猫。我知道搞砸了，但是这个例子更容易传递;）我希望在计数之前过滤掉所有“死”的东西。我现在在生产中查询速度较慢（从上面的sqlfiddle中查询），但希望使LEFT
JOIN版本能够正常工作。

Answer 1

如果您获取 所有或大多数行， 通常最快：

SELECT pp.id
     , COALESCE(pt.a_dog_ct, 0) AS alive_dogs_count
     , COALESCE(pt.a_cat_ct, 0) AS alive_cats_count
FROM   people pp
LEFT   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_count
FROM   people pp
LEFT   JOIN pets pt ON pt.person_id = pp.id
                   AND pt.alive
WHERE  <some condition to retrieve a small subset>
GROUP  BY 1;

您至少应该pets.person_id为此指定一个索引（或上面的部分索引），并且可能还要更多，具体取决于WHERE条件。