I have two tables in master-child relationship (several children per master).
In one use-case a master with all of its children needs to be copied as new master. For this I'm using two select+insert queries -- one for making copies of the master records and other for copying the child records.
Problem: The query for copying child records is sometimes fast (50 ms) and sometimes very slow (1500 ms), even with exact same database state (I'm clearing all tables before each test). I can't find any patterns. Sometimes it's slow several times in a row, sometimes it's fast several times in a row, sometimes it's fast, slow, fast, slow. Sometimes it's fast right after database restart, sometimes it's slow. I'm testing with single concurrent client.
I got the results of
explain analyze for both queries -- the whole difference came from the foreign key connecting child table with the master table:
- Trigger for constraint fk_attribute_value_element_id: time=1109.641 calls=1436
- Trigger for constraint fk_attribute_value_element_id: time=9.779 calls=1436
It looks like Postgres, while validating new child records, sometimes is able to look up (recently inserted) parent records easily and sometimes not.
Any idea how to debug this further? How to make the inserts always fast?
I have PostgreSQL 9.6.11 in Ubuntu 18.04.
I'm afraid I can't reproduce the problem with simpler and/or smaller datasets.
Here is the first query, which copies masters (this query is always fast):
insert into element (id, code, parent_code, hierarchy, version_id, version) select nextval('element_seq'), e.code, e.parent_code, e.hierarchy, :new_version_id, e.version from element e where e.version_id = :old_version_id
Here is the second query, which copies children (sometimes too slow):
insert into attribute_value (id, language, value, attribute_description_id, element_id, version) select nextval('attribute_value_seq') as id, old_av.language, old_av.value, old_av.attribute_description_id, new_e.id as element_id, old_av.version from element old_e join element new_e on new_e.version_id = new_version_id and new_e.code = old_e.code join attribute_value old_av on old_av.element_id = old_e.id where old_e.version_id = :old_version_id
explain (analyze, buffers) results for the second query (left=slow, right=fast): https://www.diffchecker.com/lBA6sgaf
The slow FK is relating