Re: BUG #16256: Checking deferred FK constraints when updating PK is ordrs of magnitude slower than when updating FK

On Wednesday, February 12, 2020, PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 16256
> Logged by: Valentin Kovalenko
> Email address: valentin(dot)male(dot)kovalenko(at)gmail(dot)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.