PostgreSQL 中大表的 UPDATE 隨機耗時太長

我試圖弄清楚為什麼一個UPDATE語句需要太長時間（> 30 秒）。

這是隨機的，即在大多數情況下，它會在 100 毫秒內完成。但是，有時（隨機）需要> 30秒才能完成。

一些細節：

• 我正在使用 PostgreSQL 12（實際上是 AWS Aurora）
• 我正在一個沒有流量的數據庫中嘗試這個，所以它不會受到同時執行的任何其他查詢的影響。我還在監視日誌以查看是否有其他東西在執行，但我什麼也沒看到。
• 我試過REINDEXing、VACUUMing (and VACUUM ANALYZE)，但沒有任何改善
• 我檢查了鎖，（log_lock_waits），我什麼也沒看到。
• 查詢在循環中執行（來自 Python 應用程序）。它執行約 5000 個查詢，在某些時候，其中一些查詢似乎沒有遵循某種模式，需要很長時間才能完成。
• 我試過分批執行它們，但同樣，有些批次隨機花費的時間太長。
• 表的大小有點大，~10000000 行和~25 個索引。

查詢：

UPDATE "my_table" SET "match_request_id" = 'c607789f-4816-4a38-844b-173fa7bf64ed'::uuid WHERE "my_table"."id" = 129624354;

的輸出EXPLAIN (ANALYZE VERBOSE BUFFERS COSTS)

Update on public.my_table  (cost=0.56..8.58 rows=1 width=832) (actual time=34106.965..34106.966 rows=0 loops=1)
  Buffers: shared hit=431280 read=27724
  I/O Timings: read=32469.021
  ->  Index Scan using my_table_pkey on public.my_table  (cost=0.56..8.58 rows=1 width=832) (actual time=0.100..0.105 rows=1 loops=1)
        Output: (...)
        Index Cond: (my_table.id = 130561719)
        Buffers: shared hit=7
Planning Time: 23.872 ms
Execution Time: 34107.047 ms

請注意，這是EXPLAIN ANALYZE. 我很困惑，因為雖然成本真的很低，但實際執行時間卻是巨大的！

我試圖了解這是否是預期的，以及我是否可以以某種方式改善這種情況。歡迎任何想法，我有點用完了！

編輯：添加評論要求的更多資訊：

“正常”更新的查詢計劃

Update on public.my_table  (cost=0.43..8.45 rows=1 width=837) (actual time=2.037..2.037 rows=0 loops=1)
  Buffers: shared hit=152 read=1
  I/O Timings: read=1.225
  ->  Index Scan using my_table_pkey on public.my_table  (cost=0.43..8.45 rows=1 width=837) (actual time=0.024..0.026 rows=1 loops=1)
        Output: (...)
        Index Cond: (my_table.id = 129624354)
        Buffers: shared hit=4
Planning Time: 1.170 ms
Execution Time: 2.133 ms
(9 rows)

該表有 23 個索引，6 個外鍵約束。3 布林，1 杜松子酒。其餘的 B 樹。我不確定如何檢查fastupdateGIN 索引，輸出\d+ index_name是

Column | Type | Key? | Definition | Storage  | Stats target
--------+------+------+------------+----------+--------------
search | text | yes  | search     | extended |
gin, for table "public.my_table"

GIN 索引具有“快速更新”機制，其中新數據以線性方式寫入特定部分。一旦超過了某個設定的大小（gin_pending_list_limit，它是全域設置的，但可以按索引覆蓋），下一個寫入索引的過程將被分配將這些條目合併到索引主要部分的任務，這可能導致長時間凍結該過程。對於您看到的凍結長度，您必須為 gin_pending_list_limit 設置一個相當高的設置。

如果一致的延遲對您來說比整體插入/更新性能更重要，您可以禁用快速更新。或者您可以降低 gin_pending_list_limit 的值，以在降低凍結時間的同時保留一些好處。

如果您需要一致性和整體效率，您可以編寫一個手動程序/執行緒，它將打開自己的連接並select gin_clean_pending_list(...)在主更新操作正在進行時循環在索引上執行。這樣，您就可以從待處理列表中受益，而清理它的延遲全部載入到無關緊要的後台程序中。（vacuum 和 autovacuum 也會清理它，但你不能真正讓它們執行足夠頻繁以完成足夠的工作，因為它們還需要做所有其他工作。因此 gin_clean_pending_list）

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