Re: LONG delete with LOTS of FK's

On 2013-05-02 10:08, Tom Lane wrote:
> Larry Rosenman <ler(at)lerctr(dot)org> writes:
> 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?
>
> I would think that such an index would be sufficient, but you could
> check for yourself by doing something like
>
> explain select 1 from <table> where account_id = <something>
>
> and verifying that you get an indexscan not a seqscan, for each
> dependent table.
>
> regards, tom lane
even having done that, and with a SMALL list, we still are getting VERY
LONG deletes:
druckerdb=> select * from pg_stat_activity where procpid=17795;
datid | datname | procpid | usesysid | usename | application_name |
client_addr | client_hostname | client_port | backend_start
| xact_start | query_start
| waiting |
current_query

-------+-----------+---------+----------+---------+------------------+-----------------+-----------------+-------------+-------------------------------+-------------------------------+-----------------------
--------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------
16407 | druckerdb | 17795 | 16385 | drucker | |
192.168.100.230 | blueprint-app1 | 44191 | 2013-05-09
15:07:56.070267-05 | 2013-05-09 15:07:59.111114-05 | 2013-05-09
15:08:20.59
7237-05 | f | DELETE FROM account WHERE id IN
(248512512,9310573878,588120064,643694592,255393792,512884736,440139776,47448064,324337664,311361536,459276288,5013159782,5009475335,637796352,355794944,48
6866944,692846592)
(1 row)

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

Ideas on how to debug?

this is with 9.1.9 on RHEL5 from the PGDG RPM's.

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

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general