Never Ending query in PostgreSQL

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 

Attachment: 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))"

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux