explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cMLQ

Settings
# exclusive inclusive rows x rows loops node
1. 2.932 350,237.468 ↓ 938.0 938 1

GroupAggregate (cost=75.57..75.66 rows=1 width=600) (actual time=350,234.514..350,237.468 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. 9.371 350,234.536 ↓ 1,302.0 1,302 1

Sort (cost=75.57..75.57 rows=1 width=425) (actual time=350,234.428..350,234.536 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.861 350,225.165 ↓ 1,302.0 1,302 1

Nested Loop (cost=6.21..75.56 rows=1 width=425) (actual time=163.814..350,225.165 rows=1,302 loops=1)

4. 1.751 350,215.794 ↓ 1,302.0 1,302 1

Nested Loop (cost=5.93..75.25 rows=1 width=388) (actual time=163.749..350,215.794 rows=1,302 loops=1)

  • Join Filter: (p.id = lp.property_id)
  • Rows Removed by Join Filter: 2604
5. 0.078 0.355 ↓ 6.0 6 1

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

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

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

7. 0.085 0.085 ↑ 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.026..0.085 rows=4 loops=1)

  • Index Cond: (cid = 8839)
8. 0.035 0.068 ↓ 2.5 10 4

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

9. 0.033 0.033 ↓ 2.5 10 1

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

  • Index Cond: (cid = 8839)
10. 0.051 0.063 ↓ 8.5 51 1

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

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

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

12. 9.582 350,213.688 ↓ 651.0 651 6

Nested Loop (cost=3.66..72.91 rows=1 width=101) (actual time=143.010..58,368.948 rows=651 loops=6)

  • Join Filter: (aps.id = ale.ap_payee_id)
13. 60.114 350,188.482 ↓ 651.0 651 6

Nested Loop (cost=3.37..71.02 rows=1 width=117) (actual time=142.991..58,364.747 rows=651 loops=6)

14. 91.692 349,942.392 ↓ 10,332.0 10,332 6

Nested Loop (cost=3.07..70.38 rows=1 width=86) (actual time=2.886..58,323.732 rows=10,332 loops=6)

15. 119.472 349,664.616 ↓ 10,338.0 10,338 6

Nested Loop (cost=2.65..69.51 rows=1 width=86) (actual time=2.881..58,277.436 rows=10,338 loops=6)

  • Join Filter: (ah.ap_payee_id = aps.id)
16. 66.918 349,297.032 ↓ 10,338.0 10,338 6

Nested Loop (cost=2.24..67.20 rows=1 width=78) (actual time=2.870..58,216.172 rows=10,338 loops=6)

17. 250.500 348,857.946 ↓ 10,338.0 10,338 6

Nested Loop (cost=1.94..65.14 rows=1 width=54) (actual time=2.860..58,142.991 rows=10,338 loops=6)

18. 122,573.328 347,927.262 ↓ 18,894.0 18,894 6

Nested Loop (cost=1.52..64.12 rows=1 width=54) (actual time=2.841..57,987.877 rows=18,894 loops=6)

  • Join Filter: ((lp.property_id = ad.property_id) AND ((aa.charge_ap_detail_id = ad.id) OR (aa.credit_ap_detail_id = ad.id)))
  • Rows Removed by Join Filter: 86256480
19. 156.060 836.532 ↓ 9,447.0 9,447 6

Nested Loop (cost=1.10..61.31 rows=1 width=41) (actual time=2.833..139.422 rows=9,447 loops=6)

  • Join Filter: (lp.property_id = aa.property_id)
20. 60.018 171.222 ↓ 547.6 16,975 6

Nested Loop (cost=0.68..35.28 rows=31 width=29) (actual time=2.825..28.537 rows=16,975 loops=6)

21. 16.974 16.974 ↓ 3.0 3 6

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

  • Filter: ((is_disabled = 0) AND (is_test = 0))
22. 94.230 94.230 ↓ 182.5 5,658 18

Index Scan using idx_ap_details_cid_property_id on ap_details ad_payments (cost=0.42..33.97 rows=31 width=25) (actual time=0.014..5.235 rows=5,658 loops=18)

  • Index Cond: ((cid = 8839) AND (property_id = lp.property_id))
  • Filter: (reversal_ap_detail_id IS NULL)
  • Rows Removed by Filter: 505
23. 509.250 509.250 ↑ 1.0 1 101,850

Index Scan using idx_ap_allocations_credit_ap_detail_id on ap_allocations aa (cost=0.42..0.83 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=101,850)

  • Index Cond: (credit_ap_detail_id = ad_payments.id)
  • Filter: ((cid = 8839) AND (ad_payments.property_id = property_id))
  • Rows Removed by Filter: 0
24. 224,517.402 224,517.402 ↓ 260.9 9,133 56,682

Index Scan using idx_ap_details_cid_property_id on ap_details ad (cost=0.42..2.20 rows=35 width=29) (actual time=0.008..3.961 rows=9,133 loops=56,682)

  • Index Cond: ((cid = 8839) AND (property_id = ad_payments.property_id))
25. 680.184 680.184 ↑ 1.0 1 113,364

Index Scan using idx_ap_headers on ap_headers ah (cost=0.42..1.01 rows=1 width=24) (actual time=0.006..0.006 rows=1 loops=113,364)

  • 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: 0
26. 372.168 372.168 ↑ 1.0 1 62,028

Index Scan using idx_ap_payee_locations_ap_payee_id on ap_payee_locations apl (cost=0.29..2.05 rows=1 width=24) (actual time=0.005..0.006 rows=1 loops=62,028)

  • Index Cond: (ap_payee_id = ah.ap_payee_id)
  • Filter: (is_primary AND (cid = 8839))
  • Rows Removed by Filter: 1
27. 248.112 248.112 ↑ 1.0 1 62,028

Index Scan using idx_ap_payees_id on ap_payees aps (cost=0.42..2.29 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=62,028)

  • Index Cond: (id = apl.ap_payee_id)
  • Filter: (cid = 8839)
  • Rows Removed by Filter: 0
28. 186.084 186.084 ↑ 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.003..0.003 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
29. 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
30. 15.624 15.624 ↑ 1.0 1 3,906

Index Scan using idx_ap_legal_entities_ap_payee_id on ap_legal_entities ale (cost=0.29..1.88 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=3,906)

  • Index Cond: (ap_payee_id = apl.ap_payee_id)
  • Filter: ((cid = 8839) AND (apl.ap_legal_entity_id = id))
31. 6.510 6.510 ↑ 1.0 1 1,302

Index Scan using idx_bank_accounts on bank_accounts ba (cost=0.28..0.30 rows=1 width=41) (actual time=0.005..0.005 rows=1 loops=1,302)

  • Index Cond: (id = ad.bank_account_id)
  • Filter: (cid = 8839)
Planning time : 865.908 ms