Performance problem with joins

[imranpariyani]

Hi

i have a severe performance problem with one of my views which has 6 to 8 joins .. any help will be appreciated..
the view is:

CREATE OR REPLACE VIEW thsn.trade_view AS
SELECT tra.tra_id, tra.per_id, tra.fir_id, tra.tra_dcn, tra.tra_startdate::date AS tra_startdate, tra.tra_enddate::date AS tra_enddate, tra.tra_highprice, tra.tra_lowprice, tra.tra_shares, tra.tra_marketvalue, tra.tra_commonsharesheld, tra.tra_directsharesheld, tra.tra_indirectsharesheld, tra.fun_id, tra.tra_amended, tra.tra_ownership, tra.tra_touchdate::date AS tra_touchdate, tra.tra_cdate, tra.tra_udate, tra.tra_relevant, tra.tra_type, tra.tra_date::date AS tra_date,
per.per_fullname, fir.fir_name, fir.bra_id, cac90.pc_perf AS tra_performance90, incac90.pc_perf AS tra_indexperformance90, cac180.pc_perf AS tra_performance180, incac180.pc_perf AS tra_indexperformance180, cac270.pc_perf AS tra_performance270, incac270.pc_perf AS tra_indexperformance270, kurl.kur_marketcap AS tra_marketkap, kurl.kur_close AS tra_close, thsn.per_letztebewertungkauf(per.per_id, fir.fir_id) AS per_punktekauf, thsn.per_letztebewertungverkauf(per.per_id, fir.fir_id) AS per_punkteverkauf, fun.fun_thid, fun.fun_name, wp.wp_symbol
FROM thsn.trade tra
JOIN thsn.person per ON tra.per_id = per.per_id
JOIN thsn.firma fir ON tra.fir_id = fir.fir_id
LEFT JOIN thsn.kurs_latest kurl ON ('U'::text || fir.fir_cusip::text) =kurl.fir_cusip
LEFT JOIN thsn.perfcache90 cac90 ON tra.tra_id = cac90.tra_id
LEFT JOIN thsn.indexperfcache90 incac90 ON tra.tra_id = incac90.tra_id
LEFT JOIN thsn.perfcache180 cac180 ON tra.tra_id = cac180.tra_id
LEFT JOIN thsn.indexperfcache180 incac180 ON tra.tra_id = incac180.tra_id
LEFT JOIN thsn.perfcache270 cac270 ON tra.tra_id = cac270.tra_id
LEFT JOIN thsn.indexperfcache270 incac270 ON tra.tra_id = incac270.tra_id
LEFT JOIN thsn.funktion fun ON tra.fun_id = fun.fun_id
LEFT JOIN thsn.wertpapier wp ON fir.wp_id = wp.wp_id;


and now if i query this view with this explain query :

explain select * from thsn.trade_view tra where tra_date>'2006-05-29'

the output:

"Merge Right Join (cost=304605.98..319519.02 rows=324367 width=370)"
" Merge Cond: ("outer".wp_id = "inner".wp_id)"
" -> Index Scan using pk_wertpapier on wertpapier wp (cost=0.00..1134.06 rows=30651 width=12)"
" -> Sort (cost=304605.98..305416.90 rows=324367 width=370)"
" Sort Key: fir.wp_id"
" -> Hash Left Join (cost=102943.82..274914.62 rows=324367 width=370)"
" Hash Cond: ("outer".fun_id = "inner".fun_id)"
" -> Hash Left Join (cost=102942.07..271019.38 rows=324367 width=340)"
" Hash Cond: ("outer".tra_id = "inner".tra_id)"
" -> Hash Left Join (cost=71679.05..216585.25 rows=324367 width=308)"
" Hash Cond: ("outer".tra_id = "inner".tra_id)"
" -> Hash Left Join (cost=53148.50..189791.47 rows=324367 width=297)"
" Hash Cond: ("outer".tra_id = "inner".tra_id)"
" -> Hash Left Join (cost=25994.49..148209.39 rows=324367 width=275)"
" Hash Cond: (('U'::text || ("outer".fir_cusip)::text) = ("inner".fir_cusip)::text)"
" -> Hash Join (cost=24702.75..133134.22 rows=324367 width=264)"
" Hash Cond: ("outer".per_id = "inner".per_id)"
" -> Hash Join (cost=1450.91..99340.45 rows=324367 width=237)"
" Hash Cond: ("outer".fir_id = "inner".fir_id)"
" -> Seq Scan on trade tra (cost=0.00..88158.53 rows=324367 width=181)"
" Filter: ((tra_date)::date > '2006-05-29'::date)"
"-> Hash (cost=1374.53..1374.53 rows=30553 width=56)"
"-> Seq Scan on firma fir (cost=0.00..1374.53 rows=30553 width=56)"
"-> Hash (cost=22629.87..22629.87 rows=248787 width=27)"
"-> Seq Scan on person per (cost=0.00..22629.87 rows=248787 width=27)"
"-> Hash (cost=1232.59..1232.59 rows=23659 width=35)"
"-> Seq Scan on kurs_latest kurl (cost=0.00..1232.59 rows=23659 width=35)"
"-> Hash (cost=17244.44..17244.44 rows=814044 width=19)"
"-> Seq Scan on perfcache90 cac90 (cost=0.00..17244.44 rows=814044 width=19)"
" -> Hash (cost=6994.97..6994.97 rows=351797 width=19)"
" -> Seq Scan on indexperfcache90 incac90 (cost=0.00..6994.97 rows=351797 width=19)"
" -> Hash (cost=16590.44..16590.44 rows=776044 width=19)"
" -> Seq Scan on perfcache180 cac180 (cost=0.00..16590.44 rows=776044 width=19)"
" -> Hash (cost=6704.00..6704.00 rows=336800 width=18)"
" -> Seq Scan on indexperfcache180 incac180 (cost=0.00..6704.00 rows=336800 width=18)"
" -> Hash (cost=14755.09..14755.09 rows=695309 width=19)"
" -> Seq Scan on perfcache270 cac270 (cost=0.00..14755.09 rows=695309 width=19)"
" -> Hash (cost=6413.93..6413.93 rows=323893 width=19)"
" -> Seq Scan on indexperfcache270 incac270 (cost=0.00..6413.93 rows=323893 width=19)"
" -> Hash (cost=1.60..1.60 rows=60 width=34)"
" -> Seq Scan on funktion fun (cost=0.00..1.60 rows=60 width=34)"


and without the joins if i run a explain on this query:

EXPLAIN SELECT tra.tra_id, tra.per_id, tra.fir_id, tra.tra_dcn, tra.tra_startdate::date AS tra_startdate, tra.tra_enddate::date AS tra_enddate, tra.tra_highprice, tra.tra_lowprice, tra.tra_shares, tra.tra_marketvalue, tra.tra_commonsharesheld, tra.tra_directsharesheld, tra.tra_indirectsharesheld, tra.fun_id, tra.tra_amended, tra.tra_ownership, tra.tra_touchdate::date AS tra_touchdate, tra.tra_cdate, tra.tra_udate, tra.tra_relevant, tra.tra_type, tra.tra_date::date AS tra_date, per.per_fullname, fir.fir_name, fir.bra_id, cac90.pc_perf AS tra_performance90, incac90.pc_perf AS tra_indexperformance90, cac180.pc_perf AS tra_performance180, incac180.pc_perf AS tra_indexperformance180, cac270.pc_perf AS tra_performance270, incac270.pc_perf AS tra_indexperformance270, kurl.kur_marketcap AS tra_marketkap, kurl.kur_close AS tra_close,
thsn.per_letztebewertungkauf(per.per_id, fir.fir_id) AS per_punktekauf, thsn.per_letztebewertungverkauf(per.per_id, fir.fir_id) AS per_punkteverkauf, fun.fun_thid, fun.fun_name, wp.wp_symbol

FROM thsn.trade tra , thsn.person per, thsn.firma fir,thsn.kurs_latest kurl , thsn.perfcache90 cac90, thsn.indexperfcache90 incac90 , thsn.perfcache180 cac180 ,thsn.indexperfcache180 incac180 ,thsn.perfcache270 cac270, thsn.indexperfcache270 incac270 , thsn.funktion fun, thsn.wertpapier wp

where tra_date>'2006-06-30' and tra.per_id = per.per_id and tra.fir_id = fir.fir_id and ('U'::text || fir.fir_cusip::text) = kurl.fir_cusip::text and tra.tra_id = cac90.tra_id and tra.tra_id = incac90.tra_id and tra.tra_id = cac180.tra_id and tra.tra_id = incac180.tra_id and tra.tra_id = cac270.tra_id and tra.tra_id = incac270.tra_id and tra.fun_id = fun.fun_id and fir.wp_id = wp.wp_id

the output:

"Nested Loop (cost=64179.28..90645.20 rows=394 width=370)"
" -> Nested Loop (cost=64179.28..89072.83 rows=394 width=343)"
" -> Nested Loop (cost=64179.28..87183.66 rows=471 width=372)"
" -> Nested Loop (cost=64179.28..81962.24 rows=1304 width=353)"
" -> Nested Loop (cost=64179.28..74632.57 rows=1825 width=334)"
" -> Merge Join (cost=64179.28..65424.31 rows=2289 width=315)"
" Merge Cond: ("outer".wp_id = "inner".wp_id)"
" -> Index Scan using pk_wertpapier on wertpapier wp (cost=0.00..1134.06 rows=30651 width=12)"
" -> Sort (cost=64179.28..64185.15 rows=2349 width=315)"
" Sort Key: fir.wp_id"
" -> Seq Scan on indexperfcache180 incac180 (cost=0.00..6704.00 rows=336800 width=18)"
" -> Hash (cost=54717.99..54717.99 rows=9690 width=267)"
" -> Merge Join (cost=42275.34..54717.99 rows=9690 width=267)"
" Merge Cond: ("outer".tra_id = "inner".tra_id)"
" -> Index Scan using pk_indexperfcache270 on indexperfcache270 incac270 (cost=0.00..11393.83 rows=323893 width=19)"
" -> Sort (cost=42275.34..42348.12 rows=29114 width=248)"
" Sort Key: tra.tra_id"
" -> Hash Join (cost=4224.87..40116.62 rows=29114 width=248)"
" Hash Cond: ("outer".fir_id = "inner".fir_id)"
" -> Bitmap Heap Scan on trade tra (cost=183.96..35201.91 rows=29133 width=181)"
" Recheck Cond: (tra_date > '2006-06-30 00:00:00'::timestamp without time zone)"
" -> Bitmap Index Scan on trade_date_index (cost=0.00..183.96 rows=29133 width=0)"
" Index Cond: (tra_date > '2006-06-30 00:00:00'::timestamp without time zone)"
" -> Hash (cost=3964.57..3964.57 rows=30533 width=67)"
" -> Hash Join (cost=1291.74..3964.57 rows=30533 width=67)"
" Hash Cond: (('U'::text || ("outer".fir_cusip)::text) = ("inner".fir_cusip)::text)"
" -> Seq Scan on firma fir (cost=0.00..1374.53 rows=30553 width=56)"
" -> Hash (cost=1232.59..1232.59 rows=23659 width=35)"
" -> Seq Scan on kurs_latest kurl (cost=0.00..1232.59 rows=23659 width=35)"
" -> Hash (cost=1.60..1.60 rows=60 width=34)"
"-> Seq Scan on funktion fun (cost=0.00..1.60 rows=60 width=34)"
"-> Index Scan using pk_perfcache180 on perfcache180 cac180 (cost=0.00..4.01 rows=1 width=19)"
" Index Cond: ("outer".tra_id = cac180.tra_id)"
"-> Index Scan using pk_perfcache270 on perfcache270 cac270 (cost=0.00..4.00 rows=1 width=19)"
" Index Cond: ("outer".tra_id = cac270.tra_id)"
"-> Index Scan using pk_indexperfcache90 on indexperfcache90 incac90 (cost=0.00..3.99 rows=1 width=19)"
"Index Cond: ("outer".tra_id = incac90.tra_id)"
" -> Index Scan using pk_perfcache90 on perfcache90 cac90 (cost=0.00..4.00 rows=1 width=19)"
" Index Cond: ("outer".tra_id = cac90.tra_id)"
"-> Index Scan using pk_person on person per (cost=0.00..3.96 rows=1 width=27)"
" Index Cond: ("outer".per_id = per.per_id)"


In this case the time taken is much less and also the index in the tra_date cloumn is considered while with the view the index is not considered and also other indexes are not considered.

What is it that i am doing wrong?

Thanks in advance.

[PEB]

The things that impression me are:

LEFT JOIN thsn.kurs_latest kurl ON ('U'::text || fir.fir_cusip::text) =kurl.fir_cusip

U concatenate string in a join... Try to do it without concatenation...

U also use a multiple Left Joins...

The Left Join isn't as the Inner Join... It is slower...

Try to minimize the use of Left joins..

:)