explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C8FO

Settings
# exclusive inclusive rows x rows loops node
1. 3.642 117,545.719 ↓ 623.0 623 1

GroupAggregate (cost=128.74..128.83 rows=1 width=599) (actual time=117,541.255..117,545.719 rows=623 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, 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. 8.736 117,542.077 ↓ 2,255.0 2,255 1

Sort (cost=128.74..128.75 rows=1 width=425) (actual time=117,541.203..117,542.077 rows=2,255 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, ah.lease_customer_id, apl.vendor_code
  • Sort Method: quicksort Memory: 807kB
3. 6.498 117,533.341 ↓ 2,255.0 2,255 1

Nested Loop (cost=4.50..128.73 rows=1 width=425) (actual time=3.043..117,533.341 rows=2,255 loops=1)

4. 6.555 117,517.823 ↓ 2,255.0 2,255 1

Nested Loop (cost=4.22..124.62 rows=1 width=144) (actual time=3.033..117,517.823 rows=2,255 loops=1)

  • Join Filter: (lp.property_id = p.id)
5. 6.748 117,502.248 ↓ 2,255.0 2,255 1

Nested Loop (cost=3.94..124.29 rows=1 width=137) (actual time=3.025..117,502.248 rows=2,255 loops=1)

6. 7.010 117,486.480 ↓ 2,255.0 2,255 1

Nested Loop (cost=3.66..123.98 rows=1 width=100) (actual time=3.017..117,486.480 rows=2,255 loops=1)

  • Join Filter: ((aps.id = apl.ap_payee_id) AND (ale.id = apl.ap_legal_entity_id))
7. 5.959 117,454.665 ↓ 2,255.0 2,255 1

Nested Loop (cost=3.37..123.26 rows=1 width=101) (actual time=3.007..117,454.665 rows=2,255 loops=1)

  • Join Filter: (aps.id = ale.ap_payee_id)
8. 14.716 117,423.901 ↓ 2,255.0 2,255 1

Nested Loop (cost=3.07..122.63 rows=1 width=93) (actual time=3.000..117,423.901 rows=2,255 loops=1)

9. 23.947 117,303.543 ↓ 11,738.0 11,738 1

Nested Loop (cost=2.78..122.01 rows=1 width=62) (actual time=2.990..117,303.543 rows=11,738 loops=1)

10. 33.322 117,150.478 ↓ 11,738.0 11,738 1

Nested Loop (cost=2.36..121.24 rows=1 width=62) (actual time=2.984..117,150.478 rows=11,738 loops=1)

11. 44.087 117,070.204 ↓ 11,738.0 11,738 1

Nested Loop (cost=1.94..120.10 rows=1 width=54) (actual time=2.974..117,070.204 rows=11,738 loops=1)

12. 49,883.023 116,759.669 ↓ 22,204.0 22,204 1

Nested Loop (cost=1.52..119.35 rows=1 width=54) (actual time=2.951..116,759.669 rows=22,204 loops=1)

  • 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: 104566430
13. 49.923 364.564 ↓ 11,102.0 11,102 1

Nested Loop (cost=1.10..115.50 rows=1 width=41) (actual time=2.942..364.564 rows=11,102 loops=1)

  • Join Filter: (lp.property_id = aa.property_id)
14. 26.691 52.297 ↓ 352.6 21,862 1

Nested Loop (cost=0.68..68.04 rows=62 width=29) (actual time=2.930..52.297 rows=21,862 loops=1)

15. 2.922 2.922 ↓ 4.0 4 1

Function Scan on load_properties lp (cost=0.25..1.00 rows=1 width=4) (actual time=2.915..2.922 rows=4 loops=1)

  • Filter: ((is_disabled = 0) AND (is_test = 0))
16. 22.684 22.684 ↓ 88.2 5,466 4

Index Scan using idx_ap_details_cid_property_id on ap_details ad_payments (cost=0.42..66.42 rows=62 width=25) (actual time=0.011..5.671 rows=5,466 loops=4)

  • Index Cond: ((cid = 10276) AND (property_id = lp.property_id))
  • Filter: (reversal_ap_detail_id IS NULL)
  • Rows Removed by Filter: 532
17. 262.344 262.344 ↑ 1.0 1 21,862

Index Scan using idx_ap_allocations_cid_credit_ap_detail_id_allocation_amount on ap_allocations aa (cost=0.42..0.75 rows=1 width=16) (actual time=0.012..0.012 rows=1 loops=21,862)

  • Index Cond: ((cid = 10276) AND (credit_ap_detail_id = ad_payments.id))
  • Filter: (ad_payments.property_id = property_id)
18. 66,512.082 66,512.082 ↓ 134.6 9,421 11,102

Index Scan using idx_ap_details_cid_property_id on ap_details ad (cost=0.42..2.63 rows=70 width=29) (actual time=0.008..5.991 rows=9,421 loops=11,102)

  • Index Cond: ((cid = 10276) AND (property_id = ad_payments.property_id))
19. 266.448 266.448 ↑ 1.0 1 22,204

Index Scan using idx_ap_headers on ap_headers ah (cost=0.42..0.74 rows=1 width=24) (actual time=0.012..0.012 rows=1 loops=22,204)

  • Index Cond: (id = ad.ap_header_id)
  • Filter: ((ap_payment_id IS NULL) AND (ap_payee_id IS NOT NULL) AND (ap_header_sub_type_id <> 15) AND (cid = 10276) AND (ad.gl_transaction_type_id = gl_transaction_type_id) AND (ad.post_month = post_month))
  • Rows Removed by Filter: 0
20. 46.952 46.952 ↑ 1.0 1 11,738

Index Scan using pk_ap_payees on ap_payees aps (cost=0.42..1.12 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=11,738)

  • Index Cond: ((cid = 10276) AND (id = ah.ap_payee_id))
21. 129.118 129.118 ↑ 1.0 1 11,738

Index Scan using idx_ap_headers on ap_headers ah_payments (cost=0.42..0.77 rows=1 width=12) (actual time=0.011..0.011 rows=1 loops=11,738)

  • Index Cond: (id = ad_payments.ap_header_id)
  • Filter: ((ap_header_sub_type_id <> 15) AND (cid = 10276))
  • Rows Removed by Filter: 0
22. 105.642 105.642 ↓ 0.0 0 11,738

Index Scan using idx_ap_payments_id on ap_payments ap (cost=0.29..0.63 rows=1 width=35) (actual time=0.009..0.009 rows=0 loops=11,738)

  • 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 = 10276))
  • Rows Removed by Filter: 1
23. 24.805 24.805 ↑ 1.0 1 2,255

Index Scan using idx_ap_legal_entities_ap_payee_id on ap_legal_entities ale (cost=0.29..0.61 rows=1 width=12) (actual time=0.010..0.011 rows=1 loops=2,255)

  • Index Cond: (ap_payee_id = ah.ap_payee_id)
  • Filter: (cid = 10276)
  • Rows Removed by Filter: 0
24. 24.805 24.805 ↑ 1.0 1 2,255

Index Scan using idx_ap_payee_locations_ap_payee_id on ap_payee_locations apl (cost=0.29..0.70 rows=1 width=23) (actual time=0.010..0.011 rows=1 loops=2,255)

  • Index Cond: (ap_payee_id = ah.ap_payee_id)
  • Filter: (is_primary AND (cid = 10276))
  • Rows Removed by Filter: 2
25. 9.020 9.020 ↑ 1.0 1 2,255

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

  • Index Cond: (id = ad.bank_account_id)
  • Filter: (cid = 10276)
26. 9.020 9.020 ↑ 1.0 1 2,255

Index Scan using idx_properties_id on properties p (cost=0.29..0.32 rows=1 width=27) (actual time=0.004..0.004 rows=1 loops=2,255)

  • Index Cond: (id = ad.property_id)
  • Filter: (cid = 10276)
27. 9.020 9.020 ↑ 1.0 1 2,255

Index Scan using pk_owners on owners o (cost=0.29..2.20 rows=1 width=289) (actual time=0.004..0.004 rows=1 loops=2,255)

  • Index Cond: ((cid = 10276) AND (id = p.owner_id))
  • Filter: (id = ANY ('{29812,29810,29809,29811}'::integer[]))
Planning time : 52.196 ms