explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2Jti

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 9,850.800 ↓ 6.0 6 1

GroupAggregate (cost=294.35..294.40 rows=1 width=52) (actual time=9,850.795..9,850.800 rows=6 loops=1)

  • Group Key: pgs.cid, pgs.property_id, pgs.retained_earnings_gl_account_id
  • Buffers: shared hit=718,293
2. 0.023 9,850.787 ↓ 6.0 6 1

Sort (cost=294.35..294.35 rows=1 width=76) (actual time=9,850.787..9,850.787 rows=6 loops=1)

  • Sort Key: pgs.property_id, pgs.retained_earnings_gl_account_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=718,293
3. 0.008 9,850.764 ↓ 6.0 6 1

Nested Loop (cost=289.31..294.34 rows=1 width=76) (actual time=9,838.155..9,850.764 rows=6 loops=1)

  • Buffers: shared hit=718,290
4. 0.015 9,850.708 ↓ 6.0 6 1

Nested Loop (cost=288.89..292.83 rows=1 width=76) (actual time=9,838.128..9,850.708 rows=6 loops=1)

  • Join Filter: (load_prop.property_id = pgs.property_id)
  • Buffers: shared hit=718,270
5. 0.012 9,849.013 ↓ 6.0 6 1

Nested Loop (cost=288.60..290.47 rows=1 width=84) (actual time=9,836.475..9,849.013 rows=6 loops=1)

  • Join Filter: (load_prop.property_id = gl_activity.property_id)
  • Rows Removed by Join Filter: 36
  • Buffers: shared hit=717,865
6. 32.612 9,846.217 ↓ 6.0 6 1

GroupAggregate (cost=288.35..288.40 rows=1 width=80) (actual time=9,833.699..9,846.217 rows=6 loops=1)

  • Group Key: gl_activity.cid, gl_activity.property_id
  • Buffers: shared hit=717,260
7. 43.096 9,813.605 ↓ 132,011.0 132,011 1

Sort (cost=288.35..288.36 rows=1 width=25) (actual time=9,806.524..9,813.605 rows=132,011 loops=1)

  • Sort Key: gl_activity.property_id
  • Sort Method: quicksort Memory: 16,458kB
  • Buffers: shared hit=717,260
8. 19.967 9,770.509 ↓ 132,011.0 132,011 1

Nested Loop (cost=148.70..288.34 rows=1 width=25) (actual time=37.642..9,770.509 rows=132,011 loops=1)

  • Join Filter: (gl_activity.gl_account_id = gat_1.gl_account_id)
  • Buffers: shared hit=717,257
9. 1.822 14.132 ↓ 510.0 510 1

Nested Loop (cost=0.99..130.48 rows=1 width=16) (actual time=0.034..14.132 rows=510 loops=1)

  • Buffers: shared hit=4,108
10. 1.787 8.572 ↓ 207.7 623 1

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

  • Buffers: shared hit=2,239
11. 1.801 1.801 ↓ 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.013..1.801 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_accounts_id on gl_accounts ga (cost=0.29..1.92 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=623)

  • Index Cond: (id = gat_1.gl_account_id)
  • Filter: ((is_confidential IS FALSE) AND (cid = 13,576))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1,877
13. 3.738 3.738 ↑ 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.006..0.006 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,869
14. 28.050 9,736.410 ↓ 259.0 259 510

Nested Loop (cost=147.71..157.83 rows=1 width=33) (actual time=10.249..19.091 rows=259 loops=510)

  • Buffers: shared hit=713,149
15. 8.670 8.670 ↓ 7.0 7 510

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

  • Filter: ((is_disabled = 0) AND (is_test = 0))
  • Buffers: shared hit=2,464
16. 453.390 9,699.690 ↓ 37.0 37 3,570

Bitmap Heap Scan on cached_gl_account_periods gl_activity (cost=147.46..155.80 rows=1 width=33) (actual time=2.645..2.717 rows=37 loops=3,570)

  • Recheck Cond: ((gl_account_id = ga.id) AND (property_id = load_prop_1.property_id))
  • Filter: ((is_temporary IS FALSE) AND (post_month < '2019-01-01'::date) AND (cid = 13,576) AND (gl_book_id = 937))
  • Rows Removed by Filter: 45
  • Heap Blocks: exact=165,110
  • Buffers: shared hit=710,685
17. 204.190 9,246.300 ↓ 0.0 0 3,570

BitmapAnd (cost=147.46..147.46 rows=8 width=0) (actual time=2.590..2.590 rows=0 loops=3,570)

  • Buffers: shared hit=545,575
18. 667.590 667.590 ↑ 1.1 2,369 3,570

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.187..0.187 rows=2,369 loops=3,570)

  • Index Cond: (gl_account_id = ga.id)
  • Buffers: shared hit=33,855
19. 8,374.520 8,374.520 ↓ 5.1 59,661 3,080

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

  • Index Cond: (property_id = load_prop_1.property_id)
  • Buffers: shared hit=511,720
20. 2.784 2.784 ↓ 7.0 7 6

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

  • Filter: ((is_disabled = 0) AND (is_test = 0))
  • Buffers: shared hit=605
21. 1.680 1.680 ↑ 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.280..0.280 rows=1 loops=6)

  • Index Cond: ((cid = 13,576) AND (property_id = gl_activity.property_id))
  • Buffers: shared hit=405
22. 0.048 0.048 ↑ 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.008..0.008 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=20
Planning time : 5.317 ms
Execution time : 9,852.508 ms