在PostgreSQL中更改表行的顺序

Question

我在PostgreSQL中有一张表，我想根据特定列（不是主键）对行数据进行物理重新排序。 在我的情况下，这个列的类型是date。 我该如何做？

- f.ashouri

---

在选择数据时使用 order by。 - user330315

请注意：这些行没有被排序。即使您将它们排成某种顺序，也是徒劳无功的，因为对表格进行更改将更改行的物理位置。而且并不保证选择会按照物理位置返回行。你所问的毫无意义。 - user330315

@a_horse_with_no_name - 在9.5中，原地重排序似乎对利用新的BRIN功能很有用，但我认为问题仍然存在。 - jangorecki

@a_horse_with_no_name 开发人员认为，数据有序会带来好处：https://www.youtube.com/watch?v=K0xHOPXZq9g - jangorecki

@a_horse_with_no_name，对于其他已经可以应用BRIN的情况呢？如果您有一个静态无序的大数据集需要运行大量查询，那么使用BRIN可能会带来好处。目前你需要使用“create table as select ... order by idx_col”创建表格。用户有充分的理由寻找替代方案。 - jangorecki

Answer 1

如果您在该列上有一个索引，那么CLUSTER命令将根据该索引对行进行物理“排序”。

CLUSTER [VERBOSE] table_name [ USING index_name ]

http://www.postgresql.org/docs/current/static/sql-cluster.html

请注意，这个“顺序”不会自动维护，您需要定期手动运行该语句。

---

但是，即使没有联接或聚合，也无法保证检索行的任何特定顺序。即使您只是执行select * from the_table，返回的行的顺序仍不能保证。例如：Postgres有一个名为“同步序列扫描”的功能，这意味着如果一个会话开始了一个序列扫描（select * from ...），而另一个会话正在做相同的事情，则第二个会话将在第一个序列扫描（不管在哪里）上进行搭便车，然后在结果末尾添加“遗漏”的行。

保证结果集的顺序的唯一方法是提供一个order by子句。

---

只有在服务器上有单个硬盘（不是SSD）时（至少对我来说）才有意义。在这种情况下，序列扫描可能更快，因为所有块可能紧挨着彼此（这也不是保证，因为文件系统重用空闲空间的方式）。

在SSD或使用许多硬盘的RAID阵列的适当服务器上，我看不出任何方面的好处。

- user330315

---

这个功能是否在Postgres未来版本的路线图上呢？其他一些数据库允许用户对索引表进行聚集，这对于时间查询非常有用，因为行的顺序最为重要。 - jangorecki

@jangorecki：我不知道有任何这样的变化。而且，考虑到任何严肃的数据库服务器都会使用许多硬盘（潜在地可能达到数百个），我仍然认为这并不是真正有用的——在这种情况下，你如何定义“顺序”？而且，在SSD上进行随机访问根本没有开销。 - user330315

假设我有一个BRIN索引，并且我正在使用CLUSTER来物理重新排序行，以更好地适应索引，那么重新排序会破坏我的索引吗？还是它会随着CLUSTER操作一起更新？我想说的是，为了使BRIN发挥最佳作用，不应该反过来进行：重新排序，然后创建索引。那么如何实现呢？ - Damian Birchler

一个数据库不是以块/页的形式检索数据吗（每个块可能包含比请求的更多的数据）？如果您需要使用连续的数据，聚集它不会帮助防止加载其他块吗？ - Ben Sandeen

Answer 2

你尝试过使用 "ORDER BY" 吗？例如，"SELECT * FROM table_name ORDER BY date ASC;"？如果你想要日期倒序排列，可以尝试使用 "DESC" 而不是 "ASC"。

- edlee

---

这只是按顺序检索数据，与排序无关。 - Chris Kolenko