explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wAcE

Settings
# exclusive inclusive rows x rows loops node
1. 2.845 107,897.298 ↓ 938.0 938 1

GroupAggregate (cost=56.06..56.15 rows=1 width=600) (actual time=107,894.399..107,897.298 rows=938 loops=1)

  • Group Key: ad.cid, ad.property_id, ah.ap_payee_id, apl.ap_legal_entity_id, aps.default_ap_remittance_id, ba.cid, ba.id, p.property_name, o.cid, o.id, ap.cid, ap.id, po.owner_name, ah.lease_customer_id, apl.vendor_code
  • Filter: ('0'::numeric <> sum(CASE WHEN (ad_payments.gl_transaction_type_id = 3) THEN (ad_payments.transaction_amount * '-1'::numeric) ELSE ad_payments.transaction_amount END))
2. 6.751 107,894.453 ↓ 1,302.0 1,302 1

Sort (cost=56.06..56.06 rows=1 width=425) (actual time=107,894.348..107,894.453 rows=1,302 loops=1)

  • Sort Key: ad.property_id, ah.ap_payee_id, apl.ap_legal_entity_id, aps.default_ap_remittance_id, ba.id, p.property_name, o.id, ap.id, po.owner_name, ah.lease_customer_id, apl.vendor_code
  • Sort Method: quicksort Memory: 394kB
3. 2.026 107,887.702 ↓ 1,302.0 1,302 1

Nested Loop (cost=14.03..56.05 rows=1 width=425) (actual time=79.847..107,887.702 rows=1,302 loops=1)

  • Join Filter: (p.id = lp.property_id)
  • Rows Removed by Join Filter: 2604
4. 0.071 0.246 ↓ 6.0 6 1

Merge Join (cost=2.28..2.32 rows=1 width=315) (actual time=0.087..0.246 rows=6 loops=1)

  • Merge Cond: (p.id = po.property_id)
  • Join Filter: (o.id = po.owner_id)
  • Rows Removed by Join Filter: 54
5. 0.037 0.133 ↓ 2.0 40 1

Nested Loop (cost=0.57..11.88 rows=20 width=297) (actual time=0.037..0.133 rows=40 loops=1)

6. 0.052 0.052 ↑ 1.2 4 1

Index Scan using idx_properties_cid_id_is_disabled_remote_primary_key on properties p (cost=0.29..6.33 rows=5 width=23) (actual time=0.018..0.052 rows=4 loops=1)

  • Index Cond: (cid = 8839)
7. 0.024 0.044 ↓ 2.5 10 4

Materialize (cost=0.29..5.31 rows=4 width=274) (actual time=0.005..0.011 rows=10 loops=4)

8. 0.020 0.020 ↓ 2.5 10 1

Index Scan using pk_owners on owners o (cost=0.29..5.29 rows=4 width=274) (actual time=0.012..0.020 rows=10 loops=1)

  • Index Cond: (cid = 8839)
9. 0.033 0.042 ↓ 8.5 51 1

Sort (cost=1.14..1.15 rows=6 width=22) (actual time=0.021..0.042 rows=51 loops=1)

  • Sort Key: po.property_id
  • Sort Method: quicksort Memory: 25kB
10. 0.009 0.009 ↑ 1.0 6 1

Seq Scan on property_owner_association_temp po (cost=0.00..1.06 rows=6 width=22) (actual time=0.008..0.009 rows=6 loops=1)

11. 62.568 107,885.430 ↓ 651.0 651 6

Nested Loop (cost=11.76..53.72 rows=1 width=138) (actual time=72.569..17,980.905 rows=651 loops=6)

12. 91.314 107,636.886 ↓ 10,332.0 10,332 6

Nested Loop (cost=11.47..53.08 rows=1 width=107) (actual time=1.719..17,939.481 rows=10,332 loops=6)

13. 146.760 107,297.460 ↓ 10,338.0 10,338 6

Nested Loop (cost=11.05..52.21 rows=1 width=107) (actual time=1.715..17,882.910 rows=10,338 loops=6)

  • Join Filter: (lp.property_id = ad_payments.property_id)
14. 66.996 106,737.636 ↓ 11,474.0 11,474 6

Nested Loop (cost=10.62..50.87 rows=1 width=90) (actual time=1.706..17,789.606 rows=11,474 loops=6)

  • Join Filter: (lp.property_id = aa.property_id)
15. 78.570 1,535.688 ↓ 8,242.0 8,242 6

Nested Loop (cost=10.20..49.17 rows=1 width=86) (actual time=1.684..255.948 rows=8,242 loops=6)

  • Join Filter: (ah.ap_payee_id = aps.id)
16. 53.796 1,259.310 ↓ 8,242.0 8,242 6

Nested Loop (cost=9.78..46.88 rows=1 width=90) (actual time=1.678..209.885 rows=8,242 loops=6)

17. 68.418 1,007.706 ↓ 8,242.0 8,242 6

Nested Loop (cost=9.49..44.82 rows=1 width=74) (actual time=1.670..167.951 rows=8,242 loops=6)

18. 78.668 274.548 ↓ 18,465.0 18,465 6

Hash Join (cost=9.07..43.80 rows=1 width=70) (actual time=1.655..45.758 rows=18,465 loops=6)

  • Hash Cond: (ad.bank_account_id = ba.id)
19. 87.468 195.828 ↓ 528.3 18,489 6

Nested Loop (cost=0.68..35.32 rows=35 width=33) (actual time=1.644..32.638 rows=18,489 loops=6)

20. 9.810 9.810 ↓ 3.0 3 6

Function Scan on load_properties lp (cost=0.25..1.00 rows=1 width=4) (actual time=1.629..1.635 rows=3 loops=6)

  • Filter: ((is_disabled = 0) AND (is_test = 0))
21. 98.550 98.550 ↓ 176.1 6,163 18

Index Scan using idx_ap_details_cid_property_id on ap_details ad (cost=0.42..33.97 rows=35 width=29) (actual time=0.012..5.475 rows=6,163 loops=18)

  • Index Cond: ((cid = 8839) AND (property_id = lp.property_id))
22. 0.004 0.052 ↑ 1.0 7 1

Hash (cost=8.31..8.31 rows=7 width=41) (actual time=0.052..0.052 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.048 0.048 ↑ 1.0 7 1

Index Scan using pk_bank_accounts on bank_accounts ba (cost=0.28..8.31 rows=7 width=41) (actual time=0.021..0.048 rows=7 loops=1)

  • Index Cond: (cid = 8839)
24. 664.740 664.740 ↓ 0.0 0 110,790

Index Scan using idx_ap_headers on ap_headers ah (cost=0.42..1.01 rows=1 width=28) (actual time=0.006..0.006 rows=0 loops=110,790)

  • Index Cond: (id = ad.ap_header_id)
  • Filter: ((ap_payment_id IS NULL) AND (lease_customer_id IS NULL) AND (ap_header_sub_type_id <> 15) AND (cid = 8839) AND (ad.gl_transaction_type_id = gl_transaction_type_id) AND (ad.post_month = post_month) AND (ap_payee_id = ANY ('{12536,296644,12537,12711,29411,12538,29635,297939,12539,507555,12540,12541,12535,28521,431396,12597,17422,29709,412673,442427,12542,29413,197640,25132,12543,12544,12545,12546,12821,12547,12548,12549,12550,12551,12552,12553,509198,12554,12555,12556,12557,336029,347196,12558,12559,361130,206436,301050,12561,297945,12562,12563,12564,12945,318627,12565,12566,12567,12568,29162,197639,25131,12569,12570,12571,12572,29521,12573,12574,29540,442442,12575,12576,12560,12577,12578,12579,12580,12581,12582,12583,200661,12584,29412,12585,12586,28985,12587,353738,12588,402935,12589,14834,509219,12590,392504,12591,12592,509340,509341,509342,12593,12594,509218,12595,12596,11924,12598,413576,373147,29826,206437,12599,12600,200660,12601,12602,12603,12604,12605,28995,238934,12606,12607,12608,12609,12610,29353,12611,197641,25133,27711,6203,294142,12612,29427,12613,28986,12614,12615,12942,12616,12617}'::integer[])))
  • Rows Removed by Filter: 1
25. 197.808 197.808 ↑ 1.0 1 49,452

Index Scan using idx_ap_payee_locations_id on ap_payee_locations apl (cost=0.29..2.05 rows=1 width=28) (actual time=0.004..0.004 rows=1 loops=49,452)

  • Index Cond: (id = ah.ap_payee_location_id)
  • Filter: ((cid = 8839) AND (ah.ap_payee_id = ap_payee_id))
26. 197.808 197.808 ↑ 1.0 1 49,452

Index Scan using idx_ap_payees_id on ap_payees aps (cost=0.42..2.27 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=49,452)

  • Index Cond: (id = apl.ap_payee_id)
  • Filter: (cid = 8839)
  • Rows Removed by Filter: 0
27. 105,134.952 105,134.952 ↑ 1.0 1 49,452

Index Scan using idx_ap_allocations_cid_property_id on ap_allocations aa (cost=0.42..1.69 rows=1 width=16) (actual time=0.941..2.126 rows=1 loops=49,452)

  • Index Cond: ((cid = 8839) AND (property_id = ad.property_id))
  • Filter: ((charge_ap_detail_id = ad.id) OR (credit_ap_detail_id = ad.id))
  • Rows Removed by Filter: 5723
28. 413.064 413.064 ↑ 1.0 1 68,844

Index Scan using idx_ap_details on ap_details ad_payments (cost=0.42..1.32 rows=1 width=25) (actual time=0.006..0.006 rows=1 loops=68,844)

  • Index Cond: (id = aa.credit_ap_detail_id)
  • Filter: ((reversal_ap_detail_id IS NULL) AND (cid = 8839) AND (aa.property_id = property_id))
  • Rows Removed by Filter: 0
29. 248.112 248.112 ↑ 1.0 1 62,028

Index Scan using idx_ap_headers on ap_headers ah_payments (cost=0.42..0.87 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=62,028)

  • Index Cond: (id = ad_payments.ap_header_id)
  • Filter: ((ap_header_sub_type_id <> 15) AND (cid = 8839))
  • Rows Removed by Filter: 0
30. 185.976 185.976 ↓ 0.0 0 61,992

Index Scan using idx_ap_payments_id on ap_payments ap (cost=0.29..0.64 rows=1 width=35) (actual time=0.003..0.003 rows=0 loops=61,992)

  • Index Cond: (id = ah_payments.ap_payment_id)
  • Filter: ((payment_date >= '2019-01-01'::date) AND (payment_date < '2020-01-01 00:00:00'::timestamp without time zone) AND (cid = 8839))
  • Rows Removed by Filter: 1
Planning time : 838.306 ms