explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gExj

Settings
# exclusive inclusive rows x rows loops node
1. 19.344 660,009.747 ↓ 168.0 168 1

Nested Loop (cost=30,087.14..56,461.85 rows=1 width=4) (actual time=11,363.257..660,009.747 rows=168 loops=1)

  • Join Filter: (gd.accrual_gl_account_id = ad.ap_gl_account_id)
  • Rows Removed by Join Filter: 40536
2. 3.359 7,061.458 ↓ 1,009.0 1,009 1

Nested Loop (cost=2.40..26,357.71 rows=1 width=8) (actual time=19.859..7,061.458 rows=1,009 loops=1)

  • Join Filter: (COALESCE(ad_credit.gl_transaction_type_id, 0) <> COALESCE(ad_charge.gl_transaction_type_id, '-1'::integer))
  • Rows Removed by Join Filter: 1
3. 4.357 7,051.029 ↓ 1,010.0 1,010 1

Nested Loop (cost=1.98..26,355.06 rows=1 width=32) (actual time=19.853..7,051.029 rows=1,010 loops=1)

4. 865.859 7,036.572 ↓ 1,010.0 1,010 1

Nested Loop (cost=1.55..26,352.41 rows=1 width=28) (actual time=19.844..7,036.572 rows=1,010 loops=1)

  • Join Filter: ((aa.cid = gd.cid) AND (((gd.amount > '0'::numeric) AND (gd.accrual_gl_account_id <> aa.accrual_debit_gl_account_id)) OR ((gd.amount < '0'::numeric) AND (gd.accrual_gl_account_id <> aa.accrual_credit_gl_account_id))) AND ((aa.origin_ap_allocation_id IS NOT NULL) OR ((gd.amount > '0'::numeric) AND (gd.accrual_gl_account_id <> aa.accrual_debit_gl_account_id)) OR ((gd.amount < '0'::numeric) AND (gd.accrual_gl_account_id <> aa.accrual_credit_gl_account_id))))
  • Rows Removed by Join Filter: 980852
5. 758.658 3,718.343 ↓ 32,698.3 490,474 1

Nested Loop (cost=1.11..26,289.51 rows=15 width=40) (actual time=11.490..3,718.343 rows=490,474 loops=1)

6. 396.889 943.993 ↓ 243.2 503,923 1

Nested Loop (cost=0.68..18,824.53 rows=2,072 width=36) (actual time=11.479..943.993 rows=503,923 loops=1)

7. 11.676 11.676 ↓ 5.6 278 1

Function Scan on load_properties lp (cost=0.25..0.75 rows=50 width=8) (actual time=11.462..11.676 rows=278 loops=1)

8. 535.428 535.428 ↓ 13.1 1,813 278

Index Scan using idx_ap_allocations_cid_gl_transaction_type_id_property_id_id on ap_allocations aa (cost=0.42..375.10 rows=138 width=36) (actual time=0.012..1.926 rows=1,813 loops=278)

  • Index Cond: ((cid = lp.cid) AND (gl_transaction_type_id = 4) AND (property_id = lp.property_id))
  • Filter: ((NOT is_initial_import) AND is_posted)
  • Rows Removed by Filter: 0
9. 2,015.692 2,015.692 ↑ 1.0 1 503,923

Index Scan using idx_gl_headers_cid_reference_id on gl_headers gh (cost=0.43..3.59 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=503,923)

  • Index Cond: ((cid = aa.cid) AND (reference_id = aa.id))
  • Filter: (gl_transaction_type_id = 4)
  • Rows Removed by Filter: 0
10. 2,452.370 2,452.370 ↓ 2.0 2 490,474

Index Scan using idx_gl_details_cid_gl_header_id on gl_details gd (cost=0.44..4.16 rows=1 width=21) (actual time=0.003..0.005 rows=2 loops=490,474)

  • Index Cond: ((cid = gh.cid) AND (gl_header_id = gh.id))
  • Filter: ((gl_transaction_type_id = 4) AND ((amount > '0'::numeric) OR (amount < '0'::numeric)))
11. 10.100 10.100 ↑ 1.0 1 1,010

Index Scan using idx_ap_details on ap_details ad_credit (cost=0.43..2.64 rows=1 width=12) (actual time=0.010..0.010 rows=1 loops=1,010)

  • Index Cond: (id = aa.credit_ap_detail_id)
  • Filter: (aa.cid = cid)
  • Rows Removed by Filter: 1
12. 7.070 7.070 ↑ 1.0 1 1,010

Index Scan using idx_ap_details on ap_details ad_charge (cost=0.43..2.64 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=1,010)

  • Index Cond: (id = aa.charge_ap_detail_id)
  • Filter: (aa.cid = cid)
  • Rows Removed by Filter: 0
13. 329,255.871 652,928.945 ↓ 1.7 40 1,009

Group (cost=30,084.74..30,103.62 rows=23 width=4) (actual time=2.515..647.105 rows=40 loops=1,009)

  • Group Key: ad.ap_gl_account_id
14. 321,613.500 323,673.074 ↓ 223.0 842,385 1,009

Sort (cost=30,084.74..30,094.18 rows=3,777 width=4) (actual time=2.514..320.786 rows=842,385 loops=1,009)

  • Sort Key: ad.ap_gl_account_id
  • Sort Method: quicksort Memory: 70575kB
15. 726.994 2,059.574 ↓ 259.8 981,300 1

Nested Loop (cost=0.68..29,860.33 rows=3,777 width=4) (actual time=8.023..2,059.574 rows=981,300 loops=1)

16. 8.188 8.188 ↓ 5.6 278 1

Function Scan on load_properties lp_1 (cost=0.25..0.75 rows=50 width=8) (actual time=8.007..8.188 rows=278 loops=1)

17. 1,324.392 1,324.392 ↓ 17.7 3,530 278

Index Scan using idx_ap_details_cid_property_id on ap_details ad (cost=0.43..595.20 rows=199 width=12) (actual time=0.010..4.764 rows=3,530 loops=278)

  • Index 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: 97