PostgreSQL, UPDATE CASE 語句佔用大量空間

我正在我的一張表上執行 CASE 語句，它消耗了大量的硬碟空間，即使我執行 VACUUM ANALYZE 也永遠不會釋放。Autovacuum 也已打開。

我在 Windows 2012 伺服器上使用 PostgreSQL 9.3 64Bit。我使用 wiki 上的建議調整了 postgresql.conf。因此，它已盡我所能進行優化。

該表很大（> 1.5 億行），我需要添加一個額外的列並根據其他 3 個列的內容填充它。這個表的大小是 53Gb，沒有索引。

在測試了很多方法後，我正在使用 CASE 語句。其中兩列是數組，所以我使用了 GIN 索引和標準索引。

我的 CASE 語句範例如下所示，規則最多為 38

UPDATE osmm.topographicarea
   SET fcode = (CASE
       --building CASE statements
       WHEN (descriptivegroup @> '{Building}' and descriptiveterm @> '{Archway}') then 1
       WHEN (descriptivegroup @> '{Building}') then 2

       WHEN (descriptiveterm @> '{Step}') then 3
       WHEN (descriptivegroup @> '{Glasshouse}') then 4
       WHEN (descriptivegroup @> '{Historic Interest}') then 5
       WHEN (descriptivegroup @> '{Inland Water}') then 6
     ELSE 99             
  END);
COMMIT;

這個過程需要超過 5 小時，但增加了一個巨大的 180Gb 到表中！！

向該表添加整數列肯定不應該這樣做嗎？

請問我該如何解決這個問題？

表的大小是 53Gb，沒有索引。顯然，您有索引，其中一個是 GIN 索引，它通常是僅儲存索引列的普通表大小的數倍。我希望你得到大約 180 GB

SELECT pg_total_relation_size(osmm.topographicarea);

測量尺寸的詳細資訊：

• 測量 PostgreSQL 表行的大小

如前所述，由於Postgres的MVCC 模型UPDATE， in place 有效地為每個更改的行（以及受影響的索引）寫入一個新的行版本，這大致複製了您操作中表的大小。

如果您不必擔心並發訪問，您可以走不同的路線：

• 優化 PostgreSQL 中的批量更新性能

60 GB（表大小，向上取整）對於記憶體中的臨時表來說太大了。它會溢出到磁碟，從而使預期效果無效。您可以創建一個新的正常表，刪除舊表並重命名新表 -如果您沒有並發訪問或依賴對象。

要回收您需要的空間

VACUUM **FULL** osmm.topographicarea;

一邊做一邊做：

VACUUM FULL ANALYZE osmm.topographicarea;

或者執行CLUSTER或使用pg_repack，或者pg_squeeze如果您無法承受大表上的排他鎖。

細節：

• 刪除後如何在不重建表的情況下回收磁碟空間？
• VACUUM 將磁碟空間返回給作業系統

優化UPDATE

相反，索引對此沒有幫助。UPDATE由於您正在更新每一行並且所有涉及的列都在同一行中，因此索引在這裡毫無用處。但是，它們仍然必須始終保持最新狀態。刪除所有索引並稍後添加它們會便宜得多——尤其是相對昂貴的 GIN 索引。還有更多……詳細資訊：

• 如何加快緩慢的 UPDATE 查詢

此外，您正在無條件地更新*每一行。*如果某些行在 中已經具有正確的值fcode，則根本不碰它們會更便宜。詳情（最後一章）：

• 我如何（或我可以）在多列上選擇 DISTINCT？

但聽起來你正在添加一個新列。在這種情況下，這裡沒有任何好處。

當然，所有關於性能優化的常用建議也適用。

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