Postgres 需要 3 小時才能刪除 CASCADE

我有一個 Postgresql 9.3，其中包含一個包含項目的“母”表，以及一些包含不同種類部分的“子”表（例如項目“AAA”由 1000 個部分“P1”、800 個部分“P2”、40 個部分組成“P3”，…）。

該items表包含約 500k 行，每行引用每個表上的 10 到 10000 行part，這意味著每個part表可以包含幾百萬行。

每個表都有一個_id_主鍵，每個“子”表都有一個指向item._id_（帶有索引）和 UPDATE/DELETE CASCADE 的外鍵，以便在刪除項目時刪除所有部分。有些部分也相互引用。

SELECTon itemand partstables 非常快（SELECT * FROM p1 WHERE item_id=?<1000 毫秒），但DELETE速度非常慢：僅DELETE FROM item WHERE _id_=?.

我嘗試按順序從每個刪除中刪除parts：DELETE FROM p1 WHERE item_id=?第一次刪除花了 3 小時，但下一次刪除只花了幾毫秒……這可能是一些低效的記憶體獲取嗎？如果統計數據有問題，我在VACUUM ANALYZE整個數據庫上執行了一個（在幾個小時內完成），但刪除仍然非常緩慢。項目以 10/h 的速度添加，所以我最終會在不久的將來耗盡磁碟空間（現在還剩約 100 GB）。

我跑了EXPLAIN ANALYZE DELETE FROM item WHERE _id_=?：

Delete on "item"  (cost=0.42..8.44 rows=1 width=6) (actual time=10375838.942..10375838.942 rows=0 loops=1)
 ->  Index Scan using "item_pkey" on "item"  (cost=0.42..8.44 rows=1 width=6) (actual time=0.043..0.054 rows=1 loops=1)
       Index Cond: ("_id_" = 29878)
Total runtime: 10375838.996 ms"

我不太了解這些成本及其含義。我只是看到它正在使用索引，但我無法解釋為什麼需要這麼長時間。刪除的項目是舊的，幾乎沒有訪問過（如果有的話），所以我可以理解記憶體未命中。

是否有任何參數可以調整以加快速度？我也可以等待維護時間來做一些事情，比如刪除索引、刪除行、重建索引，因為我們可以鎖定數據庫（但是，該怎麼辦？），但我更希望能夠實時完成，如果可能的話。如果我必須選擇，數據庫應該針對快速 INSERT 和 SELECT 而不是快速 DELETE 進行優化。

以下是表定義的摘錄：

TABLE item(
   _id_ serial,
   ...
   CONSTRAINT item_pkey PRIMAY KEY (_id_)
)

TABLE p1(
   _id_ serial,
   item_id integer,
   ...
   CONSTRAINT p1_pkey PRIMARY KEY (_id_)
   CONSTRAINT fk_item FOREIGN KEY (item_id)
       REFERENCES item(_id_)
       ON UPDATE CASCADE
       ON DELETE CASCADE
)
CREATE INDEX idx_p1_item ON p1 USING btree(item_id)

TABLE p2(
   _id_ serial,
   item_id integer,
   p1_id integer,
   ...
   CONSTRAINT p2_pkey PRIMARY KEY (_id_)
   CONSTRAINT fk_item FOREIGN KEY (item_id)
       REFERENCES item(_id_)
       ON UPDATE CASCADE
       ON DELETE CASCADE
   CONSTRAINT fk_p1 FOREIGN KEY (p1_id)
       REFERENCES p1(_id_)
       ON UPDATE CASCADE
       ON DELETE CASCADE
)
CREATE INDEX idx_p2_item ON p2 USING btree(item_id)
CREATE INDEX idx_p2_p1 ON p2 USING btree(p1_id)

Danke schön 到 @a_horse_with_no_name 指出EXPLAIN ANALYZE VERBOSE這闡明了我定義中的外鍵循環。

我需要解釋更多關於表格的資訊：

• Anitem由p1, p2, … (12 或 13 種類型) 和meta_p1由多個部分組成的“元部分”組成p1
• Adevice類似於 an item（它也由元件和元元件 1 組成）但具有不同的列。

這意味著我們有這樣的圖表（注意 ASCII-art ;)）

DEVICE       ITEM
(1)(1)       (1)(1)
|   \       /   |
 \  (n)   (n)  /
  \  META_P1  /
   \   (1)   /
    \   |   /
    (n)(n)(n)
        P1

（我可能已經顛倒了(1)，(n)我的意思是“ （1）項有（n） meta_p1”）。

每當item刪除 an 時，我希望所有部分p1, p2, … andmeta_p1也被刪除。刪除 a 時相同meta_p1：元部分中的所有p1“包含”都應該被刪除（這是一個邏輯缺陷，因為 的實例p1也可以存在於 之外meta_p1）。這意味著由於該循環（刪除項目 => 刪除 p1 和 meta_p1 但刪除 meta_p1 也意味著刪除 p1），將有一種 n^2 搜尋，如EXPLAIN ANALYZE VERBOSE結果所示：

Delete on public.item  (cost=0.42..8.44 rows=1 width=6) (actual time=0.100..0.100 rows=0 loops=1)
 ->  Index Scan using "item_pkey" on public.item  (cost=0.42..8.44 rows=1 width=6) (actual time=0.040..0.041 rows=1 loops=1)
       Output: ctid
       Index Cond: (item._id_ = 29919)
Trigger RI_ConstraintTrigger_a_24686 for constraint fk_item on item: time=0.116 calls=1
Trigger RI_ConstraintTrigger_a_24719 for constraint fk_item on item: time=0.232 calls=1
Trigger RI_ConstraintTrigger_a_24747 for constraint fk_item on item: time=33.225 calls=1
Trigger RI_ConstraintTrigger_a_24784 for constraint fk_item on item: time=50.434 calls=1
Trigger RI_ConstraintTrigger_a_24817 for constraint fk_item on item: time=0.351 calls=1
Trigger RI_ConstraintTrigger_a_24840 for constraint fk_item on item: time=0.333 calls=1
Trigger RI_ConstraintTrigger_a_24863 for constraint fk_item on item: time=0.278 calls=1
Trigger RI_ConstraintTrigger_a_24886 for constraint fk_item on item: time=0.269 calls=1
Trigger RI_ConstraintTrigger_a_24909 for constraint fk_item on item: time=0.318 calls=1
Trigger RI_ConstraintTrigger_a_24932 for constraint fk_item on item: time=0.142 calls=1
Trigger RI_ConstraintTrigger_a_24950 for constraint fk_item on item: time=16.547 calls=1
Trigger RI_ConstraintTrigger_a_24977 for constraint fk_item on item: time=48.029 calls=1
Trigger RI_ConstraintTrigger_a_25003 for constraint fk_item on item: time=0.182 calls=1
Trigger RI_ConstraintTrigger_a_24769 for constraint fk_p1 on meta_p1: time=1285.033 calls=53
Trigger RI_ConstraintTrigger_a_24789 for constraint fk_dev on meta_p1: time=102571647.818 calls=53
Total runtime: 102573083.569 ms

constraint fk_item on item每個表都有一個，p*最後兩行顯示循環（儘管我不明白fk_dev這裡在做什麼，因為我不考慮“設備”而是“元件”）。

（為簡潔起見，未包括表格定義）。

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