在PostgreSQL中更改表行的顺序 [英] Change order of the table rows in PostgreSQL

问题描述

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

I have a table in PostgreSQL. I want to re-order the rows data physically according to a specific column (which is not primary key). In my case this column type is date. How can I do it?

推荐答案

如果该列上有索引，则 CLUSTER 命令将根据该索引对行进行物理排序

If you have an index on that column, then the CLUSTER command will physically "order" the rows according to that index

CLUSTER [VERBOSE] table_name [ USING index_name ]

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

请注意，此订单不会自动维护，您需要定期手动运行该语句。

Note that this "order" isn't automatically maintained, you need to run that statement on a regular basis manually.

但这将 不 保证在检索时有任何特定顺序行。即使不涉及任何联接或聚合也不会。

This will however not guarantee any specific order when retrieving the rows. Not even when no joins or aggregates are involved.

即使您只做 select * from the_table ，仍不能保证返回行的顺序。例如：Postgres具有称为同步seq扫描的功能，这意味着如果一个会话开始seq扫描（ select * from ... ），而另一会话正在执行同样，第二个在第一次seq扫描时piggy带（无论在哪里），然后在结果末尾添加缺失行。

Even if all you do is select * from the_table the order in which the rows are returned is still not guaranteed. For example: Postgres has a feature called "synchronized seq scan", which means that if one session starts a seq scan (select * from ...) and another session is doing the same thing, the second one piggy-backs on the first seq scan (where ever that is) and then adds the "missed" rows at the end of the result.

确保结果集顺序的仅 方法（确实： only ）是提供订单by 子句。

The only way to guarantee an order of a result set (really: the only) is to supply an order by clause.

这仅对您有意义（至少对我来说）服务器中的单个硬盘（不是 SSD）。在那种情况下，seq扫描可能会更快，因为所有块可能都彼此相邻（由于文件系统的重用方式，因此无法保证）可用空间）。

This only makes sense (at least to me) if you have a single harddisk in your server (which is not a SSD). In that case a seq scan might be faster because all blocks might be right next to each other (which isn't guaranteed either because of the way a file system re-uses free space).

在使用RAID阵列且包含许多硬盘的SSD或适当的服务器上，我看不到这有什么好处。

On a SSD or a proper server which uses a RAID array with many, many hard disks I can't see how this could be beneficial in any way.

这篇关于在PostgreSQL中更改表行的顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！