explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uWQk

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

GroupAggregate (cost=410.04..410.09 rows=1 width=52) (actual time=1,715.061..1,715.065 rows=6 loops=1)

  • Group Key: pgs.cid, pgs.property_id, pgs.retained_earnings_gl_account_id
  • Buffers: shared hit=522045 read=7247, local hit=2
2. 0.019 1,715.053 ↓ 6.0 6 1

Sort (cost=410.04..410.04 rows=1 width=76) (actual time=1,715.053..1,715.053 rows=6 loops=1)

  • Sort Key: pgs.property_id, pgs.retained_earnings_gl_account_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=522045 read=7247, local hit=2
3. 0.015 1,715.034 ↓ 6.0 6 1

Nested Loop (cost=406.65..410.03 rows=1 width=76) (actual time=1,693.814..1,715.034 rows=6 loops=1)

  • Buffers: shared hit=522042 read=7247, local hit=2
4. 0.025 1,714.935 ↓ 6.0 6 1

Nested Loop (cost=406.23..408.52 rows=1 width=76) (actual time=1,693.754..1,714.935 rows=6 loops=1)

  • Join Filter: (gl_activity.property_id = pgs.property_id)
  • Buffers: shared hit=522024 read=7246, local hit=2
5. 0.015 1,714.820 ↓ 6.0 6 1

Merge Join (cost=405.94..406.15 rows=1 width=84) (actual time=1,693.719..1,714.820 rows=6 loops=1)

  • Merge Cond: (gl_activity.property_id = lp.property_id)
  • Buffers: shared hit=522006 read=7246, local hit=2
6. 29.156 1,714.775 ↓ 6.0 6 1

GroupAggregate (cost=404.50..404.62 rows=1 width=80) (actual time=1,693.686..1,714.775 rows=6 loops=1)

  • Group Key: gl_activity.cid, gl_activity.property_id
  • Buffers: shared hit=522006 read=7246, local hit=1
7. 72.168 1,685.619 ↓ 132,011.0 132,011 1

Merge Semi Join (cost=404.50..404.57 rows=1 width=25) (actual time=1,632.848..1,685.619 rows=132,011 loops=1)

  • Merge Cond: (gl_activity.property_id = lp_1.property_id)
  • Buffers: shared hit=522006 read=7246, local hit=1
8. 239.229 1,613.424 ↓ 239,257.7 717,773 1

Sort (cost=403.18..403.19 rows=3 width=25) (actual time=1,569.760..1,613.424 rows=717,773 loops=1)

  • Sort Key: gl_activity.property_id
  • Sort Method: quicksort Memory: 78762kB
  • Buffers: shared hit=522006 read=7246
9. 97.500 1,374.195 ↓ 239,257.7 717,773 1

Nested Loop (cost=1.42..403.16 rows=3 width=25) (actual time=0.052..1,374.195 rows=717,773 loops=1)

  • Join Filter: (gat_1.gl_account_id = gl_activity.gl_account_id)
  • Buffers: shared hit=522003 read=7246
10. 0.662 5.265 ↓ 510.0 510 1

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

  • Buffers: shared hit=4108
11. 0.724 3.357 ↓ 207.7 623 1

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

  • Buffers: shared hit=2239
12. 0.764 0.764 ↓ 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.015..0.764 rows=623 loops=1)

  • Index Cond: ((cid = 13576) AND (gl_tree_id = 2718))
  • Buffers: shared hit=362
13. 1.869 1.869 ↑ 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.003..0.003 rows=1 loops=623)

  • Index Cond: (id = gat_1.gl_account_id)
  • Filter: ((is_confidential IS FALSE) AND (cid = 13576))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1877
14. 1.246 1.246 ↑ 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.002..0.002 rows=1 loops=623)

  • Index Cond: (id = gat_1.gl_branch_id)
  • Filter: ((gl_group_type_id = ANY ('{4,5}'::integer[])) AND (cid = 13576))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1869
15. 1,271.430 1,271.430 ↓ 4.8 1,407 510

Index Scan using idx_cached_gl_account_periods_gl_account_id on cached_gl_account_periods gl_activity (cost=0.43..263.09 rows=295 width=33) (actual time=0.025..2.493 rows=1,407 loops=510)

  • Index Cond: (gl_account_id = ga.id)
  • Filter: ((is_temporary IS FALSE) AND (post_month < '01/01/2019'::date) AND (cid = 13576) AND (gl_book_id = 937))
  • Rows Removed by Filter: 962
  • Buffers: shared hit=517895 read=7246
16. 0.015 0.027 ↑ 1.2 6 1

Sort (cost=1.31..1.33 rows=7 width=4) (actual time=0.025..0.027 rows=6 loops=1)

  • Sort Key: lp_1.property_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: local hit=1
17. 0.012 0.012 ↑ 1.0 7 1

Seq Scan on lp lp_1 (cost=0.00..1.21 rows=7 width=4) (actual time=0.011..0.012 rows=7 loops=1)

  • Buffers: local hit=1
18. 0.010 0.030 ↑ 1.0 7 1

Sort (cost=1.44..1.46 rows=7 width=4) (actual time=0.029..0.030 rows=7 loops=1)

  • Sort Key: lp.property_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: local hit=1
19. 0.003 0.020 ↑ 1.0 7 1

Unique (cost=1.31..1.34 rows=7 width=4) (actual time=0.018..0.020 rows=7 loops=1)

  • Buffers: local hit=1
20. 0.005 0.017 ↑ 1.0 7 1

Sort (cost=1.31..1.33 rows=7 width=4) (actual time=0.017..0.017 rows=7 loops=1)

  • Sort Key: lp.property_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: local hit=1
21. 0.012 0.012 ↑ 1.0 7 1

Seq Scan on lp (cost=0.00..1.21 rows=7 width=4) (actual time=0.011..0.012 rows=7 loops=1)

  • Buffers: local hit=1
22. 0.090 0.090 ↑ 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.015..0.015 rows=1 loops=6)

  • Index Cond: ((cid = 13576) AND (property_id = lp.property_id))
  • Buffers: shared hit=18
23. 0.084 0.084 ↑ 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.014..0.014 rows=1 loops=6)

  • Index Cond: ((cid = 13576) AND (gl_tree_id = 2718) AND (gl_account_id = pgs.retained_earnings_gl_account_id))
  • Heap Fetches: 0
  • Buffers: shared hit=18 read=1
Planning time : 8.578 ms
Execution time : 1,720.506 ms