0

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)
  • 2
    Can you run explain (analyze, verbose) please, then add the output to your question. That should include the time the delete trigger took for the cascading delete – a_horse_with_no_name Aug 29 '18 at 16:18
  • @a_horse_with_no_name thanks, I ran it a couple hours after your post, it is still running (showing >48 000 000 ms so far). Please be patient... – Matthieu Aug 30 '18 at 9:00
  • Did you check if it's maybe just waiting for a lock? – a_horse_with_no_name Aug 30 '18 at 9:01
  • @a_horse_with_no_name It could be something like that, as my auto-clean process was still running a DELETE FROM item in the background. So I stopped and restarted it. I went to "Server status" in pgAdmin and I see around 80 "RowExclusiveLock" taken by the query. It is shown as "active" and not blocked by anything. How do I check locking status? (sorry for the n00b question) – Matthieu Aug 30 '18 at 10:10
  • @a_horse_with_no_name the query finally ended after 28h30, and it showed a foreign key loop that I will detail in an upcoming answer. After deleting those constraints (that didn't really need to be there in the first place), it now deletes 15 rows/s. Many thanks for the (analyze, verbose) tip! :) – Matthieu Aug 31 '18 at 18:21
0

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

  • 1
    The usual reason for such slow RI Triggers are missing indexes on the FK column (the referencing the table being deleted) – a_horse_with_no_name Aug 31 '18 at 20:32
  • @a_horse_with_no_name I have indexes on all foreign keys, though I now recall I dropped those on device because of very low disk space (and no devices on that particular system). It explains the huge time on fk_dev. Always nice to have an explanation :) What is an "RI trigger"? – Matthieu Aug 31 '18 at 21:22
  • 1
    An "RI Trigger" is the trigger that is used to enforce foreign keys during DML statements. When deleting rows from a table that is referenced by other tables, the RI trigger checks if nothing else references the row to be deleted. It does that by using a SQL query using the foreign key column in the WHERE clause. If the FK columns aren't indexed, that SQL statement is going to run a Seq Scan for each and every deleted row. – a_horse_with_no_name Sep 2 '18 at 8:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.