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); 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) I will post the schema in a seperate email - the list has rejected one big
email 3 times now.
David |
- Re: [PERFORM] Tuning/performance issue... David Griffiths
- Re: [PERFORM] Tuning/performance issue... Tom Lane
- Re: [PERFORM] Tuning/performance issue... David Griffiths
- Re: [PERFORM] Tuning/performance issue... Oleg Lebedev
- Re: [PERFORM] Tuning/performance issue... Jeff
- Re: [PERFORM] Tuning/performance issue... Bruce Momjian
- Re: [PERFORM] Tuning/performance issue... Rod Taylor
- Re: [PERFORM] Tuning/performance issue... Bruce Momjian
- Re: [PERFORM] Tuning/performance issue... Tom Lane