explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rJiq

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

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

2.          

CTE data

3. 5.673 63.097 ↑ 128.5 68 1

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

4. 32.698 57.424 ↓ 1.4 12,025 1

Sort (cost=2,700.58..2,722.43 rows=8,740 width=317) (actual time=44.684..57.424 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.667 24.726 ↓ 1.4 12,025 1

Hash Left Join (cost=53.83..840.40 rows=8,740 width=317) (actual time=1.302..24.726 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.339 17.048 ↓ 1.0 7,439 1

Nested Loop Left Join (cost=52.58..540.76 rows=7,214 width=324) (actual time=1.272..17.048 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.794 4.270 ↓ 2.1 7,439 1

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

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

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

9. 0.311 1.222 ↓ 1.9 624 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 87kB
10. 0.194 0.911 ↓ 1.9 624 1

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

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

Hash Left Join (cost=35.89..44.62 rows=325 width=324) (actual time=0.319..0.705 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.108 0.533 ↑ 1.0 324 1

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

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

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

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

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

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

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

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

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

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

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

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

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

19. 0.003 0.038 ↓ 2.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.019 0.035 ↓ 2.0 2 1

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

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

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

22. 0.003 0.009 ↓ 2.0 2 1

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

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

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

  • Filter: is_main
24. 0.008 0.058 ↓ 2.7 8 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.021 0.050 ↓ 2.7 8 1

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

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

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

27. 0.008 0.018 ↓ 2.7 8 1

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

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

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

  • Filter: is_main
  • Rows Removed by Filter: 18
29. 0.014 0.024 ↑ 1.0 20 1

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

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

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

31. 0.012 0.028 ↓ 1.2 14 1

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

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

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

33. 0.009 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.006 0.006 ↑ 32.0 5 1

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

35. 0.009 0.019 ↑ 1.0 20 1

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

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

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

37. 0.007 0.014 ↑ 23.3 6 1

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

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

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

39. 0.005 0.012 ↑ 1.0 10 1

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

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

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

41. 7.432 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.007 0.007 ↑ 1.0 6 1

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

43. 0.005 0.011 ↑ 1.0 11 1

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

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

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

45.          

Initplan (for Limit)

46. 0.017 18.463 ↑ 1.0 1 1

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

47. 18.446 18.446 ↑ 128.5 68 1

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

48. 63.161 63.161 ↑ 582.7 15 1

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

Planning time : 5.730 ms
Execution time : 66.038 ms