Tuning/performance issue (part 2)

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