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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
