EXPLAIN and FK references?
Pg 12.11
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)?
--
Born in Arizona, moved to Babylonia.
Ron <ronljohnsonjr@gmail.com> 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. It's not going to be hard to
figure out which one(s) are using indexed plans and which are not.
regards, tom lane
On Jan 11, 2023, at 22:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
No, not directly, but you could look at EXPLAIN ANALYZE to see which
of the RI triggers is eating the time. It's not going to be hard to
figure out which one(s) are using indexed plans and which are not.
Also, IIRC, the SELECTs generated to do foreign key checks do appear in pg_stat_statements, so that might provide a guide to ones that are consuming an unusually large amount of resources.
On Wed, Jan 11, 2023 at 10:13:11PM -0800, Christophe Pettus wrote:
Also, IIRC, the SELECTs generated to do foreign key checks do appear in
pg_stat_statements, so that might provide a guide to ones that are consuming
an unusually large amount of resources.
Yes, but you need to have pg_stat_statements.track = all configured for that,
which isn't the default value (and requires superuser privileges to change).
On 1/12/23 00:07, Tom Lane wrote:
Ron<ronljohnsonjr@gmail.com> 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.
Ron <ronljohnsonjr@gmail.com> writes:
On 1/12/23 00:07, Tom Lane wrote:
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.
So delete a relatively small number of rows, and do it with
EXPLAIN *ANALYZE*. Without ANALYZE, you aren't getting any
relevant data.
regards, tom lane
On 1/12/23 01:11, Tom Lane wrote:
Ron<ronljohnsonjr@gmail.com> writes:
On 1/12/23 00:07, Tom Lane wrote:
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.So delete a relatively small number of rows, and do it with
EXPLAIN *ANALYZE*. Without ANALYZE, you aren't getting any
relevant data.
Doing that when trying to delete *one minute* of data is exactly what was
needed. 99.999% of the time was spent on a checking another table which
didn't have a supporting index.
Adding that index makes things run as expected.
--
Born in Arizona, moved to Babylonia.
On Thu, Jan 12, 2023 at 01:33:56AM -0600, Ron wrote:
On 1/12/23 01:11, Tom Lane wrote:
Ron<ronljohnsonjr@gmail.com> writes:
On 1/12/23 00:07, Tom Lane wrote:
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.So delete a relatively small number of rows, and do it with
EXPLAIN *ANALYZE*. Without ANALYZE, you aren't getting any
relevant data.Doing that when trying to delete *one minute* of data is exactly what was
needed.� 99.999% of the time was spent on a checking another table which
didn't have a supporting index.Adding that index makes things run as expected.
Just in case, I just remembered that pgcluu [1]https://github.com/darold/pgcluu has a feature to automatically
detect missing indexes on FK. You can see an example report at [2]https://pgcluu.darold.net/example/dolibarr-missing-index.html.
[1]: https://github.com/darold/pgcluu
[2]: https://pgcluu.darold.net/example/dolibarr-missing-index.html