explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GLiZd

Settings
# exclusive inclusive rows x rows loops node
1. 0.468 114,608.786 ↓ 311.5 623 1

Append (cost=128.12..144.58 rows=2 width=2,060) (actual time=114,600.440..114,608.786 rows=623 loops=1)

2. 0.518 114,605.436 ↓ 623.0 623 1

Subquery Scan on "*SELECT* 1" (cost=128.12..128.22 rows=1 width=564) (actual time=114,600.438..114,605.436 rows=623 loops=1)

3. 3.665 114,604.918 ↓ 623.0 623 1

GroupAggregate (cost=128.12..128.21 rows=1 width=599) (actual time=114,600.436..114,604.918 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))
4. 8.566 114,601.253 ↓ 2,255.0 2,255 1

Sort (cost=128.12..128.12 rows=1 width=425) (actual time=114,600.381..114,601.253 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
5. 5.450 114,592.687 ↓ 2,255.0 2,255 1

Nested Loop (cost=4.21..128.11 rows=1 width=425) (actual time=5.651..114,592.687 rows=2,255 loops=1)

6. 5.249 114,578.217 ↓ 2,255.0 2,255 1

Nested Loop (cost=3.93..123.99 rows=1 width=144) (actual time=5.641..114,578.217 rows=2,255 loops=1)

  • Join Filter: (lp.property_id = p.id)
7. 6.791 114,563.948 ↓ 2,255.0 2,255 1

Nested Loop (cost=3.64..123.66 rows=1 width=137) (actual time=5.633..114,563.948 rows=2,255 loops=1)

8. 5.816 114,550.392 ↓ 2,255.0 2,255 1

Nested Loop (cost=3.37..123.35 rows=1 width=100) (actual time=5.626..114,550.392 rows=2,255 loops=1)

  • Join Filter: (aps.id = apl.ap_payee_id)
9. 22.925 114,535.556 ↓ 2,255.0 2,255 1

Nested Loop (cost=3.07..122.63 rows=1 width=97) (actual time=5.619..114,535.556 rows=2,255 loops=1)

10. 23.179 114,477.417 ↓ 11,738.0 11,738 1

Nested Loop (cost=2.78..122.01 rows=1 width=66) (actual time=5.609..114,477.417 rows=11,738 loops=1)

11. 26.073 114,419.024 ↓ 11,738.0 11,738 1

Nested Loop (cost=2.36..121.24 rows=1 width=66) (actual time=5.604..114,419.024 rows=11,738 loops=1)

12. 51.016 114,345.999 ↓ 11,738.0 11,738 1

Nested Loop (cost=1.94..120.10 rows=1 width=58) (actual time=5.594..114,345.999 rows=11,738 loops=1)

13. 49,231.433 114,206.167 ↓ 22,204.0 22,204 1

Nested Loop (cost=1.52..119.35 rows=1 width=54) (actual time=5.570..114,206.167 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: 104,566,430
14. 34.018 172.360 ↓ 11,102.0 11,102 1

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

  • Join Filter: (lp.property_id = aa.property_id)
15. 24.164 50.894 ↓ 352.6 21,862 1

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

16. 5.542 5.542 ↓ 4.0 4 1

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

  • Filter: ((is_disabled = 0) AND (is_test = 0))
17. 21.188 21.188 ↓ 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.297 rows=5,466 loops=4)

  • Index Cond: ((cid = 10,276) AND (property_id = lp.property_id))
  • Filter: (reversal_ap_detail_id IS NULL)
  • Rows Removed by Filter: 532
18. 87.448 87.448 ↑ 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.003..0.004 rows=1 loops=21,862)

  • Index Cond: ((cid = 10,276) AND (credit_ap_detail_id = ad_payments.id))
  • Filter: (ad_payments.property_id = property_id)
19. 64,802.374 64,802.374 ↓ 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.837 rows=9,421 loops=11,102)

  • Index Cond: ((cid = 10,276) AND (property_id = ad_payments.property_id))
20. 88.816 88.816 ↑ 1.0 1 22,204

Index Scan using idx_ap_headers on ap_headers ah (cost=0.42..0.74 rows=1 width=28) (actual time=0.004..0.004 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 = 10,276) AND (ad.gl_transaction_type_id = gl_transaction_type_id) AND (ad.post_month = post_month))
  • Rows Removed by Filter: 0
21. 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 = 10,276) AND (id = ah.ap_payee_id))
22. 35.214 35.214 ↑ 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.003..0.003 rows=1 loops=11,738)

  • Index Cond: (id = ad_payments.ap_header_id)
  • Filter: ((ap_header_sub_type_id <> 15) AND (cid = 10,276))
  • Rows Removed by Filter: 0
23. 35.214 35.214 ↓ 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.003..0.003 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 = 10,276))
  • Rows Removed by Filter: 1
24. 9.020 9.020 ↑ 1.0 1 2,255

Index Scan using idx_ap_payee_locations_id on ap_payee_locations apl (cost=0.29..0.71 rows=1 width=27) (actual time=0.004..0.004 rows=1 loops=2,255)

  • Index Cond: (id = ah.ap_payee_location_id)
  • Filter: ((cid = 10,276) AND (ah.ap_payee_id = ap_payee_id))
25. 6.765 6.765 ↑ 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.003..0.003 rows=1 loops=2,255)

  • Index Cond: (id = ad.bank_account_id)
  • Filter: (cid = 10,276)
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 = 10,276)
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 = 10,276) AND (id = p.owner_id))
  • Filter: (id = ANY ('{29812,29810,29809,29811}'::integer[]))
28. 0.001 2.882 ↓ 0.0 0 1

Subquery Scan on "*SELECT* 2" (cost=16.30..16.36 rows=1 width=674) (actual time=2.882..2.882 rows=0 loops=1)

29. 0.001 2.881 ↓ 0.0 0 1

GroupAggregate (cost=16.30..16.35 rows=1 width=654) (actual time=2.881..2.881 rows=0 loops=1)

  • Group Key: ap1p.cid, ap1p.property_id, ap1p.ap_payee_id, ap1p.ap_legal_entity_id, p_1.property_name, o_1.cid, o_1.id
30. 0.011 2.880 ↓ 0.0 0 1

Sort (cost=16.30..16.31 rows=1 width=324) (actual time=2.879..2.880 rows=0 loops=1)

  • Sort Key: ap1p.property_id, ap1p.ap_payee_id, ap1p.ap_legal_entity_id, p_1.property_name, o_1.id
  • Sort Method: quicksort Memory: 25kB
31. 0.001 2.869 ↓ 0.0 0 1

Nested Loop (cost=1.39..16.29 rows=1 width=324) (actual time=2.868..2.869 rows=0 loops=1)

32. 0.001 2.868 ↓ 0.0 0 1

Nested Loop (cost=1.10..12.18 rows=1 width=43) (actual time=2.867..2.868 rows=0 loops=1)

  • Join Filter: (lp_1.property_id = p_1.id)
33. 0.001 2.867 ↓ 0.0 0 1

Nested Loop (cost=0.81..9.86 rows=1 width=24) (actual time=2.866..2.867 rows=0 loops=1)

34. 0.007 2.866 ↓ 0.0 0 1

Nested Loop (cost=0.52..7.48 rows=1 width=24) (actual time=2.865..2.866 rows=0 loops=1)

  • Join Filter: (ap1p.property_id = lp_1.property_id)
35. 2.835 2.835 ↓ 4.0 4 1

Function Scan on load_properties lp_1 (cost=0.25..1.00 rows=1 width=4) (actual time=2.833..2.835 rows=4 loops=1)

  • Filter: ((is_disabled = 0) AND (is_test = 0))
36. 0.024 0.024 ↓ 0.0 0 4

Index Scan using idx_ap_payee_1099_adjustments_ap_legal_entity_id on ap_payee_1099_adjustments ap1p (cost=0.27..6.45 rows=2 width=20) (actual time=0.006..0.006 rows=0 loops=4)

  • Index Cond: (cid = 10,276)
  • Filter: ((ap_payee_id IS NOT NULL) AND (year >= '2019-01-01'::date) AND (year < '2020-01-01 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 8
37. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_ap_legal_entities on ap_legal_entities al (cost=0.29..2.31 rows=1 width=8) (never executed)

  • Index Cond: ((cid = 10,276) AND (id = ap1p.ap_legal_entity_id))
  • Filter: receives_1099
38. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_properties_id on properties p_1 (cost=0.29..2.31 rows=1 width=27) (never executed)

  • Index Cond: (id = ap1p.property_id)
  • Filter: (cid = 10,276)
39. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_owners on owners o_1 (cost=0.29..2.20 rows=1 width=289) (never executed)

  • Index Cond: ((cid = 10,276) AND (id = p_1.owner_id))
  • Filter: (id = ANY ('{29812,29810,29809,29811}'::integer[]))