pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance problem with joins
4+ messages / 2 participants
[nested] [flat]
* Performance problem with joins
@ 2006-09-08 12:48 fardeen memon <frank69in@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: fardeen memon @ 2006-09-08 12:48 UTC (permalink / raw)
To: pgsql-performance
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.
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance problem with joins
@ 2006-09-08 14:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: fardeen memon <frank69in@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2006-09-08 14:46 UTC (permalink / raw)
To: fardeen memon <frank69in@yahoo.com>; +Cc: pgsql-performance
fardeen memon <frank69in@yahoo.com> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance problem with joins
@ 2006-09-11 11:29 fardeen memon <frank69in@yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: fardeen memon @ 2006-09-11 11:29 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance problem with joins
@ 2006-09-11 14:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: fardeen memon <frank69in@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2006-09-11 14:21 UTC (permalink / raw)
To: fardeen memon <frank69in@yahoo.com>; +Cc: pgsql-performance
fardeen memon <frank69in@yahoo.com> writes:
> here is the output of the explain query after changing the tra_date column to timestamp.
If you want intelligent commentary, please (a) post EXPLAIN ANALYZE not
EXPLAIN output, and (b) don't mangle the indentation. This is just
about unreadable :-(
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-09-11 14:21 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-08 12:48 Performance problem with joins fardeen memon <frank69in@yahoo.com>
2006-09-08 14:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-11 11:29 ` fardeen memon <frank69in@yahoo.com>
2006-09-11 14:21 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox