explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sbCM : view_approve_gl_entries

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 32,777.554 ↓ 3.0 3 1

Limit (cost=870,849.98..870,849.99 rows=1 width=237) (actual time=32,777.550..32,777.554 rows=3 loops=1)

2. 0.010 32,777.550 ↓ 3.0 3 1

Sort (cost=870,849.98..870,849.99 rows=1 width=237) (actual time=32,777.549..32,777.550 rows=3 loops=1)

  • Sort Key: gh.header_number DESC
  • Sort Method: quicksort Memory: 25kB
3. 0.006 32,777.540 ↓ 3.0 3 1

Group (cost=870,849.95..870,849.97 rows=1 width=237) (actual time=32,777.535..32,777.540 rows=3 loops=1)

  • Group Key: gh.id, gh.cid, (sum(CASE WHEN (gd.amount > '0'::numeric) THEN gd.amount ELSE '0'::numeric END)), (CASE WHEN (min((p.property_name)::text) <> max((p.property_name)::text)) THEN 'Multiple'::text ELSE max((p.property_name)::text) END), (max(CASE WHEN ((gd.accrual_gl_account_id IS NOT NULL) AND (gd.cash_gl_account_id IS NOT NULL)) THEN 'Both'::text WHEN (gd.accrual_gl_account_id IS NOT NULL) THEN 'Accrual'::text WHEN (gd.accrual_gl_account_id IS NULL) THEN 'Cash'::text ELSE NULL::text END)), gb.name
4. 0.015 32,777.534 ↓ 3.0 3 1

Sort (cost=870,849.95..870,849.96 rows=1 width=233) (actual time=32,777.532..32,777.534 rows=3 loops=1)

  • Sort Key: gh.id, (sum(CASE WHEN (gd.amount > '0'::numeric) THEN gd.amount ELSE '0'::numeric END)), (CASE WHEN (min((p.property_name)::text) <> max((p.property_name)::text)) THEN 'Multiple'::text ELSE max((p.property_name)::text) END), (max(CASE WHEN ((gd.accrual_gl_account_id IS NOT NULL) AND (gd.cash_gl_account_id IS NOT NULL)) THEN 'Both'::text WHEN (gd.accrual_gl_account_id IS NOT NULL) THEN 'Accrual'::text WHEN (gd.accrual_gl_account_id IS NULL) THEN 'Cash'::text ELSE NULL::text END)), gb.name
  • Sort Method: quicksort Memory: 25kB
5. 0.008 32,777.519 ↓ 3.0 3 1

Nested Loop (cost=870,840.27..870,849.94 rows=1 width=233) (actual time=32,777.396..32,777.519 rows=3 loops=1)

6. 0.014 32,767.317 ↓ 3.0 3 1

Nested Loop (cost=870,810.46..870,820.10 rows=1 width=141) (actual time=32,767.290..32,767.317 rows=3 loops=1)

  • Join Filter: (gh.gl_book_id = gb.id)
  • Rows Removed by Join Filter: 6
7. 3,963.565 32,767.285 ↓ 3.0 3 1

Index Scan using idx_gl_headers_gl_header_status_type_id on gl_headers gh (cost=870,810.46..870,818.95 rows=1 width=133) (actual time=32,767.271..32,767.285 rows=3 loops=1)

  • Index Cond: (gl_header_status_type_id = ANY ('{2,5}'::integer[]))
  • Filter: ((is_template IS FALSE) AND (reference_id IS NULL) AND (NOT (hashed SubPlan 1)) AND (cid = 2,603) AND (gl_transaction_type_id = 1))
8.          

SubPlan (for Index Scan)

9. 1,568.701 28,803.720 ↓ 1,258,483.0 1,258,483 1

Unique (cost=870,810.01..870,810.02 rows=1 width=4) (actual time=26,164.621..28,803.720 rows=1,258,483 loops=1)

10. 2,982.849 27,235.019 ↓ 2,657,122.0 2,657,122 1

Sort (cost=870,810.01..870,810.02 rows=1 width=4) (actual time=26,164.619..27,235.019 rows=2,657,122 loops=1)

  • Sort Key: gd_1.gl_header_id
  • Sort Method: quicksort Memory: 222,857kB
11. 13,000.539 24,252.170 ↓ 2,657,122.0 2,657,122 1

Hash Anti Join (cost=109.44..870,810.00 rows=1 width=4) (actual time=2.035..24,252.170 rows=2,657,122 loops=1)

  • Hash Cond: ((gd_1.cid = p_1.cid) AND (gd_1.property_id = p_1.id))
12. 11,249.623 11,249.623 ↓ 1.0 24,539,916 1

Seq Scan on gl_details gd_1 (cost=0.00..686,721.98 rows=24,530,478 width=12) (actual time=0.006..11,249.623 rows=24,539,916 loops=1)

  • Filter: (cid = 2,603)
13. 0.228 2.008 ↓ 1.0 474 1

Hash (cost=102.35..102.35 rows=473 width=8) (actual time=2.007..2.008 rows=474 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 27kB
14. 0.372 1.780 ↓ 1.0 474 1

Nested Loop (cost=38.84..102.35 rows=473 width=8) (actual time=0.599..1.780 rows=474 loops=1)

15. 0.010 0.010 ↑ 1.0 1 1

Index Scan using pk_company_users on company_users cu (cost=0.29..8.31 rows=1 width=4) (actual time=0.008..0.010 rows=1 loops=1)

  • Index Cond: ((cid = 2,603) AND (id = 6,948))
  • Filter: (company_user_type_id = 2)
16. 0.409 1.398 ↓ 1.0 474 1

Hash Join (cost=38.55..89.31 rows=473 width=8) (actual time=0.589..1.398 rows=474 loops=1)

  • Hash Cond: (p_1.id = pgs.property_id)
17. 0.410 0.410 ↑ 1.0 474 1

Seq Scan on properties p_1 (cost=0.00..49.50 rows=474 width=8) (actual time=0.004..0.410 rows=474 loops=1)

  • Filter: ((property_type_id <> 99) AND (cid = 2,603) AND (is_disabled = 0))
  • Rows Removed by Filter: 69
18. 0.250 0.579 ↑ 1.0 542 1

Hash (cost=31.77..31.77 rows=542 width=8) (actual time=0.578..0.579 rows=542 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
19. 0.329 0.329 ↑ 1.0 542 1

Seq Scan on property_gl_settings pgs (cost=0.00..31.77 rows=542 width=8) (actual time=0.004..0.329 rows=542 loops=1)

  • Filter: (cid = 2,603)
20. 0.018 0.018 ↑ 1.3 3 3

Seq Scan on gl_books gb (cost=0.00..1.10 rows=4 width=16) (actual time=0.005..0.006 rows=3 loops=3)

  • Filter: (cid = 2,603)
  • Rows Removed by Filter: 3
21. 0.033 10.194 ↑ 1.0 1 3

Aggregate (cost=29.81..29.82 rows=1 width=96) (actual time=3.397..3.398 rows=1 loops=3)

  • Filter: (sum(CASE WHEN (gd.amount > '0'::numeric) THEN gd.amount ELSE '0'::numeric END) IS NOT NULL)
22. 0.023 10.161 ↓ 5.0 5 3

Nested Loop (cost=1.60..29.78 rows=1 width=38) (actual time=3.368..3.387 rows=5 loops=3)

23. 0.030 10.110 ↓ 5.0 5 3

Hash Semi Join (cost=1.32..22.46 rows=1 width=25) (actual time=3.362..3.370 rows=5 loops=3)

  • Hash Cond: (gd.property_id = lp.property_id)
24. 0.039 0.039 ↓ 1.2 5 3

Index Scan using idx_gl_details_gl_header_id on gl_details gd (cost=0.44..21.56 rows=4 width=21) (actual time=0.010..0.013 rows=5 loops=3)

  • Index Cond: (gh.id = gl_header_id)
  • Filter: ((cid = 2,603) AND (gl_transaction_type_id = gh.gl_transaction_type_id))
25. 0.213 10.041 ↓ 468.0 468 1

Hash (cost=0.88..0.88 rows=1 width=8) (actual time=10.040..10.041 rows=468 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 27kB
26. 9.828 9.828 ↓ 468.0 468 1

Function Scan on load_properties lp (cost=0.25..0.88 rows=1 width=8) (actual time=9.619..9.828 rows=468 loops=1)

  • Filter: (cid = 2,603)
27. 0.028 0.028 ↑ 1.0 1 14

Index Scan using idx_properties_id on properties p (cost=0.28..7.30 rows=1 width=33) (actual time=0.002..0.002 rows=1 loops=14)

  • Index Cond: (id = gd.property_id)
  • Filter: ((cid = 2,603) AND (is_disabled = 0))