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

On 2013-05-10 09:14, Tom Lane wrote:
> Larry Rosenman <ler@lerctr.org> writes:
> Any ideas on how to figure out if we ARE getting seqscan check plans,
> and better fix it?
>
> Try an EXPLAIN ANALYZE VERBOSE on something that just deletes one row,
> and wait however long it takes.  The printout should show how much time
> is taken in the implementation trigger for each foreign key.  That will
> at least nail down which table(s) are causing problems.
>
> A different line of thought is that the EXPLAIN I suggested in
> <25119.1367507317@sss.pgh.pa.us> isn't an entirely accurate
> representation of what a foreign-key checking query is like, because
> the
> check queries are parameterized.  You might need to do this instead:
>
> prepare foo(referenced_column_data_type) as
> select 1 from <referencing_table> where referencing_column = $1;
> explain execute foo(sample_value);
>
> and verify you get a cheap plan for each referencing table.
>
>             regards, tom lane
We don't :(

[lrosenman@233175-blueprint-db1 ~]$ cat Seq.new_exp
Seq Scan on account_billing_info  (cost=0.00..7.19 rows=1 width=0)
Seq Scan on account_main_admin  (cost=0.00..4.69 rows=1 width=0)
Seq Scan on bnymellon1_values  (cost=0.00..288848.72 rows=10357338
width=0)
Seq Scan on capgemini8_values  (cost=0.00..380499.85 rows=12309748
width=0)
Seq Scan on cityofcalgary_values  (cost=0.00..245690.53 rows=8410682
width=0)
Seq Scan on css_fro_values  (cost=0.00..505110.71 rows=15228057 width=0)
Seq Scan on cvscaremarkadp_values  (cost=0.00..17062.58 rows=602126
width=0)
Seq Scan on ericsson2_values  (cost=0.00..104704.84 rows=3513987
width=0)
Seq Scan on ibmbpmandrules_values  (cost=0.00..153210.55 rows=5337724
width=0)
Seq Scan on ibmbwlteam_values  (cost=0.00..7903.44 rows=274515 width=0)
Seq Scan on ibmgbs_values  (cost=0.00..399206.24 rows=13983459 width=0)
Seq Scan on ibmtechsales_values  (cost=0.00..232201.80 rows=8204144
width=0)
Seq Scan on jmffamilyent_values  (cost=0.00..53596.24 rows=1874339
width=0)
Seq Scan on johnsoncontrols5_values  (cost=0.00..69047.31 rows=2405705
width=0)
Seq Scan on keybank3_values  (cost=0.00..23789.16 rows=855293 width=0)
Seq Scan on mondialassistancegroup_values  (cost=0.00..122394.54
rows=4454283 width=0)
Seq Scan on permitted_ips  (cost=0.00..4.01 rows=15 width=0)
Seq Scan on presby_health_serv_values  (cost=0.00..37387.31 rows=1340345
width=0)
Seq Scan on principal_fin_grp4_values  (cost=0.00..69872.73 rows=2436698
width=0)
Seq Scan on processdoc_tc_nz_values  (cost=0.00..360360.30 rows=10975144
width=0)
Seq Scan on saml2_idp_account_junction  (cost=0.00..1.07 rows=1 width=0)
Seq Scan on troweprice2_values  (cost=0.00..253867.86 rows=9135429
width=0)
[lrosenman@233175-blueprint-db1 ~]$ cat fix_sql

[lrosenman@233175-blueprint-db1 ~]$ cat fix_sql
prepare foo_account_activity(bigint) as
select 1 from account_activity where account_id = $1;
explain execute foo_account_activity(29818880);
prepare foo_account_billing_info(bigint) as
select 1 from account_billing_info where account_id = $1;
explain execute foo_account_billing_info(29818880);
prepare foo_account_cleaving(bigint) as
select 1 from account_cleaving where account_id = $1;
explain execute foo_account_cleaving(29818880);
prepare foo_account_locked_by(bigint) as
select 1 from account_locked_by where account_id = $1;
explain execute foo_account_locked_by(29818880);
prepare foo_account_logo(bigint) as
select 1 from account_logo where account_id = $1;
explain execute foo_account_logo(29818880);
prepare foo_account_main_admin(bigint) as
select 1 from account_main_admin where account_id = $1;
explain execute foo_account_main_admin(29818880);
prepare foo_account_organization(bigint) as
select 1 from account_organization where account_id = $1;
explain execute foo_account_organization(29818880);
prepare foo_preferences(bigint) as
select 1 from preferences where account_id = $1;
explain execute foo_preferences(29818880);
prepare foo_account_properties(bigint) as
select 1 from account_properties where account_id = $1;
explain execute foo_account_properties(29818880);
prepare foo_avatars(bigint) as
select 1 from avatars where account_id = $1;
explain execute foo_avatars(29818880);
prepare foo_billing_address(bigint) as
select 1 from billing_address where account_id = $1;
explain execute foo_billing_address(29818880);
prepare foo_billing_transaction(bigint) as
select 1 from billing_transaction where account_id = $1;
explain execute foo_billing_transaction(29818880);
prepare foo_billing_transaction_item(bigint) as
select 1 from billing_transaction_item where account_id = $1;
explain execute foo_billing_transaction_item(29818880);
prepare foo_blobs(bigint) as
select 1 from blobs where account_id = $1;
explain execute foo_blobs(29818880);
prepare foo_bnymellon1_values(bigint) as
select 1 from bnymellon1_values where account_id = $1;
explain execute foo_bnymellon1_values(29818880);
prepare foo_capgemini8_values(bigint) as
select 1 from capgemini8_values where account_id = $1;
explain execute foo_capgemini8_values(29818880);
prepare foo_cityofcalgary_values(bigint) as
select 1 from cityofcalgary_values where account_id = $1;
explain execute foo_cityofcalgary_values(29818880);
prepare foo_comments(bigint) as
select 1 from comments where account_id = $1;
explain execute foo_comments(29818880);
prepare foo_comments_history(bigint) as
select 1 from comments_history where account_id = $1;
explain execute foo_comments_history(29818880);
prepare foo_continued_business_need(bigint) as
select 1 from continued_business_need where account_id = $1;
explain execute foo_continued_business_need(29818880);
prepare foo_css_fro_values(bigint) as
select 1 from css_fro_values where account_id = $1;
explain execute foo_css_fro_values(29818880);
prepare foo_cvscaremarkadp_values(bigint) as
select 1 from cvscaremarkadp_values where account_id = $1;
explain execute foo_cvscaremarkadp_values(29818880);
prepare foo_epayment_capture_response(bigint) as
select 1 from epayment_capture_response where account_id = $1;
explain execute foo_epayment_capture_response(29818880);
prepare foo_ericsson2_values(bigint) as
select 1 from ericsson2_values where account_id = $1;
explain execute foo_ericsson2_values(29818880);
prepare foo_file_attachment(bigint) as
select 1 from file_attachment where account_id = $1;
explain execute foo_file_attachment(29818880);
prepare foo_frozen_values(bigint) as
select 1 from frozen_values where account_id = $1;
explain execute foo_frozen_values(29818880);
prepare foo_future_transaction(bigint) as
select 1 from future_transaction where account_id = $1;
explain execute foo_future_transaction(29818880);
prepare foo_ibm_values(bigint) as
select 1 from ibm_values where account_id = $1;
explain execute foo_ibm_values(29818880);
prepare foo_ibmbpmandrules_values(bigint) as
select 1 from ibmbpmandrules_values where account_id = $1;
explain execute foo_ibmbpmandrules_values(29818880);
prepare foo_ibmbwlteam_values(bigint) as
select 1 from ibmbwlteam_values where account_id = $1;
explain execute foo_ibmbwlteam_values(29818880);
prepare foo_ibmgbs_values(bigint) as
select 1 from ibmgbs_values where account_id = $1;
explain execute foo_ibmgbs_values(29818880);
prepare foo_ibmtechsales_values(bigint) as
select 1 from ibmtechsales_values where account_id = $1;
explain execute foo_ibmtechsales_values(29818880);
prepare foo_instance(bigint) as
select 1 from instance where account_id = $1;
explain execute foo_instance(29818880);
prepare foo_instance_values(bigint) as
select 1 from instance_values where account_id = $1;
explain execute foo_instance_values(29818880);
prepare foo_jmffamilyent_values(bigint) as
select 1 from jmffamilyent_values where account_id = $1;
explain execute foo_jmffamilyent_values(29818880);
prepare foo_johnsoncontrols5_values(bigint) as
select 1 from johnsoncontrols5_values where account_id = $1;
explain execute foo_johnsoncontrols5_values(29818880);
prepare foo_keybank3_values(bigint) as
select 1 from keybank3_values where account_id = $1;
explain execute foo_keybank3_values(29818880);
prepare foo_mondialassistancegroup_values(bigint) as
select 1 from mondialassistancegroup_values where account_id = $1;
explain execute foo_mondialassistancegroup_values(29818880);
prepare foo_names(bigint) as
select 1 from names where account_id = $1;
explain execute foo_names(29818880);
prepare foo_permitted_ips(bigint) as
select 1 from permitted_ips where account_id = $1;
explain execute foo_permitted_ips(29818880);
prepare foo_presby_health_serv_values(bigint) as
select 1 from presby_health_serv_values where account_id = $1;
explain execute foo_presby_health_serv_values(29818880);
prepare foo_principal_fin_grp4_values(bigint) as
select 1 from principal_fin_grp4_values where account_id = $1;
explain execute foo_principal_fin_grp4_values(29818880);
prepare foo_item_publications(bigint) as
select 1 from item_publications where account_id = $1;
explain execute foo_item_publications(29818880);
prepare foo_processdoc_tc_nz_values(bigint) as
select 1 from processdoc_tc_nz_values where account_id = $1;
explain execute foo_processdoc_tc_nz_values(29818880);
prepare foo_property_descriptions(bigint) as
select 1 from property_descriptions where account_id = $1;
explain execute foo_property_descriptions(29818880);
prepare foo_saml2_idp_account_junction(bigint) as
select 1 from saml2_idp_account_junction where account_id = $1;
explain execute foo_saml2_idp_account_junction(29818880);
prepare foo_task(bigint) as
select 1 from task where account_id = $1;
explain execute foo_task(29818880);
prepare foo_template_category(bigint) as
select 1 from template_category where account_id = $1;
explain execute foo_template_category(29818880);
prepare foo_template(bigint) as
select 1 from template where account_id = $1;
explain execute foo_template(29818880);
prepare foo_text_search_data(bigint) as
select 1 from text_search_data where account_id = $1;
explain execute foo_text_search_data(29818880);
prepare foo_troweprice2_values(bigint) as
select 1 from troweprice2_values where account_id = $1;
explain execute foo_troweprice2_values(29818880);
prepare foo_usage(bigint) as
select 1 from usage where account_id = $1;
explain execute foo_usage(29818880);
prepare foo_user_expanded_subs(bigint) as
select 1 from user_expanded_subs where account_id = $1;
explain execute foo_user_expanded_subs(29818880);
prepare foo_user_favorites(bigint) as
select 1 from user_favorites where account_id = $1;
explain execute foo_user_favorites(29818880);
prepare foo_user_followed(bigint) as
select 1 from user_followed where account_id = $1;
explain execute foo_user_followed(29818880);
prepare foo_user_groups(bigint) as
select 1 from user_groups where account_id = $1;
explain execute foo_user_groups(29818880);
prepare foo_user_permissions(bigint) as
select 1 from user_permissions where account_id = $1;
explain execute foo_user_permissions(29818880);
prepare foo_user_prefs(bigint) as
select 1 from user_prefs where account_id = $1;
explain execute foo_user_prefs(29818880);
prepare foo_user_viewed(bigint) as
select 1 from user_viewed where account_id = $1;
explain execute foo_user_viewed(29818880);
prepare foo_userid(bigint) as
select 1 from userid where account_id = $1;
explain execute foo_userid(29818880);
prepare foo_values(bigint) as
select 1 from values where account_id = $1;
explain execute foo_values(29818880);
[lrosenman@233175-blueprint-db1 ~]$

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