explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7Xmx

Settings
# exclusive inclusive rows x rows loops node
1. 34.250 6,162.638 ↓ 0.0 0 1

Nested Loop (cost=477,048.67..535,333.73 rows=277 width=88) (actual time=6,162.638..6,162.638 rows=0 loops=1)

  • Join Filter: ((COALESCE(ah_credit.gl_transaction_type_id, 0) <> COALESCE(ah_charge.gl_transaction_type_id, '-1'::integer)) AND (aa.cid = ah_charge.cid))
  • Rows Removed by Join Filter: 19942
2. 301.801 6,068.370 ↓ 72.0 20,006 1

Nested Loop (cost=477,048.24..534,250.63 rows=278 width=104) (actual time=1,505.018..6,068.370 rows=20,006 loops=1)

  • Join Filter: ((aa.cid = ah_credit.cid) AND ((aa.accrual_debit_gl_account_id IS NULL) OR (aa.accrual_credit_gl_account_id IS NULL) OR (aa.cash_debit_gl_account_id IS NULL) OR (aa.cash_credit_gl_account_id IS NULL) OR (aa.accrual_debit_gl_account_id = aa.accrual_credit_gl_account_id) OR ((ah_credit.gl_transaction_type_id = 3) AND (aa.origin_ap_allocation_id IS NULL) AND (NOT (hashed SubPlan 1))) OR ((ah_credit.gl_transaction_type_id = 2) AND (aa.origin_ap_allocation_id IS NULL) AND (NOT (hashed SubPlan 2))) OR (aa.is_posted AND aa.is_deleted AND (aa.origin_ap_allocation_id IS NOT NULL) AND (((ah_credit.gl_transaction_type_id = 2) AND (NOT (hashed SubPlan 3))) OR ((ah_credit.gl_transaction_type_id = 3) AND (NOT (hashed SubPlan 4)))))))
  • Rows Removed by Join Filter: 187131
3. 236.095 2,119.963 ↓ 745.1 207,137 1

Nested Loop (cost=8.59..56,122.75 rows=278 width=106) (actual time=33.435..2,119.963 rows=207,137 loops=1)

4. 400.465 1,262.457 ↓ 745.1 207,137 1

Nested Loop (cost=8.16..54,692.27 rows=278 width=82) (actual time=33.428..1,262.457 rows=207,137 loops=1)

5. 180.857 447.718 ↓ 745.1 207,137 1

Nested Loop (cost=7.72..53,262.48 rows=278 width=74) (actual time=33.418..447.718 rows=207,137 loops=1)

6. 33.101 33.101 ↓ 1.9 96 1

Function Scan on load_properties lp (cost=0.25..0.75 rows=50 width=8) (actual time=33.032..33.101 rows=96 loops=1)

7. 219.168 233.760 ↓ 126.9 2,158 96

Bitmap Heap Scan on ap_allocations aa (cost=7.47..1,065.06 rows=17 width=70) (actual time=0.207..2.435 rows=2,158 loops=96)

  • Recheck Cond: ((cid = lp.cid) AND (property_id = lp.property_id))
  • Filter: (NOT is_initial_import)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=44411
8. 14.592 14.592 ↓ 7.1 2,158 96

Bitmap Index Scan on idx_ap_allocations_cid_property_id (cost=0.00..7.47 rows=304 width=0) (actual time=0.152..0.152 rows=2,158 loops=96)

  • Index Cond: ((cid = lp.cid) AND (property_id = lp.property_id))
9. 414.274 414.274 ↑ 1.0 1 207,137

Index Scan using idx_ap_details on ap_details ad_credit (cost=0.43..5.13 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=207,137)

  • Index Cond: (id = aa.credit_ap_detail_id)
  • Filter: (aa.cid = cid)
  • Rows Removed by Filter: 0
10. 621.411 621.411 ↑ 1.0 1 207,137

Index Scan using idx_ap_details on ap_details ad_charge (cost=0.43..5.14 rows=1 width=28) (actual time=0.003..0.003 rows=1 loops=207,137)

  • Index Cond: (id = aa.charge_ap_detail_id)
  • Filter: ((ap_transaction_type_id <> 2) AND (aa.cid = cid))
  • Rows Removed by Filter: 0
11. 621.411 621.411 ↑ 1.0 1 207,137

Index Scan using idx_ap_headers on ap_headers ah_credit (cost=0.43..3.88 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=207,137)

  • Index Cond: (id = ad_credit.ap_header_id)
  • Filter: (ad_credit.cid = cid)
  • Rows Removed by Filter: 0
12.          

SubPlan (forNested Loop)

13. 170.172 1,471.378 ↑ 36.6 15 1

Group (cost=119,255.67..119,262.53 rows=549 width=16) (actual time=1,149.570..1,471.378 rows=15 loops=1)

  • Group Key: ad.ap_gl_account_id, ad.inter_co_ap_gl_account_id, ad.ap_transaction_type_id
14. 384.890 1,301.206 ↓ 741.6 407,119 1

Sort (cost=119,255.67..119,257.04 rows=549 width=12) (actual time=1,149.567..1,301.206 rows=407,119 loops=1)

  • Sort Key: ad.ap_gl_account_id, ad.inter_co_ap_gl_account_id, ad.ap_transaction_type_id
  • Sort Method: quicksort Memory: 31372kB
15. 306.261 916.316 ↓ 741.6 407,119 1

Nested Loop (cost=15.14..119,230.69 rows=549 width=12) (actual time=30.119..916.316 rows=407,119 loops=1)

16. 29.159 29.159 ↓ 1.9 96 1

Function Scan on load_properties lp_1 (cost=0.25..0.75 rows=50 width=8) (actual time=29.101..29.159 rows=96 loops=1)

17. 548.640 580.896 ↓ 146.2 4,241 96

Bitmap Heap Scan on ap_details ad (cost=14.89..2,384.31 rows=29 width=20) (actual time=0.539..6.051 rows=4,241 loops=96)

  • Recheck Cond: ((cid = lp_1.cid) AND (property_id = lp_1.property_id))
  • Filter: ((ap_gl_account_id IS NOT NULL) AND (deleted_by IS NULL) AND (deleted_on IS NULL))
  • Rows Removed by Filter: 71
  • Heap Blocks: exact=152053
18. 32.256 32.256 ↓ 6.7 4,312 96

Bitmap Index Scan on idx_ap_details_cid_property_id (cost=0.00..14.88 rows=645 width=0) (actual time=0.336..0.336 rows=4,312 loops=96)

  • Index Cond: ((cid = lp_1.cid) AND (property_id = lp_1.property_id))
19. 0.000 0.000 ↓ 0.0 0

Group (cost=119,255.67..119,258.41 rows=10 width=4) (never executed)

  • Group Key: ad_1.ap_gl_account_id
20. 0.000 0.000 ↓ 0.0 0

Sort (cost=119,255.67..119,257.04 rows=549 width=4) (never executed)

  • Sort Key: ad_1.ap_gl_account_id
21. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=15.14..119,230.69 rows=549 width=4) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Function Scan on load_properties lp_2 (cost=0.25..0.75 rows=50 width=8) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on ap_details ad_1 (cost=14.89..2,384.31 rows=29 width=12) (never executed)

  • Recheck Cond: ((cid = lp_2.cid) AND (property_id = lp_2.property_id))
  • Filter: ((ap_gl_account_id IS NOT NULL) AND (deleted_by IS NULL) AND (deleted_on IS NULL))
24. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_ap_details_cid_property_id (cost=0.00..14.88 rows=645 width=0) (never executed)

  • Index Cond: ((cid = lp_2.cid) AND (property_id = lp_2.property_id))
25. 0.000 0.000 ↓ 0.0 0

Group (cost=119,255.67..119,258.41 rows=10 width=4) (never executed)

  • Group Key: ad_2.ap_gl_account_id
26. 0.000 0.000 ↓ 0.0 0

Sort (cost=119,255.67..119,257.04 rows=549 width=4) (never executed)

  • Sort Key: ad_2.ap_gl_account_id
27. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=15.14..119,230.69 rows=549 width=4) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Function Scan on load_properties lp_3 (cost=0.25..0.75 rows=50 width=8) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on ap_details ad_2 (cost=14.89..2,384.31 rows=29 width=12) (never executed)

  • Recheck Cond: ((cid = lp_3.cid) AND (property_id = lp_3.property_id))
  • Filter: ((ap_gl_account_id IS NOT NULL) AND (deleted_by IS NULL) AND (deleted_on IS NULL))
30. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_ap_details_cid_property_id (cost=0.00..14.88 rows=645 width=0) (never executed)

  • Index Cond: ((cid = lp_3.cid) AND (property_id = lp_3.property_id))
31. 158.274 1,553.817 ↑ 1.2 8 1

Group (cost=119,255.67..119,258.41 rows=10 width=4) (actual time=1,243.300..1,553.817 rows=8 loops=1)

  • Group Key: ad_3.ap_gl_account_id
32. 564.088 1,395.543 ↓ 741.9 407,324 1

Sort (cost=119,255.67..119,257.04 rows=549 width=4) (actual time=1,243.298..1,395.543 rows=407,324 loops=1)

  • Sort Key: ad_3.ap_gl_account_id
  • Sort Method: quicksort Memory: 31382kB
33. 302.872 831.455 ↓ 741.9 407,324 1

Nested Loop (cost=15.14..119,230.69 rows=549 width=4) (actual time=35.535..831.455 rows=407,324 loops=1)

34. 34.567 34.567 ↓ 1.9 96 1

Function Scan on load_properties lp_4 (cost=0.25..0.75 rows=50 width=8) (actual time=34.504..34.567 rows=96 loops=1)

35. 461.184 494.016 ↓ 146.3 4,243 96

Bitmap Heap Scan on ap_details ad_3 (cost=14.89..2,384.31 rows=29 width=12) (actual time=0.543..5.146 rows=4,243 loops=96)

  • Recheck Cond: ((cid = lp_4.cid) AND (property_id = lp_4.property_id))
  • Filter: (ap_gl_account_id IS NOT NULL)
  • Rows Removed by Filter: 69
  • Heap Blocks: exact=152053
36. 32.832 32.832 ↓ 6.7 4,312 96

Bitmap Index Scan on idx_ap_details_cid_property_id (cost=0.00..14.88 rows=645 width=0) (actual time=0.342..0.342 rows=4,312 loops=96)

  • Index Cond: ((cid = lp_4.cid) AND (property_id = lp_4.property_id))
37. 60.018 60.018 ↑ 1.0 1 20,006

Index Scan using idx_ap_headers on ap_headers ah_charge (cost=0.43..3.88 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=20,006)

  • Index Cond: (id = ad_charge.ap_header_id)
  • Filter: ((NOT is_initial_import) AND (ad_charge.cid = cid))
  • Rows Removed by Filter: 0