pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
EXPLAIN and FK references?
8+ messages / 4 participants
[nested] [flat]

* EXPLAIN and FK references?
@ 2023-01-12 05:42  Ron <ronljohnsonjr@gmail.com>
  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 06:07  Tom Lane <tgl@sss.pgh.pa.us>
  parent: 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 06:13  Christophe Pettus <xof@thebuild.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  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 06:42  Julien Rouhaud <rjuju123@gmail.com>
  parent: Christophe Pettus <xof@thebuild.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 07:03  Ron <ronljohnsonjr@gmail.com>
  parent: 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 07:11  Tom Lane <tgl@sss.pgh.pa.us>
  parent: 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 07:33  Ron <ronljohnsonjr@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  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 07:39  Julien Rouhaud <rjuju123@gmail.com>
  parent: Ron <ronljohnsonjr@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