Postgres Pro
Downloads
Home   >   mailing lists

Thread: Tuning/performance issue (part 2)

Tuning/performance issue (part 2)

From
David Griffiths
Date:
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