|
Sorry, i forgot to add the following:
Explain / Analyze for the last "update type on B" call, normally there the table has million of rows but i removed most of them since otherwise it would not finish sometime soon:
The tables have Index on each other. The vacuum
can not be called, since all those table modifications are part of one big transaction to be able to make a rollback on any problem without causing an abnormal data state regarding the program.
From: Per Kaminsky <per.kaminsky@xxxxxxxxxxxxxxx>
Sent: Monday, March 28, 2022 08:53 To: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxx <pgsql-general@xxxxxxxxxxxxxx> Subject: Re: Performance issues on FK Triggers after replacing a primary column
The table structure looks (roughly) like this:
Swapping the PK of "A" happens as following, the FK is dropped during the process since otherwise the performance issues also happen here when updating the PK. The update calls do normally utilize a file based import into a temporary table from which i
do the actual update:
ALTER TABLE "B" DROP CONSTRAINT "B_to_A_fkey";
ALTER TABLE "A" ADD COLUMN id_temp BIGINT;
// fill id_temp with new IDs
UPDATE "B" SET ref_a = "A".id_temp WHERE "B".ref_a= "A".id;
UPDATE "A" SET id = id_temp;
ALTER TABLE "B" ADD CONSTRAINT "B_to_A_fkey" FOREIGN KEY (ref_a) REFERENCES A(id);
And then the new occuring step, in the same transaction, which then also has shown the performance issues described if i would not remove the FK temporarily:
ALTER TABLE "B" DROP CONSTRAINT "B_to_A_fkey";
UPDATE "B" SET type = 2 WHERE type ISNULL;
ALTER TABLE "B" ADD CONSTRAINT "B_to_A_fkey"
FOREIGN KEY (ref_a) REFERENCES A(id);
From: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
Sent: Sunday, March 27, 2022 23:22 To: Per Kaminsky <per.kaminsky@xxxxxxxxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxx <pgsql-general@xxxxxxxxxxxxxx> Subject: Re: Performance issues on FK Triggers after replacing a primary column On 3/27/22 09:30, Per Kaminsky wrote:
> Hi there, > > i recently stumbled upon a performance issue which i can't > really understand. > The issue occured when i (roughly) did the following without a commit in > between: > > * Replace the PK column of a table A which has a referencing table B - > I have removed the FK from the referencing tables B and have > recreated them afterwards > * Now following i am working in one of the referencing tables B, > updating columns. This takes an extremely large amount of time. This > means, e.g. updating 1000 rows would now need 35-40 seconds. > * The "explain" tells, that the Foreign Key trigger in B referencing A > causes this mishap. Post the query and the explain. Also have you run vacuum and/or analyze on the tables involved? > * Re-creating the Index in B for the column referencing A does not > cause any performance gain. > * If i again remove the FK to A from B this again shrinks back to some > milliseconds. > > The question is, what does cause the FK trigger to be less performant > than recreating the FK constraint? If executed on 100k or even 1m rows > the operation takes hours or even days. > > Thank you very much. > Sincerely, Per Kaminsky > -- Adrian Klaver adrian.klaver@xxxxxxxxxxx |
