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/
Attachment:
signature.asc
Description: OpenPGP digital signature
