I have created a table called _td with about 43 000 rows. I have tried to
use this as a primary key id list to delete records from my
product.product_file table, but I could not do it. It uses 100% of one CPU
and it takes forever. Then I changed the query to delete 100 records only,
and measure the speed:
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
delete from product.product_file where id in (
select pf2_id from _td limit 100
)
It still takes 11 seconds. It means 110 msec / record, and that is
unacceptable.
I'm going to post the whole query plan at the end of this email, but I
would like to highlight the "Triggers" part:
"Triggers": [
{
"Trigger Name": "RI_ConstraintTrigger_a_26535",
"Constraint Name": "fk_pfft_product",
"Relation": "product_file",
"Time": 4.600,
"Calls": 90
},
{
"Trigger Name": "RI_ConstraintTrigger_a_26837",
"Constraint Name": "fk_product_file_src",
"Relation": "product_file",
"Time": 5.795,
"Calls": 90
},
{
"Trigger Name": "RI_ConstraintTrigger_a_75463",
"Constraint Name": "fk_pfq_src_product_file",
"Relation": "product_file",
"Time": 11179.429,
"Calls": 90
},
{
"Trigger Name": "_trg_002_aiu_audit_row",
"Relation": "product_file",
"Time": 49.410,
"Calls": 90
}
]
It seems that two foreign key constraints use 10.395 seconds out of the
total 11.24 seconds. But I don't see why it takes that much?
The product.product_file table has 477 000 rows:
CREATE TABLE product.product_file (
id uuid NOT NULL,
c_tim timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
c_uid uuid NULL,
c_sid uuid NULL,
m_tim timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
m_uid uuid NULL,
m_sid uuid NULL,
product_id uuid NOT NULL,
product_file_type_id uuid NOT NULL,
file_id uuid NOT NULL,
product_file_status_id uuid NOT NULL,
dl_url text NULL,
src_product_file_id uuid NULL,
CONSTRAINT product_file_pkey PRIMARY KEY (id),
CONSTRAINT fk_pf_file FOREIGN KEY (file_id) REFERENCES media.file(id),
CONSTRAINT fk_pf_file_type FOREIGN KEY (product_file_type_id) REFERENCES
product.product_file_type(id),
CONSTRAINT fk_pf_product FOREIGN KEY (product_id) REFERENCES
product.product(id) ON DELETE CASCADE,
CONSTRAINT fk_product_file_src FOREIGN KEY (src_product_file_id) REFERENCES
product.product_file(id),
CONSTRAINT fk_product_file_status FOREIGN KEY (product_file_status_id)
REFERENCES product.product_file_status(id)
);
CREATE INDEX idx_product_file_dl_url ON product.product_file USING btree
(dl_url) INCLUDE (product_id) WHERE (dl_url IS NOT NULL);
CREATE INDEX idx_product_file_file_product ON product.product_file USING
btree (file_id, product_id);
CREATE INDEX idx_product_file_product_file ON product.product_file USING
btree (product_id, file_id);
CREATE INDEX idx_product_file_src ON product.product_file USING btree
(src_product_file_id) WHERE (src_product_file_id IS NOT NULL);
The one with fk_pfft_product looks like this, it has about 5000 records in
it:
CREATE TABLE product.product_file_tag (
id uuid NOT NULL,
c_tim timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
c_uid uuid NULL,
c_sid uuid NULL,
m_tim timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
m_uid uuid NULL,
m_sid uuid NULL,
product_file_id uuid NOT NULL,
file_tag_id uuid NOT NULL,
CONSTRAINT product_file_tag_pkey PRIMARY KEY (id),
CONSTRAINT fk_pfft_file_tag FOREIGN KEY (file_tag_id) REFERENCES
product.file_tag(id) ON DELETE CASCADE DEFERRABLE,
CONSTRAINT fk_pfft_product FOREIGN KEY (product_file_id) REFERENCES
product.product_file(id) ON DELETE CASCADE DEFERRABLE
);
CREATE UNIQUE INDEX uidx_product_file_file_tag ON product.product_file_tag
USING btree (product_file_id, file_tag_id);
The other constraint has zero actual references, this returns zero:
select count(*) from product.product_file where src_product_file_id in (
select pf2_id from _td
); -- 0
I was trying to figure out how a foreign key constraint with zero actual
references can cost 100 msec / record, but I failed.
Can somebody please explain what is wrong here?
The plan is also visualized here:
http://tatiyants.com/pev/#/plans/plan_1692129126258
[
{
"Plan": {
"Node Type": "ModifyTable",
"Operation": "Delete",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "product_file",
"Schema": "product",
"Alias": "product_file",
"Startup Cost": 4.21,
"Total Cost": 840.79,
"Plan Rows": 0,
"Plan Width": 0,
"Actual Startup Time": 0.567,
"Actual Total Time": 0.568,
"Actual Rows": 0,
"Actual Loops": 1,
"Shared Hit Blocks": 582,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 10,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 4.21,
"Total Cost": 840.79,
"Plan Rows": 100,
"Plan Width": 46,
"A
