From: | Les <nagylzs(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: slow delete |
Date: | 2023-08-16 04:43:05 |
Message-ID: | CAKXe9UDay-87yFzm6D89Z+a_V9fasnTMpUyQgsa7YBRON5QmDA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2023-08-16 14:03:49 | Re: slow delete |
Previous Message | Tom Lane | 2023-08-15 20:37:39 | Re: slow delete |