PostgreSQL 9.6：更新單行表需要不合理的時間

我有一個名為的表source，其中有一個主鍵，id列上有一個“唯一約束” name。

這是表格的外觀：

id                         int4
name                       varchar
last_updated_from_source   timestampz
last_graph_update_job_time timestampz
active                     bool
source_data                jsonb

該表實際上只有一行。這就是它的全部。數據庫中的其他表要大得多（最大的表有大約 100M 行），但這個表與其他表沒有任何關係。

當我執行以下查詢時：

update "source" set source_data = '{"seq": 1}' where "name" = 'abc';

大約需要 3 分鐘才能完成，這對於里面只有一行的表來說聽起來絕對是瘋狂的。我執行explain analyze它，結果查詢：

explain analyze update "source" set source_data = '{"seq": 1}' where "name" = 'abc';

結果：

Update on source  (cost=0.00..1.01 rows=1 width=91) (actual time=158007.882..158007.882 rows=0 loops=1)
 ->  Seq Scan on source  (cost=0.00..1.01 rows=1 width=91) (actual time=0.023..0.026 rows=1 loops=1)
       Filter: ((name)::text = 'abc'::text)
Planning time: 0.208 ms
Execution time: 158007.953 ms

該數據庫目前駐留在 Google Cloud Platform 上。有趣的是，當我在本地 PostgreSQL 數據庫（具有相同的架構和索引、外鍵等）上執行它時，它不會花費這麼長時間並且行為符合預期。在 GCP 上，它佔用 CPU 高達 40%。

老實說，我對在 GCP 上花費這麼長時間感到困惑。有什麼我可能會失去的嗎？

讓我知道是否還有其他可能有助於解釋這種行為的資訊。

編輯：這裡是鎖

relation    16407   16614                               4/158336    240154  AccessShareLock true    true
relation    16407   16607                               4/158336    240154  AccessShareLock true    true
relation    16407   16600                               4/158336    240154  AccessShareLock true    true
relation    16407   16591                               4/158336    240154  AccessShareLock true    true
relation    16407   16589                               4/158336    240154  AccessShareLock true    true
relation    16407   16582                               4/158336    240154  AccessShareLock true    true
relation    16407   16578                               4/158336    240154  AccessShareLock true    true
relation    16407   16576                               4/158336    240154  AccessShareLock true    true
relation    16407   16569                               4/158336    240154  AccessShareLock true    true
relation    16407   16565                               4/158336    240154  AccessShareLock true    true
relation    16407   16565                               4/158336    240154  RowExclusiveLock    true    true
relation    16407   16563                               4/158336    240154  AccessShareLock true    true
relation    16407   16563                               4/158336    240154  RowExclusiveLock    true    true
relation    16407   16556                               4/158336    240154  AccessShareLock true    true
relation    16407   16556                               4/158336    240154  RowExclusiveLock    true    true
virtualxid                  4/158336                    4/158336    240154  ExclusiveLock   true    true
relation    16407   11695                               5/532161    241496  AccessShareLock true    true
virtualxid                  5/532161                    5/532161    241496  ExclusiveLock   true    true
relation    16407   16565                               8/207258    241601  RowExclusiveLock    true    true
relation    16407   16563                               8/207258    241601  RowExclusiveLock    true    true
relation    16407   16556                               8/207258    241601  RowExclusiveLock    true    true
virtualxid                  8/207258                    8/207258    241601  ExclusiveLock   true    true
transactionid                       2264338             4/158336    240154  ExclusiveLock   true    false
transactionid                       2264338             8/207258    241601  ShareLock   false   false
tuple   16407   16556   0   47                      8/207258    241601  ExclusiveLock   true    false
transactionid                       2264534             8/207258    241601  ExclusiveLock   true    false

有兩個可能的原因：

1. 並發事務持有與您的UPDATE.

您可以通過查詢pg_locks和查看您的掛起後端是否有一行帶有granted = FALSE. 2. 有指向未索引表的外鍵，驗證外鍵約束需要很長時間。

選項 2. 可以排除，因為

• 它會顯示在EXPLAIN (ANALYZE)輸出中。
• 它需要source_data有一個UNIQUE由這些外鍵引用的約束，這聽起來不太可能。

所以剩下的就是鎖。

你的pg_locks輸出告訴我：

• 後端程序 id 為 241601 的會話想要鎖定表 16556 中的行。
• 此鎖目前由事務 2264338（後端程序 ID 240154）持有。

因此，您是否希望更新更快，請確保沒有並發事務持有更長時間的鎖定。

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