sql - 在 Postgres 中将多个子查询合并为一个

Question

我有两个表:

CREATE TABLE items
(
  root_id integer NOT NULL,
  id serial NOT NULL,
  -- Other fields...

  CONSTRAINT items_pkey PRIMARY KEY (root_id, id)
)

CREATE TABLE votes
(
  root_id integer NOT NULL,
  item_id integer NOT NULL,
  user_id integer NOT NULL,
  type smallint NOT NULL,
  direction smallint,

  CONSTRAINT votes_pkey PRIMARY KEY (root_id, item_id, user_id, type),
  CONSTRAINT votes_root_id_fkey FOREIGN KEY (root_id, item_id)
      REFERENCES items (root_id, id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  -- Other constraints...
)

我试图在单个查询中提取特定 root_id 的所有项目以及以特定方式投票的用户的一些 user_id 数组。以下查询满足我的需要:

SELECT *,
  ARRAY(SELECT user_id from votes where root_id = i.root_id AND item_id = i.id AND type = 0 AND direction = 1) as upvoters,
  ARRAY(SELECT user_id from votes where root_id = i.root_id AND item_id = i.id AND type = 0 AND direction = -1) as downvoters,
  ARRAY(SELECT user_id from votes where root_id = i.root_id AND item_id = i.id AND type = 1) as favoriters
FROM items i
WHERE root_id = 1
ORDER BY id

问题是我正在使用三个子查询来获取我需要的信息，而我似乎应该能够在一个子查询中执行相同的操作。我认为 Postgres(我使用的是 8.4)可能足够聪明，可以为我将它们全部折叠成一个查询，但是查看 pgAdmin 中的解释输出，看起来这并没有发生——它正在对选票运行多个主键查找表代替。我觉得我可以重新处理此查询以提高效率，但我不确定该怎么做。

有什么建议吗？

编辑:更新以解释我现在的位置。根据 pgsql-general 邮件列表的建议，我尝试更改查询以使用 CTE:

WITH v AS (
  SELECT item_id, type, direction, array_agg(user_id) as user_ids
  FROM votes
  WHERE root_id = 5305
  GROUP BY type, direction, item_id
  ORDER BY type, direction, item_id
)
SELECT *,
  (SELECT user_ids from v where item_id = i.id AND type = 0 AND direction = 1) as upvoters,
  (SELECT user_ids from v where item_id = i.id AND type = 0 AND direction = -1) as downvoters,
  (SELECT user_ids from v where item_id = i.id AND type = 1) as favoriters
FROM items i
WHERE root_id = 5305
ORDER BY id

从我的应用程序中对每一个进行基准测试(我将每个设置为准备好的语句以避免在查询计划上花费时间，然后使用各种 root_id 运行每个语句数千次)我的初始方法平均为 15 毫秒，CTE接近平均 17 毫秒。我能够在几次运行中重复这个结果。

当我有时间的时候，我会用我的测试数据来尝试 jkebinger 和 Dragontamer5788 的方法，看看它们是如何工作的，但我也会开始悬赏，看看我是否能得到更多建议。

我还应该提到，如果它可以加快此查询，我愿意更改我的模式(系统尚未投入生产，并且不会在几个月内投入生产)。我以这种方式设计了我的投票表，以利用主键的唯一性约束——例如，给定的用户既可以喜欢也可以投票赞成一个项目，但不能赞成它也可以反对它——但我可以放松/解决这个约束，如果代表这些选项以不同的方式更有意义。

编辑 #2: 我已经对所有四种解决方案进行了基准测试。令人惊讶的是，Sequel足够灵活，我可以编写所有四个语句而无需掉落到 SQL 一次(甚至对于 CASE 语句也不异常(exception))。像以前一样，我将它们全部作为准备好的语句运行，这样查询计划时间就不会成为问题，并且每个运行了数千次。然后我在两种情况下运行所有​​查询——最坏的情况是有很多行(265 项和 4911 票)，相关行很快就会在缓存中，所以 CPU 使用率应该是决定因素，而且更多为每次运行选择随机 root_id 的现实场景。我结束了:

Original query  - Typical: ~10.5 ms, Worst case: ~26 ms
CTE query       - Typical: ~16.5 ms, Worst case: ~70 ms
Dragontamer5788 - Typical: ~15 ms,   Worst case: ~36 ms
jkebinger       - Typical: ~42 ms,   Worst case: ~180 ms

我想现在从中吸取的教训是 Postgres 的查询规划器非常聪明，并且可能在表面下做一些聪明的事情。我认为我不会再花时间尝试解决它。如果有人想提交另一个查询尝试，我很乐意对其进行基准测试，但除此之外，我认为 Dragontamer 是赏金和正确(或最接近正确)答案的赢家。除非其他人能够阐明 Postgres 正在做什么——那会很酷。 :)

Answer 1

有两个问题:

1. 一种将多个子查询合并为一个的语法。
2. 优化。

对于 #1，我无法将“完整”的东西放入单个 Common Table Expression 中，因为您在每个项目上使用相关子查询。不过，如果您使用公用表表达式，您可能会有一些好处。显然，这将取决于数据，因此请进行基准测试以查看是否有帮助。

对于 #2，因为您的表中有三个经常访问的项目“类”，我预计 partial indexes提高查询速度，无论您是否能够由于 #1 提高速度。

首先，简单的事情。要向该表添加部分索引，我会这样做:

CREATE INDEX upvote_vote_index ON votes (type, direction)
WHERE (type = 0 AND direction = 1);

CREATE INDEX downvote_vote_index ON votes (type, direction)
WHERE (type = 0 AND direction = -1);

CREATE INDEX favoriters_vote_index ON votes (type)
WHERE (type = 1);

这些索引越小，查询的效率就越高。不幸的是，在我的测试中，它们似乎没有帮助:-( 不过，也许您可​​以找到它们的用途，这在很大程度上取决于您的数据。

---

至于整体优化，我会以不同的方式处理问题。我将查询“展开”为这种形式(使用内部联接并使用 conditional expressions 来“拆分”三种类型的选票)，然后使用“Group By”和“数组”聚合运算符将它们组合起来. IMO，我宁愿更改我的应用程序代码以接受它的“展开”形式，但如果您不能更改应用程序代码，那么“分组依据”+聚合函数应该可以工作。

SELECT array_agg(v.user_id), -- array_agg(anything else you needed), 
    i.root_id, i.id, -- I presume you needed the primary key?
CASE
    WHEN v.type = 0 AND v.direction = 1
        THEN 'upvoter'
    WHEN v.type = 0 AND v.direction = -1
        THEN 'downvoter'
    WHEN v.type = 1
        THEN 'favoriter'
END as vote_type
FROM items i 
    JOIN votes v ON i.root_id = v.root_id AND i.id = v.item_id
WHERE i.root_id = 1 
  AND ((type=0 AND (direction=1 OR direction=-1)) 
       OR type=1)
GROUP BY i.root_id, i.id, vote_type
ORDER BY id

与您的代码相比，它仍然是“展开的一步”(vote_type 是垂直的，而在您的情况下，它是水平的，跨列)。但这似乎更有效率。