pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedEXPLAIN and FK references?
8+ messages / 4 participants
[nested] [flat]
* EXPLAIN and FK references?
@ 2023-01-12 05:42 Ron <ronljohnsonjr@gmail.com>
2023-01-12 06:07 ` Re: EXPLAIN and FK references? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Ron @ 2023-01-12 05:42 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: EXPLAIN and FK references?
2023-01-12 05:42 EXPLAIN and FK references? Ron <ronljohnsonjr@gmail.com>
@ 2023-01-12 06:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-12 06:13 ` Re: EXPLAIN and FK references? Christophe Pettus <xof@thebuild.com>
2023-01-12 07:03 ` Re: EXPLAIN and FK references? Ron <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Tom Lane @ 2023-01-12 06:07 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: EXPLAIN and FK references?
2023-01-12 05:42 EXPLAIN and FK references? Ron <ronljohnsonjr@gmail.com>
2023-01-12 06:07 ` Re: EXPLAIN and FK references? Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-01-12 06:13 ` Christophe Pettus <xof@thebuild.com>
2023-01-12 06:42 ` Re: EXPLAIN and FK references? Julien Rouhaud <rjuju123@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Christophe Pettus @ 2023-01-12 06:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-general <pgsql-general@lists.postgresql.org>
> 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: EXPLAIN and FK references?
2023-01-12 05:42 EXPLAIN and FK references? Ron <ronljohnsonjr@gmail.com>
2023-01-12 06:07 ` Re: EXPLAIN and FK references? Tom Lane <tgl@sss.pgh.pa.us>
2023-01-12 06:13 ` Re: EXPLAIN and FK references? Christophe Pettus <xof@thebuild.com>
@ 2023-01-12 06:42 ` Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Julien Rouhaud @ 2023-01-12 06:42 UTC (permalink / raw)
To: Christophe Pettus <xof@thebuild.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Ron <ronljohnsonjr@gmail.com>; pgsql-general <pgsql-general@lists.postgresql.org>
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).
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: EXPLAIN and FK references?
2023-01-12 05:42 EXPLAIN and FK references? Ron <ronljohnsonjr@gmail.com>
2023-01-12 06:07 ` Re: EXPLAIN and FK references? Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-01-12 07:03 ` Ron <ronljohnsonjr@gmail.com>
2023-01-12 07:11 ` Re: EXPLAIN and FK references? Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 8+ messages in thread
From: Ron @ 2023-01-12 07:03 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: EXPLAIN and FK references?
2023-01-12 05:42 EXPLAIN and FK references? Ron <ronljohnsonjr@gmail.com>
2023-01-12 06:07 ` Re: EXPLAIN and FK references? Tom Lane <tgl@sss.pgh.pa.us>
2023-01-12 07:03 ` Re: EXPLAIN and FK references? Ron <ronljohnsonjr@gmail.com>
@ 2023-01-12 07:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-12 07:33 ` Re: EXPLAIN and FK references? Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2023-01-12 07:11 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: EXPLAIN and FK references?
2023-01-12 05:42 EXPLAIN and FK references? Ron <ronljohnsonjr@gmail.com>
2023-01-12 06:07 ` Re: EXPLAIN and FK references? Tom Lane <tgl@sss.pgh.pa.us>
2023-01-12 07:03 ` Re: EXPLAIN and FK references? Ron <ronljohnsonjr@gmail.com>
2023-01-12 07:11 ` Re: EXPLAIN and FK references? Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-01-12 07:33 ` Ron <ronljohnsonjr@gmail.com>
2023-01-12 07:39 ` Re: EXPLAIN and FK references? Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Ron @ 2023-01-12 07:33 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: EXPLAIN and FK references?
2023-01-12 05:42 EXPLAIN and FK references? Ron <ronljohnsonjr@gmail.com>
2023-01-12 06:07 ` Re: EXPLAIN and FK references? Tom Lane <tgl@sss.pgh.pa.us>
2023-01-12 07:03 ` Re: EXPLAIN and FK references? Ron <ronljohnsonjr@gmail.com>
2023-01-12 07:11 ` Re: EXPLAIN and FK references? Tom Lane <tgl@sss.pgh.pa.us>
2023-01-12 07:33 ` Re: EXPLAIN and FK references? Ron <ronljohnsonjr@gmail.com>
@ 2023-01-12 07:39 ` Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Julien Rouhaud @ 2023-01-12 07:39 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
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] has a feature to automatically
detect missing indexes on FK. You can see an example report at [2].
[1] https://github.com/darold/pgcluu
[2] https://pgcluu.darold.net/example/dolibarr-missing-index.html
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-01-12 07:39 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-12 05:42 EXPLAIN and FK references? Ron <ronljohnsonjr@gmail.com>
2023-01-12 06:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-12 06:13 ` Christophe Pettus <xof@thebuild.com>
2023-01-12 06:42 ` Julien Rouhaud <rjuju123@gmail.com>
2023-01-12 07:03 ` Ron <ronljohnsonjr@gmail.com>
2023-01-12 07:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-12 07:33 ` Ron <ronljohnsonjr@gmail.com>
2023-01-12 07:39 ` Julien Rouhaud <rjuju123@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox