Tom Lane <tgl@xxxxxxxxxxxxx> ezt írta (időpont: 2023. aug. 15., K, 22:37):
Les <nagylzs@xxxxxxxxx> writes:
> 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?
Probably because you don't have an index on the referencing column.
You can get away with that, if you don't care about the speed of
deletes from the PK table ...
For fk_pfft_product constraint this is true, but I always thought that PostgreSQL can use an index "partially". There is already an index:
CREATE UNIQUE INDEX uidx_product_file_file_tag ON product.product_file_tag USING btree (product_file_id, file_tag_id);
It has the same order, only it has one column more. Wouldn't it be possible to use it for the plan?
After I created these two missing indices:
CREATE INDEX idx_pft_pf ON product.product_file_tag USING btree (product_file_id);
CREATE INDEX idx_pfq_src_pf ON product.product_file_queue USING btree (src_product_file_id);
I could delete all 40 000 records in 10 seconds.
Thank you!
Laszlo
