Re: Performance problem with joins

Thanks for the reply .. you are right after i changed tra_date to timestamp in the view it considered the index and the performance did increase a bit .. but still compared to the query without the joins its much less .. any idea why?

here is the output of the explain query after changing the tra_date column to timestamp.

"Merge Right Join (cost=229025.77..231549.17 rows=32995 width=366)"
"Merge Cond: ("outer".wp_id = "inner".wp_id)"
"-> Index Scan using pk_wertpapier on wertpapier wp (cost=0.00..1132.90 rows=30654 width=12)"
"-> Sort (cost=229025.77..229108.26 rows=32995 width=366)"
" Sort Key: fir.wp_id"
"-> Hash Left Join (cost=190376.33..226549.51 rows=32995 width=366)"
" Hash Cond: ("outer".fun_id = "inner".fun_id)"
"-> Hash Left Join (cost=190374.58..226147.09 rows=32995 width=336)"
" Hash Cond: ("outer".tra_id = "inner".tra_id)"
"-> Merge Right Join (cost=182608.86..211107.70 rows=32995 width=326)"
" Merge Cond: ("outer".tra_id = "inner".tra_id)"
"-> Index Scan using uk1_perfcache270 on perfcache270 cac270 (cost=0.00..26360.00 rows=695309 width=19)"
"-> Sort (cost=182608.86..182691.35 rows=32995 width=315)"
" Sort Key: tra.tra_id"
"-> Hash Left Join (cost=143070.31..180132.60 rows=32995 width=315)"
" Hash Cond: ("outer".tra_id = "inner".tra_id)"
"-> Hash Left Join (cost=134981.89..163162.72 rows=32995 width=305)"
" Hash Cond: ("outer".tra_id = "inner".tra_id)"
"-> Merge Right Join (cost=116451.34..130707.85 rows=32995 width=294)"
" Merge Cond: ("outer".tra_id = "inner".tra_id)"
"-> Index Scan using pk_indexperfcache90 on indexperfcache90 incac90 (cost=0.00..12969.65 rows=395189 width=18)"
"-> Sort (cost=116451.34..116533.83 rows=32995 width=284)"
" Sort Key: tra.tra_id"
"-> Merge Right Join (cost=80758.34..113975.08 rows=32995 width=284)"
" Merge Cond: ("outer".tra_id = "inner".tra_id)"
"-> Index Scan using uk1_perfcache90 on perfcache90 cac90 (cost=0.00..30740.84 rows=814044 width=19)"
"-> Sort (cost=80758.34..80840.83 rows=32995 width=273)"
" Sort Key: tra.tra_id"
"-> Hash Left Join (cost=26205.11..78282.08 rows=32995 width=273)"
" Hash Cond: (('U'::text || ("outer".fir_cusip)::text) = ("inner".fir_cusip)::text)"
"-> Hash Join (cost=24911.18..75586.30 rows=32995 width=263)"
" Hash Cond: ("outer".per_id = "inner".per_id)"
"-> Hash Join (cost=1658.41..40649.44 rows=32995 width=236)"
" Hash Cond: ("outer".fir_id = "inner".fir_id)"
"-> Bitmap Heap Scan on trade tra (cost=207.48..38208.67 rows=32995 width=180)"
" Recheck Cond: (tra_date > '2006-06-30 00:00:00'::timestamp without time zone)"
"-> Bitmap Index Scan on trade_date_index (cost=0.00..207.48 rows=32995 width=0)"
" Index Cond: (tra_date > '2006-06-30 00:00:00'::timestamp without time zone)"
"-> Hash (cost=1374.54..1374.54 rows=30554 width=56)"
"-> Seq Scan on firma fir (cost=0.00..1374.54 rows=30554 width=56)"
"-> Hash (cost=22630.62..22630.62 rows=248862 width=27)"
"-> Seq Scan on person per (cost=0.00..22630.62 rows=248862 width=27)"
"-> Hash (cost=1234.74..1234.74 rows=23674 width=34)"
"-> Seq Scan on kurs_latest kurl (cost=0.00..1234.74 rows=23674 width=34)"
"-> 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=7137.93..7137.93 rows=380193 width=18)"
"-> Seq Scan on indexperfcache180 incac180 (cost=0.00..7137.93 rows=380193 width=18)"
"-> Hash (cost=6847.57..6847.57 rows=367257 width=18)"
"-> Seq Scan on indexperfcache270 incac270 (cost=0.00..6847.57 rows=367257 width=18)"
"-> Hash (cost=1.60..1.60 rows=60 width=34)"
"-> Seq Scan on funktion fun (cost=0.00..1.60 rows=60 width=34)"


It is still doing a sequence scan on the person , perfcache180 and perfcache270 table and with out the joins it performs a index scan on these tables.

Is something wrong with the view?

once again thanks for your help.

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote: fardeen memon writes:
> What is it that i am doing wrong?

I think the forced coercion to date type in the view case is preventing
the planner from making a good guess about the selectivity of the
condition on tra_date. It has stats about tra_date's distribution,
but none about the distribution of "tra_date::date".

regards, tom lane

---------------------------------
Stay in the know. Pulse on the new Yahoo.com. Check it out.