Ron <ronljohnsonjr@xxxxxxxxx> writes:Deletes are slow in one table with many indices and FK references. That's not surprising, but it's *VERY* slow, and I'm trying to figure out why.Is there any EXPLAIN option which shows what "query plans" Pg is using when checking FK references (index scan, seq scan, etc) during deletes (and inserts and updates)?No, not directly, but you could look at EXPLAIN ANALYZE to see which of the RI triggers is eating the time.
Good to know, but even deleting one day of data (90,000 rows using an index scan on the date field) takes forever.
This is the DELETE explain plan, and the table definition after I deleted its FK constraints. (All July 2020 records were previously deleted from tables referencing strans.transmission.)
sides=> explain (format yaml) DELETE FROM strans.transmission
WHERE part_date BETWEEN '2020-07-01'::timestamp AND '2020-07-01'::timestamp + INTERVAL'1 DAY' - INTERVAL'1 SECOND';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
- Plan: +
Node Type: "ModifyTable" +
Operation: "Delete" +
Parallel Aware: false +
Relation Name: "transmission" +
Alias: "transmission" +
Startup Cost: 0.56 +
Total Cost: 297639.15 +
Plan Rows: 94500 +
Plan Width: 6 +
Plans: +
- Node Type: "Index Scan" +
Parent Relationship: "Member" +
Parallel Aware: false +
Scan Direction: "Forward" +
Index Name: "xif_sit_part_date" +
Relation Name: "transmission" +
Alias: "transmission" +
Startup Cost: 0.56 +
Total Cost: 297639.15 +
Plan Rows: 94500 +
Plan Width: 6 +
Index Cond: "((part_date >= '2020-07-01 00:00:00'::timestamp without time zone) AND (part_date <= '2020-07-01 23:59:59'::timestamp without time
zone))"
(1 row)
sides=> \d strans.transmission
Table "strans.transmission"
Column | Type | Collation | Nullable | Default
---------------------------+-----------------------------+-----------+----------+---------
transmission_id | numeric(38,0) | | not null |
transmission_type | character varying(20) | | not null |
endpoint_id | numeric(38,0) | | not null |
destination_endpoint_id | numeric(38,0) | | |
begin_transmission_dts | timestamp without time zone | | not null |
processing_completed_dts | timestamp without time zone | | |
failed_ind | character varying(1) | | |
message_size | numeric(38,0) | | |
record_count | numeric(38,0) | | |
attachement_count | numeric(38,0) | | |
attachment_size | numeric(38,0) | | |
file_guid | character varying(36) | | |
acknowledge_by_dts | timestamp without time zone | | |
acknowledged_dts | timestamp without time zone | | |
endpoint_ip | character varying(220) | | |
duplicate_ind | numeric(38,0) | | not null | 0
parent_transmission_id | numeric(38,0) | | |
message_code | character varying(4) | | |
acknowledged_override_dts | timestamp without time zone | | |
push_attempt | numeric(8,0) | | |
bundle_parent_id | numeric(38,0) | | |
partition_date | timestamp without time zone | | |
part_date | timestamp without time zone | | not null |
Indexes:
"transmission_pkey" PRIMARY KEY, btree (transmission_id, part_date)
"xif8transmission" UNIQUE, btree (transmission_id, endpoint_id, destination_endpoint_id, part_date)
"apr25_begin_transmission_dts" btree (begin_transmission_dts)
"apr25_bundle_parent_id" btree (bundle_parent_id)
"apr25_parent_transmission_id" btree (parent_transmission_id)
"xif1transmission" btree (endpoint_id)
"xif4transmission" btree (destination_endpoint_id)
"xif5transmission" btree (processing_completed_dts)
"xif6transmission" btree (file_guid)
"xif7transmission" btree (failed_ind)
"xif9transmission" btree (transmission_type, destination_endpoint_id, endpoint_id, processing_completed_dts, transmission_id)
"xif_sit_part_date" btree (part_date)
Referenced by:
TABLE "sew_pin" CONSTRAINT "sew_pin_to_tran" FOREIGN KEY (transmission_id, part_date) REFERENCES transmission(transmission_id, part_date) ON DELETE SET NULL
TABLE "separation_request" CONSTRAINT "trans_to_sep_request" FOREIGN KEY (transmission_id, part_date) REFERENCES transmission(transmission_id, part_date)
TABLE "transmission_x_error" CONSTRAINT "trans_to_trans_x_error" FOREIGN KEY (transmission_id, part_date) REFERENCES transmission(transmission_id, part_date)
TABLE "si_digital_signature" CONSTRAINT "xfksi_digital_sig_to_transm" FOREIGN KEY (transmission_id, part_date) REFERENCES transmission(transmission_id, part_date)
It's not going to be hard to figure out which one(s) are using indexed plans and which are not. regards, tom lane
--
Born in Arizona, moved to Babylonia.
Born in Arizona, moved to Babylonia.
