On Wednesday, February 12, 2020, PG Bug reporting form <
noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 16256
Logged by: Valentin Kovalenko
Email address: valentin.male.kovalenko@gmail.com
PostgreSQL version: 12.1
Operating system: Ubuntu 18.04.2 LTS
Description:
create unlogged table fk_holder (
fk bigint not null,
serial bigint unique not null,
constraint fk_holder_fk_fkey foreign key (fk) references pk_holder
(pk) deferrable initially immediate);
In the proposed experiment committing a transaction that updates PK values
on 2000 rows in the pk_holder table takes 2 minutes / 30 milliseconds = 4000
times more time than committing a transaction that updated FK values in 2000
rows in the fk_holder table.
As noted on the CREATE TABLE page FK section:
“If the referenced column(s) are changed frequently, it might be wise to add an index to the referencing column(s) so that referential actions associated with the foreign key constraint can be performed more efficiently.”
With the omission of such an index in your test fixture you’ve demonstrated why that advice is provided.
David J.