Re: LONG delete with LOTS of FK's - Mailing list pgsql-general
| From | Larry Rosenman |
|---|---|
| Subject | Re: LONG delete with LOTS of FK's |
| Date | |
| Msg-id | f6202b163961e286989ff6d5bdef367f@webmail.lerctr.org Whole thread Raw |
| In response to | Re: LONG delete with LOTS of FK's (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: LONG delete with LOTS of FK's
|
| List | pgsql-general |
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
pgsql-general by date: