explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZRp6

Settings
# exclusive inclusive rows x rows loops node
1. 68.947 1,367.547 ↓ 269.3 60,315 1

Sort (cost=10,815.00..10,815.56 rows=224 width=239) (actual time=1,344.621..1,367.547 rows=60,315 loops=1)

  • Sort Key: gh.header_number DESC
  • Sort Method: quicksort Memory: 11500kB
2. 64.309 1,298.600 ↓ 269.3 60,315 1

Group (cost=10,802.34..10,806.26 rows=224 width=239) (actual time=1,209.817..1,298.600 rows=60,315 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. 79.639 1,234.291 ↓ 269.3 60,315 1

Sort (cost=10,802.34..10,802.90 rows=224 width=235) (actual time=1,209.814..1,234.291 rows=60,315 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: 11353kB
4. 90.835 1,154.652 ↓ 269.3 60,315 1

Nested Loop (cost=37.35..10,793.59 rows=224 width=235) (actual time=4.530..1,154.652 rows=60,315 loops=1)

5. 136.999 279.722 ↓ 269.3 60,315 1

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

  • Join Filter: (gh.gl_book_id = gb.id)
  • Rows Removed by Join Filter: 120630
6. 82.408 82.408 ↓ 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..82.408 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.004..0.006 rows=3 loops=1)

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

Aggregate (cost=36.91..36.93 rows=1 width=96) (actual time=0.012..0.013 rows=1 loops=60,315)

10.          

Initplan (forAggregate)

11. 4.469 4.469 ↑ 50.0 1 1

Function Scan on load_properties (cost=0.25..0.75 rows=50 width=0) (actual time=4.469..4.469 rows=1 loops=1)

12. 60.315 663.465 ↓ 0.0 0 60,315

Result (cost=0.71..36.64 rows=1 width=38) (actual time=0.011..0.011 rows=0 loops=60,315)

  • One-Time Filter: $0
13. 0.000 603.150 ↓ 0.0 0 60,315

Nested Loop (cost=0.71..36.64 rows=1 width=38) (actual time=0.010..0.010 rows=0 loops=60,315)

14. 603.150 603.150 ↓ 0.0 0 60,315

Index Scan using idx_gl_details_gl_header_id on gl_details gd (cost=0.44..28.33 rows=1 width=21) (actual time=0.010..0.010 rows=0 loops=60,315)

  • Index Cond: (gh.id = gl_header_id)
  • Filter: ((cid = 2603) AND (gl_transaction_type_id = gh.gl_transaction_type_id) AND (amount = '0'::numeric))
  • Rows Removed by Filter: 15
15. 0.218 0.218 ↑ 1.0 1 109

Index Scan using idx_properties_id on properties p (cost=0.28..8.29 rows=1 width=33) (actual time=0.002..0.002 rows=1 loops=109)

  • Index Cond: (id = gd.property_id)
  • Filter: (cid = 2603)