Re: LONG delete with LOTS of FK's - Mailing list pgsql-general

On 2013-05-01 10:17, Larry Rosenman wrote:
> I have an app that we have a number of tables that all have FK
> relationships with the account table.
>
> We did a massive (2900+ account, probably multi-thousand rows) delete
> from all the tables, and the
> delete from the account table is taking a lot of time.
>
> druckerdb=>
>
> select * from pg_stat_activity where procpid=19019;
>  datid |  datname  | procpid | usesysid | usename | application_name
> |  client_addr   | client_hostname | client_port |
> backend_start        |          xact_start           |
> query_start
>        | waiting |
>
>
>                                       current_query
>
>
>
>
-------+-----------+---------+----------+---------+------------------+-----------------+-----------------+-------------+-------------------------------+-------------------------------+-----------------------
>
-------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> --------
>  16407 | druckerdb |   19019 |    16385 | drucker |
> | 192.168.100.230 | blueprint-app1  |       49595 | 2013-04-30
> 12:16:39.774119-05 | 2013-04-30 12:18:46.630727-05 | 2013-04-30
> 12:47:17.63
> 253-05 | f       | DELETE FROM account WHERE id IN
>
(248512512,9310573878,588120064,643694592,255393792,512884736,440139776,47448064,324337664,311361536,459276288,5013159782,5009475335,637796352,355794944,486
>
866944,692846592,235077632,4998139130,566034432,13591495132,47967841244,465108992,265486336,239927296,185532416,26474002862,298319872,515571712,576192512,99504409134,478150656,13601202599,703164469,363921408
>
,359071744,251002880,454623232,47968253184,95211488107,719454210,305463296,540147712,636289024,247595008,90916420061,457965568,404291584,313327616,76808192,332595200,180879360,259194880,284819456,392036352,9
>
5206827337,209453056,272498688,292159488,618659840,78029243449,81985536,83820544,123273216,78032615730,210632704,43671807462,60838681784,149291008,718645548,53805056,579272704,377552896,579862528,175570944,7
>
8577664,301727744,711147420,5000969673,131072000,22180216181,43662172854,58195968,184483840,216989696,237961216,186384384,502988800,146341888,30777274875,395182080,379256832,618397696,602800128,269221888,501
> 1399726
> (1 row)
>
> druckerdb=>
>
>
> The xact_start is when the delete's started.
>
> Is there anything I can do to:
> 1) find out where it is?
> 2) speed it up in the future?
>
>
> druckerdb=> \d account
>                                                   Table
> "public.account"
>                 Column                 |           Type           |
>                   Modifiers
>
---------------------------------------+--------------------------+-------------------------------------------------------
>  id                                    | bigint                   |
> not null
>  name                                  | character varying(64)    |
> not null
>  value_table_name                      | character varying(32)    |
> not null
>  version_item_id                       | bigint                   |
> not null
>  blob_table_name                       | character varying(32)    |
> not null default 'blobs'::character varying
>  account_type                          | smallint                 |
> not null default 1
>  account_status                        | smallint                 |
> not null default 1
>  editor_licenses                       | integer                  |
> not null default 1
>  expire_date                           | date                     |
>  appserver                             | text                     |
> not null default 'appserver1'::text
>  appport                               | text                     |
> not null default '8080'::text
>  file_space                            | bigint                   |
>  security_policy                       | integer                  |
> not null
>  expiry_processed                      | boolean                  |
> default false
>  contributor_licenses                  | integer                  |
> not null default 0
>  valid_invite_email_domains            | character varying        |
>  allow_api_calls                       | boolean                  |
> default false
>  allow_chat                            | boolean                  |
> default true
>  is_template_account                   | boolean                  |
> not null default false
>  billing_type                          | integer                  |
> not null default 0
>  epayment_profile_id                   | text                     |
> not null
>  instance_value_table_name             | character varying(32)    |
> not null default 'instance_values'::character varying
>  show_public_stream                    | boolean                  |
> not null
>  enable_posting                        | boolean                  |
> default true
>  cbn_type                              | smallint                 |
> not null default 0
>  account_roles                         | integer                  |
> not null
>  sap_id                                | text                     |
>  performance_logging                   | smallint                 |
> default 4
>  admins_access_glossary_and_all_spaces | boolean                  |
> not null default false
>  signup_country_code                   | text                     |
>  viewer_licenses                       | integer                  |
> not null default 0
>  glossary_id                           | bigint                   |
>  terms_of_use_version_accepted         | integer                  |
>  terms_of_use_accepted_date            | timestamp with time zone |
>  terms_of_use_admin_id                 | bigint                   |
>  terms_of_use_accepted_by_id           | bigint                   |
>  eval_agreement_accepted_date          | timestamp with time zone |
>  eval_agreement_accepted_by_id         | bigint                   |
>  previous_load_duration                | bigint                   |
> not null default 0
> Indexes:
>     "account_id_key" UNIQUE CONSTRAINT, btree (id) CLUSTER
>     "account_name_key" UNIQUE CONSTRAINT, btree (name)
>     "expire_date_idx" btree (expire_date)
> Referenced by:
>     TABLE "account_activity" CONSTRAINT
> "account_activity_account_id_fkey" FOREIGN KEY (account_id) REFERENCES
> account(id)
>     TABLE "account_billing_info" CONSTRAINT
> "account_billing_info_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "account_cleaving" CONSTRAINT
> "account_cleaving_account_id_fkey" FOREIGN KEY (account_id) REFERENCES
> account(id)
>     TABLE "account_locked_by" CONSTRAINT
> "account_locked_by_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "account_logo" CONSTRAINT "account_logo_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
>     TABLE "account_main_admin" CONSTRAINT
> "account_main_admin_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "account_organization" CONSTRAINT
> "account_organization_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "preferences" CONSTRAINT
> "account_preferences_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "account_properties" CONSTRAINT
> "account_properties_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "avatars" CONSTRAINT "avatars_account_id_fkey" FOREIGN KEY
> (account_id) REFERENCES account(id)
>     TABLE "billing_address" CONSTRAINT
> "billing_address_account_id_fkey" FOREIGN KEY (account_id) REFERENCES
> account(id)
>     TABLE "billing_transaction" CONSTRAINT
> "billing_transaction_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "billing_transaction_item" CONSTRAINT
> "billing_transaction_item_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "blobs" CONSTRAINT "blobs_account_id_fkey" FOREIGN KEY
> (account_id) REFERENCES account(id)
>     TABLE "bnymellon1_values" CONSTRAINT
> "bnymellon1_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "capgemini8_values" CONSTRAINT
> "capgemini8_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "cityofcalgary_values" CONSTRAINT
> "cityofcalgary_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "comments" CONSTRAINT "comments_account_id_fkey" FOREIGN
> KEY (account_id) REFERENCES account(id)
>     TABLE "comments_history" CONSTRAINT
> "comments_history_account_id_fkey" FOREIGN KEY (account_id) REFERENCES
> account(id)
>     TABLE "continued_business_need" CONSTRAINT
> "continued_business_need_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "css_fro_values" CONSTRAINT
> "css_fro_values_account_id_fkey" FOREIGN KEY (account_id) REFERENCES
> account(id)
>     TABLE "cvscaremarkadp_values" CONSTRAINT
> "cvscaremarkadp_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "epayment_capture_response" CONSTRAINT
> "epayment_capture_response_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "ericsson2_values" CONSTRAINT
> "ericsson2_values_account_id_fkey" FOREIGN KEY (account_id) REFERENCES
> account(id)
>     TABLE "file_attachment" CONSTRAINT
> "file_attachment_info_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "frozen_values" CONSTRAINT "frozen_values_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
>     TABLE "future_transaction" CONSTRAINT
> "future_transaction_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "ibm_values" CONSTRAINT "ibm_values_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
>     TABLE "ibmbpmandrules_values" CONSTRAINT
> "ibmbpmandrules_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "ibmbwlteam_values" CONSTRAINT
> "ibmbwlteam_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "ibmgbs_values" CONSTRAINT "ibmgbs_values_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
>     TABLE "ibmtechsales_values" CONSTRAINT
> "ibmtechsales_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "instance" CONSTRAINT "instance_account_id_fkey" FOREIGN
> KEY (account_id) REFERENCES account(id)
>     TABLE "instance_values" CONSTRAINT
> "instance_values_account_id_fkey" FOREIGN KEY (account_id) REFERENCES
> account(id)
>     TABLE "jmffamilyent_values" CONSTRAINT
> "jmffamilyent_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "johnsoncontrols5_values" CONSTRAINT
> "johnsoncontrols5_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "keybank3_values" CONSTRAINT
> "keybank3_values_account_id_fkey" FOREIGN KEY (account_id) REFERENCES
> account(id)
>     TABLE "mondialassistancegroup_values" CONSTRAINT
> "mondialassistancegroup_values_account_id_fkey" FOREIGN KEY
> (account_id) REFERENCES account(id)
>     TABLE "names" CONSTRAINT "names_account_id_fkey" FOREIGN KEY
> (account_id) REFERENCES account(id)
>     TABLE "permitted_ips" CONSTRAINT "permitted_ips_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
>     TABLE "presby_health_serv_values" CONSTRAINT
> "presby_health_serv_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "principal_fin_grp4_values" CONSTRAINT
> "principal_fin_grp4_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "item_publications" CONSTRAINT
> "process_publications_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "processdoc_tc_nz_values" CONSTRAINT
> "processdoc_tc_nz_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "property_descriptions" CONSTRAINT
> "property_descriptions_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "saml2_idp_account_junction" CONSTRAINT
> "saml2_idp_account_junction_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "task" CONSTRAINT "task_account_id_fkey" FOREIGN KEY
> (account_id) REFERENCES account(id)
>     TABLE "template_category" CONSTRAINT
> "template_category_template_account_id_fkey" FOREIGN KEY
> (template_account_id) REFERENCES account(id)
>     TABLE "template" CONSTRAINT "template_template_account_id_fkey"
> FOREIGN KEY (template_account_id) REFERENCES account(id)
>     TABLE "text_search_data" CONSTRAINT
> "text_search_data_account_id_fkey" FOREIGN KEY (account_id) REFERENCES
> account(id)
>     TABLE "troweprice2_values" CONSTRAINT
> "troweprice2_values_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "usage" CONSTRAINT "usage_account_id_fkey" FOREIGN KEY
> (account_id) REFERENCES account(id)
>     TABLE "user_expanded_subs" CONSTRAINT
> "user_expanded_subs_account_id_fkey" FOREIGN KEY (account_id)
> REFERENCES account(id)
>     TABLE "user_favorites" CONSTRAINT
> "user_favorites_account_id_fkey" FOREIGN KEY (account_id) REFERENCES
> account(id)
>     TABLE "user_followed" CONSTRAINT "user_favorites_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
>     TABLE "user_groups" CONSTRAINT "user_groups_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
>     TABLE "user_permissions" CONSTRAINT
> "user_permissions_account_id_fkey" FOREIGN KEY (account_id) REFERENCES
> account(id)
>     TABLE "user_prefs" CONSTRAINT "user_prefs_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
>     TABLE "user_viewed" CONSTRAINT "user_viewed_account_id_fkey"
> FOREIGN KEY (account_id) REFERENCES account(id)
>     TABLE "userid" CONSTRAINT "userid_account_id_fkey" FOREIGN KEY
> (account_id) REFERENCES account(id)
>     TABLE ""values"" CONSTRAINT "values_account_id_fkey" FOREIGN KEY
> (account_id) REFERENCES account(id)
>
> druckerdb=>
Question: Do all these need to have a bare index just on the account_id
column, or is a multicolumn index with account_id first
sufficient for the check to be reasonably quick?

The delete was still running this AM, so we killed it.

I'm looking at the schema and not finding any missing indexes (assuming
a multicolumn index with account_id first is sufficient).

Thanks!

--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 214-642-9640 (c)     E-Mail: ler@lerctr.org
US Mail: 430 Valona Loop, Round Rock, TX 78681-3893