On 2013-05-10 09:14, Tom Lane wrote:
Larry Rosenman <ler@xxxxxxxxxx> 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@xxxxxxxxxxxxx> 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@xxxxxxxxxx
US Mail: 430 Valona Loop, Round Rock, TX 78681-3893
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
