X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id E1F61D1D2B9 for ; Wed, 1 Oct 2003 03:10:46 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 18100-03 for ; Wed, 1 Oct 2003 00:10:00 -0300 (ADT) Received: from www.postgresql.com (unknown [64.117.225.209]) by svr1.postgresql.org (Postfix) with ESMTP id 7CDA1D1B56E for ; Tue, 30 Sep 2003 23:22:01 -0300 (ADT) Received: from boats_exch01.boats.com (h209-17-182-106.gtconnect.net [209.17.182.106]) by www.postgresql.com (Postfix) with ESMTP id 10057CF4A3F for ; Tue, 30 Sep 2003 17:16:18 -0300 (ADT) Received: by BOATS_EXCH01 with Internet Mail Service (5.5.2653.19) id ; Tue, 30 Sep 2003 13:14:51 -0700 Received: from GRIFFITHS2 ([192.168.12.195]) by boats_exch01.boats.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id TTY64KCZ; Tue, 30 Sep 2003 13:14:48 -0700 From: David Griffiths To: pgsql-performance@postgresql.org Message-ID: <057501c38790$d9b9aad0$6501a8c0@griffiths2> Subject: Tuning/performance issue... Date: Tue, 30 Sep 2003 13:24:24 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0572_01C38756.2A7EDC00" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4922.1500 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4925.2800 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=1.5 tagged_above=0.0 required=5.0 tests=BAYES_30, HTML_20_30, RCVD_IN_OSIRUSOFT_COM X-Spam-Level: * X-Archive-Number: 200310/1 X-Sequence-Number: 3749 This is a multi-part message in MIME format. ------=_NextPart_000_0572_01C38756.2A7EDC00 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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. =20 Here's the query: =20 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 =3D state_province.state_province_id LEFT JOIN contact_info ON address_list.contact_info_id =3D contact_info.contact_info_id WHERE address_list.address_type_id =3D 101 AND commercial_entity.commercial_entity_id=3D225528 AND commercial_entity.commercial_entity_id =3D address_list.commercial_entity_id AND address_list.country_id =3D country.country_id AND commercial_entity.user_account_id =3D user_account.user_account_id AND user_account.user_role_id IN (101, 101); =20 Here's the explain: =20 ------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------------------------------ Nested Loop (cost=3D0.00..64570.33 rows=3D1 width=3D385) (actual time=3D42141.08..42152.06 rows=3D1 loops=3D1) -> Nested Loop (cost=3D0.00..64567.30 rows=3D1 width=3D361) (actual time=3D42140.80..42151.77 rows=3D1 loops=3D1) -> Nested Loop (cost=3D0.00..64563.97 rows=3D1 width=3D349) (act= ual time=3D42140.31..42151.27 rows=3D1 loops=3D1) Join Filter: ("outer".commercial_entity_id =3D "inner".commercial_entity_id) -> Index Scan using commercial_entity_pkey on commercial_entity (cost=3D0.00..5.05 rows=3D1 width=3D94) (actual time=3D0.57..0.58 rows=3D1 loops=3D1) Index Cond: (commercial_entity_id =3D 225528::numeric) -> Materialize (cost=3D63343.66..63343.66 rows=3D97221 width=3D255) (actual time=3D41741.96..41901.17 rows=3D90527 loops=3D1) -> Merge Join (cost=3D0.00..63343.66 rows=3D97221 width=3D255) (actual time=3D1.44..41387.68 rows=3D90527 loops=3D1) Merge Cond: ("outer".contact_info_id =3D "inner".contact_info_id) -> Nested Loop (cost=3D0.00..830457.52 rows=3D97221 width=3D222) (actual time=3D0.95..39178.32 rows=3D90527 loops= =3D1) Join Filter: ("outer".state_province_id =3D "inner".state_province_id) -> Index Scan using addr_list_ci_id_i on address_list (cost=3D0.00..586676.65 rows=3D97221 width=3D205) (actual time=3D0.49..2159.90 rows=3D90527 loops=3D1) Filter: (address_type_id =3D 101::numeric) -> Seq Scan on state_province (cost=3D0.00..1.67 rows=3D67 width=3D17) (actual time=3D0.00..0.21 rows=3D67 loops=3D90527) -> Index Scan using contact_info_pkey on contact_info (cost=3D0.00..3366.76 rows=3D56435 width=3D33) (actual time=3D0.44..395.75 rows=3D55916 loops=3D1) -> Index Scan using user_account_pkey on user_account (cost=3D0.00..3.32 rows=3D1 width=3D12) (actual time=3D0.46..0.46 rows=3D1 loops=3D1) Index Cond: ("outer".user_account_id =3D user_account.user_account_id) Filter: (user_role_id =3D 101::numeric) -> Index Scan using country_pkey on country (cost=3D0.00..3.01 rows=3D1 width=3D24) (actual time=3D0.25..0.25 rows=3D1 loops=3D1) Index Cond: ("outer".country_id =3D country.country_id) Total runtime: 42165.44 msec (21 rows) =20 =20 I will post the schema in a seperate email - the list has rejected one big email 3 times now. =20 David ------=_NextPart_000_0572_01C38756.2A7EDC00 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
We're having a problem with a query during= our=20 investigation into Postgres (as an Oracle replacement). This query Postgres= =20 takes 20-40 seconds (multiple runs). Tom Lan recommended I post it here, wi= th an=20 explain-analyze.
 
Here's the query:
 
EXPLAIN ANALYZE SELECT company_name, addre= ss_1,=20 address_2, address_3, city,
address_list.state_province_id,=20 state_province_short_desc, country_desc, zip_code,=20 address_list.country_id,
contact_info.email,=20 commercial_entity.user_account_id, phone_num_1, phone_num_fax, website,=20 boats_website
FROM commercial_entity, country, user_account,
address_= list=20 LEFT JOIN state_province ON address_list.state_province_id =3D=20 state_province.state_province_id
LEFT JOIN contact_info ON=20 address_list.contact_info_id =3D contact_info.contact_info_id
WHERE=20 address_list.address_type_id =3D 101
AND=20 commercial_entity.commercial_entity_id=3D225528
AND=20 commercial_entity.commercial_entity_id =3D=20 address_list.commercial_entity_id
AND address_list.country_id =3D=20 country.country_id
AND commercial_entity.user_account_id =3D=20 user_account.user_account_id
AND user_account.user_role_id IN (101,=20 101);
 
Here's the explain:
 
------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------
 Nested=20 Loop  (cost=3D0.00..64570.33 rows=3D1 width=3D385) (actual=20 time=3D42141.08..42152.06 rows=3D1 loops=3D1)
   ->  N= ested=20 Loop  (cost=3D0.00..64567.30 rows=3D1 width=3D361) (actual=20 time=3D42140.80..42151.77 rows=3D1=20 loops=3D1)
         ->  = Nested=20 Loop  (cost=3D0.00..64563.97 rows=3D1 width=3D349) (actual=20 time=3D42140.31..42151.27 rows=3D1=20 loops=3D1)
          &= nbsp;   =20 Join Filter: ("outer".commercial_entity_id =3D=20 "inner".commercial_entity_id)
       =        =20 ->  Index Scan using commercial_entity_pkey on commercial_entity&nb= sp;=20 (cost=3D0.00..5.05 rows=3D1 width=3D94) (actual time=3D0.57..0.58 rows=3D1= =20 loops=3D1)
          &= nbsp;         =20 Index Cond: (commercial_entity_id =3D=20 225528::numeric)
         &= nbsp;    =20 ->  Materialize  (cost=3D63343.66..63343.66 rows=3D97221 width= =3D255)=20 (actual time=3D41741.96..41901.17 rows=3D90527=20 loops=3D1)
          &= nbsp;         =20 ->  Merge Join  (cost=3D0.00..63343.66 rows=3D97221 width=3D25= 5) (actual=20 time=3D1.44..41387.68 rows=3D90527=20 loops=3D1)
          &= nbsp;           &nbs= p;   =20 Merge Cond: ("outer".contact_info_id =3D=20 "inner".contact_info_id)
        = ;            &n= bsp;     =20 ->  Nested Loop  (cost=3D0.00..830457.52 rows=3D97221 width=3D= 222)=20 (actual time=3D0.95..39178.32 rows=3D90527=20 loops=3D1)
          &= nbsp;           &nbs= p;         =20 Join Filter: ("outer".state_province_id =3D=20 "inner".state_province_id)
       &nb= sp;            =             =20 ->  Index Scan using addr_list_ci_id_i on address_list =20 (cost=3D0.00..586676.65 rows=3D97221 width=3D205) (actual time=3D0.49..2159= .90=20 rows=3D90527=20 loops=3D1)
          &= nbsp;           &nbs= p;            &= nbsp;  =20 Filter: (address_type_id =3D=20 101::numeric)
         &nbs= p;            &= nbsp;         =20 ->  Seq Scan on state_province  (cost=3D0.00..1.67 rows=3D67 w= idth=3D17)=20 (actual time=3D0.00..0.21 rows=3D67=20 loops=3D90527)
         &nb= sp;            =     =20 ->  Index Scan using contact_info_pkey on contact_info =20 (cost=3D0.00..3366.76 rows=3D56435 width=3D33) (actual time=3D0.44..395.75 = rows=3D55916=20 loops=3D1)
         ->  = Index=20 Scan using user_account_pkey on user_account  (cost=3D0.00..3.32 rows= =3D1=20 width=3D12) (actual time=3D0.46..0.46 rows=3D1=20 loops=3D1)
          &= nbsp;   =20 Index Cond: ("outer".user_account_id =3D=20 user_account.user_account_id)
       =        =20 Filter: (user_role_id =3D 101::numeric)
   ->  Index S= can=20 using country_pkey on country  (cost=3D0.00..3.01 rows=3D1 width=3D24)= (actual=20 time=3D0.25..0.25 rows=3D1=20 loops=3D1)
         Index Cond:= =20 ("outer".country_id =3D country.country_id)
 Total runtime: 42165.4= 4=20 msec
(21 rows)
 
 
I will post the schema in a seperate email - the list has rejected one= big=20 email 3 times now.
 
David
------=_NextPart_000_0572_01C38756.2A7EDC00--