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.

UPDATE:

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.

  • Could you show a full explain (analyze,buffers) for both case? I think we'll need postgreSQL log too with informations like slow queries, locks and tempfiles... – Arkhena Jan 10 at 14:17
  • 1
    Can you provide a simplified example which demonstrates the issues? That is, create two tables, populate them with values (from a sequence or from random() calls, for example) and the two select+insert queries? This exercise will both help us help you if you succeed, and often will help you debug the situation yourself while you are attempting it. – jjanes Jan 10 at 14:28
  • @Arkhena, I added queries and explains to the post – Aivar Jan 10 at 20:18
  • @jjanes, I'm afraid I can't reproduce the problem with simpler and/or smaller datasets. – Aivar Jan 10 at 20:19
  • Do you query all elements for specific attributes? – Evan Carroll Jan 10 at 20:22

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.