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_startda te::date AS tra_startdate, tra.tra_enddate ::date AS tra_enddate, tra.tra_highpri ce, tra.tra_lowpric e, tra.tra_shares, tra.tra_marketv alue, tra.tra_commons haresheld, tra.tra_directs haresheld, tra.tra_indirec tsharesheld, tra.fun_id, tra.tra_amended , tra.tra_ownersh ip, tra.tra_touchda te::date AS tra_touchdate, tra.tra_cdate, tra.tra_udate, tra.tra_relevan t, tra.tra_type, tra.tra_date::d ate AS tra_date,
per.per_fullnam e, fir.fir_name, fir.bra_id, cac90.pc_perf AS tra_performance 90, incac90.pc_perf AS tra_indexperfor mance90, cac180.pc_perf AS tra_performance 180, incac180.pc_per f AS tra_indexperfor mance180, cac270.pc_perf AS tra_performance 270, incac270.pc_per f AS tra_indexperfor mance270, kurl.kur_market cap AS tra_marketkap, kurl.kur_close AS tra_close, thsn.per_letzte bewertungkauf(p er.per_id, fir.fir_id) AS per_punktekauf, thsn.per_letzte bewertungverkau f(per.per_id, fir.fir_id) AS per_punkteverka uf, 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_lates t kurl ON ('U'::text || fir.fir_cusip:: text) =kurl.fir_cusip
LEFT JOIN thsn.perfcache9 0 cac90 ON tra.tra_id = cac90.tra_id
LEFT JOIN thsn.indexperfc ache90 incac90 ON tra.tra_id = incac90.tra_id
LEFT JOIN thsn.perfcache1 80 cac180 ON tra.tra_id = cac180.tra_id
LEFT JOIN thsn.indexperfc ache180 incac180 ON tra.tra_id = incac180.tra_id
LEFT JOIN thsn.perfcache2 70 cac270 ON tra.tra_id = cac270.tra_id
LEFT JOIN thsn.indexperfc ache270 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..113 4.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_cu sip)::text) = ("inner".fir_cu sip)::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..881 58.53 rows=324367 width=181)"
" Filter: ((tra_date)::da te > '2006-05-29'::date)"
"-> Hash (cost=1374.53.. 1374.53 rows=30553 width=56)"
"-> Seq Scan on firma fir (cost=0.00..137 4.53 rows=30553 width=56)"
"-> Hash (cost=22629.87. .22629.87 rows=248787 width=27)"
"-> Seq Scan on person per (cost=0.00..226 29.87 rows=248787 width=27)"
"-> Hash (cost=1232.59.. 1232.59 rows=23659 width=35)"
"-> Seq Scan on kurs_latest kurl (cost=0.00..123 2.59 rows=23659 width=35)"
"-> Hash (cost=17244.44. .17244.44 rows=814044 width=19)"
"-> Seq Scan on perfcache90 cac90 (cost=0.00..172 44.44 rows=814044 width=19)"
" -> Hash (cost=6994.97.. 6994.97 rows=351797 width=19)"
" -> Seq Scan on indexperfcache9 0 incac90 (cost=0.00..699 4.97 rows=351797 width=19)"
" -> Hash (cost=16590.44. .16590.44 rows=776044 width=19)"
" -> Seq Scan on perfcache180 cac180 (cost=0.00..165 90.44 rows=776044 width=19)"
" -> Hash (cost=6704.00.. 6704.00 rows=336800 width=18)"
" -> Seq Scan on indexperfcache1 80 incac180 (cost=0.00..670 4.00 rows=336800 width=18)"
" -> Hash (cost=14755.09. .14755.09 rows=695309 width=19)"
" -> Seq Scan on perfcache270 cac270 (cost=0.00..147 55.09 rows=695309 width=19)"
" -> Hash (cost=6413.93.. 6413.93 rows=323893 width=19)"
" -> Seq Scan on indexperfcache2 70 incac270 (cost=0.00..641 3.93 rows=323893 width=19)"
" -> Hash (cost=1.60..1.6 0 rows=60 width=34)"
" -> Seq Scan on funktion fun (cost=0.00..1.6 0 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_startda te::date AS tra_startdate, tra.tra_enddate ::date AS tra_enddate, tra.tra_highpri ce, tra.tra_lowpric e, tra.tra_shares, tra.tra_marketv alue, tra.tra_commons haresheld, tra.tra_directs haresheld, tra.tra_indirec tsharesheld, tra.fun_id, tra.tra_amended , tra.tra_ownersh ip, tra.tra_touchda te::date AS tra_touchdate, tra.tra_cdate, tra.tra_udate, tra.tra_relevan t, tra.tra_type, tra.tra_date::d ate AS tra_date, per.per_fullnam e, fir.fir_name, fir.bra_id, cac90.pc_perf AS tra_performance 90, incac90.pc_perf AS tra_indexperfor mance90, cac180.pc_perf AS tra_performance 180, incac180.pc_per f AS tra_indexperfor mance180, cac270.pc_perf AS tra_performance 270, incac270.pc_per f AS tra_indexperfor mance270, kurl.kur_market cap AS tra_marketkap, kurl.kur_close AS tra_close,
thsn.per_letzte bewertungkauf(p er.per_id, fir.fir_id) AS per_punktekauf, thsn.per_letzte bewertungverkau f(per.per_id, fir.fir_id) AS per_punkteverka uf, fun.fun_thid, fun.fun_name, wp.wp_symbol

FROM thsn.trade tra , thsn.person per, thsn.firma fir,thsn.kurs_l atest kurl , thsn.perfcache9 0 cac90, thsn.indexperfc ache90 incac90 , thsn.perfcache1 80 cac180 ,thsn.indexperf cache180 incac180 ,thsn.perfcache 270 cac270, thsn.indexperfc ache270 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..113 4.06 rows=30651 width=12)"
" -> Sort (cost=64179.28. .64185.15 rows=2349 width=315)"
" Sort Key: fir.wp_id"
" -> Seq Scan on indexperfcache1 80 incac180 (cost=0.00..670 4.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_indexperfcac he270 on indexperfcache2 70 incac270 (cost=0.00..113 93.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..3 5201.91 rows=29133 width=181)"
" Recheck Cond: (tra_date > '2006-06-30 00:00:00'::time stamp without time zone)"
" -> Bitmap Index Scan on trade_date_inde x (cost=0.00..183 .96 rows=29133 width=0)"
" Index Cond: (tra_date > '2006-06-30 00:00:00'::time stamp 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_cu sip)::text) = ("inner".fir_cu sip)::text)"
" -> Seq Scan on firma fir (cost=0.00..137 4.53 rows=30553 width=56)"
" -> Hash (cost=1232.59.. 1232.59 rows=23659 width=35)"
" -> Seq Scan on kurs_latest kurl (cost=0.00..123 2.59 rows=23659 width=35)"
" -> Hash (cost=1.60..1.6 0 rows=60 width=34)"
"-> Seq Scan on funktion fun (cost=0.00..1.6 0 rows=60 width=34)"
"-> Index Scan using pk_perfcache180 on perfcache180 cac180 (cost=0.00..4.0 1 rows=1 width=19)"
" Index Cond: ("outer".tra _id = cac180.tra_id)"
"-> Index Scan using pk_perfcache270 on perfcache270 cac270 (cost=0.00..4.0 0 rows=1 width=19)"
" Index Cond: ("outer".tra _id = cac270.tra_id)"
"-> Index Scan using pk_indexperfcac he90 on indexperfcache9 0 incac90 (cost=0.00..3.9 9 rows=1 width=19)"
"Index Cond: ("outer".tra _id = incac90.tra_id) "
" -> Index Scan using pk_perfcache90 on perfcache90 cac90 (cost=0.00..4.0 0 rows=1 width=19)"
" Index Cond: ("outer".tra _id = cac90.tra_id)"
"-> Index Scan using pk_person on person per (cost=0.00..3.9 6 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_lates t 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..

:)