explain.depesz.com

PostgreSQL's explain analyze made readable

Result: taTG

Settings
# exclusive inclusive rows x rows loops node
1. 0.074 533.151 ↓ 91.0 91 1

Sort (cost=516.48..516.49 rows=1 width=196) (actual time=533.144..533.151 rows=91 loops=1)

  • Sort Key: sub.id DESC
  • Sort Method: quicksort Memory: 49kB
2. 0.042 533.077 ↓ 91.0 91 1

Subquery Scan on sub (cost=516.41..516.47 rows=1 width=196) (actual time=532.911..533.077 rows=91 loops=1)

  • Filter: (sub.cid = 8839)
3. 0.118 533.035 ↓ 91.0 91 1

Unique (cost=516.41..516.46 rows=1 width=200) (actual time=532.908..533.035 rows=91 loops=1)

4. 0.450 532.917 ↓ 155.0 155 1

Sort (cost=516.41..516.41 rows=1 width=200) (actual time=532.907..532.917 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))) THEN 'true'::text WHEN ((1 = ad_charge.ap_transaction_type_id) AND (3 = ad_charge.reimbursement_method_id)) 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. 9.024 532.467 ↓ 155.0 155 1

WindowAgg (cost=516.08..516.40 rows=1 width=200) (actual time=525.054..532.467 rows=155 loops=1)

6. 0.282 523.443 ↓ 155.0 155 1

Sort (cost=516.08..516.08 rows=1 width=138) (actual time=523.419..523.443 rows=155 loops=1)

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

Nested Loop Left Join (cost=425.54..516.07 rows=1 width=138) (actual time=195.046..523.161 rows=155 loops=1)

8. 18.357 522.901 ↓ 155.0 155 1

Nested Loop Left Join (cost=425.26..515.76 rows=1 width=138) (actual time=195.023..522.901 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. 11.130 452.533 ↓ 5,779.0 5,779 1

Nested Loop Left Join (cost=424.82..513.33 rows=1 width=159) (actual time=17.154..452.533 rows=5,779 loops=1)

10. 12.495 389.392 ↓ 5,779.0 5,779 1

Nested Loop Left Join (cost=424.39..511.08 rows=1 width=163) (actual time=17.058..389.392 rows=5,779 loops=1)

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

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

12. 11.473 279.375 ↓ 5,779.0 5,779 1

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

13. 9.288 227.449 ↓ 5,779.0 5,779 1

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

14. 10.250 189.266 ↓ 5,779.0 5,779 1

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

15. 11.851 150.121 ↓ 5,779.0 5,779 1

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

16. 14.690 103.596 ↓ 5,779.0 5,779 1

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

17. 4.759 77.348 ↓ 5,779.0 5,779 1

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

18. 5.352 47.259 ↓ 2,533.0 2,533 1

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

19. 4.247 22.286 ↓ 934.3 2,803 1

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

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

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

  • Filter: (cid = 8839)
21. 2.790 16.298 ↓ 28.6 7,188 1

Hash (cost=418.02..418.02 rows=251 width=25) (actual time=16.297..16.298 rows=7,188 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 486kB
22. 13.508 13.508 ↓ 28.6 7,188 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.065..13.508 rows=7,188 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. 19.621 19.621 ↑ 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.007..0.007 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. 25.330 25.330 ↓ 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.010 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. 11.558 11.558 ↑ 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.002..0.002 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. 28.895 28.895 ↑ 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.005..0.005 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. 28.895 28.895 ↑ 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.005..0.005 rows=1 loops=5,779)

  • Index Cond: ((cid = ad_charge.cid) AND (cid = 8839) AND (id = ad_charge.ap_header_id))
32. 52.011 52.011 ↑ 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.009..0.009 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. 52.011 52.011 ↓ 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.009 rows=2 loops=5,779)

  • Index Cond: ((cid = gh.cid) AND (cid = 8839) AND (gl_header_id = gh.id))
34. 0.000 0.000 ↓ 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.000..0.000 rows=0 loops=155)

  • Index Cond: ((cid = gd.cid) AND (cid = 8839) AND (id = gd.gl_reconciliation_id))
Planning time : 151.767 ms