pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: David Griffiths <dgriffiths@boats.com>
To: pgsql-performance@postgresql.org
Subject: Tuning/performance issue...
Date: Tue, 30 Sep 2003 13:24:24 -0700
Message-ID: <057501c38790$d9b9aad0$6501a8c0@griffiths2> (raw)

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

view thread (16+ messages)  latest in thread

Message-ID: <057501c38790$d9b9aad0$6501a8c0@griffiths2>
Permalink:  ../057501c38790$d9b9aad0$6501a8c0@griffiths2/
Also on:    postgresql.org/message-id/057501c38790$d9b9aad0$6501a8c0@griffiths2

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: dgriffiths@boats.com
  Subject: Re: Tuning/performance issue...
  In-Reply-To: <057501c38790$d9b9aad0$6501a8c0@griffiths2>
  References: <057501c38790$d9b9aad0$6501a8c0@griffiths2>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox