Postgres：大型连接优化

Question

我有两个表格，假设它们是

CREATE TABLE a (
  a_a BIGINT,
  a_b BIGINT,
  a_c BIGINT,
  a_someval NUMERIC
);

CREATE TABLE b (
  b_a BIGINT,
  b_b BIGINT,
  b_c BIGINT,
  b_someval NUMERIC
);

我是以下面的方式加入他们的：

SELECT *
FROM a
  JOIN b ON (a.a_a = b.b_a AND a.a_b = b.b_b AND a.a_c = b.b_c)
;

解释展示出，规划者需要按照在JOIN中使用的列对那些表进行排序。

是否有一种方式可以预先对这些表进行排序，以便它们每次加入时不会被排序？

一些可能重要的事情：

- 查询使用了两个表的全部内容（而不是一个小子集） - 每个表中都有数亿行 - 表的内容将不会更改 - 这两个表都是在用于分析需求的生产数据库快照中生成的（CREATE TABLE x AS SELECT…）

- Krzysztof Jędrzejewski

---

你为这些列创建了索引吗？ - seva titov

是的，我有它们，但它们没有被使用，因为查询正在连接整个表。 - Krzysztof Jędrzejewski

这些表有多少行？有时候，当表非常小的时候，索引并不会被使用。 - Kevin

在这种情况下，索引并未被使用，因为两个表的全部内容都被使用了。规划器没有使用索引的理由。 - Krzysztof Jędrzejewski

你尝试过聚集吗？由于你的表是只读的，它可能会回答你关于预排序表的问题。 - Radek Postołowicz

Answer 1

如果你真的想确保表格已经被实质上预-join并排序，你可以创建一个物化视图，将这些表格join在一起。

这将导致物化视图已经拥有了两个表格被join在一起并按照所选顺序排序的结果。与常规视图不同，你还可以在任何字段上创建索引。

代码将类似于：

CREATE MATERIALIZED VIEW ab_mat AS
SELECT *
FROM a
JOIN b ON (a.a_a = b.b_a AND a.a_b = b.b_b AND a.a_c = b.b_c);

这种方法的一个潜在缺点是，物化视图无法更新，因此信息不是实时的（这就是为什么它们提供更好的性能--它们本质上是持久化到磁盘的视图快照）。然而，对于许多用例来说，这是完全可以接受的。

要更新信息，只需创建一个定期运行REFRESH MATERIALIZED VIEW命令的cron job，并在所需的间隔时间内对物化视图进行更新。这可以从相对激进（例如每5分钟）到相对宽松（例如每天或每周）。

请记住，物化视图可以与其他表和视图连接起来，以混合实时信息。我最近使用了这样的混合设置，极大地提高了一个非常复杂的查询的速度，其中只有一些数据需要真正实时。

此外，请注意，物化视图在9.3版本之前不可用。

针对OP评论的编辑：

您可以选择在视图中指定顺序，这样它将默认为该排序，或者像我上面那样保持无序，并在每次动态排序时进行排序。

你可以这样查询物化视图：

SELECT *
FROM ab_mat
-- optional ordering
order by a, b, c;

这意味着它根本不需要执行任何join操作，因为它已经完成并保存了。

- khampson

---

您可以在源表的插入/更新/删除/截断上设置触发器以刷新物化视图。例如，请参见此代码片段。如果表不经常更改但您需要一直从视图中选择，则这很好用。 - Kevin

True。对于我之前提到的情况不起作用，但可能是OP的一个选择。 - khampson

这仍然需要运行相同有问题的查询。本质上，每次需要刷新物化视图时，它与重新运行查询并存储结果基本相同。在缺乏增量刷新的情况下，PostgreSQL MV只是语法糖，用于避免截断和重新填充表。 - David Aldridge

没有更新的可能性并不是问题。如果a和b是物化视图，并且在两个视图中都使用了相同顺序的ORDER BY组织列，那么连接它们的查询将使用它们已经存在的顺序吗？ - Krzysztof Jędrzejewski

@khampson 是的，但我的观点是，在这两个表创建为生产数据库快照的一部分之后，最好使用连接创建一个表。MV本身不会使此查询更有效率。 - David Aldridge

Answer 2

我对于这个需要排序感到惊讶，但如果确实如此，那么关键在于获得一个大的工作内存区域。说实话，我本来期望是哈希连接。

您可以考虑是否有可能实现两个表的分区，并且源表都根据相同的键定义进行分区。我不确定PostgreSQL是否类似于Oracle实现了分区联接，但如果没有，则可以使用以下查询手动实现：

SELECT *
FROM a_part01
JOIN b_part01 ON (a.a_a = b.b_a AND a.a_b = b.b_b AND a.a_c = b.b_c)
union all
SELECT *
FROM a_part02
JOIN b_part02 ON (a.a_a = b.b_a AND a.a_b = b.b_b AND a.a_c = b.b_c)
union all
...
union all
SELECT *
FROM a_part0n
JOIN b_part0n ON (a.a_a = b.b_a AND a.a_b = b.b_b AND a.a_c = b.b_c);

...或者作为一系列单独的查询：

CREATE TABLE result
AS
SELECT *
FROM a_part01
JOIN b_part01 ON (a.a_a = b.b_a AND a.a_b = b.b_b AND a.a_c = b.b_c);

...

INSERT INTO result
SELECT *
FROM a_part0n
JOIN b_part0n ON (a.a_a = b.b_a AND a.a_b = b.b_b AND a.a_c = b.b_c)

这样可以以更低的内存占用完成查询。

关于预排序表数据，我不确定PostgreSQL是否会在插入或创建表时遵循ORDER BY，但你可以轻松测试一下来找出答案。如果是这样，你可以对表进行排序，但数据库不会知道它们已经排序。然而，实际影响可能只是连接更高效，因为对已经排序的数据进行排序可能更有效率。我认为值得进行测试。

然而，你仍然在实现对数据的排序，只是在整个操作中的不同部分。

如果索引已经建立，实际上可能会有帮助，前提是索引覆盖了所有表的列。然而，创建索引仍然需要排序，所以你只是在其他地方做了相同的工作。

- David Aldridge