explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ITd9

Settings
# exclusive inclusive rows x rows loops node
1. 0.113 2,518.637 ↑ 1.0 101 1

Limit (cost=13,917.44..13,917.70 rows=101 width=586) (actual time=2,518.518..2,518.637 rows=101 loops=1)

2. 71.461 2,518.524 ↑ 2.0 101 1

Sort (cost=13,917.44..13,917.94 rows=200 width=586) (actual time=2,518.517..2,518.524 rows=101 loops=1)

  • Sort Key: (max(plan_users.first_name))
  • Sort Method: top-N heapsort Memory: 110kB
3. 455.681 2,447.063 ↓ 533.4 106,675 1

GroupAggregate (cost=13,786.94..13,909.80 rows=200 width=586) (actual time=1,854.333..2,447.063 rows=106,675 loops=1)

  • Group Key: plan_users.user_handle
4. 673.913 1,991.382 ↓ 58.8 107,706 1

Sort (cost=13,786.94..13,791.52 rows=1,831 width=458) (actual time=1,854.303..1,991.382 rows=107,706 loops=1)

  • Sort Key: plan_users.user_handle
  • Sort Method: external merge Disk: 19728kB
5. 33.292 1,317.469 ↓ 58.8 107,706 1

Subquery Scan on plan_users (cost=1,908.58..13,687.71 rows=1,831 width=458) (actual time=20.228..1,317.469 rows=107,706 loops=1)

6. 15.926 1,284.177 ↓ 58.8 107,706 1

Append (cost=1,908.58..13,669.40 rows=1,831 width=522) (actual time=20.227..1,284.177 rows=107,706 loops=1)

7. 0.000 1,221.849 ↓ 63.2 105,154 1

Gather (cost=1,908.58..8,999.54 rows=1,664 width=447) (actual time=20.226..1,221.849 rows=105,154 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 96.736 1,264.643 ↓ 50.6 35,051 3

Hash Left Join (cost=908.58..7,833.14 rows=693 width=447) (actual time=9.277..1,264.643 rows=35,051 loops=3)

  • Hash Cond: ((m.plan_id = d.plan_id) AND (g.department_id = d.id))
  • Filter: ((u.first_name ~~* '%a%'::text) OR (u.last_name ~~* '%a%'::text) OR (u.email ~~* '%a%'::text) OR (pu.note ~~* '%a%'::text) OR (t.name ~~* '%a%'::text) OR (
9. 32.752 1,167.884 ↓ 50.6 35,051 3

Hash Left Join (cost=906.88..7,827.17 rows=693 width=200) (actual time=9.218..1,167.884 rows=35,051 loops=3)

  • Hash Cond: ((m.plan_id = g.plan_id) AND (t.group_id = g.id))
10. 512.929 1,134.976 ↓ 50.6 35,051 3

Nested Loop (cost=897.76..7,814.42 rows=693 width=191) (actual time=9.020..1,134.976 rows=35,051 loops=3)

11. 567.533 622.034 ↓ 42.4 35,051 3

Nested Loop (cost=897.34..7,198.10 rows=826 width=179) (actual time=8.993..622.034 rows=35,051 loops=3)

12. 19.823 54.486 ↓ 19.4 35,051 3

Hash Join (cost=896.92..5,932.70 rows=1,806 width=141) (actual time=8.966..54.486 rows=35,051 loops=3)

  • Hash Cond: (m.team_id = t.id)
13. 31.649 31.649 ↑ 1.2 35,051 3

Parallel Seq Scan on members m (cost=0.00..4,926.74 rows=41,538 width=90) (actual time=5.872..31.649 rows=35,051 loops=3)

  • Filter: (plan_id = 'bank-of-america'::text)
  • Rows Removed by Filter: 40304
14. 2.169 3.014 ↓ 1.0 2,401 3

Hash (cost=866.95..866.95 rows=2,397 width=84) (actual time=3.013..3.014 rows=2,401 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 318kB
15. 0.845 0.845 ↓ 1.0 2,401 3

Bitmap Heap Scan on teams t (cost=86.99..866.95 rows=2,397 width=84) (actual time=0.263..0.845 rows=2,401 loops=3)

  • Recheck Cond: (plan_id = 'bank-of-america'::text)
  • Heap Blocks: exact=38
  • -> Bitmap Index Scan on plan_teams_name_lower_idx (cost=0.00..86.39 rows=2397 width=0) (actual time=0.255..0.255 rows=2401 lo
  • Index Cond: (plan_id = 'bank-of-america'::text)
16. 0.015 0.015 ↑ 1.0 1 105,154

Index Scan using plan_users_pkey on plan_users pu (cost=0.42..0.70 rows=1 width=53) (actual time=0.015..0.015 rows=1 loops=105,154)

  • Index Cond: ((plan_id = 'bank-of-america'::text) AND (user_handle = m.user_handle))
17. 0.013 0.013 ↑ 1.0 1 105,154

Index Scan using users_pkey on users u (cost=0.42..0.75 rows=1 width=83) (actual time=0.013..0.013 rows=1 loops=105,154)

  • Index Cond: (user_handle = m.user_handle)
18. 0.084 0.156 ↓ 1.1 220 3

Hash (cost=6.16..6.16 rows=197 width=58) (actual time=0.155..0.156 rows=220 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
19. 0.072 0.072 ↓ 1.1 220 3

Seq Scan on groups g (cost=0.00..6.16 rows=197 width=58) (actual time=0.009..0.072 rows=220 loops=3)

  • Filter: (plan_id = 'bank-of-america'::text)
  • Rows Removed by Filter: 56
20. 0.010 0.023 ↑ 1.0 20 3

Hash (cost=1.40..1.40 rows=20 width=45) (actual time=0.023..0.023 rows=20 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
21. 0.013 0.013 ↑ 1.0 20 3

Seq Scan on departments d (cost=0.00..1.40 rows=20 width=45) (actual time=0.008..0.013 rows=20 loops=3)

  • Filter: (plan_id = 'bank-of-america'::text)
  • Rows Removed by Filter: 12
22. 0.011 0.244 ↓ 3.7 11 1

Nested Loop (cost=1.26..103.44 rows=3 width=447) (actual time=0.076..0.244 rows=11 loops=1)

23. 0.007 0.123 ↓ 1.4 11 1

Nested Loop (cost=0.84..98.98 rows=8 width=151) (actual time=0.050..0.123 rows=11 loops=1)

24. 0.039 0.039 ↓ 1.4 11 1

Index Scan using admins_plan_id_user_handle_unique on admins a (cost=0.41..31.46 rows=8 width=68) (actual time=0.035..0.039 rows=11 loops=1)

  • Index Cond: (plan_id = 'bank-of-america'::text)
25. 0.077 0.077 ↑ 1.0 1 11

Index Scan using users_pkey on users u_1 (cost=0.42..8.44 rows=1 width=83) (actual time=0.007..0.007 rows=1 loops=11)

  • Index Cond: (user_handle = a.user_handle)
26. 0.110 0.110 ↑ 1.0 1 11

Index Scan using plan_users_pkey on plan_users pu_1 (cost=0.42..0.55 rows=1 width=53) (actual time=0.010..0.010 rows=1 loops=11)

  • Index Cond: ((plan_id = 'bank-of-america'::text) AND (user_handle = u_1.user_handle))
  • Filter: ((u_1.first_name ~~* '%a%'::text) OR (u_1.last_name ~~* '%a%'::text) OR (u_1.email ~~* '%a%'::text) OR (note ~~* '%a%'::text))
27. 0.016 0.771 ↓ 40.0 40 1

Subquery Scan on *SELECT* 3 (cost=12.16..21.81 rows=1 width=447) (actual time=0.105..0.771 rows=40 loops=1)

28. 0.024 0.755 ↓ 40.0 40 1

Nested Loop (cost=12.16..21.80 rows=1 width=427) (actual time=0.104..0.755 rows=40 loops=1)

29. 0.052 0.371 ↓ 40.0 40 1

Nested Loop (cost=11.74..21.24 rows=1 width=191) (actual time=0.080..0.371 rows=40 loops=1)

30. 0.032 0.079 ↓ 40.0 40 1

Hash Join (cost=11.32..12.80 rows=1 width=108) (actual time=0.059..0.079 rows=40 loops=1)

  • Hash Cond: (d_1.id = dm.department_id)
31. 0.011 0.011 ↑ 1.0 20 1

Seq Scan on departments d_1 (cost=0.00..1.40 rows=20 width=45) (actual time=0.005..0.011 rows=20 loops=1)

  • Filter: (plan_id = 'bank-of-america'::text)
  • Rows Removed by Filter: 12
32. 0.013 0.036 ↓ 13.3 40 1

Hash (cost=11.28..11.28 rows=3 width=96) (actual time=0.036..0.036 rows=40 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
33. 0.013 0.023 ↓ 13.3 40 1

Bitmap Heap Scan on department_managers dm (cost=4.17..11.28 rows=3 width=96) (actual time=0.017..0.023 rows=40 loops=1)

  • Recheck Cond: (plan_id = 'bank-of-america'::text)
  • Heap Blocks: exact=1
34. 0.010 0.010 ↓ 13.3 40 1

Bitmap Index Scan on department_managers_plan_id_idx (cost=0.00..4.17 rows=3 width=0) (actual time=0.010..0.010 rows=40 loops=1)

  • Index Cond: (plan_id = 'bank-of-america'::text)
35. 0.240 0.240 ↑ 1.0 1 40

Index Scan using users_pkey on users u_2 (cost=0.42..8.44 rows=1 width=83) (actual time=0.006..0.006 rows=1 loops=40)

  • Index Cond: (user_handle = dm.user_handle)
36. 0.360 0.360 ↑ 1.0 1 40

Index Scan using plan_users_pkey on plan_users pu_2 (cost=0.42..0.55 rows=1 width=53) (actual time=0.009..0.009 rows=1 loops=40)

  • Index Cond: ((plan_id = 'bank-of-america'::text) AND (user_handle = u_2.user_handle))
  • Filter: ((u_2.first_name ~~* '%a%'::text) OR (u_2.last_name ~~* '%a%'::text) OR (u_2.email ~~* '%a%'::text) OR (note ~~* '%a%'::text))
37. 0.171 7.851 ↓ 3.4 440 1

Subquery Scan on *SELECT* 4 (cost=9.47..2,921.07 rows=129 width=447) (actual time=0.168..7.851 rows=440 loops=1)

38. 0.579 7.680 ↓ 3.4 440 1

Nested Loop (cost=9.47..2,919.78 rows=129 width=424) (actual time=0.167..7.680 rows=440 loops=1)

  • Join Filter: (gm.user_handle = u_3.user_handle)
39. 0.110 4.021 ↓ 1.6 440 1

Nested Loop (cost=9.05..2,715.92 rows=269 width=116) (actual time=0.140..4.021 rows=440 loops=1)

40. 0.178 0.391 ↓ 1.3 440 1

Hash Join (cost=8.62..22.24 rows=339 width=94) (actual time=0.122..0.391 rows=440 loops=1)

  • Hash Cond: (gm.group_id = g_1.id)
41. 0.108 0.108 ↓ 1.0 440 1

Seq Scan on group_managers gm (cost=0.00..12.45 rows=435 width=85) (actual time=0.005..0.108 rows=440 loops=1)

  • Filter: (plan_id = 'bank-of-america'::text)
  • Rows Removed by Filter: 1
42. 0.051 0.105 ↓ 1.1 220 1

Hash (cost=6.16..6.16 rows=197 width=42) (actual time=0.105..0.105 rows=220 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
43. 0.054 0.054 ↓ 1.1 220 1

Seq Scan on groups g_1 (cost=0.00..6.16 rows=197 width=42) (actual time=0.004..0.054 rows=220 loops=1)

  • Filter: (plan_id = 'bank-of-america'::text)
  • Rows Removed by Filter: 56
44. 3.520 3.520 ↑ 1.0 1 440

Index Scan using plan_users_pkey on plan_users pu_3 (cost=0.42..7.95 rows=1 width=53) (actual time=0.008..0.008 rows=1 loops=440)

  • Index Cond: ((plan_id = 'bank-of-america'::text) AND (user_handle = gm.user_handle))
45. 3.080 3.080 ↑ 1.0 1 440

Index Scan using users_pkey on users u_3 (cost=0.42..0.75 rows=1 width=83) (actual time=0.007..0.007 rows=1 loops=440)

  • Index Cond: (user_handle = pu_3.user_handle)
  • Filter: ((first_name ~~* '%a%'::text) OR (last_name ~~* '%a%'::text) OR (email ~~* '%a%'::text) OR (pu_3.note ~~* '%a%'::text))
46. 3.394 37.536 ↓ 60.6 2,061 1

Nested Loop (cost=897.76..1,606.53 rows=34 width=449) (actual time=1.335..37.536 rows=2,061 loops=1)

  • Join Filter: (m_1.user_handle = u_4.user_handle)
47. 2.541 19.715 ↓ 29.0 2,061 1

Nested Loop (cost=897.34..1,552.56 rows=71 width=139) (actual time=1.307..19.715 rows=2,061 loops=1)

48. 0.982 2.747 ↓ 22.9 2,061 1

Hash Join (cost=896.92..967.67 rows=90 width=116) (actual time=1.282..2.747 rows=2,061 loops=1)

  • Hash Cond: (m_1.team_id = t_1.id)
49. 0.530 0.530 ↑ 1.0 2,061 1

Seq Scan on managers m_1 (cost=0.00..65.34 rows=2,064 width=82) (actual time=0.014..0.530 rows=2,061 loops=1)

  • Filter: (plan_id = 'bank-of-america'::text)
  • Rows Removed by Filter: 342
50. 0.595 1.235 ↓ 1.0 2,401 1

Hash (cost=866.95..866.95 rows=2,397 width=67) (actual time=1.235..1.235 rows=2,401 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 281kB
51. 0.436 0.640 ↓ 1.0 2,401 1

Bitmap Heap Scan on teams t_1 (cost=86.99..866.95 rows=2,397 width=67) (actual time=0.212..0.640 rows=2,401 loops=1)

  • Recheck Cond: (plan_id = 'bank-of-america'::text)
  • Heap Blocks: exact=38
52. 0.204 0.204 ↓ 1.0 2,401 1

Bitmap Index Scan on plan_teams_name_lower_idx (cost=0.00..86.39 rows=2,397 width=0) (actual time=0.203..0.204 rows=2,401 loops=1)

  • Index Cond: (plan_id = 'bank-of-america'::text)
53. 14.427 14.427 ↑ 1.0 1 2,061

Index Scan using plan_users_pkey on plan_users pu_4 (cost=0.42..6.50 rows=1 width=53) (actual time=0.007..0.007 rows=1 loops=2,061)

  • Index Cond: ((plan_id = 'bank-of-america'::text) AND (user_handle = m_1.user_handle))
54. 14.427 14.427 ↑ 1.0 1 2,061

Index Scan using users_pkey on users u_4 (cost=0.42..0.75 rows=1 width=83) (actual time=0.007..0.007 rows=1 loops=2,061)

  • Index Cond: (user_handle = pu_4.user_handle)
  • Filter: ((first_name ~~* '%a%'::text) OR (last_name ~~* '%a%'::text) OR (email ~~* '%a%'::text) OR (pu_4.note ~~* '%a%'::text))
Planning time : 19.735 ms
Execution time : 2,522.683 ms