Thread: LONG delete with LOTS of FK's
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
On 05/01/2013 10:17 AM, Larry Rosenman wrote: > I have an app that we have a number of tables that all have FK > relationships with the account table. OK so far. > Referenced by: > * Redacted one-billion foreign keys Wow. I really hope every single one of those tables has an index on the listed column, and it's the same datatype (bigint). Otherwise, this is going to take a long, long time. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 sthomas@optionshouse.com ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
On 2013-05-01 10:23, Shaun Thomas wrote: > On 05/01/2013 10:17 AM, Larry Rosenman wrote: > >> I have an app that we have a number of tables that all have FK >> relationships with the account table. > > OK so far. > >> Referenced by: >> * Redacted one-billion foreign keys > > Wow. I really hope every single one of those tables has an index on > the listed column, and it's the same datatype (bigint). Otherwise, > this is going to take a long, long time. Yes, all of them have at least one index that has account_id as the first column (we use that to separate tenants in a multi-tenant app). I still wonder if there is a way to see where it is :( -- 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
Larry Rosenman <ler@lerctr.org> writes:
> On 2013-05-01 10:23, Shaun Thomas wrote:
>> Wow. I really hope every single one of those tables has an index on
>> the listed column, and it's the same datatype (bigint). Otherwise,
>> this is going to take a long, long time.
> Yes, all of them have at least one index that has account_id as the
> first column (we use that to
> separate tenants in a multi-tenant app).
> I still wonder if there is a way to see where it is :(
You could try EXPLAIN ANALYZE on a cut-down query that just deletes
a few hundred or thousand rows. That would help identify whether
there's a particular table that's causing the bottleneck.
regards, tom lane
On 2013-05-01 10:17, Larry Rosenman wrote: > 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=> 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? The delete was still running this AM, so we killed it. I'm looking at the schema and not finding any missing indexes (assuming a multicolumn index with account_id first is sufficient). Thanks! -- 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
Larry Rosenman <ler@lerctr.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
On 2013-05-02 10:08, Tom Lane wrote: > Larry Rosenman <ler@lerctr.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 Thanks, Tom. I found 12 tables with Sequential scans. We'll fix that. Appreciate the help. -- 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
On 2013-05-02 10:08, Tom Lane wrote: > Larry Rosenman <ler@lerctr.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@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893
On 2013-05-09 15:50, Larry Rosenman wrote: > On 2013-05-02 10:08, Tom Lane wrote: > Larry Rosenman <ler@lerctr.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. When I did the cancel, the backtrace: 2013-05-09 15:54:32,618 ERROR [pool-1-thread-5] spring.LonSQLExceptionTranslator - Exception executing SQL "DELETE FROM account WHERE id IN (248512512,9310573878,588120064,643694592,255393792,512884736,440139776,47448064,324337664,311361536,459276288,5013159782,5009475335,637796352,355794944,486866944,692846592)" for task "StatementCallback". SQLState is "57014". org.postgresql.util.PSQLException: ERROR: canceling statement due to user request Where: SQL statement "SELECT 1 FROM ONLY "public"."ibmgbs_values" x WHERE $1 OPERATOR(pg_catalog.=) "account_id" FOR SHARE OF x" at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:366) at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:368) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:342) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:375) at com.lombardi.online.spring.MonitoringJdbcTemplate.execute(MonitoringJdbcTemplate.java:118) at com.lombardi.online.billing.process.DeleteExpiredAccountsProcess$1.doInTransactionWithoutResult(DeleteExpiredAccountsProcess.java:96) at org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:33) at com.lombardi.online.repository.db.DeadlockRetryTransactionTemplate$TransactionCallbackWrapper.doInTransaction(DeadlockRetryTransactionTemplate.java:90) at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:127) at com.lombardi.online.repository.db.DeadlockRetryTransactionTemplate.execute(DeadlockRetryTransactionTemplate.java:58) at com.lombardi.online.billing.process.DeleteExpiredAccountsProcess.execute(DeleteExpiredAccountsProcess.java:93) at com.lombardi.online.billing.AbstractBillingEngine.deleteExpiredAccounts(AbstractBillingEngine.java:1285) at com.lombardi.online.web.rest.StartRunAccountDeletion$2.run(StartRunAccountDeletion.java:116) at com.lombardi.online.utility.RunnableWithLogging.run(RunnableWithLogging.java:21) at com.lombardi.online.utility.ExecutorPool$2.call(ExecutorPool.java:164) at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) at java.util.concurrent.FutureTask.run(FutureTask.java:138) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:98) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:206) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:662) Is there any way to know what it's doing while it's running? -- 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
On 05/09/2013 03:58 PM, Larry Rosenman wrote: > "SELECT 1 FROM ONLY "public"."ibmgbs_values" x WHERE $1 > OPERATOR(pg_catalog.=) "account_id" FOR SHARE OF x" This is the statement it canceled on. I've found that when long deletes like this happen, it's because of the statement that you see when you cancel. Something tells me that if you try this again, it'll be the same foreign key check. Look and make sure account_id in ibmgbs_values is the same exact type as the referenced table. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 sthomas@optionshouse.com ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
Larry Rosenman <ler@lerctr.org> writes:
>> Ideas on how to debug?
Perhaps it's blocked on a lock? Did you look into pg_locks?
Did you note whether the process was consuming CPU time and/or doing IO?
regards, tom lane
On 2013-05-09 16:20, Shaun Thomas wrote: > On 05/09/2013 03:58 PM, Larry Rosenman wrote: > > "SELECT 1 FROM ONLY "public"."ibmgbs_values" x WHERE $1 > OPERATOR(pg_catalog.=) "account_id" FOR SHARE OF x" > > This is the statement it canceled on. I've found that when long > deletes like this happen, it's because of the statement that you see > when you cancel. Something tells me that if you try this again, it'll > be the same foreign key check. > > Look and make sure account_id in ibmgbs_values is the same exact type > as the referenced table. it is: druckerdb=> \d ibmgbs_values Table "public.ibmgbs_values" Column | Type | Modifiers -------------------+-----------------------------+----------- account_id | bigint | not null All are bigints. It's been on various tables, and they are all bigints. -- 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
On 2013-05-09 16:22, Tom Lane wrote: > Larry Rosenman <ler@lerctr.org> writes: > Ideas on how to debug? > > Perhaps it's blocked on a lock? Did you look into pg_locks? > Did you note whether the process was consuming CPU time and/or doing > IO? > > regards, tom lane all the locks were clear, and it was consuming CPU and doing I/O (D->S->D state), etc. -- 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
On 05/09/2013 04:22 PM, Larry Rosenman wrote:
> It's been on various tables, and they are all bigints.
Hey, ya never know. I've gotten tripped up similarly. In that case, I
defer to Tom's suggestion. If there are any '<IDLE> in transaction'
statements, or your long delete is marked as waiting in
pg_stat_activity, something is up. I've also found this query extremely
helpful in tracking down things like this:
SELECT DISTINCT l1.pid AS blocker_pid, a.current_query AS blocker_query,
a.usename AS blocker_user, a.client_addr AS blocker_client,
l2.pid AS blocked_pid, a2.current_query AS blocked_query,
a2.usename AS blocked_user, a2.client_addr AS blocked_client
FROM pg_locks l1
JOIN pg_stat_activity() a on (a.procpid = l1.pid)
JOIN pg_locks l2 ON (l1.relation = l2.relation AND l1.pid != l2.pid)
JOIN pg_stat_activity() a2 on (a2.procpid = l2.pid)
WHERE l1.granted
AND NOT l2.granted;
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
Larry Rosenman <ler@lerctr.org> writes:
> On 2013-05-09 16:22, Tom Lane wrote:
>> Perhaps it's blocked on a lock? Did you look into pg_locks?
>> Did you note whether the process was consuming CPU time and/or doing
>> IO?
> all the locks were clear, and it was consuming CPU and doing I/O
> (D->S->D state), etc.
Hm. I'm suspicious that you still ended up with a seqscan checking
plan. Was this session started after you added all the missing indexes?
If not, it seems possible that it was using a bad pre-cached plan.
regards, tom lane
On 2013-05-09 16:40, Tom Lane wrote: > Larry Rosenman <ler@lerctr.org> writes: > On 2013-05-09 16:22, Tom Lane wrote: > Perhaps it's blocked on a lock? Did you look into pg_locks? > Did you note whether the process was consuming CPU time and/or doing > IO? > > all the locks were clear, and it was consuming CPU and doing I/O > (D->S->D state), etc. > > Hm. I'm suspicious that you still ended up with a seqscan checking > plan. Was this session started after you added all the missing > indexes? > If not, it seems possible that it was using a bad pre-cached plan. > > regards, tom lane I added the indexes on last friday, and we've done a number of vacuumdb -zav's (every night) since then. So, if there's a cached plan, it's not from me. (we also restarted our app on Saturday night). -- 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
On 2013-05-09 16:43, Larry Rosenman wrote: > On 2013-05-09 16:40, Tom Lane wrote: > Larry Rosenman <ler@lerctr.org> writes: > On 2013-05-09 16:22, Tom Lane wrote: > Perhaps it's blocked on a lock? Did you look into pg_locks? > Did you note whether the process was consuming CPU time and/or doing > IO? > > all the locks were clear, and it was consuming CPU and doing I/O > (D->S->D state), etc. > > Hm. I'm suspicious that you still ended up with a seqscan checking > plan. Was this session started after you added all the missing > indexes? > If not, it seems possible that it was using a bad pre-cached plan. > > regards, tom lane > I added the indexes on last friday, and we've done a number of > vacuumdb -zav's (every night) since then. > > So, if there's a cached plan, it's not from me. > > (we also restarted our app on Saturday night). Any ideas on how to figure out if we ARE getting seqscan check plans, and better fix it? -- 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
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
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
Larry Rosenman <ler@lerctr.org> writes:
> On 2013-05-10 09:14, Tom Lane wrote:
>> ... and verify you get a cheap plan for each referencing table.
> We don't :(
Ugh. I bet the problem is that in some of these tables, there are lots
and lots of duplicate account ids, such that seqscans look like a good
bet when searching for an otherwise-unknown id. You don't see this
with a handwritten test for a specific id because then the planner can
see it's not any of the common values.
9.2 would fix this for you --- any chance of updating?
regards, tom lane
On 2013-05-10 10:57, Tom Lane wrote: > Larry Rosenman <ler@lerctr.org> writes: > On 2013-05-10 09:14, Tom Lane wrote: > ... and verify you get a cheap plan for each referencing table. > > We don't :( > > Ugh. I bet the problem is that in some of these tables, there are lots > and lots of duplicate account ids, such that seqscans look like a good > bet when searching for an otherwise-unknown id. You don't see this > with a handwritten test for a specific id because then the planner can > see it's not any of the common values. > > 9.2 would fix this for you --- any chance of updating? > > regards, tom lane I'll see what we can do. I was looking for a reason, this may be it. Thanks for all your help. -- 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
On Fri, May 10, 2013 at 11:01:15AM -0500, Larry Rosenman wrote: - On 2013-05-10 10:57, Tom Lane wrote: - >Larry Rosenman <ler@lerctr.org> writes: - >On 2013-05-10 09:14, Tom Lane wrote: - >... and verify you get a cheap plan for each referencing table. - > - >We don't :( - > - >Ugh. I bet the problem is that in some of these tables, there are lots - >and lots of duplicate account ids, such that seqscans look like a good - >bet when searching for an otherwise-unknown id. You don't see this - >with a handwritten test for a specific id because then the planner can - >see it's not any of the common values. - > - >9.2 would fix this for you --- any chance of updating? - > - > regards, tom lane - I'll see what we can do. I was looking for a reason, this may be it. - - Thanks for all your help. I haven't seen an explain for this badboy, maybe I missed it (even just a plain explain might be useful) but you may be running into a situation where the planner is trying to materialize or hash 2 big tables. I've actually run into that in the past and had some success in PG9.1 running with enable_material=false for some queries. It might be worth a shot to play with that and enable_hashagg/enable_hashjoin=false (If you get a speedup, it points to some tuning/refactoring that could happen) Dave
On 2013-05-16 17:52, David Kerr wrote: > On Fri, May 10, 2013 at 11:01:15AM -0500, Larry Rosenman wrote: > - On 2013-05-10 10:57, Tom Lane wrote: > - >Larry Rosenman <ler@lerctr.org> writes: > - >On 2013-05-10 09:14, Tom Lane wrote: > - >... and verify you get a cheap plan for each referencing table. > - > > - >We don't :( > - > > - >Ugh. I bet the problem is that in some of these tables, there are > lots > - >and lots of duplicate account ids, such that seqscans look like a > good > - >bet when searching for an otherwise-unknown id. You don't see this > - >with a handwritten test for a specific id because then the planner > can > - >see it's not any of the common values. > - > > - >9.2 would fix this for you --- any chance of updating? > - > > - > regards, tom lane > - I'll see what we can do. I was looking for a reason, this may be it. > - > - Thanks for all your help. > > I haven't seen an explain for this badboy, maybe I missed it (even just > a > plain explain might be useful) but you may be running into a situation > where > the planner is trying to materialize or hash 2 big tables. > > I've actually run into that in the past and had some success in PG9.1 > running > with enable_material=false for some queries. > > It might be worth a shot to play with that and > enable_hashagg/enable_hashjoin=false > (If you get a speedup, it points to some tuning/refactoring that could > happen) > > Dave I'll take a look tomorrow, but we WERE seeing Seq Scan's against multi-million row tables, so I suspect Tom is right on with the replanning that's in 9.2 fixing it, and I'm in the process of validating that. I'll have that news tomorrow as well. (updated a test box with the production DB to 9.2.4 and am running a Vacuum Analyze and then will redo this test with that DB/Engine. -- 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
On Thu, May 16, 2013 at 06:01:51PM -0500, Larry Rosenman wrote: - On 2013-05-16 17:52, David Kerr wrote: - >On Fri, May 10, 2013 at 11:01:15AM -0500, Larry Rosenman wrote: - >- On 2013-05-10 10:57, Tom Lane wrote: - >- >Larry Rosenman <ler@lerctr.org> writes: - >- >On 2013-05-10 09:14, Tom Lane wrote: - >- >... and verify you get a cheap plan for each referencing table. - >- > - >- >We don't :( - >- > - >- >Ugh. I bet the problem is that in some of these tables, there are - >lots - >- >and lots of duplicate account ids, such that seqscans look like a - >good - >- >bet when searching for an otherwise-unknown id. You don't see this - >- >with a handwritten test for a specific id because then the planner - >can - >- >see it's not any of the common values. - >- > - >- >9.2 would fix this for you --- any chance of updating? - >- > - >- > regards, tom lane - >- I'll see what we can do. I was looking for a reason, this may be it. - >- - >- Thanks for all your help. - > - >I haven't seen an explain for this badboy, maybe I missed it (even just - >a - >plain explain might be useful) but you may be running into a situation - >where - >the planner is trying to materialize or hash 2 big tables. - > - >I've actually run into that in the past and had some success in PG9.1 - >running - >with enable_material=false for some queries. - > - >It might be worth a shot to play with that and - >enable_hashagg/enable_hashjoin=false - >(If you get a speedup, it points to some tuning/refactoring that could - >happen) - > - >Dave - I'll take a look tomorrow, but we WERE seeing Seq Scan's against - multi-million - row tables, so I suspect Tom is right on with the replanning that's in - 9.2 fixing - it, and I'm in the process of validating that. That seems likely, although you could try enable_seqscan=false as well. Dave
On 2013-05-16 18:35, David Kerr wrote: > - I'll take a look tomorrow, but we WERE seeing Seq Scan's against > - multi-million > - row tables, so I suspect Tom is right on with the replanning that's > in > - 9.2 fixing > - it, and I'm in the process of validating that. > > That seems likely, although you could try enable_seqscan=false as well. > > Dave The 9.2 upgrade DOES fix my issue. Thanks again, Tom and everyone. -- 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
On 18/05/13 03:06, Larry Rosenman wrote:
On 2013-05-16 18:35, David Kerr wrote:Did you also enable_seqscan=false as well?- I'll take a look tomorrow, but we WERE seeing Seq Scan's againstThe 9.2 upgrade DOES fix my issue.
- multi-million
- row tables, so I suspect Tom is right on with the replanning that's in
- 9.2 fixing
- it, and I'm in the process of validating that.
That seems likely, although you could try enable_seqscan=false as well.
Dave
Thanks again, Tom and everyone.
Cheers,
Gavin
Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:
On 18/05/13 03:06, Larry Rosenman wrote:On 2013-05-16 18:35, David Kerr wrote:Did you also enable_seqscan=false as well?- I'll take a look tomorrow, but we WERE seeing Seq Scan's againstThe 9.2 upgrade DOES fix my issue.
- multi-million
- row tables, so I suspect Tom is right on with the replanning that's in
- 9.2 fixing
- it, and I'm in the process of validating that.
That seems likely, although you could try enable_seqscan=false as well.
Dave
Thanks again, Tom and everyone.
Cheers,
Gavin
No. Same set up as 9.1
--
Sent from Kaiten Mail. Please excuse my brevity.