A 列上的聚集索引是否與創建 A 排序的表相同？

在閱讀9.0 版的官方 PostgreSQL 文件時，我讀到了一個有趣的escamotage，它比CLUSTER大表表現得更好：

CLUSTER 命令通過使用您指定的索引掃描原始表來重新排序。這在大型表上可能會很慢，因為行是按索引順序從表中獲取的，如果表是無序的，則條目位於隨機頁面上，因此每移動一行都會檢索一個磁碟頁面。（PostgreSQL 有一個記憶體，但大表的大部分內容不適合記憶體。）集群表的另一種方法是使用：

  CREATE TABLE newtable AS
    SELECT * FROM table ORDER BY columnlist;

它使用 PostgreSQL 排序程式碼來生成所需的順序；這通常比無序數據的索引掃描快得多。然後刪除舊表，使用 ALTER TABLE … RENAME 將新表重命名為舊名稱，並重新創建表的索引。這種方法的最大缺點是它不保留 OID、約束、外鍵關係、授予的權限和表的其他輔助屬性——所有這些項目都必須手動重新創建。另一個缺點是這種方式需要一個與表本身大小相同的排序臨時文件，因此峰值磁碟使用量大約是表大小的三倍而不是表大小的兩倍。

問題是這個建議沒有出現在 > 9.0 版本的官方文件中。

我的問題是這個 escamotage 是否對 9.1、9.2、9.3 和 9.4 仍然有效，因為我被困CLUSTER在兩個大表上的操作（一個有 ~750M 行，另一個有 ~1650M 行）和平均磁碟寫入/讀取由於CLUSTER官方文件中解釋的算法，速度為 3MB/s 。對於大表來說，這是一個緩慢的過程，所以我想避免它執行“在索引關聯列上創建有序表”的技巧。這將節省我幾天的數據庫處理時間。

就像@dezso 評論的那樣，在舊版本中創建一個新表並刪除舊表曾經更快，但在 pg 9.1 中的新實現不再如此。

最常見的問題CLUSTER是它需要在表上使用排他鎖，這對於並發訪問它並不順利。

這個問題的解決方案是pg_repack，它不會獨占鎖定表。

通常，請確保您的伺服器配置適合該任務。高設置（大量記憶體）對大桌子都有maintenance_work_mem幫助。標准設置對你來說太小了。請點擊連結了解詳情。CLUSTER``CREATE INDEX

您可以暫時將其設置為非常高的交易，SET LOCAL否則將其保留在合理的設置：

BEGIN;
SET LOCAL maintenance_work_mem = ????MB; -- find the sweet spot
CLUSTER tbl;
COMMIT;

如果可能，將其設置得足夠高以適應 RAM 中的整個操作。

更多的：

• PostgreSQL 9.0 如何在刪除後回收磁碟空間而不重建表？
• 為讀取性能配置 PostgreSQL

引用自：https://dba.stackexchange.com/questions/93990