Tuning/performance issue... - Mailing list pgsql-performance
| From | David Griffiths |
|---|---|
| Subject | Tuning/performance issue... |
| Date | |
| Msg-id | 057501c38790$d9b9aad0$6501a8c0@griffiths2 Whole thread Raw |
| Responses |
Re: Tuning/performance issue...
|
| List | pgsql-performance |
We're having a problem with a query during our investigation into Postgres (as an Oracle replacement). This query Postgres takes 20-40 seconds (multiple runs). Tom Lan recommended I post it here, with an explain-analyze.
Here's the query:
EXPLAIN ANALYZE SELECT company_name, address_1, address_2, address_3, city,
address_list.state_province_id, state_province_short_desc, country_desc, zip_code, address_list.country_id,
contact_info.email, commercial_entity.user_account_id, phone_num_1, phone_num_fax, website, boats_website
FROM commercial_entity, country, user_account,
address_list LEFT JOIN state_province ON address_list.state_province_id = state_province.state_province_id
LEFT JOIN contact_info ON address_list.contact_info_id = contact_info.contact_info_id
WHERE address_list.address_type_id = 101
AND commercial_entity.commercial_entity_id=225528
AND commercial_entity.commercial_entity_id = address_list.commercial_entity_id
AND address_list.country_id = country.country_id
AND commercial_entity.user_account_id = user_account.user_account_id
AND user_account.user_role_id IN (101, 101);
address_list.state_province_id, state_province_short_desc, country_desc, zip_code, address_list.country_id,
contact_info.email, commercial_entity.user_account_id, phone_num_1, phone_num_fax, website, boats_website
FROM commercial_entity, country, user_account,
address_list LEFT JOIN state_province ON address_list.state_province_id = state_province.state_province_id
LEFT JOIN contact_info ON address_list.contact_info_id = contact_info.contact_info_id
WHERE address_list.address_type_id = 101
AND commercial_entity.commercial_entity_id=225528
AND commercial_entity.commercial_entity_id = address_list.commercial_entity_id
AND address_list.country_id = country.country_id
AND commercial_entity.user_account_id = user_account.user_account_id
AND user_account.user_role_id IN (101, 101);
Here's the explain:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..64570.33 rows=1 width=385) (actual time=42141.08..42152.06 rows=1 loops=1)
-> Nested Loop (cost=0.00..64567.30 rows=1 width=361) (actual time=42140.80..42151.77 rows=1 loops=1)
-> Nested Loop (cost=0.00..64563.97 rows=1 width=349) (actual time=42140.31..42151.27 rows=1 loops=1)
Join Filter: ("outer".commercial_entity_id = "inner".commercial_entity_id)
-> Index Scan using commercial_entity_pkey on commercial_entity (cost=0.00..5.05 rows=1 width=94) (actual time=0.57..0.58 rows=1 loops=1)
Index Cond: (commercial_entity_id = 225528::numeric)
-> Materialize (cost=63343.66..63343.66 rows=97221 width=255) (actual time=41741.96..41901.17 rows=90527 loops=1)
-> Merge Join (cost=0.00..63343.66 rows=97221 width=255) (actual time=1.44..41387.68 rows=90527 loops=1)
Merge Cond: ("outer".contact_info_id = "inner".contact_info_id)
-> Nested Loop (cost=0.00..830457.52 rows=97221 width=222) (actual time=0.95..39178.32 rows=90527 loops=1)
Join Filter: ("outer".state_province_id = "inner".state_province_id)
-> Index Scan using addr_list_ci_id_i on address_list (cost=0.00..586676.65 rows=97221 width=205) (actual time=0.49..2159.90 rows=90527 loops=1)
Filter: (address_type_id = 101::numeric)
-> Seq Scan on state_province (cost=0.00..1.67 rows=67 width=17) (actual time=0.00..0.21 rows=67 loops=90527)
-> Index Scan using contact_info_pkey on contact_info (cost=0.00..3366.76 rows=56435 width=33) (actual time=0.44..395.75 rows=55916 loops=1)
-> Index Scan using user_account_pkey on user_account (cost=0.00..3.32 rows=1 width=12) (actual time=0.46..0.46 rows=1 loops=1)
Index Cond: ("outer".user_account_id = user_account.user_account_id)
Filter: (user_role_id = 101::numeric)
-> Index Scan using country_pkey on country (cost=0.00..3.01 rows=1 width=24) (actual time=0.25..0.25 rows=1 loops=1)
Index Cond: ("outer".country_id = country.country_id)
Total runtime: 42165.44 msec
(21 rows)
Nested Loop (cost=0.00..64570.33 rows=1 width=385) (actual time=42141.08..42152.06 rows=1 loops=1)
-> Nested Loop (cost=0.00..64567.30 rows=1 width=361) (actual time=42140.80..42151.77 rows=1 loops=1)
-> Nested Loop (cost=0.00..64563.97 rows=1 width=349) (actual time=42140.31..42151.27 rows=1 loops=1)
Join Filter: ("outer".commercial_entity_id = "inner".commercial_entity_id)
-> Index Scan using commercial_entity_pkey on commercial_entity (cost=0.00..5.05 rows=1 width=94) (actual time=0.57..0.58 rows=1 loops=1)
Index Cond: (commercial_entity_id = 225528::numeric)
-> Materialize (cost=63343.66..63343.66 rows=97221 width=255) (actual time=41741.96..41901.17 rows=90527 loops=1)
-> Merge Join (cost=0.00..63343.66 rows=97221 width=255) (actual time=1.44..41387.68 rows=90527 loops=1)
Merge Cond: ("outer".contact_info_id = "inner".contact_info_id)
-> Nested Loop (cost=0.00..830457.52 rows=97221 width=222) (actual time=0.95..39178.32 rows=90527 loops=1)
Join Filter: ("outer".state_province_id = "inner".state_province_id)
-> Index Scan using addr_list_ci_id_i on address_list (cost=0.00..586676.65 rows=97221 width=205) (actual time=0.49..2159.90 rows=90527 loops=1)
Filter: (address_type_id = 101::numeric)
-> Seq Scan on state_province (cost=0.00..1.67 rows=67 width=17) (actual time=0.00..0.21 rows=67 loops=90527)
-> Index Scan using contact_info_pkey on contact_info (cost=0.00..3366.76 rows=56435 width=33) (actual time=0.44..395.75 rows=55916 loops=1)
-> Index Scan using user_account_pkey on user_account (cost=0.00..3.32 rows=1 width=12) (actual time=0.46..0.46 rows=1 loops=1)
Index Cond: ("outer".user_account_id = user_account.user_account_id)
Filter: (user_role_id = 101::numeric)
-> Index Scan using country_pkey on country (cost=0.00..3.01 rows=1 width=24) (actual time=0.25..0.25 rows=1 loops=1)
Index Cond: ("outer".country_id = country.country_id)
Total runtime: 42165.44 msec
(21 rows)
I will post the schema in a seperate email - the list has rejected one big email 3 times now.
David
pgsql-performance by date: