From: | Rikard Pavelic <rikard(at)ngs(dot)hr> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | slow delete due to reference |
Date: | 2017-06-24 07:28:12 |
Message-ID: | 18791378-d62f-0d3d-b2d7-8221454cc6e0@ngs.hr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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/
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2017-06-24 16:14:52 | Re: slow delete due to reference |
Previous Message | Karl Czajkowski | 2017-06-24 02:01:16 | Re: Fwd: Slow query from ~7M rows, joined to two tables of ~100 rows each |