|
|
QUERY
-----
EXPLAIN SELECT fin_paymen0_.Fin_Payment_ID as Fin1_206_, fin_paymen0_.AD_Client_ID as AD2_206_, fin_paymen0_.AD_Org_ID as AD3_206_,
fin_paymen0_.Created as Created206_, fin_paymen0_.Createdby as Createdby206_, fin_paymen0_.Updated as Updated206_,
fin_paymen0_.Updatedby as Updatedby206_, fin_paymen0_.Isactive as Isactive206_, fin_paymen0_.Isreceipt as Isreceipt206_,
fin_paymen0_.C_Bpartner_ID as C10_206_, fin_paymen0_.Paymentdate as Payment11_206_, fin_paymen0_.C_Currency_ID as C12_206_,
fin_paymen0_.Amount as Amount206_, fin_paymen0_.Writeoffamt as Writeof14_206_, fin_paymen0_.Fin_Paymentmethod_ID as Fin15_206_,
fin_paymen0_.DocumentNo as DocumentNo206_, fin_paymen0_.Referenceno as Referen17_206_, fin_paymen0_.Status as Status206_,
fin_paymen0_.Processed as Processed206_, fin_paymen0_.Processing as Processing206_, fin_paymen0_.Posted as Posted206_,
fin_paymen0_.Description as Descrip22_206_, fin_paymen0_.Fin_Financial_Account_ID as Fin23_206_, fin_paymen0_.C_DocType_ID as C24_206_,
fin_paymen0_.C_Project_ID as C25_206_, fin_paymen0_.C_Campaign_ID as C26_206_, fin_paymen0_.C_Activity_ID as C27_206_,
fin_paymen0_.User1_ID as User28_206_, fin_paymen0_.User2_ID as User29_206_, fin_paymen0_.Generated_Credit as Generated30_206_,
fin_paymen0_.Used_Credit as Used31_206_, fin_paymen0_.CreatedByAlgorithm as Created32_206_,
fin_paymen0_.Finacc_Txn_Convert_Rate as Finacc33_206_, fin_paymen0_.Finacc_Txn_Amount as Finacc34_206_, fin_paymen0_.EM_APRM_Process_Payment as EM35_206_,
fin_paymen0_.FIN_Rev_Payment_ID as FIN36_206_, fin_paymen0_.EM_APRM_Reconcile_Payment as EM37_206_, fin_paymen0_.EM_Aprm_Add_Scheduledpayments as EM38_206_,
fin_paymen0_.EM_Aprm_Executepayment as EM39_206_, fin_paymen0_.EM_APRM_ReversePayment as EM40_206_, fin_paymen0_.C_Costcenter_ID as C41_206_
from FIN_Payment fin_paymen0_
where (
fin_paymen0_.AD_Org_ID in ('0' , 'E443A31992CB4635AFCAEABE7183CE85' , 'B843C30461EA4501935CB1D125C9C25A' , 'DC206C91AA6A4897B44DA897936E0EC3' , '19404EAD144C49A0AF37D54377CF452D'))
and (fin_paymen0_.Status in ('RPR' , 'PPM'))
and fin_paymen0_.Amount<>0
and (exists (select 1 from FIN_FinAcc_PaymentMethod financialm1_
inner join FIN_Financial_Account fin_financ2_ on financialm1_.FIN_Financial_Account_ID=fin_financ2_.Fin_Financial_Account_ID
cross join FIN_Financial_Account fin_financ3_
where fin_paymen0_.Fin_Financial_Account_ID=fin_financ3_.Fin_Financial_Account_ID
and financialm1_.FIN_Paymentmethod_ID=fin_paymen0_.Fin_Paymentmethod_ID
and fin_financ2_.Fin_Financial_Account_ID='DEDDE613C5314ACD8DCC60C474D1A107'
and case when fin_paymen0_.Isreceipt='Y' then financialm1_.Payin_Allow else financialm1_.Payout_Allow end='Y'
and fin_financ2_.C_Currency_ID=fin_financ3_.C_Currency_ID))
and not (exists (select 1 from FIN_Finacc_Transaction fin_finacc4_ where fin_paymen0_.Fin_Payment_ID=fin_finacc4_.Fin_Payment_ID))
and ad_isorgincluded(fin_paymen0_.AD_Org_ID, '0', fin_paymen0_.AD_Client_ID)<>-1
and fin_paymen0_.Fin_Financial_Account_ID='DEDDE613C5314ACD8DCC60C474D1A107' and (fin_paymen0_.AD_Client_ID in ('23C59575B9CF467C9620760EB255B389' , '0'))
and fin_paymen0_.Isactive='Y' order by fin_paymen0_.Paymentdate, fin_paymen0_.DocumentNo, fin_paymen0_.Fin_Payment_ID;
ORIGINAL PLAN
---------------
Sort (cost=486.63..486.63 rows=1 width=936)
Sort Key: fin_paymen0_.paymentdate, fin_paymen0_.documentno, fin_paymen0_.fin_payment_id
-> Nested Loop Anti Join (cost=0.55..486.62 rows=1 width=936)
-> Nested Loop Semi Join (cost=0.28..482.31 rows=1 width=936)
Join Filter: (((fin_paymen0_.fin_paymentmethod_id)::text = (financialm1_.fin_paymentmethod_id)::text) AND (CASE WHEN (fin_paymen0_.isreceipt = 'Y'::bpchar) THEN financialm1_.payin_allow ELSE financialm1_.payout_allow END = 'Y'::bpchar))
-> Seq Scan on fin_payment fin_paymen0_ (cost=0.00..462.25 rows=2 width=936)
Filter: (((status)::text = ANY ('{RPR,PPM}'::text[])) AND (amount <> 0::numeric) AND ((ad_client_id)::text = ANY ('{23C59575B9CF467C9620760EB255B389,0}'::text[])) AND (isactive = 'Y'::bpchar) AND ((fin_financial_account_id)::text = 'DED (...)
-> Materialize (cost=0.28..20.02 rows=1 width=119)
-> Nested Loop (cost=0.28..20.02 rows=1 width=119)
-> Nested Loop (cost=0.28..16.32 rows=1 width=164)
Join Filter: ((fin_financ2_.c_currency_id)::text = (fin_financ3_.c_currency_id)::text)
-> Index Scan using fin_financial_account_key on fin_financial_account fin_financ2_ (cost=0.14..8.15 rows=1 width=164)
Index Cond: ((fin_financial_account_id)::text = 'DEDDE613C5314ACD8DCC60C474D1A107'::text)
-> Index Scan using fin_financial_account_key on fin_financial_account fin_financ3_ (cost=0.14..8.15 rows=1 width=164)
Index Cond: ((fin_financial_account_id)::text = 'DEDDE613C5314ACD8DCC60C474D1A107'::text)
-> Seq Scan on fin_finacc_paymentmethod financialm1_ (cost=0.00..3.67 rows=2 width=70)
Filter: ((fin_financial_account_id)::text = 'DEDDE613C5314ACD8DCC60C474D1A107'::text)
-> Index Only Scan using fin_finacc_transaction_un on fin_finacc_transaction fin_finacc4_ (cost=0.28..4.29 rows=1 width=33)
Index Cond: (fin_payment_id = (fin_paymen0_.fin_payment_id)::text)
PLAN WITH INDEX
---------------
Sort (cost=39.46..39.46 rows=1 width=936)
Sort Key: fin_paymen0_.paymentdate, fin_paymen0_.documentno, fin_paymen0_.fin_payment_id
-> Nested Loop Anti Join (cost=0.83..39.45 rows=1 width=936)
-> Nested Loop Semi Join (cost=0.55..35.14 rows=1 width=936)
Join Filter: (((fin_paymen0_.fin_paymentmethod_id)::text = (financialm1_.fin_paymentmethod_id)::text) AND (CASE WHEN (fin_paymen0_.isreceipt = 'Y'::bpchar) THEN financialm1_.payin_allow ELSE financialm1_.payout_allow END = 'Y'::bpchar))
-> Index Scan using fin_payment_status on fin_payment fin_paymen0_ (cost=0.28..15.08 rows=2 width=936)
Index Cond: ((status)::text = ANY ('{RPR,PPM}'::text[]))
Filter: ((amount <> 0::numeric) AND ((ad_client_id)::text = ANY ('{23C59575B9CF467C9620760EB255B389,0}'::text[])) AND (isactive = 'Y'::bpchar) AND ((fin_financial_account_id)::text = 'DEDDE613C5314ACD8DCC60C474D1A107'::text) AND ((ad_or (...)
-> Materialize (cost=0.28..20.02 rows=1 width=119)
-> Nested Loop (cost=0.28..20.02 rows=1 width=119)
-> Nested Loop (cost=0.28..16.32 rows=1 width=164)
Join Filter: ((fin_financ2_.c_currency_id)::text = (fin_financ3_.c_currency_id)::text)
-> Index Scan using fin_financial_account_key on fin_financial_account fin_financ2_ (cost=0.14..8.15 rows=1 width=164)
Index Cond: ((fin_financial_account_id)::text = 'DEDDE613C5314ACD8DCC60C474D1A107'::text)
-> Index Scan using fin_financial_account_key on fin_financial_account fin_financ3_ (cost=0.14..8.15 rows=1 width=164)
Index Cond: ((fin_financial_account_id)::text = 'DEDDE613C5314ACD8DCC60C474D1A107'::text)
-> Seq Scan on fin_finacc_paymentmethod financialm1_ (cost=0.00..3.67 rows=2 width=70)
Filter: ((fin_financial_account_id)::text = 'DEDDE613C5314ACD8DCC60C474D1A107'::text)
-> Index Only Scan using fin_finacc_transaction_un on fin_finacc_transaction fin_finacc4_ (cost=0.28..4.29 rows=1 width=33) |
|