Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pFrcL-00020I-Cy for pgsql-general@arkaria.postgresql.org; Thu, 12 Jan 2023 07:03:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pFrcJ-00049O-S3 for pgsql-general@arkaria.postgresql.org; Thu, 12 Jan 2023 07:03:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pFrcJ-00049E-Er for pgsql-general@lists.postgresql.org; Thu, 12 Jan 2023 07:03:15 +0000 Received: from mail-qt1-x830.google.com ([2607:f8b0:4864:20::830]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pFrcH-0000Cb-CT for pgsql-general@lists.postgresql.org; Thu, 12 Jan 2023 07:03:15 +0000 Received: by mail-qt1-x830.google.com with SMTP id v14so15805128qtq.3 for ; Wed, 11 Jan 2023 23:03:13 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=Yr9tQty7yxIGMTPmpFANcHDJkopkwwzK310YvgvPeL0=; b=qvu+BDNGS/VFClQKQbcPzp8znXdJYoRk1lRyOvp5ANNUI0R3DFTt1xwsh+oD3ixeL4 HtlQNYT0uLX3x9voVx/V0ACEQoMcv84LrTynC5uoEDl7h9sey83+iO95gIR9qtcGb8qr rPd54PeJ09xULb4vxLePoG4K29c8dOcaaVe7bkI8fcxtEzd1LJRVTQuIZjTUTInHVQ5D Y4W107oksFYMBCVmQxZS/XhBVst3WwOy5Sh86PMBDroMmfi1gRyqZibzsc5QFTst+A8o /8lz8SlZ7zl9tIAqMcsLFM5+0fi7fOfQKufDWUFoVtShmzs6MhuLdffdQ17U5+g9hH/p 83kA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=Yr9tQty7yxIGMTPmpFANcHDJkopkwwzK310YvgvPeL0=; b=HZUheJOiTZERIZ48V01FyGtlNa6ma6Sa3s9QhNuY+JP+yieIazsiaAsMT9rWtlV132 WV+u+i+Ublwkczg1J39vBXELbnBrHXuHuwep4G6HuKzG1I0Yy3BbWR4rbc38qRAnruZ9 DeKeGDZp96ueWLFuxvKzua98Otqg9ExV5WSkRXdTMm2cpfLmbo5VHi8zLP7YUufRtp3t dB3aUtHu7WduAC2RDmQtRUGrp2YZ1g8B5K8w7WNDlUHyUSLdxWUL8x1SzZV6y3WrZBhP /7jeczwfp+7kpujKxvspQ/zTz2SV2zWrSNC2ZjFSLTJVkTu4l3lnp/wrypzSzfvClu+E ITxQ== X-Gm-Message-State: AFqh2krjKT2acaIxDaItPpf3lBusveccAhDwe/ihRmKsDFggk5lKQINx ejg/Z2OtxwkPoPp9KOyaldw1CpgFoYwIbg== X-Google-Smtp-Source: AMrXdXsTsTN8986DCna/pmTjrAd970x74iV6ogW4Hwhsd/W4A3WoQUFEnoKaYy21DcewuZt2M9EVkw== X-Received: by 2002:a05:622a:418b:b0:3a8:2f65:6ccb with SMTP id cd11-20020a05622a418b00b003a82f656ccbmr94285796qtb.65.1673506991376; Wed, 11 Jan 2023 23:03:11 -0800 (PST) Received: from [192.168.1.66] (108-250-206-84.lightspeed.nworla.sbcglobal.net. [108.250.206.84]) by smtp.googlemail.com with ESMTPSA id z26-20020ac8101a000000b003a70a675066sm8648469qti.79.2023.01.11.23.03.11 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 11 Jan 2023 23:03:11 -0800 (PST) Content-Type: multipart/alternative; boundary="------------f0ahqfRomKFYzDh0zXK7FYk0" Message-ID: <431108a8-d814-848e-202b-e3a8e78ea39a@gmail.com> Date: Thu, 12 Jan 2023 01:03:10 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: EXPLAIN and FK references? Content-Language: en-US To: pgsql-general References: <269a6794-aa20-283d-bbc4-8cddaa2038de@gmail.com> <899474.1673503646@sss.pgh.pa.us> From: Ron In-Reply-To: <899474.1673503646@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------f0ahqfRomKFYzDh0zXK7FYk0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 1/12/23 00:07, Tom Lane wrote: > Ron 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. --------------f0ahqfRomKFYzDh0zXK7FYk0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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.
--------------f0ahqfRomKFYzDh0zXK7FYk0--