explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CHp1

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

Sort (cost=10,958.98..10,959.54 rows=224 width=239) (actual time=4,162.632..4,184.325 rows=56,041 loops=1)

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

Group (cost=10,946.31..10,950.23 rows=224 width=239) (actual time=4,025.261..4,114.663 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. 82.727 4,048.872 ↓ 250.2 56,041 1

Sort (cost=10,946.31..10,946.87 rows=224 width=235) (actual time=4,025.258..4,048.872 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.380 3,966.145 ↓ 250.2 56,041 1

Nested Loop (cost=37.99..10,937.57 rows=224 width=235) (actual time=6.099..3,966.145 rows=56,041 loops=1)

5. 137.661 291.180 ↓ 269.3 60,315 1

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

  • Join Filter: (gh.gl_book_id = gb.id)
  • Rows Removed by Join Filter: 120630
6. 93.204 93.204 ↓ 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.024..93.204 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.309 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.006 0.006 ↑ 1.3 3 1

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

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

Aggregate (cost=37.56..37.57 rows=1 width=96) (actual time=0.059..0.059 rows=1 loops=60,315)

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

Nested Loop (cost=1.60..37.54 rows=1 width=38) (actual time=0.010..0.050 rows=9 loops=60,315)

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

Hash Semi Join (cost=1.32..29.23 rows=1 width=25) (actual time=0.007..0.023 rows=9 loops=60,315)

  • Hash Cond: (gd.property_id = lp.property_id)
12. 844.410 844.410 ↓ 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.004..0.014 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.205 6.000 ↓ 455.0 455 1

Hash (cost=0.88..0.88 rows=1 width=8) (actual time=6.000..6.000 rows=455 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
14. 5.795 5.795 ↓ 455.0 455 1

Function Scan on load_properties lp (cost=0.25..0.88 rows=1 width=8) (actual time=5.594..5.795 rows=455 loops=1)

  • Filter: (cid = 2603)
15. 1,045.680 1,045.680 ↑ 1.0 1 522,840

Index Scan using idx_properties_id on properties p (cost=0.28..8.30 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))