explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ypTD

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 24,801.708 ↓ 6.0 6 1

GroupAggregate (cost=326.14..326.19 rows=1 width=52) (actual time=24,801.703..24,801.708 rows=6 loops=1)

  • Group Key: pgs.cid, pgs.property_id, pgs.retained_earnings_gl_account_id
  • Buffers: shared hit=4,911,159 read=3,158
2. 0.029 24,801.696 ↓ 6.0 6 1

Sort (cost=326.14..326.15 rows=1 width=76) (actual time=24,801.696..24,801.696 rows=6 loops=1)

  • Sort Key: pgs.property_id, pgs.retained_earnings_gl_account_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=4,911,159 read=3,158
3. 0.018 24,801.667 ↓ 6.0 6 1

Nested Loop (cost=321.11..326.13 rows=1 width=76) (actual time=24,788.418..24,801.667 rows=6 loops=1)

  • Buffers: shared hit=4,911,156 read=3,158
4. 0.018 24,801.571 ↓ 6.0 6 1

Nested Loop (cost=320.69..324.62 rows=1 width=76) (actual time=24,788.360..24,801.571 rows=6 loops=1)

  • Join Filter: (load_prop.property_id = pgs.property_id)
  • Buffers: shared hit=4,911,138 read=3,157
5. 0.019 24,801.505 ↓ 6.0 6 1

Nested Loop (cost=320.40..322.26 rows=1 width=84) (actual time=24,788.345..24,801.505 rows=6 loops=1)

  • Join Filter: (load_prop.property_id = gl_activity.property_id)
  • Rows Removed by Join Filter: 36
  • Buffers: shared hit=4,911,120 read=3,157
6. 33.693 24,798.522 ↓ 6.0 6 1

GroupAggregate (cost=320.15..320.20 rows=1 width=80) (actual time=24,785.383..24,798.522 rows=6 loops=1)

  • Group Key: gl_activity.cid, gl_activity.property_id
  • Buffers: shared hit=4,910,515 read=3,157
7. 49.369 24,764.829 ↓ 132,011.0 132,011 1

Sort (cost=320.15..320.15 rows=1 width=25) (actual time=24,756.641..24,764.829 rows=132,011 loops=1)

  • Sort Key: gl_activity.property_id
  • Sort Method: quicksort Memory: 16,458kB
  • Buffers: shared hit=4,910,515 read=3,157
8. 113.155 24,715.460 ↓ 132,011.0 132,011 1

Nested Loop (cost=1.79..320.14 rows=1 width=25) (actual time=62.970..24,715.460 rows=132,011 loops=1)

  • Join Filter: (gat_1.gl_account_id = ga.id)
  • Buffers: shared hit=4,910,512 read=3,157
9. 20.509 24,470.294 ↓ 132,011.0 132,011 1

Nested Loop (cost=1.51..319.78 rows=1 width=37) (actual time=62.922..24,470.294 rows=132,011 loops=1)

  • Buffers: shared hit=4,514,388 read=3,157
10. 2.550 9.565 ↓ 510.0 510 1

Nested Loop (cost=0.70..129.46 rows=1 width=8) (actual time=0.031..9.565 rows=510 loops=1)

  • Buffers: shared hit=2,229 read=2
11. 2.031 2.031 ↓ 14.8 623 1

Index Scan using uk_gl_account_trees_gl_account_id on gl_account_trees gat_1 (cost=0.42..43.68 rows=42 width=12) (actual time=0.017..2.031 rows=623 loops=1)

  • Index Cond: ((cid = 13,576) AND (gl_tree_id = 2,718))
  • Buffers: shared hit=362
12. 4.984 4.984 ↑ 1.0 1 623

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

  • Index Cond: (id = gat_1.gl_branch_id)
  • Filter: ((gl_group_type_id = ANY ('{4,5}'::integer[])) AND (cid = 13,576))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1,867 read=2
13. 31.110 24,440.220 ↓ 259.0 259 510

Nested Loop (cost=0.81..190.30 rows=1 width=33) (actual time=29.664..47.922 rows=259 loops=510)

  • Buffers: shared hit=4,512,159 read=3,155
14. 15.300 15.300 ↓ 7.0 7 510

Function Scan on load_properties load_prop_1 (cost=0.25..2.00 rows=1 width=4) (actual time=0.019..0.030 rows=7 loops=510)

  • Filter: ((is_disabled = 0) AND (is_test = 0))
  • Buffers: shared hit=2,461
15. 24,393.810 24,393.810 ↓ 37.0 37 3,570

Index Scan using uk_cached_gl_account_periods_all_ids_post_month on cached_gl_account_periods gl_activity (cost=0.56..188.27 rows=1 width=33) (actual time=6.310..6.833 rows=37 loops=3,570)

  • Index Cond: ((cid = 13,576) AND (property_id = load_prop_1.property_id) AND (gl_account_id = gat_1.gl_account_id) AND (gl_book_id = 937) AND (post_month < '2019-01-01'::date) AND (is_temporary = false))
  • Filter: (is_temporary IS FALSE)
  • Buffers: shared hit=4,509,698 read=3,155
16. 132.011 132.011 ↑ 1.0 1 132,011

Index Scan using idx_gl_accounts_id on gl_accounts ga (cost=0.29..0.33 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=132,011)

  • Index Cond: (id = gl_activity.gl_account_id)
  • Filter: ((is_confidential IS FALSE) AND (cid = 13,576))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=396,124
17. 2.964 2.964 ↓ 7.0 7 6

Function Scan on load_properties load_prop (cost=0.25..2.00 rows=1 width=4) (actual time=0.493..0.494 rows=7 loops=6)

  • Filter: ((is_disabled = 0) AND (is_test = 0))
  • Buffers: shared hit=605
18. 0.048 0.048 ↑ 1.0 1 6

Index Scan using uk_property_gl_settings_cid_property_id on property_gl_settings pgs (cost=0.29..2.33 rows=1 width=12) (actual time=0.008..0.008 rows=1 loops=6)

  • Index Cond: ((cid = 13,576) AND (property_id = gl_activity.property_id))
  • Buffers: shared hit=18
19. 0.078 0.078 ↑ 1.0 1 6

Index Only Scan using uk_gl_account_trees_gl_account_id on gl_account_trees gat (cost=0.42..1.46 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=6)

  • Index Cond: ((cid = 13,576) AND (gl_tree_id = 2,718) AND (gl_account_id = pgs.retained_earnings_gl_account_id))
  • Heap Fetches: 0
  • Buffers: shared hit=18 read=1
Planning time : 7.759 ms
Execution time : 24,802.141 ms