Thread: [PERFORM] slow delete due to reference

Is it possible to speed up deletes which have null references so they don't check if a reference is valid?

I had this scenario:

--large table not referenced from other places
CREATE TABLE large_table
(
 id bigserial primary key,
 ref_1 bigint not null,
 ref_2 bigint not null,
 at_1 timestamptz not null,
 at_2 timestamptz not null,
 amount numeric not null,
 type_1 int not null,
 type_2 int not null,
 undo_id bigint references large_table
);
--some random data with some self references
insert into large_table
select i, i/10, i/100, now() , now(), i%1000, i%10, i%20, case when i%1000 = 3 then i -1 else null end
from generate_series(1, 1000000) i;

--create unique index ix_undo on large_table(undo_id) where undo_id is not null;

analyze large_table;

--some new data with unique type_1 which don't have self references
insert into large_table
select 1000000 + i, i/10, i/100, now(), now(), i%1000, 11, i%20, null
from generate_series(1, 100000) i;

delete from large_table where type_1 = 11;

I had to cancel the last delete and create an index on undo_id for the last query to run fast.
(I was actually expecting that commented out index to exists, but for some reason it didn't)

Regards,
Rikard

--
Rikard Pavelic
https://dsl-platform.com/
http://templater.info/

Rikard Pavelic <rikard@ngs.hr> writes:
> Is it possible to speed up deletes which have null references so they don't check if a reference is valid?

You're thinking about the problem backwards.  Since the table is
self-referential, each row is both a PK (referenced) row and an FK
(referencing) row.  In its role as an FK row, a delete requires no work,
null referencing column or otherwise --- but in its role as a PK row, a
delete does require work.  The PK column here is "id" which is not null in
any row, so for every row, the FK trigger must check to see whether that
id is referenced by any FK row.  With no index on the FK column (undo_id)
that requires an expensive seqscan.

There are optimizations to skip the check when deleting a null PK value,
but that case never occurs in your example.

> --create unique index ix_undo on large_table(undo_id) where undo_id is not null;
> (I was actually expecting that commented out index to exists, but for some reason it didn't)

It would've done the job if you'd had it, I believe.

            regards, tom lane