Hi Team, Can you please help in tunning the attached query as , i am trying to run this query and it runs for several hours and it did not give any output.
I am not able to generate the explain analyze plan as well and it keeps on running for several hours and did not give output. I have attached the query and explain plan without analyze. Please help if nayone has any idea how to tune that query. Regards, Mukesh Kumar
qyery.sql
Description: qyery.sql
"Nested Loop Left Join (cost=74158.47..94986.58 rows=2 width=347)"
" -> Nested Loop (cost=74158.19..94985.45 rows=2 width=248)"
" -> Nested Loop (cost=74157.77..94984.55 rows=2 width=266)"
" -> Nested Loop (cost=74157.35..94983.64 rows=2 width=248)"
" Join Filter: ((paybase.payment_sid_k)::text =
(a.payment_sid_c)::text)"
" -> Nested Loop (cost=74156.93..94983.04 rows=1
width=327)"
" Join Filter: (((paygroup.doc_sid_f)::text =
(paygroup_1.doc_sid_f)::text) AND ((paygroup.payment_group_sid_k)::text =
(paygroup_1.payment_group_sid_k)::text) AND ((paybase.payment_sid_k)::text =
(paybase_1.payment_sid_k)::text) AND ((docidassoc.land_contract_id)::text =
(docidassoc_1.land_contract_id)::text))"
" -> Nested Loop (cost=17409.66..34604.72 rows=1
width=250)"
" Join Filter: ((paygroup.doc_sid_f)::text =
(docidassoc.doc_sid_c)::text)"
" -> Nested Loop (cost=17409.37..34604.37
rows=1 width=218)"
" Join Filter: ((paygroup.doc_sid_f)::text
= (lms_doc_acquisition_base.doc_sid_c)::text)"
" -> Nested Loop
(cost=14955.89..31181.19 rows=1 width=160)"
" -> Nested Loop
(cost=14955.75..31181.03 rows=1 width=162)"
" -> Nested Loop
(cost=14955.47..31180.73 rows=1 width=123)"
" -> Nested Loop
(cost=14955.05..31180.28 rows=1 width=86)"
" Join Filter:
(((itemvendorbase.payment_sid_c)::text = (lineitemacct.payment_sid_c)::text)
AND (lineitem.line_item_seq_k = (lineitemacct.line_item_seq_c)::numeric))"
" -> Nested Loop
(cost=14954.63..31171.38 rows=18 width=64)"
" -> Hash
Join (cost=14954.21..29828.50 rows=2860 width=55)"
" Hash
Cond: (((itemvendorbase.payment_sid_c)::text = (lineitem.payment_sid_c)::text)
AND ((itemvendorbase.line_item_seq_c)::numeric = lineitem.line_item_seq_k))"
" ->
Hash Left Join (cost=6942.01..19960.79 rows=345211 width=31)"
"
Hash Cond: (((itemvendorbase.vendor_number_c)::text ||
(itemvendorbase.vendor_suffix_c)::text) =
((so_vendor_address_base.ap_vendor_id_lf)::text ||
(so_vendor_address_base.ap_vendor_suffix_lf)::text))"
"
-> Seq Scan on lms_payment_item_vendor_base itemvendorbase
(cost=0.00..7409.11 rows=345211 width=31)"
"
-> Hash (cost=5971.87..5971.87 rows=77611 width=10)"
"
-> Hash Left Join (cost=2617.45..5971.87 rows=77611 width=10)"
"
Hash Cond: (((so_vendor_address_base.ap_vendor_id_lf)::text ||
(so_vendor_address_base.ap_vendor_suffix_lf)::text) = (c.lfa1_emnfr)::text)"
"
-> Hash Join (cost=2306.88..4992.73 rows=77611 width=10)"
"
Hash Cond: ((so_vendor_address_base.vendor_sid_lf)::text =
(so_vendor_base.vendor_sid_k)::text)"
"
-> Seq Scan on so_vendor_address_base (cost=0.00..2482.11
rows=77611 width=28)"
"
-> Hash (cost=1517.50..1517.50 rows=63150 width=17)"
"
-> Seq Scan on so_vendor_base (cost=0.00..1517.50
rows=63150 width=17)"
"
-> Hash (cost=202.48..202.48 rows=8648 width=9)"
"
-> Seq Scan on sapecc_lfa1_assoc c (cost=0.00..202.48
rows=8648 width=9)"
" ->
Hash (cost=5121.28..5121.28 rows=192728 width=24)"
"
-> Seq Scan on lms_payment_line_item_base lineitem (cost=0.00..5121.28
rows=192728 width=24)"
" -> Index
Only Scan using lms_payment_check_request_pkey on lms_payment_check_request
checkrequest (cost=0.42..0.47 rows=1 width=29)"
"
Index Cond: ((payment_sid_c = (itemvendorbase.payment_sid_c)::text) AND
(vendor_number_c = (itemvendorbase.vendor_number_c)::text) AND (vendor_suffix_c
= (itemvendorbase.vendor_suffix_c)::text))"
" -> Index Scan
using lms_pay_line_item_acct_base_pkey on lms_pay_line_item_acct_base
lineitemacct (cost=0.42..0.48 rows=1 width=31)"
" Index
Cond: ((payment_sid_c)::text = (checkrequest.payment_sid_c)::text)"
" Filter:
((sap_gl_account)::text = ANY ('{0053010000,0053015000,0087220000}'::text[]))"
" -> Index Scan using
lms_pay_base_paymnet_idx on lms_pay_base paybase (cost=0.42..0.45 rows=1
width=37)"
" Index Cond:
((payment_sid_k)::text = (itemvendorbase.payment_sid_c)::text)"
" -> Index Scan using
lms_payment_group_base_pkey on lms_payment_group_base paygroup
(cost=0.29..0.30 rows=1 width=39)"
" Index Cond:
((payment_group_sid_k)::text = (paybase.payment_group_sid_f)::text)"
" -> Index Only Scan using
lms_payment_type_lkup_pkey on lms_payment_type_lkup paytypelkup
(cost=0.14..0.16 rows=1 width=2)"
" Index Cond: (payment_type_k
= (paygroup.payment_type_f)::text)"
" -> HashAggregate
(cost=2453.48..2751.85 rows=29837 width=78)"
" Group Key:
lms_doc_acquisition_base.doc_sid_c, so_phci_entity_base.oc_resp_code_f"
" -> Append (cost=188.02..2304.30
rows=29837 width=78)"
" -> Hash Join
(cost=188.02..1060.69 rows=21300 width=22)"
" Hash Cond:
((lms_doc_acquisition_base.aquisition_area_sid_f)::text =
(so_phci_entity_base.phci_entity_sid_k)::text)"
" -> Hash Join
(cost=30.93..847.62 rows=21300 width=55)"
" Hash Cond:
((lms_doc_acquisition_base.aquisition_area_sid_f)::text =
(x.entity_sid_c)::text)"
" -> Seq Scan on
lms_doc_acquisition_base (cost=0.00..722.67 rows=35667 width=36)"
" -> Hash
(cost=23.48..23.48 rows=596 width=19)"
" -> Seq
Scan on lms_entity_extension_base x (cost=0.00..23.48 rows=596 width=19)"
"
Filter: ((land_area_yn)::text = 'Y'::text)"
" -> Hash
(cost=112.04..112.04 rows=3604 width=24)"
" -> Seq Scan on
so_phci_entity_base (cost=0.00..112.04 rows=3604 width=24)"
" -> Nested Loop
(cost=19.41..796.05 rows=8537 width=37)"
" -> Hash Join
(cost=19.00..44.05 rows=239 width=97)"
" Hash Cond:
((x_1.entity_sid_c)::text = (t.entity_sid_c)::text)"
" -> Seq Scan on
lms_entity_extension_base x_1 (cost=0.00..23.48 rows=596 width=19)"
" Filter:
((land_area_yn)::text = 'Y'::text)"
" -> Hash
(cost=14.00..14.00 rows=400 width=78)"
" -> Seq
Scan on lms_third_party_number_base t (cost=0.00..14.00 rows=400 width=78)"
" -> Index Scan using
lms_doc_acq_base_acqf_fk_idx on lms_doc_acquisition_base
lms_doc_acquisition_base_1 (cost=0.41..2.51 rows=64 width=36)"
" Index Cond:
((aquisition_area_sid_f)::text = (x_1.entity_sid_c)::text)"
" -> Index Scan using lms_doc_id_assoc_pkey on
lms_doc_id_assoc docidassoc (cost=0.29..0.33 rows=1 width=32)"
" Index Cond: ((doc_sid_c)::text =
(lms_doc_acquisition_base.doc_sid_c)::text)"
" -> Nested Loop (cost=56747.27..60377.94 rows=19
width=315)"
" -> Hash Join (cost=56746.98..60371.12
rows=19 width=283)"
" Hash Cond: ((a_1.payment_sid_c)::text =
(paybase_1.payment_sid_k)::text)"
" -> HashAggregate
(cost=55693.11..57726.99 rows=115641 width=55)"
" Group Key: a_1.payment_sid_c,
a_1.currency_code"
" Filter: (sum(a_1.payment_amount)
<> '0'::numeric)"
" -> Seq Scan on
lms_payment_line_item_base a_1 (cost=0.00..53785.10 rows=190801 width=28)"
" Filter:
((translate_payment_status(payment_sid_c))::text <> ALL
('{CANCELLED,START}'::text[]))"
" -> Hash (cost=1053.54..1053.54 rows=26
width=228)"
" -> Nested Loop
(cost=587.61..1053.54 rows=26 width=228)"
" -> Hash Join
(cost=587.19..645.90 rows=338 width=167)"
" Hash Cond:
((lms_doc_acquisition_base_2.doc_sid_c)::text = (paygroup_1.doc_sid_f)::text)"
" -> HashAggregate
(cost=166.91..173.72 rows=681 width=78)"
" Group Key:
lms_doc_acquisition_base_2.doc_sid_c, so_phci_entity_base_1.oc_resp_code_f"
" -> Append
(cost=0.97..163.51 rows=681 width=78)"
" -> Nested
Loop (cost=0.97..53.40 rows=83 width=22)"
" ->
Nested Loop (cost=0.56..47.11 rows=2 width=43)"
"
-> Index Scan using idx_oc_resp_code_f on so_phci_entity_base
so_phci_entity_base_1 (cost=0.28..25.38 rows=14 width=24)"
"
Index Cond: ((oc_resp_code_f)::text = ANY
('{1530,1531,1556,1624,1632,1637,1641,1714,1717,1834,1835,1838,1841,1634}'::text[]))"
"
-> Index Scan using lms_entity_extension_base_pkey on
lms_entity_extension_base x_2 (cost=0.28..1.55 rows=1 width=19)"
"
Index Cond: ((entity_sid_c)::text =
(so_phci_entity_base_1.phci_entity_sid_k)::text)"
"
Filter: ((land_area_yn)::text = 'Y'::text)"
" ->
Index Scan using lms_doc_acq_base_acqf_fk_idx on lms_doc_acquisition_base
lms_doc_acquisition_base_2 (cost=0.41..2.51 rows=64 width=36)"
"
Index Cond: ((aquisition_area_sid_f)::text = (x_2.entity_sid_c)::text)"
" -> Nested
Loop (cost=21.77..99.89 rows=598 width=37)"
" ->
Hash Join (cost=21.35..46.40 rows=17 width=97)"
"
Hash Cond: ((x_3.entity_sid_c)::text = (t_1.entity_sid_c)::text)"
"
-> Seq Scan on lms_entity_extension_base x_3 (cost=0.00..23.48 rows=596
width=19)"
"
Filter: ((land_area_yn)::text = 'Y'::text)"
"
-> Hash (cost=21.00..21.00 rows=28 width=78)"
"
-> Seq Scan on lms_third_party_number_base t_1 (cost=0.00..21.00
rows=28 width=78)"
"
Filter: ((third_party_id)::text = ANY
('{1530,1531,1556,1624,1632,1637,1641,1714,1717,1834,1835,1838,1841,1634}'::text[]))"
" ->
Index Scan using lms_doc_acq_base_acqf_fk_idx on lms_doc_acquisition_base
lms_doc_acquisition_base_3 (cost=0.41..2.51 rows=64 width=36)"
"
Index Cond: ((aquisition_area_sid_f)::text = (x_3.entity_sid_c)::text)"
" -> Hash
(cost=384.07..384.07 rows=2897 width=89)"
" -> Nested Loop
(cost=0.29..384.07 rows=2897 width=89)"
" -> Seq
Scan on lms_payment_type_lkup paytypelkup_1 (cost=0.00..1.42 rows=5 width=28)"
"
Filter: ((drop_down_display)::text = ANY ('{""Earned Overriding
Royalty"",""Earned Overriding Royalty - Calculate Only"",""Earned
Royalty"",""Earned Royalty - Calculate Only"",""Earned Wheelage
Royalty""}'::text[]))"
" -> Index
Scan using idx_lms_payment_grp_pymnt_type on lms_payment_group_base paygroup_1
(cost=0.29..70.50 rows=603 width=65)"
"
Index Cond: ((payment_type_f)::text = (paytypelkup_1.payment_type_k)::text)"
" -> Index Scan using
lms_pay_base_paygroup_idx on lms_pay_base paybase_1 (cost=0.42..1.20 rows=1
width=61)"
" Index Cond:
((payment_group_sid_f)::text = (paygroup_1.payment_group_sid_k)::text)"
" Filter: ((due_date >=
'2013-03-25 00:00:00'::timestamp without time zone) AND (due_date <=
'2013-05-22 00:00:00'::timestamp without time zone))"
" -> Index Scan using lms_doc_id_assoc_pkey on
lms_doc_id_assoc docidassoc_1 (cost=0.29..0.36 rows=1 width=32)"
" Index Cond: ((doc_sid_c)::text =
(paygroup_1.doc_sid_f)::text)"
" -> Index Scan using check_status_idx on
lms_payment_check_request a (cost=0.42..0.53 rows=5 width=35)"
" Index Cond: ((payment_sid_c)::text =
(itemvendorbase.payment_sid_c)::text)"
" -> Index Scan using idx_so_vendor_address on
so_vendor_address_base so_vendor_address_base_1 (cost=0.42..0.44 rows=1
width=28)"
" Index Cond: (((ap_vendor_id_lf)::text ||
(ap_vendor_suffix_lf)::text) = ((a.vendor_number_c)::text ||
(a.vendor_suffix_c)::text))"
" -> Index Only Scan using so_vendor_base_pkey on so_vendor_base
so_vendor_base_1 (cost=0.41..0.45 rows=1 width=17)"
" Index Cond: (vendor_sid_k =
(so_vendor_address_base_1.vendor_sid_lf)::text)"
" -> Index Only Scan using indx_ztx_lfa1_assoc_emnfr on sapecc_lfa1_assoc c_1
(cost=0.29..0.31 rows=1 width=9)"
" Index Cond: (lfa1_emnfr =
((so_vendor_address_base_1.ap_vendor_id_lf)::text ||
(so_vendor_address_base_1.ap_vendor_suffix_lf)::text))"
