explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QlUO

Settings
# exclusive inclusive rows x rows loops node
1. 0.478 11,023.259 ↓ 273.0 1,092 1

Subquery Scan on trial_balance (cost=74.22..75.76 rows=4 width=298) (actual time=10,979.895..11,023.259 rows=1,092 loops=1)

2. 41.626 11,022.781 ↓ 273.0 1,092 1

GroupAggregate (cost=74.22..75.72 rows=4 width=550) (actual time=10,979.894..11,022.781 rows=1,092 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: 12852
3. 88.558 10,981.155 ↓ 3,605.5 14,422 1

Sort (cost=74.22..74.23 rows=4 width=499) (actual time=10,978.511..10,981.155 rows=14,422 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: 2603kB
4. 7,520.991 10,892.597 ↓ 3,605.5 14,422 1

Nested Loop Left Join (cost=26.04..74.18 rows=4 width=499) (actual time=2.146..10,892.597 rows=14,422 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: 25097411
5. 7.851 25.046 ↓ 3,486.0 13,944 1

Nested Loop (cost=26.04..45.50 rows=4 width=394) (actual time=0.162..25.046 rows=13,944 loops=1)

6. 3.251 3.251 ↓ 1,162.0 1,162 1

Index Scan using uk_gl_account_trees_gl_account_id on gl_account_trees gat (cost=0.42..8.44 rows=1 width=272) (actual time=0.026..3.251 rows=1,162 loops=1)

  • Index Cond: ((cid = 224) AND (gl_tree_id = 232))
  • Filter: (hide_account_balance_from_reports = 0)
7. 10.340 13.944 ↓ 3.0 12 1,162

Hash Join (cost=25.62..37.02 rows=4 width=126) (actual time=0.002..0.012 rows=12 loops=1,162)

  • Hash Cond: (load_prop.property_id = p.id)
8. 3.486 3.486 ↑ 9.2 12 1,162

Seq Scan on load_prop (cost=0.00..11.10 rows=110 width=4) (actual time=0.001..0.003 rows=12 loops=1,162)

9. 0.019 0.118 ↓ 6.0 42 1

Hash (cost=25.53..25.53 rows=7 width=126) (actual time=0.118..0.118 rows=42 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
10. 0.078 0.099 ↓ 6.0 42 1

Bitmap Heap Scan on properties p (cost=4.33..25.53 rows=7 width=126) (actual time=0.035..0.099 rows=42 loops=1)

  • Recheck Cond: (cid = 224)
  • Heap Blocks: exact=25
11. 0.021 0.021 ↓ 18.4 129 1

Bitmap Index Scan on idx_properties_remote_primary_key (cost=0.00..4.33 rows=7 width=0) (actual time=0.020..0.021 rows=129 loops=1)

  • Index Cond: (cid = 224)
12. 3,345.834 3,346.560 ↓ 360.0 1,800 13,944

Materialize (cost=0.00..28.29 rows=5 width=125) (actual time=0.000..0.240 rows=1,800 loops=13,944)

13. 0.726 0.726 ↓ 360.0 1,800 1

Seq Scan on temp_tbal_data tbr_data (cost=0.00..28.26 rows=5 width=125) (actual time=0.007..0.726 rows=1,800 loops=1)

  • Filter: (cid = 224)
Planning time : 2.489 ms