explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JstQ

Settings
# exclusive inclusive rows x rows loops node
1. 1.136 68,909.584 ↓ 2,689.0 2,689 1

Subquery Scan on trial_balance (cost=100.09..100.43 rows=1 width=173) (actual time=68,809.259..68,909.584 rows=2,689 loops=1)

2. 90.783 68,908.448 ↓ 2,689.0 2,689 1

GroupAggregate (cost=100.09..100.42 rows=1 width=416) (actual time=68,809.258..68,908.448 rows=2,689 loops=1)

  • Group Key: p.property_name, gat.formatted_account_number, p.id, gat.grouping_gl_account_id, gat.name, gat.details, gat.hide_account_balance_from_reports
  • Filter: CASE WHEN (max(gat.disabled_by) IS NOT NULL) THEN ((COALESCE(sum(tbr_data.current_amount_debits), '0'::numeric) <> '0'::numeric) OR (COALESCE(sum(tbr_data.current_amount_credits), '0'::numeric) <> '0'::numeric) OR (sum(COALESCE(tbr_data.opening_balance, '0'::numeric)) <> '0'::numeric)) ELSE ((COALESCE(sum(tbr_data.current_amount_debits), '0'::numeric) <> '0'::numeric) OR (COALESCE(sum(tbr_data.current_amount_credits), '0'::numeric) <> '0'::numeric) OR (sum(COALESCE(tbr_data.opening_balance, '0'::numeric)) <> '0'::numeric)) END
  • Rows Removed by Filter: 36887
3. 254.222 68,817.665 ↓ 40,179.0 40,179 1

Sort (cost=100.09..100.09 rows=1 width=384) (actual time=68,809.120..68,817.665 rows=40,179 loops=1)

  • Sort Key: p.property_name, gat.formatted_account_number, p.id, gat.grouping_gl_account_id, gat.name, gat.details
  • Sort Method: quicksort Memory: 7522kB
4. 34,089.170 68,563.443 ↓ 40,179.0 40,179 1

Nested Loop Left Join (cost=0.95..100.08 rows=1 width=384) (actual time=22.521..68,563.443 rows=40,179 loops=1)

  • Join Filter: ((tbr_data.cid = p.cid) AND (tbr_data.property_id = p.id) AND (tbr_data.gl_account_id = gat.gl_account_id))
  • Rows Removed by Join Filter: 154223775
5. 62.877 241.033 ↓ 39,576.0 39,576 1

Nested Loop (cost=0.95..18.03 rows=1 width=296) (actual time=20.820..241.033 rows=39,576 loops=1)

6. 18.154 59.428 ↓ 39,576.0 39,576 1

Nested Loop (cost=0.67..9.45 rows=1 width=277) (actual time=20.812..59.428 rows=39,576 loops=1)

7. 4.026 4.026 ↓ 1,164.0 1,164 1

Index Scan using uk_gl_account_trees_gl_account_id on gl_account_trees gat (cost=0.42..8.44 rows=1 width=273) (actual time=0.025..4.026 rows=1,164 loops=1)

  • Index Cond: ((cid = 224) AND (gl_tree_id = 232))
  • Filter: (hide_account_balance_from_reports = 0)
8. 37.248 37.248 ↓ 34.0 34 1,164

Function Scan on load_properties load_prop (cost=0.25..1.00 rows=1 width=4) (actual time=0.018..0.032 rows=34 loops=1,164)

  • Filter: ((is_disabled = 0) AND (is_test = 0))
9. 118.728 118.728 ↑ 1.0 1 39,576

Index Scan using idx_properties_id on properties p (cost=0.28..8.30 rows=1 width=27) (actual time=0.003..0.003 rows=1 loops=39,576)

  • Index Cond: (id = load_prop.property_id)
  • Filter: (cid = 224)
10. 34,233.240 34,233.240 ↓ 205.1 3,897 39,576

Seq Scan on temp_tbal_data tbr_data (cost=0.00..81.71 rows=19 width=120) (actual time=0.003..0.865 rows=3,897 loops=39,576)