Good day.

There is a database that is periodically updated based on some upload from an external source. All interaction with the base goes through the ORM sqlalchemy. The update is performed as follows: a sqlalchemy object is created for each element from the upload, after which a DBSession.merge is executed for it

The base has fields id, parent_id, link_id and some more fields. Moreover, parent_id and link_id contain the id of other rows in the same table or NULL. Almost all lines have parent_id, but very few lines have link_id.

Previously, there was a FOREIGN KEY and an index for the parent_id field. Today I added the FOREIGN KEY for link_id. After that I launched the update script based on the upload. The execution speed has dropped many times! Previously, the script quietly worked out in 2-3 hours, and now in three hours it has passed about a third of the elements.

Is adding FOREIGN KEY valid?

Will creating an index for the link_id field help?

  • скрипт спокойно отрабатывал за 2-3 часа - let me ask you about the approximate volumes of changes (in the approximate number of updated lines) - vp_arth
  • The index does not help, because it already exists. Foreign key restrictions without an index do not work. - vp_arth
  • @vp_arth, in the unloading comes 1330 elements belonging specifically to this table. But there are more connections with other tables, two of which each have several tens of thousands of lines, and I still have little idea how they are tied, because only recently in this project, and sql knew only at the level of writing selections: ((( Xander
  • Without structures and sample data to be imported is difficult to help. The main reason for such brakes initially - the number of requests to the database. The whole import process is desirable to collect in bundles (google bulk update) - vp_arth

0