Re: LONG delete with LOTS of FK's
От | Larry Rosenman |
---|---|
Тема | Re: LONG delete with LOTS of FK's |
Дата | |
Msg-id | ffa82cacf6329eaff2f670cd633da49b@webmail.lerctr.org обсуждение исходный текст |
Ответ на | Re: LONG delete with LOTS of FK's (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: LONG delete with LOTS of FK's
|
Список | 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
В списке pgsql-general по дате отправления:
Предыдущее
От: Merlin MoncureДата:
Сообщение: Re: Deploying PostgreSQL on CentOS with SSD and Hardware RAID
Следующее
От: Matt BrockДата:
Сообщение: Re: Deploying PostgreSQL on CentOS with SSD and Hardware RAID