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

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

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