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@sss.pgh.pa.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.