Postgres taking 3h to DELETE CASCADE
Danke schön to @a_horse_with_no_name for pointing EXPLAIN ANALYZE VERBOSE that shed light on a foreign key loop in my definitions.

I need to explain more about the tables:


An item is composed of parts p1, p2, ... (12 or 13 types) and of "meta parts" meta_p1 that are groups of several parts p1
A device is like an item (it is also composed of parts and meta part 1) but with different columns.


Which means we have a graph like this (mind the ASCII-art ;))

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


(I might have reversed (1) and (n), I mean "(1) item has (n) meta_p1").

Whenever an item is deleted, I want all parts p1, p2, ... and meta_p1 to be deleted as well. Same when deleting a meta_p1: all p1 "contained" in the meta part were supposed to be deleted (which is a logic flaw, as an instance of p1 can also exist outside of meta_p1). It meant that there would be a kind of n^2 search because of that loop (delete item => delete p1 and meta_p1 but delete meta_p1 also means delete p1), as shown on the EXPLAIN ANALYZE VERBOSE results:

Delete on public.item  (cost=0.42..8.44 rows=1 width=6) (actual time=0.100..0.100 rows=0 loops=1)
  -&gt;  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


The constraint fk_item on item are one for each p* table, and the last two lines show the loop (though I don't understand what fk_dev is doing here as I'm not considering "devices" but "parts").

(Table definitions were not included for brevity).