Outer Join performance in PostgreSQL - Mailing list pgsql-performance
From | Ashok Agrawal |
---|---|
Subject | Outer Join performance in PostgreSQL |
Date | |
Msg-id | 43725511.3040809@Sun.COM Whole thread Raw |
Responses |
Re: Outer Join performance in PostgreSQL
Re: Outer Join performance in PostgreSQL |
List | pgsql-performance |
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
pgsql-performance by date: