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