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@aklaver.com>
Sent: Monday, March 28, 2022 18:49
To: Per Kaminsky <per.kaminsky@hawk-intech.com>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
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@aklaver.com