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/