explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qxzE : New Query

Settings
# exclusive inclusive rows x rows loops node
1. 0.093 2,007.235 ↑ 1.0 101 1

Limit (cost=13,834.33..13,834.58 rows=101 width=271) (actual time=2,007.137..2,007.235 rows=101 loops=1)

2. 50.784 2,007.142 ↑ 2.0 101 1

Sort (cost=13,834.33..13,834.83 rows=200 width=271) (actual time=2,007.135..2,007.142 rows=101 loops=1)

  • Sort Key: (max(u.first_name))
  • Sort Method: top-N heapsort Memory: 66kB
3. 155.177 1,956.358 ↓ 533.4 106,675 1

GroupAggregate (cost=13,760.56..13,826.68 rows=200 width=271) (actual time=1,667.382..1,956.358 rows=106,675 loops=1)

  • Group Key: u.user_handle
4. 647.599 1,801.181 ↓ 58.8 107,706 1

Sort (cost=13,760.56..13,765.14 rows=1,832 width=193) (actual time=1,667.369..1,801.181 rows=107,706 loops=1)

  • Sort Key: u.user_handle
  • Sort Method: external merge Disk: 17504kB
5. 16.291 1,153.582 ↓ 58.8 107,706 1

Append (cost=1,908.58..13,661.27 rows=1,832 width=193) (actual time=23.740..1,153.582 rows=107,706 loops=1)

6. 0.000 1,095.036 ↓ 63.2 105,154 1

Gather (cost=1,908.58..8,999.17 rows=1,664 width=193) (actual time=23.739..1,095.036 rows=105,154 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 26.365 1,153.493 ↓ 50.6 35,051 3

Hash Left Join (cost=908.58..7,832.77 rows=693 width=193) (actual time=10.934..1,153.493 rows=35,051 loops=3)

  • Hash Cond: ((m.plan_id = d.plan_id) AND (g.department_id = d.id))
8. 28.068 1,127.105 ↓ 50.6 35,051 3

Hash Left Join (cost=906.88..7,827.17 rows=693 width=168) (actual time=10.896..1,127.105 rows=35,051 loops=3)

  • Hash Cond: ((m.plan_id = g.plan_id) AND (t.group_id = g.id))
9. 499.813 1,098.879 ↓ 50.6 35,051 3

Nested Loop (cost=897.76..7,814.42 rows=693 width=159) (actual time=10.694..1,098.879 rows=35,051 loops=3)

10. 543.819 599.053 ↓ 42.4 35,051 3

Nested Loop (cost=897.34..7,198.10 rows=826 width=147) (actual time=10.670..599.053 rows=35,051 loops=3)

11. 26.878 55.220 ↓ 19.4 35,051 3

Hash Join (cost=896.92..5,932.70 rows=1,806 width=109) (actual time=10.645..55.220 rows=35,051 loops=3)

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

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

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 318kB
14. 0.602 0.866 ↓ 1.0 2,401 3

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

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

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

  • Index Cond: (plan_id = 'bank-of-america'::text)
16. 0.014 0.014 ↑ 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.014..0.014 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.079 0.158 ↓ 1.1 220 3

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

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

Seq Scan on groups g (cost=0.00..6.16 rows=197 width=58) (actual time=0.010..0.079 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.015 0.232 ↓ 3.7 11 1

Nested Loop (cost=1.26..103.28 rows=3 width=193) (actual time=0.081..0.232 rows=11 loops=1)

23. 0.007 0.129 ↓ 1.4 11 1

Nested Loop (cost=0.84..98.98 rows=8 width=135) (actual time=0.057..0.129 rows=11 loops=1)

24. 0.045 0.045 ↓ 1.4 11 1

Index Only Scan using admins_plan_id_user_handle_unique on admins a (cost=0.41..31.46 rows=8 width=52) (actual time=0.041..0.045 rows=11 loops=1)

  • Index Cond: (plan_id = 'bank-of-america'::text)
  • Heap Fetches: 11
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.088 0.088 ↑ 1.0 1 11

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

  • Index Cond: ((plan_id = 'bank-of-america'::text) AND (user_handle = u_1.user_handle))
27. 0.018 0.702 ↓ 40.0 40 1

Nested Loop (cost=12.16..21.78 rows=1 width=193) (actual time=0.096..0.702 rows=40 loops=1)

28. 0.049 0.364 ↓ 40.0 40 1

Nested Loop (cost=11.74..21.24 rows=1 width=147) (actual time=0.076..0.364 rows=40 loops=1)

29. 0.027 0.075 ↓ 40.0 40 1

Hash Join (cost=11.32..12.80 rows=1 width=64) (actual time=0.057..0.075 rows=40 loops=1)

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

Seq Scan on departments d_1 (cost=0.00..1.40 rows=20 width=33) (actual time=0.008..0.013 rows=20 loops=1)

  • Filter: (plan_id = 'bank-of-america'::text)
  • Rows Removed by Filter: 12
31. 0.014 0.035 ↓ 13.3 40 1

Hash (cost=11.28..11.28 rows=3 width=80) (actual time=0.035..0.035 rows=40 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
32. 0.012 0.021 ↓ 13.3 40 1

Bitmap Heap Scan on department_managers dm (cost=4.17..11.28 rows=3 width=80) (actual time=0.016..0.021 rows=40 loops=1)

  • Recheck Cond: (plan_id = 'bank-of-america'::text)
  • Heap Blocks: exact=1
33. 0.009 0.009 ↓ 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.009..0.009 rows=40 loops=1)

  • Index Cond: (plan_id = 'bank-of-america'::text)
34. 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)
35. 0.320 0.320 ↑ 1.0 1 40

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

  • Index Cond: ((plan_id = 'bank-of-america'::text) AND (user_handle = u_2.user_handle))
36. 0.585 7.086 ↓ 3.4 440 1

Nested Loop (cost=9.47..2,913.07 rows=130 width=193) (actual time=0.154..7.086 rows=440 loops=1)

37. 0.407 3.421 ↓ 1.3 440 1

Nested Loop (cost=9.05..2,730.65 rows=339 width=136) (actual time=0.134..3.421 rows=440 loops=1)

38. 0.166 0.374 ↓ 1.3 440 1

Hash Join (cost=8.62..22.24 rows=339 width=53) (actual time=0.117..0.374 rows=440 loops=1)

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

Seq Scan on group_managers gm (cost=0.00..12.45 rows=435 width=69) (actual time=0.004..0.109 rows=440 loops=1)

  • Filter: (plan_id = 'bank-of-america'::text)
  • Rows Removed by Filter: 1
40. 0.048 0.099 ↓ 1.1 220 1

Hash (cost=6.16..6.16 rows=197 width=33) (actual time=0.099..0.099 rows=220 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
41. 0.051 0.051 ↓ 1.1 220 1

Seq Scan on groups g_1 (cost=0.00..6.16 rows=197 width=33) (actual time=0.005..0.051 rows=220 loops=1)

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

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

  • Index Cond: (user_handle = gm.user_handle)
43. 3.080 3.080 ↑ 1.0 1 440

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

  • Index Cond: ((plan_id = 'bank-of-america'::text) AND (user_handle = u_3.user_handle))
44. 2.600 34.235 ↓ 60.6 2,061 1

Nested Loop (cost=897.76..1,605.65 rows=34 width=193) (actual time=1.108..34.235 rows=2,061 loops=1)

  • Join Filter: (m_1.user_handle = u_4.user_handle)
45. 2.434 19.269 ↓ 29.0 2,061 1

Nested Loop (cost=897.34..1,552.56 rows=71 width=73) (actual time=1.086..19.269 rows=2,061 loops=1)

46. 0.864 2.408 ↓ 22.9 2,061 1

Hash Join (cost=896.92..967.67 rows=90 width=50) (actual time=1.065..2.408 rows=2,061 loops=1)

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

Seq Scan on managers m_1 (cost=0.00..65.34 rows=2,064 width=66) (actual time=0.016..0.525 rows=2,061 loops=1)

  • Filter: (plan_id = 'bank-of-america'::text)
  • Rows Removed by Filter: 341
48. 0.439 1.019 ↓ 1.0 2,401 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 183kB
49. 0.378 0.580 ↓ 1.0 2,401 1

Bitmap Heap Scan on teams t_1 (cost=86.99..866.95 rows=2,397 width=33) (actual time=0.210..0.580 rows=2,401 loops=1)

  • Recheck Cond: (plan_id = 'bank-of-america'::text)
  • Heap Blocks: exact=38
50. 0.202 0.202 ↓ 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.202..0.202 rows=2,401 loops=1)

  • Index Cond: (plan_id = 'bank-of-america'::text)
51. 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))
52. 12.366 12.366 ↑ 1.0 1 2,061

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

  • Index Cond: (user_handle = pu_4.user_handle)
Planning time : 5.126 ms
Execution time : 2,011.208 ms