explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XKDw

Settings
# exclusive inclusive rows x rows loops node
1. 82.199 12,792.368 ↓ 1,701.0 1,701 1

GroupAggregate (cost=288.46..288.52 rows=1 width=64) (actual time=12,694.438..12,792.368 rows=1,701 loops=1)

  • Group Key: gl_activity.cid, gl_activity.property_id, gl_activity.gl_account_id
  • Buffers: shared hit=913481 read=1302
2. 129.706 12,710.169 ↓ 276,375.0 276,375 1

Sort (cost=288.46..288.47 rows=1 width=37) (actual time=12,694.026..12,710.169 rows=276,375 loops=1)

  • Sort Key: gl_activity.property_id, gl_activity.gl_account_id
  • Sort Method: quicksort Memory: 33880kB
  • Buffers: shared hit=913481 read=1302
3. 42.183 12,580.463 ↓ 276,375.0 276,375 1

Nested Loop (cost=148.82..288.45 rows=1 width=37) (actual time=57.390..12,580.463 rows=276,375 loops=1)

  • Join Filter: (gl_activity.gl_account_id = gat.gl_account_id)
  • Buffers: shared hit=913478 read=1302
4. 2.134 16.603 ↓ 623.0 623 1

Nested Loop (cost=0.99..130.47 rows=1 width=20) (actual time=0.028..16.603 rows=623 loops=1)

  • Buffers: shared hit=4107 read=1
5. 2.463 10.108 ↓ 207.7 623 1

Nested Loop (cost=0.70..124.34 rows=3 width=20) (actual time=0.022..10.108 rows=623 loops=1)

  • Buffers: shared hit=2239
6. 2.038 2.038 ↓ 14.8 623 1

Index Scan using uk_gl_account_trees_gl_account_id on gl_account_trees gat (cost=0.42..43.68 rows=42 width=12) (actual time=0.013..2.038 rows=623 loops=1)

  • Index Cond: ((cid = 13576) AND (gl_tree_id = 2718))
  • Buffers: shared hit=362
7. 5.607 5.607 ↑ 1.0 1 623

Index Scan using idx_gl_accounts_id on gl_accounts ga (cost=0.29..1.92 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=623)

  • Index Cond: (id = gat.gl_account_id)
  • Filter: ((is_confidential IS FALSE) AND (cid = 13576))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1877
8. 4.361 4.361 ↑ 1.0 1 623

Index Scan using idx_gl_branches_id on gl_branches gb (cost=0.29..2.04 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=623)

  • Index Cond: (id = gat.gl_branch_id)
  • Filter: (cid = 13576)
  • Buffers: shared hit=1868 read=1
9. 47.971 12,521.677 ↓ 444.0 444 623

Nested Loop (cost=147.83..157.95 rows=1 width=33) (actual time=10.963..20.099 rows=444 loops=623)

  • Buffers: shared hit=909371 read=1301
10. 9.968 9.968 ↓ 7.0 7 623

Function Scan on load_properties load_prop (cost=0.25..2.00 rows=1 width=4) (actual time=0.011..0.016 rows=7 loops=623)

  • Filter: ((is_disabled = 0) AND (is_test = 0))
  • Buffers: shared hit=2495
11. 606.179 12,463.738 ↓ 63.0 63 4,361

Bitmap Heap Scan on cached_gl_account_periods gl_activity (cost=147.58..155.92 rows=1 width=33) (actual time=2.747..2.858 rows=63 loops=4,361)

  • Recheck Cond: ((gl_account_id = ga.id) AND (property_id = load_prop.property_id))
  • Filter: ((is_temporary IS FALSE) AND (post_month < '2019-06-01'::date) AND (cid = 13576) AND (gl_book_id = 937))
  • Rows Removed by Filter: 32
  • Heap Blocks: exact=224327
  • Buffers: shared hit=906876 read=1301
12. 300.398 11,857.559 ↓ 0.0 0 4,361

BitmapAnd (cost=147.58..147.58 rows=8 width=0) (actual time=2.719..2.719 rows=0 loops=4,361)

  • Buffers: shared hit=682549 read=1301
13. 981.225 981.225 ↓ 1.1 2,709 4,361

Bitmap Index Scan on idx_cached_gl_account_periods_gl_account_id (cost=0.00..25.29 rows=2,546 width=0) (actual time=0.225..0.225 rows=2,709 loops=4,361)

  • Index Cond: (gl_account_id = ga.id)
  • Buffers: shared hit=44062 read=1301
14. 10,575.936 10,575.936 ↓ 5.1 59,661 3,843

Bitmap Index Scan on idx_cached_gl_account_periods_property_id (cost=0.00..121.50 rows=11,743 width=0) (actual time=2.752..2.752 rows=59,661 loops=3,843)

  • Index Cond: (property_id = load_prop.property_id)
  • Buffers: shared hit=638487
Planning time : 3.760 ms
Execution time : 12,795.271 ms