explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GhCh : Old Query

Settings
# exclusive inclusive rows x rows loops node
1. 0.090 2,576.962 ↑ 1.0 100 1

Limit (cost=10,500.45..10,500.70 rows=100 width=429) (actual time=2,576.867..2,576.962 rows=100 loops=1)

2. 67.541 2,576.872 ↑ 2.0 100 1

Sort (cost=10,500.45..10,500.95 rows=200 width=429) (actual time=2,576.865..2,576.872 rows=100 loops=1)

  • Sort Key: (max("*SELECT* 1".first_name)), (max("*SELECT* 1".last_name))
  • Sort Method: top-N heapsort Memory: 108kB
3. 347.173 2,509.331 ↓ 531.0 106,195 1

GroupAggregate (cost=10,410.75..10,492.81 rows=200 width=429) (actual time=2,018.846..2,509.331 rows=106,195 loops=1)

  • Group Key: "*SELECT* 1".user_handle
4. 722.198 2,162.158 ↓ 64.3 107,226 1

Sort (cost=10,410.75..10,414.92 rows=1,668 width=255) (actual time=2,018.819..2,162.158 rows=107,226 loops=1)

  • Sort Key: "*SELECT* 1".user_handle
  • Sort Method: external merge Disk: 19656kB
5. 23.590 1,439.960 ↓ 64.3 107,226 1

Append (cost=1,897.76..10,321.48 rows=1,668 width=255) (actual time=21.428..1,439.960 rows=107,226 loops=1)

6. 34.187 1,370.758 ↓ 63.2 105,154 1

Subquery Scan on *SELECT* 1 (cost=1,897.76..8,997.46 rows=1,664 width=255) (actual time=21.428..1,370.758 rows=105,154 loops=1)

7. 1.860 1,336.571 ↓ 63.2 105,154 1

Gather (cost=1,897.76..8,980.82 rows=1,664 width=255) (actual time=21.427..1,336.571 rows=105,154 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 572.950 1,334.711 ↓ 50.6 35,051 3

Nested Loop (cost=897.76..7,814.42 rows=693 width=255) (actual time=10.261..1,334.711 rows=35,051 loops=3)

9. 687.729 761.747 ↓ 42.4 35,051 3

Nested Loop (cost=897.34..7,198.10 rows=826 width=148) (actual time=10.236..761.747 rows=35,051 loops=3)

10. 30.325 74.000 ↓ 19.4 35,051 3

Hash Join (cost=896.92..5,932.70 rows=1,806 width=125) (actual time=10.214..74.000 rows=35,051 loops=3)

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

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

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 318kB
13. 0.572 0.823 ↓ 1.0 2,401 3

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

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

  • Index Cond: (plan_id = 'bank-of-america'::text)
15. 0.018 0.018 ↑ 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.018..0.018 rows=1 loops=105,154)

  • Index Cond: ((plan_id = 'bank-of-america'::text) AND (user_handle = m.user_handle))
16. 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)
17. 0.472 45.372 ↓ 2,061.0 2,061 1

Subquery Scan on *SELECT* 2 (cost=898.18..1,220.71 rows=1 width=257) (actual time=1.499..45.372 rows=2,061 loops=1)

18. 3.223 44.900 ↓ 2,061.0 2,061 1

Nested Loop (cost=898.18..1,220.70 rows=1 width=257) (actual time=1.498..44.900 rows=2,061 loops=1)

  • Join Filter: (m_1.user_handle = u_1.user_handle)
19. 0.619 29.311 ↓ 687.0 2,061 1

Nested Loop (cost=897.75..1,218.45 rows=3 width=139) (actual time=1.465..29.311 rows=2,061 loops=1)

20. 1.077 12.204 ↓ 515.2 2,061 1

Nested Loop (cost=897.33..1,192.46 rows=4 width=116) (actual time=1.437..12.204 rows=2,061 loops=1)

21. 0.956 2.883 ↓ 22.9 2,061 1

Hash Join (cost=896.92..967.67 rows=90 width=98) (actual time=1.406..2.883 rows=2,061 loops=1)

  • Hash Cond: (m_1.team_id = t_2.id)
22. 0.589 0.589 ↑ 1.0 2,061 1

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

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 183kB
24. 0.488 0.761 ↓ 1.0 2,401 1

Bitmap Heap Scan on teams t_2 (cost=86.99..866.95 rows=2,397 width=33) (actual time=0.282..0.761 rows=2,401 loops=1)

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

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

Index Scan using teams_pkey on teams t_1 (cost=0.41..2.50 rows=1 width=67) (actual time=0.004..0.004 rows=1 loops=2,061)

  • Index Cond: ((plan_id = 'bank-of-america'::text) AND (id = t_2.id))
27. 16.488 16.488 ↑ 1.0 1 2,061

Index Scan using plan_users_pkey on plan_users pu_1 (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))
28. 12.366 12.366 ↑ 1.0 1 2,061

Index Scan using users_pkey on users u_1 (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_1.user_handle)
29. 0.003 0.240 ↓ 3.7 11 1

Subquery Scan on *SELECT* 3 (cost=1.26..103.31 rows=3 width=255) (actual time=0.080..0.240 rows=11 loops=1)

30. 0.009 0.237 ↓ 3.7 11 1

Nested Loop (cost=1.26..103.28 rows=3 width=255) (actual time=0.079..0.237 rows=11 loops=1)

31. 0.011 0.129 ↓ 1.4 11 1

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

32. 0.041 0.041 ↓ 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.037..0.041 rows=11 loops=1)

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

Index Scan using users_pkey on users u_2 (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)
34. 0.099 0.099 ↑ 1.0 1 11

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

  • Index Cond: ((plan_id = 'bank-of-america'::text) AND (user_handle = u_2.user_handle))
Planning time : 3.799 ms
Execution time : 2,580.841 ms