提高大表上的 UPDATE 性能

我在 Amazon RDS（2vCPU，8 GB RAM）上使用 Postgres 9.5。

我使用 pganalyze 來監控我的表現。

我在數據庫中有大約 20 萬條記錄。

在我的儀表板中，我看到以下查詢平均需要 28 秒和 11 秒才能執行：

UPDATE calls SET ... WHERE calls.uuid = ?   telephonist 28035.41    0.01    100%    0.03%

UPDATE calls SET sip_error = ? WHERE calls.uuid = ? telephonist 11629.89    0.44    100%    0.69%

我已經嘗試VACUUM、找到並清理了 7,670 個死行。

任何想法如何提高UPDATE性能？這是查詢：

UPDATE calls SET X=Y WHERE calls.uuid = 'Z'

如何改進上面的查詢？我可以添加另一個欄位嗎？例子：

UPDATE calls SET X=Y WHERE calls.uuid = 'Z' AND calls.campaign = 'W'

該列uuid未編入索引。

https://www.tutorialspoint.com/postgresql/postgresql_indexes.htm建議不建議將索引用於UPDATE操作。

CREATE TABLE public.calls (
   id int4 NOT NULL DEFAULT nextval('calls_id_seq'::regclass),
   callsid varchar(128),
   call_start timestamp(6) NOT NULL,
   call_end timestamp(6) NULL,
   result int4 DEFAULT 0,
   destination varchar(256),
   campaign varchar(128),
   request_data varchar(4096),
   uuid varchar(128) NOT NULL,
   status varchar(64),
   duration int4,
   recording_file varchar(256),
   recording_url varchar(256),
   recording_duration int4,
   recording_text varchar(4096),
   recording_download bool DEFAULT false,
   description varchar(4096),
   analysis varchar(4096),
   is_fax bool DEFAULT false,
   is_test bool,
   hangup_cause varchar(128),
   media_detected bool DEFAULT false,
   sip_callid varchar(256),
   hangup_cause_override varchar(256),
   is_blacklisted bool DEFAULT false,
   sip_error varchar(256),
   hangup_cause_report varchar(128),
   summary varchar(1024)
);

EXPLAIN ANALYZE 
   SELECT * FROM calls 
   WHERE calls.uuid='e2ce9eb4-v1lp-p14u-7kkk-lruy-e2ceaae46d';

Seq Scan on calls (cost=0.00..16716.25 rows=1 width=3301)
（實際時間=81.637..81.637 行=0 循環=1）
過濾器：((uuid)::text = 'e2ce9eb4-v1lp-p14u-7kkk-lruy-e2ceaae46d'::text)
過濾器刪除的行數：99970
規劃時間：0.482 ms
執行時間：81.683 毫秒

假設列uuid應該是UNIQUE，這個表定義應該節省一些空間並提高性能：

CREATE TABLE public.calls (
  id serial PRIMARY KEY,
  result int4 DEFAULT 0 NOT NULL,
  uuid uuid UNIQUE NOT NULL  -- creates the index you need automatically
  call_start timestamp NOT NULL,
  call_end timestamp,  -- so this can be NULL?
  duration int4,
  recording_duration int4,
  callsid varchar(128),
  destination varchar(256),
  campaign varchar(128),
  request_data varchar(4096),
  status varchar(64),
  recording_file varchar(256),
  recording_url varchar(256),
  recording_text varchar(4096),
  recording_download bool DEFAULT false,
  description varchar(4096),
  analysis varchar(4096),
  is_fax bool DEFAULT false,
  is_test bool,
  hangup_cause varchar(128),
  media_detected bool DEFAULT false,
  sip_callid varchar(256),
  hangup_cause_override varchar(256),
  is_blacklisted bool DEFAULT false,
  sip_error varchar(256),
  hangup_cause_report varchar(128),
  summary varchar(1024)
);

這裡最重要的特性是UNIQUE約束，它是通過唯一索引實現的，而索引是你最需要的（就像@ypercube 已經評論過的一樣）。

如果uuid不是唯一的，則在其上創建一個普通的 btree 索引。

如果uuid不是有效的 uuid，則將其保留為字元類型（varchar或text），但仍會創建該索引。

varchar數據類型與數據類型的大小和性能注意事項uuid：

• 當所有值都是 36 個字元時，使用 char 與 varchar 進行索引查找會明顯更快嗎

我所有其他建議的更改都是微小的改進。詳細解釋在這裡：

• 為讀取性能配置 PostgreSQL

如果您不需要強制執行特定的最大長度，我只會將text其用於所有字元列。但這對性能幾乎沒有任何直接影響。某些列可能會轉換為更合適的類型（具有實際性能優勢）。

• 使用數據類型“文本”儲存字元串有什麼缺點嗎？
• 我應該向 VARCHAR 列添加任意長度限制嗎？

---

索引和UPDATE

那麼為什麼該教程頁面會說：

什麼時候應該避免索引？

$$ … $$

• 具有頻繁、大批量更新或插入操作的表。

這是疏忽的誤導。您迫切需要一個索引uuid來支持更新的謂詞。所有其他索引都會減慢您的更新速度，因為它們需要額外的工作才能在更新後保持最新狀態。PRIMARY KEY因此，如果您對on沒有用處id（例如，允許對其進行 FK 約束），您可以刪除它（並uuid改為使用 PK - 也​​自動索引）。

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