0

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

vs

  • 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

Here are explain (analyze, buffers) results for the second query (left=slow, right=fast): https://www.diffchecker.com/lBA6sgaf

The slow FK is relating attribute_value.element_id with element.id.

  • Are autovacuum runs happening while your query is in progress? That would explain the variance in how long the constraint trigger is taking if it's referencing a table locked for vacuuming. – Larry Jan 10 at 20:34
  • @Larry, how can I check whether autovacuum is running? Can I somehow prevent this? – Aivar Jan 11 at 7:40

Your Answer

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

Browse other questions tagged or ask your own question.