pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedNever Ending query in PostgreSQL
8+ messages / 5 participants
[nested] [flat]
* Never Ending query in PostgreSQL
@ 2022-02-27 04:40 Kumar, Mukesh <MKumar@peabodyenergy.com>
0 siblings, 3 replies; 8+ messages in thread
From: Kumar, Mukesh @ 2022-02-27 04:40 UTC (permalink / raw)
To: pgsql-performance
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
"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))"
Attachments:
[application/octet-stream] qyery.sql (17.3K, ../../CH0P221MB04744E424D17609A1F8B814BDE009@CH0P221MB0474.NAMP221.PROD.OUTLOOK.COM/3-qyery.sql)
download
[text/plain] Exec.txt (17.3K, ../../CH0P221MB04744E424D17609A1F8B814BDE009@CH0P221MB0474.NAMP221.PROD.OUTLOOK.COM/4-Exec.txt)
download | inline:
"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))"
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Never Ending query in PostgreSQL
@ 2022-02-27 12:22 Julien Rouhaud <rjuju123@gmail.com>
parent: Kumar, Mukesh <MKumar@peabodyenergy.com>
2 siblings, 0 replies; 8+ messages in thread
From: Julien Rouhaud @ 2022-02-27 12:22 UTC (permalink / raw)
To: Kumar, Mukesh <MKumar@peabodyenergy.com>; +Cc: pgsql-performance
Hi,
On Sun, Feb 27, 2022 at 04:40:16AM +0000, Kumar, Mukesh wrote:
>
> 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.
You attached the explain plan in both files. Also even if there was the query
there wouldn't be enough information to be able to help, please consult
https://wiki.postgresql.org/wiki/Slow_Query_Questions to provide more details.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Never Ending query in PostgreSQL
@ 2022-02-27 17:20 Jeff Janes <jeff.janes@gmail.com>
parent: Kumar, Mukesh <MKumar@peabodyenergy.com>
2 siblings, 2 replies; 8+ messages in thread
From: Jeff Janes @ 2022-02-27 17:20 UTC (permalink / raw)
To: Kumar, Mukesh <MKumar@peabodyenergy.com>; +Cc: pgsql-performance
On Sun, Feb 27, 2022 at 7:09 AM Kumar, Mukesh <MKumar@peabodyenergy.com>
wrote:
> 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.
>
Several hours is not all that long. Without an EXPLAIN ANALYZE, we could
easily spend several hours scratching our heads and still get nowhere. So
unless having this running cripples the rest of your system, please queue
up another one and let it go longer. But first, do an ANALYZE (and
preferably a VACUUM ANALYZE) on all the tables. If you have a test db
which is a recent clone of production, you could do it there so as not to
slow down production. The problem is that the row estimates must be way
off (otherwise, it shouldn't take long) and if that is the case, we can't
use the plan to decide much of anything, since we don't trust it.
In parallel you could start evicting table joins from the query to simplify
it until it gets to the point where it will run, so you can then see the
actual row counts. To do that it does help if you know what the intent of
the query is (or for that matter, the text of the query--you attached the
plan twice).
Cheers,
Jeff
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Never Ending query in PostgreSQL
@ 2022-03-01 03:44 Mladen Gogala <gogala.mladen@gmail.com>
parent: Kumar, Mukesh <MKumar@peabodyenergy.com>
2 siblings, 0 replies; 8+ messages in thread
From: Mladen Gogala @ 2022-03-01 03:44 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
On 2/26/22 23:40, Kumar, Mukesh wrote:
> 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
Hi Team Member,
Your attachments are not SQL, they are plans. Judging by the size of the
plans, your best course of action is to completely rewrite the queries,
probably using CTE and temporary tables. May the Force be with you.
Regards
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Never Ending query in PostgreSQL
@ 2022-03-01 03:54 Mladen Gogala <gogala.mladen@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Mladen Gogala @ 2022-03-01 03:54 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
On 2/27/22 12:20, Jeff Janes wrote:
> Several hours is not all that long.
Well, the pyramids in the Valley of the Kings last for around 4500
years. Dinosaurs have ruled the Earth for approximately 120 million
years. Solar system is 5 billion years old. Cosmos is around 13 billion
years old. Compared to those numbers, indeed, several hours isn't that
long. Furthermore, you have to account for the time dilatation. One hour
on the planet that's evolving and revolving at 900 miles an hour is not
the same as one hour of standing still. To make things even more
interesting, it's orbiting at 19 miles a second, so it's reckoned,The
sun that is the source of all our power. So, several hours is relative.
Each object has its relative time so it's not possible to conclude
whether several hours is a long time or not.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Never Ending query in PostgreSQL
@ 2022-03-01 14:05 Tomas Vondra <tomas.vondra@enterprisedb.com>
parent: Jeff Janes <jeff.janes@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Tomas Vondra @ 2022-03-01 14:05 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; Kumar, Mukesh <MKumar@peabodyenergy.com>; +Cc: pgsql-performance
On 2/27/22 18:20, Jeff Janes wrote:
>
> On Sun, Feb 27, 2022 at 7:09 AM Kumar, Mukesh <MKumar@peabodyenergy.com
> <mailto:MKumar@peabodyenergy.com>> wrote:
>
> 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.
>
>
> Several hours is not all that long. Without an EXPLAIN ANALYZE, we
> could easily spend several hours scratching our heads and still get
> nowhere. So unless having this running cripples the rest of your
> system, please queue up another one and let it go longer. But first, do
> an ANALYZE (and preferably a VACUUM ANALYZE) on all the tables. If you
> have a test db which is a recent clone of production, you could do it
> there so as not to slow down production. The problem is that the row
> estimates must be way off (otherwise, it shouldn't take long) and if
> that is the case, we can't use the plan to decide much of anything,
> since we don't trust it.
>
I'd bet Jeff is right and poor estimates are the root cause. The pattern
with a cascade of "nested loop" in the explain is fairly typical. This
is likely due to the complex join conditions and correlation.
> In parallel you could start evicting table joins from the query to
> simplify it until it gets to the point where it will run, so you can
> then see the actual row counts. To do that it does help if you know
> what the intent of the query is (or for that matter, the text of the
> query--you attached the plan twice).
>
Right, simplify the query. Or maybe do it the other way around - start
with the simplest query (the inner-most part of the explain) and add
joins one by one (by following the explains) until it suddenly starts
being much slower.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: Never Ending query in PostgreSQL
@ 2022-03-01 15:01 Kumar, Mukesh <MKumar@peabodyenergy.com>
parent: Tomas Vondra <tomas.vondra@enterprisedb.com>
0 siblings, 1 reply; 8+ messages in thread
From: Kumar, Mukesh @ 2022-03-01 15:01 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@enterprisedb.com>; Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance
Hi Tomas ,
Thanks for replying , We have identified a Join condition which is creating a problem for that query.
Accept my apologies for pasting the plan twice. I am attaching the query again in this mail
We have found that by evicting the View paymenttransdetails_view from the attached query runs in approx. 10 secs and the view contains multiple conditions and 1 jojn as well.
I am attaching the View definition as well.
Please suggest if there is a work around for this query to run faster without evicting the above from the query.
Thanks and Regards,
Mukesh Kumar
-----Original Message-----
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Sent: Tuesday, March 1, 2022 7:35 PM
To: Jeff Janes <jeff.janes@gmail.com>; Kumar, Mukesh <MKumar@peabodyenergy.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Never Ending query in PostgreSQL
On 2/27/22 18:20, Jeff Janes wrote:
>
> On Sun, Feb 27, 2022 at 7:09 AM Kumar, Mukesh
> <MKumar@peabodyenergy.com <mailto:MKumar@peabodyenergy.com>> wrote:
>
> 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.
>
>
> Several hours is not all that long. Without an EXPLAIN ANALYZE, we
> could easily spend several hours scratching our heads and still get
> nowhere. So unless having this running cripples the rest of your
> system, please queue up another one and let it go longer. But first,
> do an ANALYZE (and preferably a VACUUM ANALYZE) on all the tables. If
> you have a test db which is a recent clone of production, you could do
> it there so as not to slow down production. The problem is that the
> row estimates must be way off (otherwise, it shouldn't take long) and
> if that is the case, we can't use the plan to decide much of anything,
> since we don't trust it.
>
I'd bet Jeff is right and poor estimates are the root cause. The pattern with a cascade of "nested loop" in the explain is fairly typical. This is likely due to the complex join conditions and correlation.
> In parallel you could start evicting table joins from the query to
> simplify it until it gets to the point where it will run, so you can
> then see the actual row counts. To do that it does help if you know
> what the intent of the query is (or for that matter, the text of the
> query--you attached the plan twice).
>
Right, simplify the query. Or maybe do it the other way around - start with the simplest query (the inner-most part of the explain) and add joins one by one (by following the explains) until it suddenly starts being much slower.
regards
--
Tomas Vondra
EnterpriseDB: https://urldefense.com/v3/__http://www.enterprisedb.com__;!!KupS4sW4BlfImQPd!P_2LgOrDOnTxBqFECBDdQol...
The Enterprise PostgreSQL Company
Attachments:
[application/octet-stream] Tuned_Query (002).sql (2.1K, ../../CH0P221MB04741DF0AECB008C1B2C657EDE029@CH0P221MB0474.NAMP221.PROD.OUTLOOK.COM/2-Tuned_Query%20%28002%29.sql)
download
[application/octet-stream] View.sql (2.8K, ../../CH0P221MB04741DF0AECB008C1B2C657EDE029@CH0P221MB0474.NAMP221.PROD.OUTLOOK.COM/3-View.sql)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Never Ending query in PostgreSQL
@ 2022-03-01 15:39 Tomas Vondra <tomas.vondra@enterprisedb.com>
parent: Kumar, Mukesh <MKumar@peabodyenergy.com>
0 siblings, 0 replies; 8+ messages in thread
From: Tomas Vondra @ 2022-03-01 15:39 UTC (permalink / raw)
To: Kumar, Mukesh <MKumar@peabodyenergy.com>; Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance
On 3/1/22 16:01, Kumar, Mukesh wrote:
> Hi Tomas ,
>
> Thanks for replying , We have identified a Join condition which is
> creating a problem for that query.
>
> Accept my apologies for pasting the plan twice. I am attaching the
> query again in this mail
>
Queries without explain (or even better "explain analyze") are useless.
We don't have the data, we don't know what the executed plan is, we
don't know what plan might be a better one.
There's a wiki page about reporting slow queries (what info to include,
etc):
https://wiki.postgresql.org/wiki/Slow_Query_Questions
> We have found that by evicting the View paymenttransdetails_view from
> the attached query runs in approx. 10 secs and the view contains
> multiple conditions and 1 jojn as well.
>
You need to add individual tables, not a view which is itself a join of
10+ tables. The idea is that you start with a fast query, add tables one
by one (in the join order from the explain). You'll be able to do
EXPLAIN ANALYZE and watch estimate accuracy, and then at some point it
gets much slower, which is the join that causes trouble. But you might
still be able to do explain analyze.
So looking at the explain plan you shared before, you'd start with a
join of so_vendor_address_base + so_vendor_base, and then you'd add
- sapecc_lfa1_assoc
- lms_payment_item_vendor_base
- lms_payment_line_item_base
- lms_payment_check_request
- lms_pay_line_item_acct_base
- ...
(in this order). I'd bet "lms_payment_check_request" is where things
start to go south.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2022-03-01 15:39 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-02-27 04:40 Never Ending query in PostgreSQL Kumar, Mukesh <MKumar@peabodyenergy.com>
2022-02-27 12:22 ` Julien Rouhaud <rjuju123@gmail.com>
2022-02-27 17:20 ` Jeff Janes <jeff.janes@gmail.com>
2022-03-01 03:54 ` Mladen Gogala <gogala.mladen@gmail.com>
2022-03-01 14:05 ` Tomas Vondra <tomas.vondra@enterprisedb.com>
2022-03-01 15:01 ` Kumar, Mukesh <MKumar@peabodyenergy.com>
2022-03-01 15:39 ` Tomas Vondra <tomas.vondra@enterprisedb.com>
2022-03-01 03:44 ` Mladen Gogala <gogala.mladen@gmail.com>
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