加快 Postgres 部分索引的創建

我正在嘗試為 Postgres 9.4 中的大型（1.2TB）靜態表創建部分索引。

我的數據是完全靜態的，所以我可以插入所有數據，然後創建所有索引。

在這個 1.2TB 的表中，我有一個名為的列run_id，它可以乾淨地劃分數據。通過創建涵蓋 s 範圍的索引，我們獲得了出色的性能run_id。這是一個例子：

CREATE INDEX perception_run_frame_idx_run_266_thru_270
ON run.perception
(run_id, frame)
WHERE run_id >= 266 AND run_id <= 270;

這些部分索引為我們提供了所需的查詢速度。不幸的是，每個部分索引的創建大約需要 70 分鐘。

看起來我們的 CPU 受限（top程序顯示 100%）。

我能做些什麼來加快部分索引的創建速度嗎？

系統規格：

• 18核至強
• 192GB 記憶體
• RAID 中的 12 個 SSD
• 自動吸塵器關閉
• maintenance_work_mem：64GB（太高了？）

表規格：

• 大小：1.26 TB
• 行數：105.37億
• 典型索引大小：3.2GB（有 ~.5GB 差異）

表定義：

CREATE TABLE run.perception(
id bigint NOT NULL,
run_id bigint NOT NULL,
frame bigint NOT NULL,
by character varying(45) NOT NULL,
by_anyone bigint NOT NULL,
by_me bigint NOT NULL,
by_s_id integer,
owning_p_id bigint NOT NULL,
obj_type_set bigint,
seq integer,
subj_id bigint NOT NULL,
subj_state_frame bigint NOT NULL,
CONSTRAINT perception_pkey PRIMARY KEY (id))

（不要過多地閱讀列名——我對它們進行了一些混淆。）

背景資料：

• 我們在現場有一個單獨的團隊來使用這些數據，但實際上只有一兩個使用者。（這些數據都是通過模擬生成的。）使用者只有在插入完成並完全建立索引後才開始分析數據。我們主要關心的是減少生成可用數據所需的時間，而現在的瓶頸是索引創建時間。
• 使用部分查詢時，查詢速度已經完全足夠了。事實上，我認為我們可以增加每個索引覆蓋的執行次數，並且仍然保持足夠好的查詢性能。
• 我的猜測是我們將不得不對錶進行分區。在採取那條路線之前，我們正試圖用盡所有其他選擇。

鏈索引

從 Postgres 9.5開始可用，可能正是您正在尋找的。更快的索引創建，更小的索引。但是查詢通常沒有那麼快。手冊：

BRIN 代表區塊範圍指數。BRIN 設計用於處理非常大的表，其中某些列與其在表中的物理位置具有某種自然相關性。塊範圍是表中物理上相鄰的一組頁；對於每個塊範圍，索引儲存一些摘要資訊。

繼續閱讀，還有更多。

Depesz 進行了初步測試。

最適合您的情況：如果您可以編寫聚集在 上的行run_id，您的索引會變得非常小並且創建成本要低得多。

CREATE INDEX foo ON run.perception USING brin (run_id, frame)
WHERE run_id >= 266 AND run_id <= 270;

您甚至可以只索引整個表。

表格佈局

無論您做什麼，您都可以通過像這樣對列進行排序來節省由於每行對齊要求而失去的 8 個字節：

CREATE TABLE run.perception(
 id               bigint NOT NULL PRIMARY KEY
, run_id           bigint NOT NULL
, frame            bigint NOT NULL
, by_anyone        bigint NOT NULL
, by_me            bigint NOT NULL
, owning_p_id      bigint NOT NULL
, subj_id          bigint NOT NULL
, subj_state_frame bigint NOT NULL
, obj_type_set     bigint
, by_s_id          integer
, seq              integer
, by               varchar(45) NOT NULL -- or just use type text
);

如果沒有任何列具有 NULL 值，則使您的表小 79 GB。細節：

• 為讀取性能配置 PostgreSQL

此外，您只有三列可以為 NULL。NULL 點陣圖佔用 9 - 72 列的 8 個字節。如果只有一個 整數列是 NULL，則存在儲存悖論的極端情況：使用虛擬值會更便宜：浪費了 4 個字節，但由於不需要該行的 NULL 點陣圖而節省了 8 個字節。更多細節在這裡：

• 大表中的完全空列如何影響性能？

部分索引

根據您的實際查詢，使用這五個部分索引而不是上面的一個可能更有效：

CREATE INDEX perception_run_id266_idx ON run.perception(frame) WHERE run_id = 266;
CREATE INDEX perception_run_id266_idx ON run.perception(frame) WHERE run_id = 267;
CREATE INDEX perception_run_id266_idx ON run.perception(frame) WHERE run_id = 268;
CREATE INDEX perception_run_id266_idx ON run.perception(frame) WHERE run_id = 269;
CREATE INDEX perception_run_id266_idx ON run.perception(frame) WHERE run_id = 270;

為每個執行一個事務。

以這種方式刪除run_id索引列可以為每個索引條目節省 8 個字節 - 每行 32 個而不是 40 個字節。每個索引的創建成本也更低，但是對於一個太大而無法保留在記憶體中的表（如@Jürgen 和@Chris 評論），創建五個而不是一個索引需要更長的時間。所以這可能對你有用，也可能沒用。

分區

基於繼承- Postgres 9.5 之前的唯一選擇。

（Postgres 11 或最好是 12 中的新聲明式分區更智能。）

手冊：

在約束排除期間檢查父表的所有子表的所有約束，因此大量分區可能會顯著增加查詢計劃時間。因此，傳統的基於繼承的分區可以很好地處理多達一百個分區；不要嘗試使用數千個分區。

大膽強調我的。因此，為 估計 1000 個不同的值run_id，您將創建跨越大約 10 個值的分區。

---

maintenance_work_mem

我錯過了你已經maintenance_work_mem在我的第一次閱讀中進行調整。我會在我的回答中留下報價和建議以供參考。根據文件：

maintenance_work_mem（整數）

指定維護操作使用的最大記憶體量，例如VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY。預設為 64 兆字節 ( 64MB)。由於數據庫會話一次只能執行其中一個操作，並且安裝通常不會同時執行許多操作，因此將此值設置為明顯大於work_mem. 較大的設置可能會提高畫質理和還原數據庫轉儲的性能。

請注意，autovacuum執行時，最多autovacuum_max_workers可以分配此記憶體，因此請注意不要將預設值設置得太高。單獨控制它可能很有用 setting autovacuum_work_mem。

我只會根據需要將其設置為高 - 這取決於（對我們而言）未知的索引大小。並且僅在本地執行會話。正如引用所解釋的，過高的正常設置可能會使伺服器餓死，因為 autovacuum 也可能會佔用更多 RAM。此外，不要將其設置得比需要的高得多，即使在執行會話中，空閒 RAM 也可能會很好地用於記憶體數據。

它可能看起來像這樣：

BEGIN;

**SET LOCAL** maintenance_work_mem = 10GB;  -- depends on resulting index size

CREATE INDEX perception_run_frame_idx_run_266_thru_270 ON run.perception(run_id, frame)
WHERE run_id >= 266 AND run_id <= 270;

COMMIT;

關於SET LOCAL：

持續到目前事務結束的效果SET LOCAL，無論是否已送出。

測量物體尺寸：

• 測量 PostgreSQL 表行的大小

顯然，伺服器通常應該合理配置，否則。

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