explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TjEg

Settings
# exclusive inclusive rows x rows loops node
1. 0.124 2,172.891 ↑ 1.0 101 1

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

2. 47.078 2,172.767 ↑ 2.0 101 1

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

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

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

  • Group Key: u.user_handle
4. 728.345 1,976.370 ↓ 58.8 107,706 1

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

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

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

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

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

  • Workers Planned: 2
  • Workers Launched: 2
7. 26.152 1,222.170 ↓ 50.6 35,051 3

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

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

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

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

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

10. 557.427 615.008 ↓ 42.4 35,051 3

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

11. 31.486 57.567 ↓ 19.4 35,051 3

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

  • Hash Cond: (m.team_id = t.id)
12. 23.113 23.113 ↑ 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.302..23.113 rows=35,051 loops=3)

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

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

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

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

  • Recheck Cond: (plan_id = 'bank-of-america'::text)
  • Heap Blocks: exact=38
15. 0.260 0.260 ↓ 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.259..0.260 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.014 0.014 ↑ 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.014..0.014 rows=1 loops=105,154)

  • Index Cond: (user_handle = m.user_handle)
18. 1.917 1.987 ↓ 1.1 220 3

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

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

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

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

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

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

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

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

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

23. 0.008 0.124 ↓ 1.4 11 1

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

24. 0.039 0.039 ↓ 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.035..0.039 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.052 0.699 ↓ 40.0 40 1

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

28. 0.050 0.367 ↓ 40.0 40 1

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

29. 0.028 0.077 ↓ 40.0 40 1

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

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
32. 0.014 0.024 ↓ 13.3 40 1

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

  • Recheck Cond: (plan_id = 'bank-of-america'::text)
  • Heap Blocks: exact=1
33. 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)
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.280 0.280 ↑ 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.007..0.007 rows=1 loops=40)

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

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

37. 0.468 3.480 ↓ 1.3 440 1

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

38. 0.163 0.372 ↓ 1.3 440 1

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

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

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

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

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

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

Seq Scan on groups g_1 (cost=0.00..6.16 rows=197 width=33) (actual time=0.005..0.049 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.692 34.339 ↓ 60.6 2,061 1

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

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

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

46. 0.833 2.388 ↓ 22.9 2,061 1

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

  • Hash Cond: (m_1.team_id = t_1.id)
47. 0.524 0.524 ↑ 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.524 rows=2,061 loops=1)

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 183kB
49. 0.394 0.589 ↓ 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.203..0.589 rows=2,401 loops=1)

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

  • Index Cond: (plan_id = 'bank-of-america'::text)
51. 16.488 16.488 ↑ 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.008..0.008 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.237 ms
Execution time : 2,176.464 ms