主页 / dba / 问题 / 79980
Accepted
Sam
Asked: 2014-10-12 16:19:33 +0800 CST

有没有更快的方法在 Postgres 中获得 UNION ALL 行为？

  • 772

我的 Postgres DB 中有一个表 (t0)，其数据如下所示：

t1_id  t2_id
1      1      
2      1
2      1
4      null
4      null
5      null

我有一个查询来返回我想要的结果：

t1_id  t2_id
1      1
4      null
5      null

我的查询看起来像这样：

(
  SELECT DISTINCT ON (t2_id) t1_id, t2_id
  FROM t0
  WHERE t2_id IS NOT NULL
)
UNION ALL
(
  SELECT DISTINCT ON (t1_id) t1_id, t2_id
  FROM t0
  WHERE t2_id IS NULL
)

有没有更快的方法来做这样的操作？这还不错，但是我在几个地方（通过连接）都在做这件事，所有这些重复的查询似乎让事情变慢了一点。似乎必须有更好的方法。

这是小提琴形式的查询：http ://sqlfiddle.com/#!15/d41d8/3603

postgresql performance

2 个回答

  1. Best Answer

    对于简单的情况，我只能想到对查询的小改进：

    (
  SELECT DISTINCT ON (t2_id)
         t1_id, t2_id
  FROM   t0
  WHERE  t2_id IS NOT NULL
  ORDER  BY t2_id, t1_id  -- to get consistent results
)
UNION ALL
(
  SELECT DISTINCT ON (t1_id)
         t1_id, NULL      -- cheaper
  FROM   t0
  WHERE  t2_id IS NULL
  -- if you retrieve more columns, add ORDER BY, too
)

    • 正如ypercube 提到的，您需要添加ORDER BY一个明确的表达式列表以获得确定性结果。

    • 您可以在查询的第二段中使用常量NULL而不是。t2_id也与以下指数支持有关。

    • 性能的关键是索引。尝试匹配查询的两个部分的两个部分索引：

      CREATE INDEX t0_part1_idx ON t0 (t2_id, t1_id) WHERE t2_id IS NOT NULL;
CREATE INDEX t0_part2_idx ON t0 (t1_id)        WHERE t2_id IS NULL;

      您可能希望也可能不希望包含其他列以允许仅索引扫描。

    根据表大小和数据分布，可能会有更快的替代方案：

    单身的SELECT

    如果你想把它压缩成一个SELECT：

    SELECT DISTINCT ON (coalesce(t2_id, t1_id), t2_id)
       t1_id, t2_id
FROM   t0
ORDER  BY coalesce(t2_id, t1_id), t2_id, t1_id;

    等效，但排序顺序除外。如果您希望它更快，请尝试使用功能索引：

    CREATE INDEX t0_func_idx ON t0 (coalesce(t2_id, t1_id), t2_id, t1_id);
    • 5

  2. 举个例子，这会做到：

    select distinct on (coalesce(t2_id, t1_id)) t1_id, t2_id
from t0
order by coalesce(t2_id, t1_id);

    它本质上是说“在 上做一个不同的t2_id，但如果那是null使用t1_id而不是”。

    • 4

相关问题