2

The example query is this:

EXPLAIN (ANALYZE, BUFFERS)
DELETE FROM user_account
WHERE id IN (10841464,10841563,10841868,10842077,10842163,10842229,10842279,10842326,10842343,10842348,10842354,10842490,10842544,10842923,10843046,10843082,10843119,10843122,10843160,10843189,10843190,10843293,10843306,10843323,10843364,10843517,10843612,10843631,10843710,10843768,10843788,10843837,10843994,10844021,10844031,10844034,10844064,10844111,10844130,10844180,10844191,10844342,10844401,10844426,10844445,10844545,10844569,10844787,10844852,10844879,10844884,10845028,10845747,10845819,10846048,10846139,10846228,10846234,10846236,10846237,10846332,10846337,10846423,10846558,10846639,10846852,10846875,10846971,10847002,10847011,10847031,10847048,10847060,10847146,10847188,10847337,10847959,10847993,10848124,10848139,10848210,10848368,10848424,10848434,10848447,10848450,10848465,10848482,10848494,10848518,10848543,10848598,10848665,10848677,10848740,10848773,10848822,10848858,10848905,10848956);

which produces

Delete on user_account  (cost=0.43..752.92 rows=0 width=0) (actual time=1.725..1.726 rows=0 loops=1)
  Buffers: shared hit=983 dirtied=2
  ->  Index Scan using user_account_pkey on user_account  (cost=0.43..752.92 rows=100 width=6) (actual time=0.014..0.212 rows=100 loops=1)
        Index Cond: (id = ANY ('{10841464,10841563,10841868,10842077,10842163,10842229,10842279,10842326,10842343,10842348,10842354,10842490,10842544,10842923,10843046,10843082,10843119,10843122,10843160,10843189,10843190,10843293,10843306,10843323,10843364,10843517,10843612,10843631,10843710,10843768,10843788,10843837,10843994,10844021,10844031,10844034,10844064,10844111,10844130,10844180,10844191,10844342,10844401,10844426,10844445,10844545,10844569,10844787,10844852,10844879,10844884,10845028,10845747,10845819,10846048,10846139,10846228,10846234,10846236,10846237,10846332,10846337,10846423,10846558,10846639,10846852,10846875,10846971,10847002,10847011,10847031,10847048,10847060,10847146,10847188,10847337,10847959,10847993,10848124,10848139,10848210,10848368,10848424,10848434,10848447,10848450,10848465,10848482,10848494,10848518,10848543,10848598,10848665,10848677,10848740,10848773,10848822,10848858,10848905,10848956}'::integer[]))
        Buffers: shared hit=385
Planning Time: 0.145 ms
Trigger for constraint user_account_id_fkey on user_account: time=15.319 calls=100
Trigger for constraint requested_feature_user_account_id_fk on user_account: time=1.974 calls=100
Trigger for constraint user_account_user_group_history_user_account_id_fkey on user_account: time=2.972 calls=100
Trigger for constraint user_account_user_group_history_user_actor_account_id_fkey on user_account: time=1.618 calls=100
Trigger for constraint user_account_last_active_user_account_id_fkey on user_account: time=16.374 calls=100
Trigger for constraint referral_intent_user_account_id_fkey on user_account: time=134.401 calls=100
Trigger for constraint referral_intent_referral_action_referee_user_account_id_fkey on user_account: time=8.698 calls=100
Trigger for constraint user_account_stripe_merchant_customer_user_account_id_fkey on user_account: time=8.033 calls=100
Trigger for constraint independent_portfolio_plan_subscription_user_account_id_fk on user_account: time=7.219 calls=100
Trigger for constraint ledger_account_user_account_id_fkey on user_account: time=3.410 calls=100
Trigger for constraint user_account_id_fkey on user_account: time=4.292 calls=100
Trigger for constraint user_account_list_owner_user_account_id_fkey on user_account: time=3.164 calls=100
Trigger for constraint user_account_list_user_account_id_fkey on user_account: time=1.702 calls=100
Trigger for constraint short_url_user_account_id_fkey on user_account: time=3.319 calls=100
Trigger for constraint instagram_account_user_account_id_fkey on user_account: time=1.276 calls=100
Trigger for constraint chat_message_recipient_user_account_id_fkey on user_account: time=4.947 calls=100
Trigger for constraint chat_message_sender_user_account_id_fkey on user_account: time=3.223 calls=100
Trigger for constraint collaborator_user_account_id_fkey on user_account: time=1.745 calls=100
Trigger for constraint contra_signup_referral_referred_by_user_account_id_fkey on user_account: time=1.149 calls=100
Trigger for constraint contra_signup_referral_referred_user_account_id_fkey on user_account: time=0.904 calls=100
Trigger for constraint contra_transfer_recipient_user_account_id_fkey on user_account: time=1.343 calls=100
Trigger for constraint contra_transfer_sender_user_account_id_fkey on user_account: time=1.123 calls=100
Trigger for constraint country_payments_open_notification_request_user_account_id_fkey on user_account: time=1.097 calls=100
Trigger for constraint crypto_currency_address_user_account_id_fkey on user_account: time=1.401 calls=100
Trigger for constraint crypto_payout_user_account_id_fkey on user_account: time=1.334 calls=100
Trigger for constraint email_subscription_settings_user_account_id_fkey on user_account: time=1.723 calls=100
Trigger for constraint featured_user_account_user_account_id_fkey on user_account: time=1.085 calls=100
Trigger for constraint globally_suppressed_email_addre_created_by_user_account_id_fkey on user_account: time=1.042 calls=100
Trigger for constraint hiring_request_match_user_account_id_fkey on user_account: time=3.585 calls=100
Trigger for constraint job_posting_application_user_account_id_fkey on user_account: time=1.378 calls=100
Trigger for constraint job_posting_match_email_user_account_id_fkey on user_account: time=3.325 calls=100
Trigger for constraint job_posting_subcategory_user_account_user_account_id_fkey on user_account: time=1.264 calls=100
Trigger for constraint job_posting_user_account_id_fkey on user_account: time=1.940 calls=100
Trigger for constraint job_posting_view_event_user_account_id_fkey on user_account: time=3.214 calls=100
Trigger for constraint notification_user_account_id_fkey on user_account: time=1.576 calls=100
Trigger for constraint organization_approved_by_user_account_id_fkey on user_account: time=4.994 calls=100
Trigger for constraint organization_created_by_user_account_id_fkey on user_account: time=7.703 calls=100
Trigger for constraint paid_project_cancelled_by_user_account_id_fkey on user_account: time=1.630 calls=100
Trigger for constraint paid_project_client_user_account_id_fkey on user_account: time=1.270 calls=100
Trigger for constraint paid_project_contractor_user_account_id_fkey on user_account: time=1.299 calls=100
Trigger for constraint paid_project_disputed_by_user_account_id_fkey on user_account: time=1.065 calls=100
Trigger for constraint paid_project_history_user_account_id_fkey on user_account: time=1.516 calls=100
Trigger for constraint paid_project_inquiry_cc_user_account_id_fkey on user_account: time=1.331 calls=100
Trigger for constraint paid_project_invite_recipient_user_account_id_fkey on user_account: time=1.355 calls=100
Trigger for constraint paid_project_invite_sender_user_account_id_fkey on user_account: time=1.224 calls=100
Trigger for constraint paid_project_terms_declined_user_account_fkey on user_account: time=1.197 calls=100
Trigger for constraint paypal_order_user_account_id_fkey on user_account: time=0.962 calls=100
Trigger for constraint paypal_payout_user_account_id_fkey on user_account: time=1.169 calls=100
Trigger for constraint pending_paid_project_inquiry_client_user_account_id_fkey on user_account: time=1.199 calls=100
Trigger for constraint pending_paid_project_inquiry_contractor_user_account_id_fkey on user_account: time=3.017 calls=100
Trigger for constraint persona_inquiry_user_account_id_fkey on user_account: time=1.475 calls=100
Trigger for constraint portfolio_project_blog_author_user_account_fkey on user_account: time=1.387 calls=100
Trigger for constraint portfolio_project_collaborator_user_account_id_fkey on user_account: time=1.287 calls=100
Trigger for constraint portfolio_project_view_event_viewer_user_account_id_fkey on user_account: time=1.235 calls=100
Trigger for constraint productized_service_collaborator_user_account_id_fkey on user_account: time=2.271 calls=100
Trigger for constraint productized_service_sort_order_user_account_id_fkey on user_account: time=1.426 calls=100
Trigger for constraint productized_service_user_account_id_fkey on user_account: time=1.919 calls=100
Trigger for constraint productized_service_view_event_viewer_user_account_id_fkey on user_account: time=1.246 calls=100
Trigger for constraint professional_role_approved_by_user_account_id_fkey on user_account: time=2.941 calls=100
Trigger for constraint professional_role_created_by_user_account_id_fkey on user_account: time=2.262 calls=100
Trigger for constraint professional_role_user_account_user_account_id_fkey on user_account: time=1.339 calls=100
Trigger for constraint professional_tool_approved_by_user_account_id_fkey on user_account: time=1.216 calls=100
Trigger for constraint professional_tool_created_by_user_account_id_fkey on user_account: time=1.068 calls=100
Trigger for constraint profile_picture_user_account_id_fkey on user_account: time=1.605 calls=100
Trigger for constraint project_user_account_id_fkey on user_account: time=1.917 calls=100
Trigger for constraint requested_feature_user_account_user_account_id_fkey on user_account: time=1.236 calls=100
Trigger for constraint retool_user_account_flag_user_account_id_fkey on user_account: time=0.850 calls=100
Trigger for constraint social_media_profile_user_account_id_fkey on user_account: time=1.335 calls=100
Trigger for constraint stripe_external_account_user_account_id_fkey on user_account: time=1.322 calls=100
Trigger for constraint stripe_payment_intent_user_account_id_fkey on user_account: time=1.347 calls=100
Trigger for constraint stripe_payment_method_user_account_id_fkey on user_account: time=1.344 calls=100
Trigger for constraint stripe_payout_id_user_account_id_fkey on user_account: time=4.268 calls=100
Trigger for constraint stripe_refund_beneficiary_user_account_id on user_account: time=1.346 calls=100
Trigger for constraint stripe_refund_user_account_id_fkey on user_account: time=0.940 calls=100
Trigger for constraint stripe_transfer_recipient_user_account_id_fkey on user_account: time=3.534 calls=100
Trigger for constraint stripe_transfer_sender_user_account_id_fkey on user_account: time=2.592 calls=100
Trigger for constraint stripe_user_account_user_account_id_fkey on user_account: time=1.370 calls=100
Trigger for constraint testimonial_author_user_account_id_fkey on user_account: time=1.164 calls=100
Trigger for constraint testimonial_recipient_user_account_id_fkey on user_account: time=0.945 calls=100
Trigger for constraint uploaded_file_user_account_id_fkey on user_account: time=1.209 calls=100
Trigger for constraint uploaded_video_user_account_id_fkey on user_account: time=1.021 calls=100
Trigger for constraint user_account_authentication_token_user_account_id_fkey on user_account: time=1.280 calls=100
Trigger for constraint user_account_confirmation_token_user_account_id_fkey on user_account: time=2.211 calls=100
Trigger for constraint user_account_dismissed_job_posting_user_account_fkey on user_account: time=3.391 calls=100
Trigger for constraint user_account_preferred_professional_tool_user_account_id_fkey on user_account: time=1.412 calls=100
Trigger for constraint user_account_professional_language_user_account_id_fkey on user_account: time=1.099 calls=100
Trigger for constraint user_account_user_group_user_account_id_fkey on user_account: time=2.936 calls=100
Trigger for constraint user_account_work_preferences_user_account_id_fkey on user_account: time=1.775 calls=100
Trigger for constraint user_payout_method_user_account_id_fkey on user_account: time=1.512 calls=100
Trigger for constraint user_profile_auto_rating_user_account_fkey on user_account: time=1.407 calls=100
Trigger for constraint user_profile_banner_image_user_account_id_fkey on user_account: time=1.300 calls=100
Trigger for constraint user_profile_external_link_user_account_id_fkey on user_account: time=1.665 calls=100
Trigger for constraint user_profile_intro_video_user_account_id_fkey on user_account: time=1.003 calls=100
Trigger for constraint user_profile_manual_rating_rated_by_fkey on user_account: time=3.285 calls=100
Trigger for constraint user_profile_manual_rating_user_account_fkey on user_account: time=0.934 calls=100
Trigger for constraint user_profile_view_event_subject_user_account_id_fkey on user_account: time=3.365 calls=100
Trigger for constraint user_profile_view_event_viewer_user_account_id_fkey on user_account: time=2.850 calls=100
Trigger for constraint user_profile_custom_domain_user_account_id_fkey on user_account: time=6.346 calls=100
Trigger for constraint user_account_list_user_account_list_id_fkey on user_account_list: time=1.387 calls=100
Trigger for constraint short_code_confirmation_attempt_confirmation_token_id_fkey on user_account_confirmation_token: time=1.553 calls=100
Trigger user_account_user_group_after_delete_trigger on user_account_user_group: time=7.157 calls=253
Trigger for constraint user_account_user_profile_avatar_image_id_fkey on user_profile_avatar_image: time=0.915 calls=36
Trigger for constraint job_posting_application_portfolio_project_project_id_fkey on portfolio_project: time=0.855 calls=76
Trigger for constraint notification_portfolio_project_id_fkey on portfolio_project: time=0.702 calls=76
Trigger for constraint organization_portfolio_project_portfolio_project_id_fkey on portfolio_project: time=1.194 calls=76
Trigger for constraint portfolio_project_collaborator_linked_portfolio_project_id_fkey on portfolio_project: time=0.760 calls=76
Trigger for constraint portfolio_project_collaborator_portfolio_project_id_fkey on portfolio_project: time=0.622 calls=76
Trigger for constraint portfolio_project_content_image_portfolio_project_id_fkey on portfolio_project: time=0.747 calls=76
Trigger for constraint portfolio_project_content_video_portfolio_project_id_fkey on portfolio_project: time=0.704 calls=76
Trigger for constraint portfolio_project_external_link_portfolio_project_id_fkey on portfolio_project: time=0.767 calls=76
Trigger for constraint portfolio_project_id_fkey on portfolio_project: time=1.761 calls=76
Trigger for constraint portfolio_project_productized_service_portfolio_project_id_fkey on portfolio_project: time=0.696 calls=76
Trigger for constraint portfolio_project_view_event_portfolio_project_id_fkey on portfolio_project: time=2.131 calls=76
Trigger for constraint professional_role_portfolio_project_portfolio_project_id_fkey on portfolio_project: time=0.786 calls=76
Trigger for constraint professional_tool_portfolio_project_portfolio_project_id_fkey on portfolio_project: time=1.131 calls=76
Trigger for constraint user_profile_auto_rating_project_deductions_project_id_fkey on portfolio_project: time=0.810 calls=76
Trigger for constraint ledger_transaction_entry_account_id_fkey on ledger_account: time=11.073 calls=81
Trigger for constraint stripe_external_account_stripe_user_account_id_fkey on stripe_user_account: time=0.322 calls=10
Trigger for constraint crypto_payout_crypto_currency_address_id_fkey on crypto_currency_address: time=0.838 calls=19
Trigger for constraint user_account_default_crypto_currency_address_id_fkey on crypto_currency_address: time=0.688 calls=19
Trigger for constraint user_payout_method_crypto_currency_address_id_fkey on crypto_currency_address: time=0.414 calls=19
Trigger for constraint notification_productized_service_id_fkey on productized_service: time=0.619 calls=48
Trigger for constraint paid_project_inquiry_productized_service_id_fkey on productized_service: time=0.491 calls=48
Trigger for constraint paid_project_invite_productized_service_id_fkey on productized_service: time=0.468 calls=48
Trigger for constraint pending_paid_project_inquiry_productized_service_id_fkey on productized_service: time=1.427 calls=48
Trigger for constraint portfolio_project_productized_service_productized_service_id_fk on productized_service: time=0.496 calls=48
Trigger for constraint productized_service_collaborator_productized_service_id_fkey on productized_service: time=0.923 calls=48
Trigger for constraint productized_service_deliverable_productized_service_fkey on productized_service: time=0.704 calls=48
Trigger for constraint productized_service_fee_productized_service_id_fkey on productized_service: time=0.884 calls=48
Trigger for constraint productized_service_sort_order_productized_service_id_fkey on productized_service: time=0.611 calls=48
Trigger for constraint productized_service_view_event_productized_service_id_fkey on productized_service: time=1.341 calls=48
Trigger for constraint professional_role_productized_service_productized_service_id_fk on productized_service: time=0.582 calls=48
Trigger for constraint professional_tool_productized_service_productized_service_id_fk on productized_service: time=0.547 calls=48
Trigger for constraint user_profile_auto_rating_service_deductions_service_id_fkey on productized_service: time=0.590 calls=48
Trigger for constraint short_code_confirmation_attempt_authentication_token_id_fkey on user_account_authentication_token: time=0.082 calls=1
Trigger for constraint user_account_authentication_t_user_account_authentication__fkey on user_account_authentication_token: time=0.202 calls=1
Trigger for constraint job_posting_professional_language_job_posting_id_fkey on job_posting: time=0.702 calls=5
Trigger for constraint paid_project_inquiry_job_posting_id_fkey on job_posting: time=0.247 calls=5
Trigger for constraint hiring_request_job_posting_id_fkey on job_posting: time=0.368 calls=5
Trigger for constraint job_posting_application_job_posting_id_fkey on job_posting: time=0.217 calls=5
Trigger for constraint job_posting_deliverable_job_posting_fkey on job_posting: time=0.280 calls=5
Trigger for constraint job_posting_job_posting_subcategory_job_posting_id_fkey on job_posting: time=0.309 calls=5
Trigger for constraint job_posting_match_email_job_posting_id_fkey on job_posting: time=0.284 calls=5
Trigger for constraint job_posting_professional_role_job_posting_id_fkey on job_posting: time=0.293 calls=5
Trigger for constraint job_posting_professional_tool_job_posting_id_fkey on job_posting: time=0.256 calls=5
Trigger for constraint job_posting_view_event_job_posting_id_fkey on job_posting: time=0.147 calls=5
Trigger for constraint notification_job_posting_id_fkey on job_posting: time=0.205 calls=5
Trigger for constraint user_account_dismissed_job_posting_job_posting_fkey on job_posting: time=0.167 calls=5
Trigger for constraint job_posting_application_portfolio_project_application_id_fkey on job_posting_application: time=0.081 calls=1
Trigger for constraint job_posting_application_response_job_posting_application_id_fke on job_posting_application: time=0.159 calls=1
Trigger for constraint paid_project_bonus_paid_project_id_fkey on paid_project: time=1.889 calls=1
Trigger for constraint paid_project_cadence_cylce_paid_project_id_fkey on paid_project: time=0.188 calls=1
Trigger for constraint invoice_paid_project_id_fkey on paid_project: time=0.727 calls=1
Trigger for constraint chat_message_sender_paid_project_id_fkey on paid_project: time=0.088 calls=1
Trigger for constraint job_posting_application_paid_project_id_fkey on paid_project: time=0.057 calls=1
Trigger for constraint paid_project_fee_paid_project_id_fkey on paid_project: time=0.280 calls=1
Trigger for constraint paid_project_history_paid_project_id_fkey on paid_project: time=0.068 calls=1
Trigger for constraint paid_project_inquiry_paid_project_id_fkey on paid_project: time=0.051 calls=1
Trigger for constraint paid_project_invite_paid_project_id_fkey on paid_project: time=0.082 calls=1
Trigger for constraint paid_project_paypal_order_paid_project_id_fkey on paid_project: time=0.128 calls=1
Trigger for constraint paid_project_stripe_payment_intent_paid_project_id_fkey on paid_project: time=0.194 calls=1
Trigger for constraint paid_project_stripe_payout_paid_project_fkey on paid_project: time=0.138 calls=1
Trigger for constraint paid_project_submission_paid_project_id_fkey on paid_project: time=0.171 calls=1
Trigger for constraint paid_project_term_paid_project_id_fkey on paid_project: time=0.163 calls=1
Trigger for constraint paid_project_terms_declined_paid_project_fkey on paid_project: time=0.050 calls=1
Trigger for constraint testimonial_paid_project_id_fkey on paid_project: time=0.049 calls=1
Trigger for constraint hiring_request_match_hiring_request_id_fkey on hiring_request: time=0.288 calls=5
Trigger for constraint paid_project_term_paid_project_fee_id_fkey on paid_project_fee: time=0.069 calls=1
Trigger for constraint paid_project_current_paid_project_term_id_fkey on paid_project_term: time=0.108 calls=1
Trigger for constraint paid_project_term_deliverable_paid_project_fkey on paid_project_term: time=0.210 calls=1
Trigger for constraint paid_project_term_paid_project_milestone_term_id_fkey on paid_project_term: time=0.232 calls=1
Trigger ledger_transaction_entry_after_delete_trigger on ledger_transaction_entry: time=3.395 calls=2
Execution Time: 436.255 ms

or this visual reference https://explain.depesz.com/s/bR75#html

enter image description here

The subject constraint comes from referral_intent table.

postgres=> SELECT conrelid::regclass AS table_from, conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE
  contype IN ('f', 'p ') AND
  conname = 'referral_intent_user_account_id_fkey' AND
  connamespace = 'public'::regnamespace
ORDER BY conrelid::regclass::text, contype DESC;
   table_from    |               conname                |                                 pg_get_constraintdef
-----------------+--------------------------------------+--------------------------------------------------------------------------------------
 referral_intent | referral_intent_user_account_id_fkey | FOREIGN KEY (referrer_user_account_id) REFERENCES user_account(id) ON DELETE CASCADE
(1 row)

The table itself has the necessary indexes and does not have a lot of data:

postgres=> \d+ referral_intent
                                                                 Table "public.referral_intent"
          Column          |           Type           | Collation | Nullable |           Default            | Storage | Compression | Stats target | Description
--------------------------+--------------------------+-----------+----------+------------------------------+---------+-------------+--------------+-------------
 id                       | integer                  |           | not null | generated always as identity | plain   |             |              |
 referrer_user_account_id | integer                  |           | not null |                              | plain   |             |              |
 http_client_id           | integer                  |           | not null |                              | plain   |             |              |
 created_at               | timestamp with time zone |           | not null | now()                        | plain   |             |              |
 updated_at               | timestamp with time zone |           | not null | now()                        | plain   |             |              |
Indexes:
    "referral_intent_pkey" PRIMARY KEY, btree (id)
    "referral_intent_http_client_id_idx" btree (http_client_id)
    "referral_intent_user_account_id_idx" btree (referrer_user_account_id)
Foreign-key constraints:
    "referral_intent_http_client_id_fkey" FOREIGN KEY (http_client_id) REFERENCES http_client(id) ON DELETE CASCADE
    "referral_intent_user_account_id_fkey" FOREIGN KEY (referrer_user_account_id) REFERENCES user_account(id) ON DELETE CASCADE
Referenced by:
    TABLE "referral_intent_referral_action" CONSTRAINT "referral_intent_referral_action_referral_intent_id_fkey" FOREIGN KEY (referral_intent_id) REFERENCES referral_intent(id) ON DELETE CASCADE
Publications:
    "gce_publication"
Triggers:
    referral_intent_before_update_trigger BEFORE UPDATE ON referral_intent FOR EACH ROW EXECUTE FUNCTION updated_at_before_update_trigger()
Access method: heap

Table referral_intent_referral_action references referral_intent by its PK.

psql (14.5 (Homebrew), server 14.4)
Type "help" for help.

postgres=> \d referral_intent_referral_action
                              Table "public.referral_intent_referral_action"
         Column          |           Type           | Collation | Nullable |           Default
-------------------------+--------------------------+-----------+----------+------------------------------
 id                      | integer                  |           | not null | generated always as identity
 referral_intent_id      | integer                  |           | not null |
 referee_user_account_id | integer                  |           | not null |
 referral_action_id      | integer                  |           | not null |
 created_at              | timestamp with time zone |           | not null | now()
 updated_at              | timestamp with time zone |           | not null | now()
Indexes:
    "referral_intent_referral_action_pkey" PRIMARY KEY, btree (id)
    "referral_intent_referral_action_referee_user_account_id_idx" btree (referee_user_account_id)
    "referral_intent_referral_action_referral_action_id_idx" btree (referral_action_id)
    "referral_intent_referral_action_referral_intent_id_idx" btree (referral_intent_id)
Foreign-key constraints:
    "referral_intent_referral_action_referee_user_account_id_fkey" FOREIGN KEY (referee_user_account_id) REFERENCES user_account(id) ON DELETE CASCADE
    "referral_intent_referral_action_referral_action_id_fkey" FOREIGN KEY (referral_action_id) REFERENCES referral_action(id) ON DELETE CASCADE
    "referral_intent_referral_action_referral_intent_id_fkey" FOREIGN KEY (referral_intent_id) REFERENCES referral_intent(id) ON DELETE CASCADE
Publications:
    "gce_publication"
Triggers:
    referral_intent_referral_action_before_update_trigger BEFORE UPDATE ON referral_intent_referral_action FOR EACH ROW EXECUTE FUNCTION updated_at_before_update_trigger()

referral_intent table size is tiny (25k rows) and we are deleting just 100 rows in this query.

postgres=> select schemaname,
relname,
pg_size_pretty(pg_relation_size(schemaname|| '.' || relname)) as size,
n_live_tup,
n_dead_tup,
CASE WHEN n_live_tup > 0 THEN round((n_dead_tup::float /
n_live_tup::float)::numeric, 4) END AS dead_tup_ratio,
last_autovacuum,
last_autoanalyze
from pg_stat_user_tables
where relname = 'referral_intent'
order by dead_tup_ratio desc NULLS LAST;
 schemaname |     relname     |  size   | n_live_tup | n_dead_tup | dead_tup_ratio |        last_autovacuum        |       last_autoanalyze
------------+-----------------+---------+------------+------------+----------------+-------------------------------+------------------------------
 public     | referral_intent | 1648 kB |      27193 |          0 |         0.0000 | 2022-11-10 11:40:49.533801+00 | 2022-11-10 14:31:14.91536+00
(1 row)

What am I missing?

6
  • 1
    If you've set ON CASCADE DELETE and you have to delete those records across, uh, 170+ tables, it's going to take longer than deleting from one table.
    – user212533
    Nov 10, 2022 at 15:09
  • Of course, but the subject here is a specific table referral_intent. Other tables have 10s of millions of records and delete in under a few ms. Whereas this small table is taking more 10x-50x more.
    – Gajus
    Nov 10, 2022 at 15:12
  • 1
    That particular table has 3 indexes, so you're "deleting" four times - once for the heap and then once for each index. It's taking less than half a second to execute a delete across 170+ tables - I question the need to focus on less than 10% of that execution time.
    – user212533
    Nov 10, 2022 at 15:21
  • You are not deleting only 100 rows though. You are deleting 100 rows from the user_account table and all referencing rows from other tables. They may well be 10k rows in that referral_intent table that are referencing those 100. Nov 10, 2022 at 17:03
  • 1
    Maybe just the index is bloated though the table is not? Anyway, a way to get a more or less definitive answer is to enable auto_explain with auto_explain.log_nested_statements, auto_explain.log_analyze and auto_explain.log_buffers all set to ON, and with auto_explain.log_min_duration set to some small value, maybe even zero. If you are a superuser, you can make the changes only in the local session to avoid flooding the log files.
    – jjanes
    Nov 10, 2022 at 19:38

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.