explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JZMu

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 62.635 ↑ 1.0 15 1

Limit (cost=3,093.90..3,094.20 rows=15 width=942) (actual time=62.628..62.635 rows=15 loops=1)

2.          

CTE data

3. 5.781 62.572 ↑ 128.5 68 1

Unique (cost=2,700.58..2,897.23 rows=8,740 width=317) (actual time=44.005..62.572 rows=68 loops=1)

4. 32.819 56.791 ↓ 1.4 12,025 1

Sort (cost=2,700.58..2,722.43 rows=8,740 width=317) (actual time=44.005..56.791 rows=12,025 loops=1)

  • Sort Key: a.id DESC, a.code, a.created_at, aa.name, a.const_borrower_type, (COALESCE(lec.name, (concat_ws(' '::text, ic.first_name, ic.last_name))::character varying)), s.name, u.full_name
  • Sort Method: external merge Disk: 1088kB
5. 7.629 23.972 ↓ 1.4 12,025 1

Hash Left Join (cost=53.83..840.40 rows=8,740 width=317) (actual time=0.877..23.972 rows=12,025 loops=1)

  • Hash Cond: (c.id = cmb.committee_id)
  • Filter: ((a.created_by_id = 1) OR (ltmb.user_id = 1) OR (cmb.user_id = 1))
  • Rows Removed by Filter: 8928
6. 5.069 16.334 ↓ 1.0 7,439 1

Nested Loop Left Join (cost=52.58..540.76 rows=7,214 width=324) (actual time=0.855..16.334 rows=7,439 loops=1)

  • Join Filter: ((c.id = t.committee_id) OR ((c.branch_id = a.branch_id) AND (c.branch_id <> 1)))
  • Rows Removed by Join Filter: 37877
7. 2.764 3.826 ↓ 2.1 7,439 1

Hash Right Join (cost=52.58..111.29 rows=3,570 width=328) (actual time=0.841..3.826 rows=7,439 loops=1)

  • Hash Cond: (t.application_id = a.id)
8. 0.240 0.240 ↓ 1.0 752 1

Seq Scan on timeline t (cost=0.00..16.47 rows=747 width=8) (actual time=0.008..0.240 rows=752 loops=1)

9. 0.208 0.822 ↓ 1.9 624 1

Hash (cost=48.52..48.52 rows=325 width=324) (actual time=0.822..0.822 rows=624 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 87kB
10. 0.122 0.614 ↓ 1.9 624 1

Hash Left Join (cost=37.12..48.52 rows=325 width=324) (actual time=0.261..0.614 rows=624 loops=1)

  • Hash Cond: (lt.id = ltmb.lsm_team_id)
11. 0.095 0.484 ↑ 1.0 324 1

Hash Left Join (cost=35.89..44.62 rows=325 width=324) (actual time=0.240..0.484 rows=324 loops=1)

  • Hash Cond: (ltcf.lsm_team_id = lt.id)
  • Join Filter: ((lt.branch_id = 1) OR (lt.branch_id = a.branch_id))
  • Rows Removed by Join Filter: 174
12. 0.067 0.380 ↑ 1.0 324 1

Hash Left Join (cost=22.74..30.62 rows=325 width=324) (actual time=0.216..0.380 rows=324 loops=1)

  • Hash Cond: (a.appraisal_id = ltcf.appraisal_id)
13. 0.027 0.299 ↑ 1.0 68 1

Hash Join (cost=21.29..25.33 rows=68 width=324) (actual time=0.191..0.299 rows=68 loops=1)

  • Hash Cond: (a.appraisal_id = aa.id)
14. 0.035 0.257 ↑ 1.0 68 1

Hash Join (cost=7.69..11.55 rows=68 width=106) (actual time=0.165..0.257 rows=68 loops=1)

  • Hash Cond: (a.created_by_id = u.id)
15. 0.026 0.201 ↑ 1.0 68 1

Hash Join (cost=6.42..10.03 rows=68 width=93) (actual time=0.134..0.201 rows=68 loops=1)

  • Hash Cond: (a.status_id = s.id)
16. 0.024 0.158 ↑ 1.0 68 1

Hash Left Join (cost=4.97..8.37 rows=68 width=83) (actual time=0.108..0.158 rows=68 loops=1)

  • Hash Cond: (a.id = abi.application_id)
17. 0.022 0.092 ↑ 1.0 68 1

Hash Left Join (cost=2.20..5.14 rows=68 width=72) (actual time=0.055..0.092 rows=68 loops=1)

  • Hash Cond: (a.id = able.application_id)
18. 0.039 0.039 ↑ 1.0 68 1

Seq Scan on application a (cost=0.00..2.68 rows=68 width=56) (actual time=0.014..0.039 rows=68 loops=1)

19. 0.003 0.031 ↓ 2.0 2 1

Hash (cost=2.18..2.18 rows=1 width=20) (actual time=0.031..0.031 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.015 0.028 ↓ 2.0 2 1

Hash Right Join (cost=1.02..2.18 rows=1 width=20) (actual time=0.023..0.028 rows=2 loops=1)

  • Hash Cond: (lec.id = able.legal_entity_customer_id)
21. 0.005 0.005 ↓ 1.3 14 1

Seq Scan on legal_entity_customer lec (cost=0.00..1.11 rows=11 width=20) (actual time=0.004..0.005 rows=14 loops=1)

22. 0.004 0.008 ↓ 2.0 2 1

Hash (cost=1.01..1.01 rows=1 width=8) (actual time=0.008..0.008 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.004 0.004 ↓ 2.0 2 1

Seq Scan on application_borrower_legal_entity able (cost=0.00..1.01 rows=1 width=8) (actual time=0.004..0.004 rows=2 loops=1)

  • Filter: is_main
24. 0.006 0.042 ↓ 2.7 8 1

Hash (cost=2.74..2.74 rows=3 width=15) (actual time=0.042..0.042 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.016 0.036 ↓ 2.7 8 1

Hash Right Join (cost=1.27..2.74 rows=3 width=15) (actual time=0.027..0.036 rows=8 loops=1)

  • Hash Cond: (ic.id = abi.individual_customer_id)
26. 0.007 0.007 ↓ 1.1 36 1

Seq Scan on individual_customer ic (cost=0.00..1.32 rows=32 width=15) (actual time=0.005..0.007 rows=36 loops=1)

27. 0.006 0.013 ↓ 2.7 8 1

Hash (cost=1.23..1.23 rows=3 width=8) (actual time=0.013..0.013 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
28. 0.007 0.007 ↓ 2.7 8 1

Seq Scan on application_borrower_individual abi (cost=0.00..1.23 rows=3 width=8) (actual time=0.005..0.007 rows=8 loops=1)

  • Filter: is_main
  • Rows Removed by Filter: 18
29. 0.011 0.017 ↑ 1.0 20 1

Hash (cost=1.20..1.20 rows=20 width=18) (actual time=0.017..0.017 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
30. 0.006 0.006 ↑ 1.0 20 1

Seq Scan on status s (cost=0.00..1.20 rows=20 width=18) (actual time=0.004..0.006 rows=20 loops=1)

31. 0.008 0.021 ↓ 1.2 14 1

Hash (cost=1.12..1.12 rows=12 width=17) (actual time=0.021..0.021 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.013 0.013 ↓ 1.2 14 1

Seq Scan on "user" u (cost=0.00..1.12 rows=12 width=17) (actual time=0.011..0.013 rows=14 loops=1)

33. 0.011 0.015 ↑ 32.0 5 1

Hash (cost=11.60..11.60 rows=160 width=222) (actual time=0.015..0.015 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 0.004 0.004 ↑ 32.0 5 1

Seq Scan on appraisal aa (cost=0.00..11.60 rows=160 width=222) (actual time=0.003..0.004 rows=5 loops=1)

35. 0.007 0.014 ↑ 1.0 20 1

Hash (cost=1.20..1.20 rows=20 width=8) (actual time=0.014..0.014 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.007 0.007 ↑ 1.0 20 1

Seq Scan on lsm_team_config ltcf (cost=0.00..1.20 rows=20 width=8) (actual time=0.006..0.007 rows=20 loops=1)

37. 0.005 0.009 ↑ 23.3 6 1

Hash (cost=11.40..11.40 rows=140 width=8) (actual time=0.009..0.009 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
38. 0.004 0.004 ↑ 23.3 6 1

Seq Scan on lsm_team lt (cost=0.00..11.40 rows=140 width=8) (actual time=0.004..0.004 rows=6 loops=1)

39. 0.003 0.008 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=8) (actual time=0.008..0.008 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
40. 0.005 0.005 ↑ 1.0 10 1

Seq Scan on lsm_team_member ltmb (cost=0.00..1.10 rows=10 width=8) (actual time=0.004..0.005 rows=10 loops=1)

41. 7.434 7.439 ↑ 1.0 6 7,439

Materialize (cost=0.00..1.09 rows=6 width=8) (actual time=0.000..0.001 rows=6 loops=7,439)

42. 0.005 0.005 ↑ 1.0 6 1

Seq Scan on committee c (cost=0.00..1.06 rows=6 width=8) (actual time=0.004..0.005 rows=6 loops=1)

43. 0.005 0.009 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=8) (actual time=0.009..0.009 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
44. 0.004 0.004 ↑ 1.0 11 1

Seq Scan on committee_member cmb (cost=0.00..1.11 rows=11 width=8) (actual time=0.003..0.004 rows=11 loops=1)

45.          

Initplan (for Limit)

46. 0.016 18.615 ↑ 1.0 1 1

Aggregate (cost=196.65..196.66 rows=1 width=8) (actual time=18.615..18.615 rows=1 loops=1)

47. 18.599 18.599 ↑ 128.5 68 1

CTE Scan on data data_1 (cost=0.00..174.80 rows=8,740 width=0) (actual time=0.000..18.599 rows=68 loops=1)

48. 62.632 62.632 ↑ 582.7 15 1

CTE Scan on data (cost=0.00..174.80 rows=8,740 width=942) (actual time=62.627..62.632 rows=15 loops=1)

Planning time : 3.365 ms
Execution time : 65.403 ms