explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 965D

Settings
# exclusive inclusive rows x rows loops node
1. 0.175 589.043 ↓ 89.0 89 1

Sort (cost=518.92..518.93 rows=1 width=196) (actual time=589.030..589.043 rows=89 loops=1)

  • Sort Key: sub.id DESC
  • Sort Method: quicksort Memory: 48kB
2. 0.074 588.868 ↓ 89.0 89 1

Subquery Scan on sub (cost=518.85..518.91 rows=1 width=196) (actual time=588.556..588.868 rows=89 loops=1)

  • Filter: (sub.cid = 8839)
3. 0.240 588.794 ↓ 89.0 89 1

Unique (cost=518.85..518.90 rows=1 width=200) (actual time=588.552..588.794 rows=89 loops=1)

4. 0.906 588.554 ↓ 155.0 155 1

Sort (cost=518.85..518.85 rows=1 width=200) (actual time=588.534..588.554 rows=155 loops=1)

  • Sort Key: ap.id, (CASE WHEN ((ad_charge.reimbursement_ap_detail_id IS NOT NULL) OR ((2 = ad_charge.ap_transaction_type_id) AND (1 = ad_charge.reimbursement_method_id)) OR (alternatives: SubPlan 1 or hashed SubPlan 2)) THEN 'true'::text ELSE 'false'::text END), ba.account_name, ap.bank_account_id, apt.name, apt.id, ap.payment_date, ap.payment_amount, (func_format_refund_customer_names(ap.payee_name, false)), ap.payment_number, ah.post_month, ah.id, (CASE WHEN ah.is_initial_import THEN 1 ELSE 0 END), ah1.property_name, (max(ad_charge.property_id) OVER (?)), ap.payment_status_type_id, apl.vendor_code, (CASE WHEN ((ap.ap_payment_type_id = 8) OR (ap.ap_payment_type_id = 9)) THEN CASE WHEN ((gd.gl_reconciliation_id IS NOT NULL) AND (gr.gl_reconciliation_status_type_id = 3)) THEN 'Cleared'::text WHEN ((gd.gl_reconciliation_id IS NOT NULL) AND (gr.gl_reconciliation_status_type_id = 1)) THEN 'Reconciled'::text WHEN (ap.payment_status_type_id = 1) THEN 'Pending'::text WHEN (ap.payment_status_type_id = 2) THEN 'Received [Unreconciled]'::text WHEN (ap.payment_status_type_id = 7) THEN 'Voiding'::text WHEN (ap.payment_status_type_id = 9) THEN 'Capturing'::text WHEN (ap.payment_status_type_id = 10) THEN 'Captured'::text WHEN (ap.payment_status_type_id = 8) THEN 'Voided'::text WHEN (ap.payment_status_type_id = ANY ('{15,30}'::integer[])) THEN 'Returned'::text ELSE NULL::text END WHEN (ap.payment_status_type_id = 8) THEN 'Voided'::text WHEN (ap.payment_status_type_id = ANY ('{15,30}'::integer[])) THEN 'Returned'::text ELSE CASE WHEN ((gd.gl_reconciliation_id IS NOT NULL) AND (gr.gl_reconciliation_status_type_id = 3)) THEN 'Cleared'::text WHEN (gd.gl_reconciliation_id IS NULL) THEN 'Unreconciled'::text ELSE 'Reconciled'::text END END), gr.gl_reconciliation_status_type_id
  • Sort Method: quicksort Memory: 66kB
5. 19.779 587.648 ↓ 155.0 155 1

WindowAgg (cost=516.08..518.84 rows=1 width=200) (actual time=567.805..587.648 rows=155 loops=1)

6. 0.442 563.839 ↓ 155.0 155 1

Sort (cost=516.08..516.08 rows=1 width=146) (actual time=563.761..563.839 rows=155 loops=1)

  • Sort Key: ah1.ap_payment_id
  • Sort Method: quicksort Memory: 66kB
7. 0.209 563.397 ↓ 155.0 155 1

Nested Loop Left Join (cost=425.54..516.07 rows=1 width=146) (actual time=194.900..563.397 rows=155 loops=1)

8. 19.029 563.033 ↓ 155.0 155 1

Nested Loop Left Join (cost=425.26..515.76 rows=1 width=146) (actual time=194.875..563.033 rows=155 loops=1)

  • Join Filter: ((gd.amount = CASE WHEN ((aa.origin_ap_allocation_id IS NOT NULL) OR (aa.lump_ap_header_id IS NOT NULL)) THEN (aa.allocation_amount * '-1'::numeric) ELSE aa.allocation_amount END) AND (gd.property_id = CASE WHEN (ba.reimbursed_property_id <> aa.property_id) THEN ba.reimbursed_property_id ELSE aa.property_id END))
  • Rows Removed by Join Filter: 5783
  • Filter: (gd.gl_reconciliation_id IS NULL)
  • Rows Removed by Filter: 5624
9. 7.195 486.214 ↓ 5,779.0 5,779 1

Nested Loop Left Join (cost=424.82..513.33 rows=1 width=167) (actual time=24.897..486.214 rows=5,779 loops=1)

10. 12.035 415.450 ↓ 5,779.0 5,779 1

Nested Loop Left Join (cost=424.39..511.08 rows=1 width=171) (actual time=24.839..415.450 rows=5,779 loops=1)

  • Filter: (ah_charge.reversal_ap_header_id IS NULL)
11. 17.195 368.741 ↓ 5,779.0 5,779 1

Nested Loop Left Join (cost=423.97..510.32 rows=1 width=171) (actual time=24.805..368.741 rows=5,779 loops=1)

12. 13.155 299.535 ↓ 5,779.0 5,779 1

Nested Loop Left Join (cost=423.55..508.75 rows=1 width=151) (actual time=24.760..299.535 rows=5,779 loops=1)

13. 12.459 245.927 ↓ 5,779.0 5,779 1

Nested Loop Left Join (cost=423.13..508.10 rows=1 width=126) (actual time=24.709..245.927 rows=5,779 loops=1)

14. 7.863 204.573 ↓ 5,779.0 5,779 1

Nested Loop Left Join (cost=422.71..505.86 rows=1 width=125) (actual time=24.639..204.573 rows=5,779 loops=1)

15. 9.970 162.036 ↓ 5,779.0 5,779 1

Nested Loop Left Join (cost=422.29..503.67 rows=1 width=129) (actual time=24.545..162.036 rows=5,779 loops=1)

16. 8.364 117.392 ↓ 5,779.0 5,779 1

Nested Loop Left Join (cost=422.01..502.49 rows=1 width=111) (actual time=24.486..117.392 rows=5,779 loops=1)

17. 5.964 91.691 ↓ 5,779.0 5,779 1

Nested Loop Left Join (cost=421.87..502.30 rows=1 width=96) (actual time=24.465..91.691 rows=5,779 loops=1)

18. 4.453 57.864 ↓ 2,533.0 2,533 1

Nested Loop (cost=421.45..499.88 rows=1 width=92) (actual time=24.423..57.864 rows=2,533 loops=1)

19. 4.896 30.987 ↓ 934.3 2,803 1

Hash Join (cost=421.15..496.83 rows=3 width=44) (actual time=24.280..30.987 rows=2,803 loops=1)

  • Hash Cond: (ah1.id = ah.id)
20. 1.851 1.851 ↑ 1.0 2,954 1

Seq Scan on ah1 (cost=0.00..67.93 rows=2,954 width=23) (actual time=0.017..1.851 rows=2,954 loops=1)

  • Filter: (cid = 8839)
21. 4.283 24.240 ↓ 28.6 7,190 1

Hash (cost=418.02..418.02 rows=251 width=25) (actual time=24.240..24.240 rows=7,190 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 486kB
22. 19.957 19.957 ↓ 28.6 7,190 1

Index Scan using idx_ap_headers_ap_payee_id_remote_primary_key on ap_headers ah (cost=0.42..418.02 rows=251 width=25) (actual time=0.127..19.957 rows=7,190 loops=1)

  • Index Cond: ((cid = 8839) AND (ap_payee_id = ANY ('{296644,12536,12537,12538,29635,12539,12540,12541,433484,12535,28521,431396,12597,17422,412673,12542,197640,12543,12544,12545,12546,12821,12547,12548,12549,12550,390219,12551,12552,12553,12554,12555,12556,12557,336029,347196,12558,12559,361130,301050,12561,12562,12563,12564,12945,318627,12565,12566,12567,12568,29162,197639,12569,12570,12571,12572,29521,12574,29540,12575,12576,12560,12577,12578,12579,12581,12582,12583,200661,12584,12585,12586,28985,12587,353738,12588,402935,12589,14834,12590,392504,12591,12592,12593,437241,12594,12595,12596,11924,12598,413576,373147,29826,206437,12599,12600,200660,12601,12602,12603,12604,12605,28995,238934,12606,12607,12608,12609,12610,293162,29353,12611,197641,294142,358668,12612,29427,28986,12614,12615,12942,12616,12617}'::integer[])))
23. 22.424 22.424 ↑ 1.0 1 2,803

Index Scan using pk_ap_payments on ap_payments ap (cost=0.29..1.01 rows=1 width=52) (actual time=0.008..0.008 rows=1 loops=2,803)

  • Index Cond: ((cid = 8839) AND (id = ah1.ap_payment_id))
  • Filter: ((NOT is_unclaimed_property) AND (payment_status_type_id <> 8) AND (bank_account_id = ANY ('{1811,5008,4859,1218,1216,5291,1217,1416}'::integer[])) AND (ap_payment_type_id = ANY ('{6,1,2,4,3,7,5,10,12,13,11}'::integer[])))
  • Rows Removed by Filter: 0
24. 27.863 27.863 ↓ 2.0 2 2,533

Index Scan using idx_ap_details_cid_apheaderid on ap_details ad (cost=0.42..2.41 rows=1 width=20) (actual time=0.007..0.011 rows=2 loops=2,533)

  • Index Cond: ((ah.cid = cid) AND (cid = 8839) AND (ah.id = ap_header_id))
  • Filter: ((deleted_by IS NULL) AND (deleted_on IS NULL) AND (ah.gl_transaction_type_id = gl_transaction_type_id) AND (ah.post_month = post_month))
25. 17.337 17.337 ↑ 1.0 1 5,779

Index Scan using pk_ap_payment_types on ap_payment_types apt (cost=0.14..0.19 rows=1 width=15) (actual time=0.003..0.003 rows=1 loops=5,779)

  • Index Cond: (id = ap.ap_payment_type_id)
26. 34.674 34.674 ↑ 1.0 1 5,779

Index Scan using pk_bank_accounts on bank_accounts ba (cost=0.28..1.04 rows=1 width=26) (actual time=0.006..0.006 rows=1 loops=5,779)

  • Index Cond: ((cid = ap.cid) AND (cid = 8839) AND (id = ap.bank_account_id))
27. 34.674 34.674 ↑ 1.0 1 5,779

Index Only Scan using pk_ap_payees on ap_payees ap1 (cost=0.42..2.17 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=5,779)

  • Index Cond: ((cid = ah.cid) AND (cid = 8839) AND (id = ah.ap_payee_id))
  • Heap Fetches: 5779
28. 28.895 28.895 ↑ 1.0 1 5,779

Index Scan using pk_ap_payee_locations on ap_payee_locations apl (cost=0.42..2.23 rows=1 width=17) (actual time=0.005..0.005 rows=1 loops=5,779)

  • Index Cond: ((cid = ah.cid) AND (cid = 8839) AND (id = ah.ap_payee_location_id))
29. 40.453 40.453 ↑ 1.0 1 5,779

Index Scan using idx_ap_allocations_credit_ap_detail_id on ap_allocations aa (cost=0.42..0.64 rows=1 width=37) (actual time=0.007..0.007 rows=1 loops=5,779)

  • Index Cond: (credit_ap_detail_id = ad.id)
  • Filter: ((cid = 8839) AND (cid = ad.cid))
  • Rows Removed by Filter: 0
30. 52.011 52.011 ↑ 1.0 1 5,779

Index Scan using idx_ap_details on ap_details ad_charge (cost=0.42..1.57 rows=1 width=28) (actual time=0.009..0.009 rows=1 loops=5,779)

  • Index Cond: (id = aa.charge_ap_detail_id)
  • Filter: ((cid = 8839) AND (cid = aa.cid))
  • Rows Removed by Filter: 0
31. 34.674 34.674 ↑ 1.0 1 5,779

Index Scan using pk_ap_headers on ap_headers ah_charge (cost=0.42..0.74 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=5,779)

  • Index Cond: ((cid = ad_charge.cid) AND (cid = 8839) AND (id = ad_charge.ap_header_id))
32. 63.569 63.569 ↑ 1.0 1 5,779

Index Scan using idx_gl_headers_cid_reference_id on gl_headers gh (cost=0.43..2.25 rows=1 width=16) (actual time=0.010..0.011 rows=1 loops=5,779)

  • Index Cond: ((cid = aa.cid) AND (cid = 8839) AND (reference_id = aa.id))
  • Filter: ((gl_transaction_type_id = 4) AND (gl_transaction_type_id = aa.gl_transaction_type_id))
  • Rows Removed by Filter: 0
33. 57.790 57.790 ↓ 2.0 2 5,779

Index Scan using idx_gl_details_cid_gl_header_id on gl_details gd (cost=0.43..2.41 rows=1 width=21) (actual time=0.008..0.010 rows=2 loops=5,779)

  • Index Cond: ((cid = gh.cid) AND (cid = 8839) AND (gl_header_id = gh.id))
34. 0.155 0.155 ↓ 0.0 0 155

Index Scan using pk_gl_reconciliations on gl_reconciliations gr (cost=0.28..0.31 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=155)

  • Index Cond: ((cid = gd.cid) AND (cid = 8839) AND (id = gd.gl_reconciliation_id))
35.          

SubPlan (forWindowAgg)

36. 4.030 4.030 ↓ 0.0 0 155

Index Scan using idx_ap_details_cid_apheaderid on ap_details (cost=0.42..2.45 rows=1 width=0) (actual time=0.026..0.026 rows=0 loops=155)

  • Index Cond: ((cid = ad_charge.cid) AND (ap_header_id = ad_charge.ap_header_id))
  • Filter: (3 = reimbursement_method_id)
  • Rows Removed by Filter: 2
37. 0.000 0.000 ↓ 0.0 0

Seq Scan on ap_details ap_details_1 (cost=0.00..36,231.00 rows=64 width=8) (never executed)

  • Filter: (3 = reimbursement_method_id)
Planning time : 199.941 ms