Date: Wed, 09 Nov 2005 11:59:13 -0800
From: Ashok Agrawal <Ashok.Agrawal@Sun.COM>
Subject: Outer Join performance in PostgreSQL
To: pgsql-performance@postgresql.org
Reply-To: Ashok.Agrawal@Sun.COM
Message-id: <43725511.3040809@Sun.COM>
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7BIT
User-Agent: Mozilla/5.0 (X11; U; SunOS sun4u; en-US; rv:1.4) Gecko/20041214

I noticed outer join is very very slow in postgresql as compared
to Oracle.

SELECT a.dln_code, a.company_name,
to_char(a.certificate_date,'DD-MON-YYYY'),
to_char(a.certificate_type_id, '99'),
COALESCE(b.certificate_type_description,'None') ,
a.description, a.blanket_single, a.certificate_status,
COALESCE(a.sun_legal_entity, 'None'),
COALESCE(a.other_entity_name, 'None'),
COALESCE(a.notes, 'None'),COALESCE(c.name, NULL),
COALESCE(to_char(a.created_date,'DD-MON-YYYY'), 'N/A'),
COALESCE(c.name, NULL),
COALESCE(to_char(a.updated_date,'DD-MON-YYYY'), 'N/A'),
COALESCE(e.name, NULL),
COALESCE(to_char(a.approved_date,'DD-MON-YYYY'), 'N/A')
  FROM ((((ecms_cert_headers a
        LEFT OUTER JOIN taxpack_user c ON (a.created_by = c.emp_no))
        LEFT OUTER JOIN taxpack_user d ON (a.updated_by = d.emp_no))
        LEFT OUTER JOIN taxpack_user e ON (a.approved_by = e.emp_no))
        INNER JOIN ecms_certificate_types b ON
      (a.certificate_type_id= b.certificate_type_id ))
 WHERE a.dln_code = '17319'


This query return only 1 record but take 25 second to execute in postgreSQL
as compared to 1.3 second in Oracle. Any suggestion ? Below is explain output.


 Hash Join  (cost=1666049.74..18486619.37 rows=157735046 width=874)
   Hash Cond: ("outer".certificate_type_id = "inner".certificate_type_id)
   ->  Merge Right Join  (cost=1666048.13..11324159.05 rows=643816513 width=826)
         Merge Cond: ("outer"."?column3?" = "inner"."?column16?")
         ->  Sort  (cost=30776.19..31207.80 rows=172645 width=64)
               Sort Key: (e.emp_no)::text
               ->  Seq Scan on taxpack_user e  (cost=0.00..4898.45 rows=172645
width=64)
         ->  Sort  (cost=1635271.94..1637136.51 rows=745827 width=811)
               Sort Key: (a.approved_by)::text
               ->  Merge Left Join  (cost=25230.45..36422.18 rows=745827 width=811)
                     Merge Cond: ("outer"."?column17?" = "inner"."?column2?")
                     ->  Sort  (cost=3117.35..3119.51 rows=864 width=844)
                           Sort Key: (a.updated_by)::text
                           ->  Nested Loop Left Join  (cost=0.00..3075.21
rows=864 width=844)
                                 ->  Index Scan using pk_ecms_cert_headers on
ecms_cert_headers a  (cost=0.00..6.01 rows=1 width=829)
                                       Index Cond: ((dln_code)::text =
'17319'::text)
                                 ->  Index Scan using ash_n1 on taxpack_user c
(cost=0.00..3058.40 rows=864 width=64)
                                       Index Cond: (("outer".created_by)::text =
(c.emp_no)::text)
                     ->  Sort  (cost=22113.10..22544.71 rows=172645 width=16)
                           Sort Key: (d.emp_no)::text
                           ->  Seq Scan on taxpack_user d  (cost=0.00..4898.45
rows=172645 width=16)
   ->  Hash  (cost=1.49..1.49 rows=49 width=50)
         ->  Seq Scan on ecms_certificate_types b  (cost=0.00..1.49 rows=49
width=50)
(23 rows)

Thanks
Ashok