Postgres taking 3h to DELETE CASCADE

Question

I have a Postgresql 9.3 with a "mother" table containing items, and a number of "child" tables containing parts of different kinds (e.g. item "AAA" is composed of 1000 parts "P1", 800 parts "P2", 40 parts "P3", ...).

The items table contains ~500k rows, and each row references between 10 and 10000 rows on each part table, meaning each part table can contain several million rows.

Each table has a _id_ primary key, and each "child" table has a foreign key pointing to item._id_ (with an index) and UPDATE/DELETE CASCADE so that all parts are deleted when an item is deleted. Some parts reference each other as well.

SELECT on item and parts tables is quite fast (SELECT * FROM p1 WHERE item_id=? <1000 ms) but DELETE is awfully slow: it took a full 24h just to DELETE FROM item WHERE _id_=?.

I tried to delete sequentially from each parts with DELETE FROM p1 WHERE item_id=?: the first deletion took 3h but the next ones took a few ms only... Can it be some inefficient cache-fetching? In case statistics were a problem, I ran a VACUUM ANALYZE on the whole database (which completed in a couple hours) but deletion still is painfully slow. Items are added at a pace of 10/h so I will eventually run out of disk space in the near future (~100 GB left now).

I ran 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"

I'm don't know much about those costs and their meaning; I just see it is using the index, but I can't explain why it takes so long. The deleted items are the old ones, barely accessed (if at all), so I could understand cache miss.

Is there any parameter to tune to speed it up? I can also wait for maintenance time to do some things like dropping indexes, deleting rows, rebuilding indexes, as we can lock down the database (but then, what to do?), but I would prefer to be able to do it live, if possible. If I have to choose, the database should be optimized for fast INSERT and SELECT over fast DELETE.

Here is an excerpt from the table definitions:

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)

Answer 1

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)
  ->  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).