explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ge6D

Settings
# exclusive inclusive rows x rows loops node
1. 71.014 4,324.384 ↓ 250.2 56,041 1

Sort (cost=9,867.54..9,868.10 rows=224 width=239) (actual time=4,302.954..4,324.384 rows=56,041 loops=1)

  • Sort Key: gh.header_number DESC
  • Sort Method: quicksort Memory: 15781kB
2. 67.886 4,253.370 ↓ 250.2 56,041 1

Group (cost=9,854.87..9,858.79 rows=224 width=239) (actual time=4,161.241..4,253.370 rows=56,041 loops=1)

  • Group Key: gh.id, gh.cid, (sum(gd.amount)), (CASE WHEN (min((p.property_name)::text) <> max((p.property_name)::text)) THEN 'Multiple'::text ELSE max((p.property_name)::text) END), (max(CASE WHEN ((gd.accrual_gl_account_id IS NOT NULL) AND (gd.cash_gl_account_id IS NOT NULL)) THEN 'Both'::text WHEN (gd.accrual_gl_account_id IS NOT NULL) THEN 'Accrual'::text WHEN (gd.accrual_gl_account_id IS NULL) THEN 'Cash'::text ELSE NULL::text END)), gb.name
3. 85.542 4,185.484 ↓ 250.2 56,041 1

Sort (cost=9,854.87..9,855.43 rows=224 width=235) (actual time=4,161.238..4,185.484 rows=56,041 loops=1)

  • Sort Key: gh.id, (sum(gd.amount)), (CASE WHEN (min((p.property_name)::text) <> max((p.property_name)::text)) THEN 'Multiple'::text ELSE max((p.property_name)::text) END), (max(CASE WHEN ((gd.accrual_gl_account_id IS NOT NULL) AND (gd.cash_gl_account_id IS NOT NULL)) THEN 'Both'::text WHEN (gd.accrual_gl_account_id IS NOT NULL) THEN 'Accrual'::text WHEN (gd.accrual_gl_account_id IS NULL) THEN 'Cash'::text ELSE NULL::text END)), gb.name
  • Sort Method: quicksort Memory: 15462kB
4. 116.969 4,099.942 ↓ 250.2 56,041 1

Nested Loop (cost=33.12..9,846.13 rows=224 width=235) (actual time=4.729..4,099.942 rows=56,041 loops=1)

5. 142.692 303.758 ↓ 269.3 60,315 1

Nested Loop (cost=0.43..2,517.74 rows=224 width=143) (actual time=0.040..303.758 rows=60,315 loops=1)

  • Join Filter: (gh.gl_book_id = gb.id)
  • Rows Removed by Join Filter: 120630
6. 100.751 100.751 ↓ 134.6 60,315 1

Index Scan using idx_gl_headers_cid_gl_transaction_type_id_post_month_reference_ on gl_headers gh (cost=0.43..2,491.76 rows=448 width=135) (actual time=0.025..100.751 rows=60,315 loops=1)

  • Index Cond: ((cid = 2603) AND (gl_transaction_type_id = 1) AND (reference_id IS NULL))
  • Filter: ((is_template IS FALSE) AND (gl_header_status_type_id = ANY ('{2,1,5,3}'::integer[])))
  • Rows Removed by Filter: 2388
7. 60.308 60.315 ↑ 1.3 3 60,315

Materialize (cost=0.00..1.12 rows=4 width=16) (actual time=0.000..0.001 rows=3 loops=60,315)

8. 0.007 0.007 ↑ 1.3 3 1

Seq Scan on gl_books gb (cost=0.00..1.10 rows=4 width=16) (actual time=0.006..0.007 rows=3 loops=1)

  • Filter: (cid = 2603)
  • Rows Removed by Filter: 3
9. 603.150 3,679.215 ↑ 1.0 1 60,315

Aggregate (cost=32.68..32.70 rows=1 width=96) (actual time=0.061..0.061 rows=1 loops=60,315)

  • Filter: (sum(gd.amount) IS NOT NULL)
  • Rows Removed by Filter: 0
10. 582.825 3,076.065 ↓ 9.0 9 60,315

Nested Loop (cost=2.09..32.66 rows=1 width=38) (actual time=0.010..0.051 rows=9 loops=60,315)

  • Join Filter: (gd.property_id = p.id)
11. 538.210 1,447.560 ↓ 9.0 9 60,315

Hash Semi Join (cost=1.81..29.71 rows=1 width=25) (actual time=0.007..0.024 rows=9 loops=60,315)

  • Hash Cond: (gd.property_id = lp.property_id)
12. 904.725 904.725 ↓ 5.0 10 60,315

Index Scan using idx_gl_details_gl_header_id on gl_details gd (cost=0.44..28.33 rows=2 width=21) (actual time=0.005..0.015 rows=10 loops=60,315)

  • Index Cond: (gh.id = gl_header_id)
  • Filter: ((amount >= '0'::numeric) AND (cid = 2603) AND (gl_transaction_type_id = gh.gl_transaction_type_id))
  • Rows Removed by Filter: 6
13. 0.203 4.625 ↓ 9.1 455 1

Hash (cost=0.75..0.75 rows=50 width=4) (actual time=4.625..4.625 rows=455 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
14. 4.422 4.422 ↓ 9.1 455 1

Function Scan on load_properties lp (cost=0.25..0.75 rows=50 width=4) (actual time=4.219..4.422 rows=455 loops=1)

15. 1,045.680 1,045.680 ↑ 1.0 1 522,840

Index Scan using idx_properties_id on properties p (cost=0.28..2.94 rows=1 width=33) (actual time=0.002..0.002 rows=1 loops=522,840)

  • Index Cond: (id = lp.property_id)
  • Filter: ((cid = 2603) AND (is_disabled = 0))