Thread: 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)?

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
guideto 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

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.

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