[PERFORM] slow delete due to reference - Mailing list pgsql-performance

From Rikard Pavelic
Subject [PERFORM] slow delete due to reference
Date
Msg-id 18791378-d62f-0d3d-b2d7-8221454cc6e0@ngs.hr
Whole thread Raw
Responses Re: [PERFORM] slow delete due to reference  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Karl Czajkowski
Date:
Subject: [PERFORM] Re: Fwd: Slow query from ~7M rows, joined to two tables of ~100 rowseach
Next
From: Tom Lane
Date:
Subject: Re: [PERFORM] slow delete due to reference