Re: slow delete

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> ezt írta (időpont: 2023. aug. 15., K, 22:37):

> Les <nagylzs(at)gmail(dot)com> 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

>
>