explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6ijw

Settings
# exclusive inclusive rows x rows loops node
1. 31.844 6,800.643 ↓ 0.0 0 1

Nested Loop (cost=636,492.70..807,135.75 rows=273 width=88) (actual time=6,800.642..6,800.643 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. 189.003 1,177.487 ↑ 19.0 10 1

Group (cost=317,964.79..318,184.77 rows=190 width=8) (actual time=813.888..1,177.487 rows=10 loops=1)

  • Group Key: ad.ap_gl_account_id, ad.cid
4. 401.338 988.484 ↓ 16.0 468,461 1

Sort (cost=317,964.79..318,038.12 rows=29,330 width=8) (actual time=813.885..988.484 rows=468,461 loops=1)

  • Sort Key: ad.ap_gl_account_id, ad.cid
  • Sort Method: quicksort Memory: 34248kB
5. 552.874 587.146 ↓ 16.0 468,461 1

Bitmap Heap Scan on ap_details ad (cost=11,906.86..315,788.49 rows=29,330 width=8) (actual time=40.218..587.146 rows=468,461 loops=1)

  • Recheck Cond: (cid = ANY ('{3190,10292,14562,10348,4785,2547,12235,11335,15024,7007}'::integer[]))
  • Filter: ((ap_gl_account_id IS NOT NULL) AND (deleted_by IS NULL) AND (deleted_on IS NULL))
  • Rows Removed by Filter: 178066
  • Heap Blocks: exact=36262
6. 34.272 34.272 ↑ 1.0 646,527 1

Bitmap Index Scan on idx_ap_details_cid_post_month (cost=0.00..11,899.52 rows=654,295 width=0) (actual time=34.272..34.272 rows=646,527 loops=1)

  • Index Cond: (cid = ANY ('{3190,10292,14562,10348,4785,2547,12235,11335,15024,7007}'::integer[]))
7. 279.854 6,708.781 ↓ 72.7 20,006 1

Nested Loop (cost=318,307.50..487,879.56 rows=275 width=104) (actual time=1,272.557..6,708.781 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
8. 237.982 3,409.205 ↓ 753.2 207,137 1

Nested Loop (cost=16.16..168,509.68 rows=275 width=106) (actual time=39.717..3,409.205 rows=207,137 loops=1)

9. 527.491 2,549.812 ↓ 753.2 207,137 1

Nested Loop (cost=15.73..167,094.63 rows=275 width=82) (actual time=39.707..2,549.812 rows=207,137 loops=1)

10. 343.810 780.405 ↓ 33.8 413,972 1

Nested Loop (cost=15.30..119,546.39 rows=12,254 width=20) (actual time=35.782..780.405 rows=413,972 loops=1)

11. 34.835 34.835 ↓ 1.9 96 1

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

12. 369.600 401.760 ↓ 6.7 4,312 96

Bitmap Heap Scan on ap_details ad_credit (cost=15.05..2,384.46 rows=645 width=20) (actual time=0.535..4.185 rows=4,312 loops=96)

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

Index Scan using idx_ap_allocations_credit_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: (credit_ap_detail_id = ad_credit.id)
  • Filter: ((NOT is_initial_import) AND (ad_credit.cid = cid))
  • Rows Removed by Filter: 0
15. 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
16. 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
17.          

SubPlan (forNested Loop)

18. 194.913 1,220.808 ↑ 210.4 19 1

Group (cost=317,964.79..318,268.09 rows=3,997 width=16) (actual time=850.893..1,220.808 rows=19 loops=1)

  • Group Key: ad_1.ap_gl_account_id, ad_1.inter_co_ap_gl_account_id, ad_1.ap_transaction_type_id
19. 440.824 1,025.895 ↓ 16.0 468,461 1

Sort (cost=317,964.79..318,038.12 rows=29,330 width=12) (actual time=850.888..1,025.895 rows=468,461 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: 34248kB
20. 546.624 585.071 ↓ 16.0 468,461 1

Bitmap Heap Scan on ap_details ad_1 (cost=11,906.86..315,788.49 rows=29,330 width=12) (actual time=44.506..585.071 rows=468,461 loops=1)

  • Recheck Cond: (cid = ANY ('{3190,10292,14562,10348,4785,2547,12235,11335,15024,7007}'::integer[]))
  • Filter: ((ap_gl_account_id IS NOT NULL) AND (deleted_by IS NULL) AND (deleted_on IS NULL))
  • Rows Removed by Filter: 178066
  • Heap Blocks: exact=36262
21. 38.447 38.447 ↑ 1.0 646,527 1

Bitmap Index Scan on idx_ap_details_cid_post_month (cost=0.00..11,899.52 rows=654,295 width=0) (actual time=38.446..38.447 rows=646,527 loops=1)

  • Index Cond: (cid = ANY ('{3190,10292,14562,10348,4785,2547,12235,11335,15024,7007}'::integer[]))
22. 0.000 0.000 ↓ 0.0 0

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

23. 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)

24. 1,177.503 1,177.503 ↑ 19.0 10 1

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

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