explain.depesz.com

PostgreSQL's explain analyze made readable

Result: f5RGY

Settings
# exclusive inclusive rows x rows loops node
1. 34.079 7,522.419 ↓ 0.0 0 1

Nested Loop (cost=238,553.54..409,276.52 rows=273 width=88) (actual time=7,522.419..7,522.419 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.          

CTE ap_details_ad

3. 165.266 1,402.789 ↑ 23.8 8 1

Group (cost=119,255.67..119,259.78 rows=190 width=8) (actual time=1,085.986..1,402.789 rows=8 loops=1)

  • Group Key: ad.ap_gl_account_id, ad.cid
4. 349.203 1,237.523 ↓ 741.6 407,119 1

Sort (cost=119,255.67..119,257.04 rows=549 width=8) (actual time=1,085.983..1,237.523 rows=407,119 loops=1)

  • Sort Key: ad.ap_gl_account_id, ad.cid
  • Sort Method: quicksort Memory: 31372kB
5. 305.449 888.320 ↓ 741.6 407,119 1

Nested Loop (cost=15.14..119,230.69 rows=549 width=8) (actual time=6.375..888.320 rows=407,119 loops=1)

6. 5.431 5.431 ↓ 1.9 96 1

Function Scan on load_properties lp_z (cost=0.25..0.75 rows=50 width=8) (actual time=5.366..5.431 rows=96 loops=1)

7. 545.280 577.440 ↓ 146.2 4,241 96

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

  • Recheck Cond: ((cid = lp_z.cid) AND (property_id = lp_z.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
8. 32.160 32.160 ↓ 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.335..0.335 rows=4,312 loops=96)

  • Index Cond: ((cid = lp_z.cid) AND (property_id = lp_z.property_id))
9. 352.823 7,428.322 ↓ 72.7 20,006 1

Nested Loop (cost=119,293.32..288,945.32 rows=275 width=104) (actual time=2,910.400..7,428.322 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 2))) OR ((ah_credit.gl_transaction_type_id = 2) AND (aa.origin_ap_allocation_id IS NULL) AND (NOT (hashed SubPlan 3))) 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 4))) OR ((ah_credit.gl_transaction_type_id = 3) AND (NOT (hashed SubPlan 5)))))))
  • Rows Removed by Join Filter: 187131
10. 222.235 3,569.192 ↓ 753.2 207,137 1

Nested Loop (cost=16.16..168,589.62 rows=275 width=106) (actual time=14.244..3,569.192 rows=207,137 loops=1)

11. 630.212 2,725.546 ↓ 753.2 207,137 1

Nested Loop (cost=15.73..167,175.26 rows=275 width=98) (actual time=14.210..2,725.546 rows=207,137 loops=1)

12. 369.439 853.418 ↓ 33.8 413,972 1

Nested Loop (cost=15.30..119,627.01 rows=12,254 width=32) (actual time=13.065..853.418 rows=413,972 loops=1)

13. 12.043 12.043 ↓ 1.9 96 1

Function Scan on load_properties lp_y (cost=0.25..0.75 rows=50 width=8) (actual time=11.967..12.043 rows=96 loops=1)

14. 439.104 471.936 ↓ 6.7 4,312 96

Bitmap Heap Scan on ap_details ad_charge (cost=15.05..2,386.08 rows=645 width=32) (actual time=0.544..4.916 rows=4,312 loops=96)

  • Recheck Cond: ((cid = lp_y.cid) AND (property_id = lp_y.property_id))
  • Filter: (ap_transaction_type_id <> 2)
  • Heap Blocks: exact=152053
15. 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_y.cid) AND (property_id = lp_y.property_id))
16. 1,241.916 1,241.916 ↑ 1.0 1 413,972

Index Scan using idx_ap_allocations_charge_ap_detail_id on ap_allocations aa (cost=0.43..3.87 rows=1 width=70) (actual time=0.003..0.003 rows=1 loops=413,972)

  • Index Cond: (charge_ap_detail_id = ad_charge.id)
  • Filter: ((NOT is_initial_import) AND (ad_charge.cid = cid))
  • Rows Removed by Filter: 0
17. 621.411 621.411 ↑ 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.003..0.003 rows=1 loops=207,137)

  • Index Cond: (id = aa.credit_ap_detail_id)
  • Filter: (aa.cid = cid)
  • Rows Removed by Filter: 0
18. 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.003..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
19.          

SubPlan (forNested Loop)

20. 170.064 1,482.092 ↑ 36.6 15 1

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

  • Group Key: ad_1.ap_gl_account_id, ad_1.inter_co_ap_gl_account_id, ad_1.ap_transaction_type_id
21. 379.801 1,312.028 ↓ 741.6 407,119 1

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

  • Sort Key: ad_1.ap_gl_account_id, ad_1.inter_co_ap_gl_account_id, ad_1.ap_transaction_type_id
  • Sort Method: quicksort Memory: 31372kB
22. 308.266 932.227 ↓ 741.6 407,119 1

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

23. 5.049 5.049 ↓ 1.9 96 1

Function Scan on load_properties lp_x (cost=0.25..0.75 rows=50 width=8) (actual time=4.980..5.049 rows=96 loops=1)

24. 585.408 618.912 ↓ 146.2 4,241 96

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

  • Recheck Cond: ((cid = lp_x.cid) AND (property_id = lp_x.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
25. 33.504 33.504 ↓ 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.349..0.349 rows=4,312 loops=96)

  • Index Cond: ((cid = lp_x.cid) AND (property_id = lp_x.property_id))
26. 0.000 0.000 ↓ 0.0 0

CTE Scan on ap_details_ad (cost=0.00..3.80 rows=190 width=8) (never executed)

27. 0.000 0.000 ↓ 0.0 0

CTE Scan on ap_details_ad ap_details_ad_1 (cost=0.00..3.80 rows=190 width=8) (never executed)

28. 1,402.804 1,402.804 ↑ 23.8 8 1

CTE Scan on ap_details_ad ap_details_ad_2 (cost=0.00..3.80 rows=190 width=8) (actual time=1,085.990..1,402.804 rows=8 loops=1)

29. 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