使用臨時表的高效 PostgreSQL 更新

我編寫了一個小程序來從文件中導入產品詳細資訊更新，這比預期的要長得多。（為簡潔起見，我將使用精簡的範例。）

該程序執行以下操作：

1. 從文件中讀取數據。
2. 執行某些修改並創建記憶體文件。
3. 創建一個臨時表來保存處理過的文件數據。
4. COPYs 將修改後的數據存入臨時表。
5. 從臨時表更新實際表。

這一切都很好，除了UPDATE查詢需要約 20 秒來處理約 2000 行的小文件。

臨時表如下所示：

CREATE TEMPORARY TABLE tmp_products (
 product_id integer,
 detail text
);

我的更新查詢非常簡單：

UPDATE products
SET detail = t.detail
FROM tmp_products t
WHERE t.product_id = products.product_id

為了加快速度，我嘗試了以下方法，但收效甚微：

在臨時表上創建 BTREE 索引。

CREATE INDEX tmp_products_idx
 ON tmp_products
 USING BTREE
 (product_id);

創建 HASH 索引：

CREATE INDEX tmp_products_idx
 ON tmp_products
 USING HASH
 (product_id);

這兩個索引都沒有顯著改善更新時間。然後我想也許對錶進行分群會有所幫助，但這意味著我不能使用 HASH 索引。所以我修改了程序中的查詢以使用 BTREE 索引，然後使用 CLUSTER/ANALYZE：

CREATE INDEX tmp_products_idx
 ON tmp_products
 USING BTREE
 (product_id);

-- Program inserts data

CLUSTER tmp_products USING tmp_products_idx;
ANALYZE tmp_products;

這也沒有任何幫助。我通過同時使用 BTREE 和 HASH 索引再次嘗試了它，希望 CLUSTER 將使用 BTREE，而 UPDATE 將使用 HASH：

CREATE INDEX tmp_products_btree_idx
 ON tmp_products
 USING BTREE
 (product_id);

CREATE INDEX tmp_products_hash_idx
 ON tmp_products
 USING BTREE
 (product_id);

-- Program inserts data

CLUSTER tmp_products USING tmp_products_btree_idx;
ANALYZE tmp_products;

再一次，沒有任何幫助。我仍然在我離開的地方 - 20 秒 2000 行。在我的工作場所，通常 20 秒的更新是可以容忍的，但 2000 行文件是我用於測試的一個*小樣本。*較大的文件將花費太長時間。

products如果重要，請提供有關該表的一些詳細資訊：

行：~630k

列：54

索引：19

觸發器：14

表大小：~1.2GB

索引大小：~2.2GB

我強烈懷疑瓶頸在一個或多個觸發器中，但是我無法刪除/修改這些觸發器。我可以做些什麼來提高更新的效率嗎？

可以做的事情，儘管它們是否有幫助……是另一回事：

1. 鑑於你UPDATE很簡單，我的第一個猜測是你的觸發器正在損害你的表現。處理觸發器通常很耗時，特別是如果它們是用任何解釋語言編寫的（這或多或少意味著它們不是用 C 編寫的）。如果您有可能使用開發機器進行檢查，請測試禁用所有觸發器，並查看它有什麼效果（查詢您的時間！）。然後一一重新啟用它們，並查看每個對時間的影響。您會發現一些觸發因素（很多）受到傷害。如果有一些需要花費大量時間，請有資格的人對其進行修改，優化，甚至必要時使用 C 重寫它們。我的經驗是，任何一種記錄或審核您的插入可能會使該過程變慢（很容易）10倍。考慮到，在您的14個觸發器之上，數據庫可能添加了更多以確保滿足所有約束（CHECK, REFERENCES, UNIQUE, . ..)。嘗試禁用它們通常不是一個好主意（如果可能的話，這樣做並不簡單）。
2. 嘗試找出您是否真的需要設置中的所有索引。查看PostgreSQL wiki 上關於未使用索引的解釋。您的查詢的工作方式（僅更新 column detail），如果detail不屬於任何索引，則不會產生太大影響。PostgreSQL應該能夠執行僅堆元組 (HOT)更新，並且索引不會有任何大的影響。
3. 要使 HOT 更新成功，您的表中需要一些可用空間。因此，請確保您的表格**fillfactor**少於 100。從文件中CREATE TABLE：

fillfactor (integer)

表格的填充因子是 10 到 100 之間的百分比。預設值為 100（完全填充）。當指定較小的填充因子時，INSERT 操作僅將表頁打包到指定的百分比；每頁上的剩餘空間保留用於更新該頁上的行。**這使 UPDATE 有機會將行的更新副本放置在與原始頁面相同的頁面上，這比將其放置在不同的頁面上更有效。**對於條目從不更新的表，完全打包是最佳選擇，但在大量更新的表中，較小的填充因子是合適的。不能為 TOAST 表設置此參數。

（強調我的） 4. 考慮在您的臨時表上設置一個**覆蓋索引。**那是：

CREATE INDEX tmp_products_idx
ON tmp_products
USING BTREE
(product_id, detail);

ANALYZE tmp_products;

detail這只有在 的長度適中時才有意義。我認為這不會有太大的不同……因為這可能允許對更新的源部分進行僅索引掃描，但除非您嘗試，否則您將無法確定。

需要更多關於您的執行計劃的資訊來提供更好的建議。

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