|
Here's the schema:
Table "public.address_list"
Column | Type | Modifiers ----------------------+------------------------+----------- address_list_id | numeric(10,0) | not null address_1 | character varying(100) | address_2 | character varying(100) | address_3 | character varying(100) | city | character varying(100) | zip_code | character varying(20) | phone_num_1 | character varying(100) | phone_num_2 | character varying(100) | phone_num_fax | character varying(100) | state_province_id | numeric(10,0) | user_account_id | numeric(10,0) | marina_id | numeric(10,0) | commercial_entity_id | numeric(10,0) | address_type_id | numeric(10,0) | not null distributor_id | numeric(10,0) | contact_info_id | numeric(10,0) | country_id | numeric(10,0) | lang_id | numeric(10,0) | boat_listing_id | numeric(10,0) | Indexes: address_list_pkey primary key btree (address_list_id), addr_list_addr_type_id_i btree (address_type_id), addr_list_bl_id_i btree (boat_listing_id), addr_list_bl_sp_count_i btree (boat_listing_id, state_province_id, country_id), addr_list_ce_sp_c_at_c_i btree (commercial_entity_id, state_province_id, country_id, address_type_id, city), addr_list_ce_sp_countr_addr_type_i btree (commercial_entity_id, state_province_id, country_id, address_type_id), addr_list_ci_id_i btree (contact_info_id), addr_list_comm_ent_id_i btree (commercial_entity_id), addr_list_count_lang_i btree (country_id, lang_id), addr_list_country_id_i btree (country_id), addr_list_cty_bl_count_i btree (city, boat_listing_id, country_id), addr_list_cty_i btree (city), addr_list_distrib_id_i btree (distributor_id), addr_list_marina_id_i btree (marina_id), addr_list_sp_id_i btree (state_province_id), addr_list_ua_id_i btree (user_account_id) Foreign Key constraints: $1 FOREIGN KEY (address_type_id) REFERENCES address_type(address_type_id) ON UPDATE NO ACTION ON DELETE NO ACTION, $2 FOREIGN KEY (commercial_entity_id) REFERENCES commercial_entity(commercial_entity_id) ON UPDATE NO ACTION ON DELETE NO ACTION, $3 FOREIGN KEY (contact_info_id) REFERENCES contact_info(contact_info_id) ON UPDATE NO ACTION ON DELETE NO ACTION, $4 FOREIGN KEY (user_account_id) REFERENCES user_account(user_account_id) ON UPDATE NO ACTION ON DELETE NO ACTION, $5 FOREIGN KEY (state_province_id) REFERENCES state_province(state_province_id) ON UPDATE NO ACTION ON DELETE NO ACTION
Table
"public.commercial_entity"
Column | Type | Modifiers ---------------------------+-----------------------------+------------------------------------------------------------- commercial_entity_id | numeric(10,0) | not null company_name | character varying(100) | not null website | character varying(200) | modify_date | timestamp without time zone | user_account_id | numeric(10,0) | source_id | numeric(10,0) | not null commercial_entity_type_id | numeric(10,0) | boats_website | character varying(200) | updated_on | timestamp without time zone | not null default ('now'::text)::timestamp(6) with time zone dealer_level_id | numeric(10,0) | lang_id | numeric(10,0) | default '100' yw_account_id | numeric(10,0) | keybank_dealer_code | numeric(10,0) | dnetaccess_id | numeric(10,0) | not null default 0 interested_in_dns | numeric(10,0) | not null default 0 parent_office_id | numeric(10,0) | marinesite_welcome_msg | character varying(500) | alt_marinesite_homepage | character varying(256) | comments | character varying(4000) | show_finance_yn | character varying(1) | not null default 'Y' show_insurance_yn | character varying(1) | not null default 'Y' show_shipping_yn | character varying(1) | not null default 'Y' yw_account_id_c | character varying(11) | sales_id | numeric(10,0) | Indexes: commercial_entity_pkey primary key btree (commercial_entity_id), comm_ent_boat_web_ui unique btree (boats_website), comm_ent_key_dlr_cd_ui unique btree (keybank_dealer_code), comm_ent_cny_name_i btree (company_name), comm_ent_dlr_lvl_id_i btree (dealer_level_id, lang_id), comm_ent_src_id_i btree (source_id), comm_ent_type_id_i btree (commercial_entity_type_id), comm_ent_upd_on btree (updated_on), comm_ent_usr_acc_id_i btree (user_account_id), comm_ent_yw_acc_id_i btree (yw_account_id) Foreign Key constraints: $1 FOREIGN KEY (source_id) REFERENCES source(source_id) ON UPDATE NO ACTION ON DELETE NO ACTION, $2 FOREIGN KEY (user_account_id) REFERENCES user_account(user_account_id) ON UPDATE NO ACTION ON DELETE NO ACTION
Table "public.country"
Column | Type | Modifiers --------------+------------------------+----------- country_id | numeric(10,0) | not null lang_id | numeric(10,0) | not null country_desc | character varying(100) | not null Indexes: country_pkey primary key btree (country_id)
Table
"public.user_account"
Column | Type | Modifiers -------------------------------+-----------------------------+----------------------------- user_account_id | numeric(10,0) | not null first_name | character varying(100) | first_name_display_ind | numeric(1,0) | not null last_name | character varying(100) | last_name_display_ind | numeric(1,0) | not null profession | character varying(100) | profession_display_ind | numeric(1,0) | not null self_description | character varying(100) | self_description_display_ind | numeric(1,0) | not null activity_interest | character varying(100) | activity_interest_display_ind | numeric(1,0) | not null make_brand | character varying(100) | make_brand_display_ind | numeric(1,0) | not null birth_date | timestamp without time zone | birth_date_display_ind | numeric(1,0) | not null my_boat_picture_link | character varying(200) | user_account_name | character varying(100) | not null password | character varying(100) | password_ind | numeric(1,0) | not null age | numeric(10,0) | blacklisted_ind | numeric(1,0) | not null auto_login_ind | numeric(1,0) | not null email_addr | character varying(100) | create_date | timestamp without time zone | default ('now'::text)::date lang_id | numeric(10,0) | not null user_role_id | numeric(10,0) | not null seller_type_id | numeric(10,0) | payment_method_id | numeric(10,0) | account_status_id | numeric(10,0) | not null source_id | numeric(10,0) | not null default 100 ebay_user_id | character varying(80) | ebay_user_password | character varying(80) | Indexes: user_account_pkey primary key btree (user_account_id), usr_acc_acc_stat_id_i btree (account_status_id), usr_acc_an_pass_i btree (user_account_name, "password"), usr_acc_email_addr_i btree (email_addr), usr_acc_first_name_i btree (first_name), usr_acc_lang_id_i btree (lang_id), usr_acc_last_name_i btree (last_name), usr_acc_pay_meth_id_i btree (payment_method_id), usr_acc_sell_type_id_i btree (seller_type_id), usr_acc_usr_acc_name_i btree (user_account_name), usr_acc_usr_role_id_i btree (user_role_id) Foreign Key constraints: $1 FOREIGN KEY (lang_id) REFERENCES lang(lang_id) ON UPDATE NO ACTION ON DELETE NO ACTION, $2 FOREIGN KEY (source_id) REFERENCES source(source_id) ON UPDATE NO ACTION ON DELETE NO ACTION, $3 FOREIGN KEY (user_role_id) REFERENCES user_role(user_role_id) ON UPDATE NO ACTION ON DELETE NO ACTION
Table "public.contact_info"
Column | Type | Modifiers -----------------+------------------------+----------- contact_info_id | numeric(10,0) | not null first_name | character varying(100) | last_name | character varying(100) | email | character varying(100) | boat_listing_id | numeric(10,0) | user_account_id | numeric(10,0) | Indexes: contact_info_pkey primary key btree (contact_info_id), boat_listing_id_i btree (boat_listing_id), user_account_id_i btree (user_account_id) Foreign Key constraints: $1 FOREIGN KEY (user_account_id) REFERENCES user_account(user_account_id) ON UPDATE NO ACTION ON DELETE NO ACTION, $2 FOREIGN KEY (user_account_id) REFERENCES user_account(user_account_id) ON UPDATE NO ACTION ON DELETE NO ACTION
Table
"public.state_province"
Column | Type | Modifiers ---------------------------+------------------------+----------- state_province_id | numeric(10,0) | not null state_province_short_desc | character varying(2) | state_province_desc | character varying(100) | not null country_id | numeric(10,0) | not null lang_id | numeric(10,0) | not null Indexes: state_province_pkey primary key btree (state_province_id), state_prov_count_lang_i btree (country_id, lang_id) All the join columns are the same type and width,
and all are indexed. I googled for what looked like the expensive parts of the
query to see if I could at least figure out where the time was being
spent.
Part 3 to follow.
David |
