|
The usage of "on update cascade" certainly sounds plausible. Since the update does only happen rarely if there is a special need to completely change the backend structure it is not added to the FK at the moment.
Just ran the whole thing again with the "ANALYZE A; ANALYZE B" but with no gain unfortunately.
Just looking at the output i am also quite puzzled why an update to a field in B which has no connection itself to A would trigger all the foreign keys, except if this is a deferred call from updating A only now triggered by touching data in B.
From: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
Sent: Monday, March 28, 2022 18:49 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 23:53, Per Kaminsky wrote:
> The table structure looks (roughly) like this: > > * Table "Base": (id, created, deleted, origin, ...) ~3m rows > * Table "A": (id as FK on "Base", ...) ~400k rows > * Table "B": (id, ref_a as FK on "A", type, ...) ~2m rows > > 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); Now that the morning coffee has taken effect, I'm wondering why the above is necessary at all? If "B_to_A_fkey" where to have ON UPDATE CASCADE then you could eliminate the dropping/adding back of the FK. The process would be: ALTER TABLE "A" ADD COLUMN id_temp BIGINT; // fill id_temp with new IDs UPDATE "A" SET id = id_temp; UPDATE "B" SET type = 2 WHERE type ISNULL; It might even be possible to further simplify depending on what '// fill id_temp with new IDs' actually does? > > 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); > > > > ** > > -- Adrian Klaver adrian.klaver@xxxxxxxxxxx |
