主页 / dba / 问题 / 161812
Accepted
jpmc26
Asked: 2017-01-21 13:03:35 +0800 CST

按顺序插入和聚类效果一样吗？

  • 772

我有一个表，其中包含一些基于其他表的预先计算的数据。（考虑到我必须处理的数据量，即时计算的计算成本太高。）我将在添加源数据时逐步生成。（我在正常使用中永远不需要UPDATE它；部分可能会被删除并重新生成。）该表将相当大。目前大约有 5000 万行，并且每年都会增长。

大多数针对该表的查询将被外键 ID 列过滤掉。因此，如果该 ID 的所有行都分组到相同的页面中，它们的性能会更好。我可以通过创建索引并CLUSTER定期调用来保证磁盘上的这种排序，但这显然不太理想，因为它需要某种计划任务、协调使用情况和其他计划任务等。

但是，由于我生成的数据块与我想要的外键相关CLUSTER，所以我可以轻松地在命令中添加一个ORDER BY子句：INSERT

INSERT INTO big_table (source_table1_id,a,b,c)
SELECT
   source_table1_id,
   5 /* some formula */,
   /* ... */
FROM source_table1
JOIN source_table2 ON ...
...
WHERE ... /* some condition indicating what needs to be generated */
ORDER BY source_table1_id

这会影响磁盘存储顺序，将行分组到接近最小页数吗？如果是这样，是否还有其他进程可能会在以后弄乱磁盘上的顺序？

我目前正在使用 PostgreSQL 9.3，但我也想了解更新版本以及升级。

postgresql performance

4 个回答

  1. 行将按保证的顺序进行处理，但这并不意味着它们在插入后将彼此相邻。这只有在您的表中的记录永远不会被删除或更新的情况下才有可能。一旦你更新或删除了一些行，在清理之后你可能会在表的中间有空闲空间，下一个插入的记录将去那里。

    这个问题的一些额外细节。

    • 3
  2. Best Answer

    Postgres 按插入顺序物理地写入元组。如果您对新表或没有死元组的表执行此操作，您将获得与CLUSTER使用与您的INSERT. CLUSTER随着以后以相同的方式写入表，效果会恶化（并且在您从不DELETE或UPDATE- 或INSERT破坏所需顺序时保持不变）。

    一些答案侧重于后来写作的影响，而忽略了问题的重点。你的问题的答案基本上是：

    是的，按顺序插入确实与聚类具有相同的效果。

    基于一些条件：

    对该表的查询将被外键 ID 列过滤掉。

    这意味着您一次访问具有相同 ID 的行，而不是一系列连续的 ID。那么你所需要的只是每个 ID 聚类的元组，ID 之间的物理顺序是无意义和不相关的。

    和：

    在与外键相关的块中生成此数据

    意思是，“块”按顺序包含相同 ID 的所有行。迟早不会插入相同 ID 的其他行。所以像：

    INSERT INTO big_table (source_table1_id,a,b,c)
SELECT s1.source_table1_id, ... 
FROM   source_table1 s1
...
WHERE s1.source_table1_id BETWEEN 123 and 125 -- example
ORDER BY s1.source_table1_id

    和：

    UPDATE在正常使用中我永远不需要它；部分可以删除并重新生成。

    关于的部分DELETE是唯一有轻微问题的部分。如果您永远不会删除任何一个，那么您将在这里完成。如果“部分”是指所有行同时说ID，你仍然很好，主要是。在同一事务中删除和插入时，ID 内没有碎片。（删除的元组还没有“死”，也没有在同一事务中被覆盖。）

    死元组开始使表膨胀，随后的插入可以开始填充物理空洞，这是碎片开始的地方。死元组的膨胀有各种累积的不良影响，但给定 ID 的所有行的索引访问大多不受影响。

    但所有这些都与您的问题正交，因为同样的考虑适用于CLUSTER.

    您是否考虑过 pg_repack，它可以做与 相同的事情CLUSTER，只是没有表上的独占锁。他们刚刚将 Postgres 9.6 添加到本周支持的版本列表中。

    有关的：

    • 2

  3. 不，它没有。

    • 聚类是在索引上完成的。
    • 如果没有ORDER BY，插入顺序可以完全随机
    • 使用 anORDER BY仍然INSERT可以留下空白，因为它不会重写表格。
    • 集群强制重写表。

    考虑这个..

    CREATE TABLE foo AS
SELECT random() AS bar, random() AS quz
FROM generate_series(1,100) AS t(x);

    通过实现，这将按照生成它们的顺序插入行。不能保证，但目前确实如此。没有显式的 .SQL 不保证返回顺序ORDER BY。但是，当这样生成数据时，数据不会显示任何有用的顺序，因此这只是微不足道的，而且无论如何对查询性能都不重要。现在我们可以做到这一点..

    CREATE INDEX foo_bar_idx ON foo(bar);
CLUSTER foo USING foo_bar_idx;

    现在 上的行foo是按 排序的bar，这可以使使用 的某些操作更快foo_bar_idx。

    如果这些行恰好已经按该顺序排列，会发生什么情况。如果索引恰好与行对齐并且聚类实际上没有对任何内容进行重新排序，会发生什么情况？然后什么也没有发生。但是，即使没有INSERTand ，这也不是典型的用例DELETE。在 PostGIS 中，我们始终插入数据，并通过边界框对复杂的几何图形表进行聚类。边界框比较是抽象的，但它使使用它们的东西更快。

    • 1

  4. 如果表从未更新或删除，则插入的行将按其插入时间顺序进行物理排序。但是，如果它确实被删除或更新，那么清理表将在表中创建可用空间空洞，并且新插入的行可能会分散在适合这些空洞的位置。如果删除发生在您想要排序的同一列上的范围指定的大型数据集上，这将不是什么问题。在这种情况下，整页数据将被一起删除，释放该空间以供一起重新使用。

    您的 INSERT INTO...SELECT...ORDER BY 不太可能有效，因为排序只会按块进行。除非您的块非常大，或者块本身按顺序处理并在每个块内排序，否则按块排序不太可能对您有多大好处。

    您可以查看在排序键上对表进行范围分区。这可能只是通过将相似的值放在一起来解决问题。如果不是这样，它至少会使对每个单独分区的 CLUSTER 花费的时间比对一个巨型表进行 CLUSTER 花费的时间少得多，这可能会使它们更容易安排。

    • 1

相关问题