postgresql - Postgres : Large join optimisation

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 ...)

Answer 1

我很惊讶为此需要排序，但如果是这样，那么诀窍就是获得一个大的工作内存区域。老实说，我本来希望散列连接。

您可能会考虑是否可以实现两个表的分区，两个源表都根据相同的键定义进行分区。我不确定 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，但您可以轻松地对其进行测试以找出答案。如果是这样，您可以对表进行排序，但数据库不会知道它们已排序。但是，实际意义可能只是连接更有效，因为对已排序的数据进行排序可能更有效。我会说它绝对值得测试。

但是，您仍在实现某种数据，只是在整个操作的不同部分。

如果索引覆盖了表的所有列，那么使用索引实际上可能会有所帮助。同样，虽然创建索引需要排序，但您只是在别处做这项工作。