explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2Ojm : Old Query

Settings
# exclusive inclusive rows x rows loops node
1. 0.091 2,401.906 ↑ 1.0 100 1

Limit (cost=10,889.40..10,889.65 rows=100 width=429) (actual time=2,401.810..2,401.906 rows=100 loops=1)

2. 63.396 2,401.815 ↑ 2.0 100 1

Sort (cost=10,889.40..10,889.90 rows=200 width=429) (actual time=2,401.809..2,401.815 rows=100 loops=1)

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

GroupAggregate (cost=10,798.21..10,881.75 rows=200 width=429) (actual time=1,888.327..2,338.419 rows=106,195 loops=1)

  • Group Key: "*SELECT* 1".user_handle
4. 725.886 2,026.495 ↓ 63.0 107,226 1

Sort (cost=10,798.21..10,802.46 rows=1,701 width=255) (actual time=1,888.303..2,026.495 rows=107,226 loops=1)

  • Sort Key: "*SELECT* 1".user_handle
  • Sort Method: external merge Disk: 19648kB
5. 16.728 1,300.609 ↓ 63.0 107,226 1

Append (cost=1,897.76..10,706.93 rows=1,701 width=255) (actual time=21.540..1,300.609 rows=107,226 loops=1)

6. 29.539 1,240.447 ↓ 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.539..1,240.447 rows=105,154 loops=1)

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

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

  • Workers Planned: 2
  • Workers Launched: 2
8. 569.878 1,266.271 ↓ 50.6 35,051 3

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

9. 630.226 696.378 ↓ 42.4 35,051 3

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

10. 32.950 66.136 ↓ 19.4 35,051 3

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

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

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

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

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

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

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

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

  • Index Cond: ((plan_id = 'bank-of-america'::text) AND (user_handle = m.user_handle))
16. 0.015 0.015 ↑ 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.015..0.015 rows=1 loops=105,154)

  • Index Cond: (user_handle = m.user_handle)
17. 0.450 43.194 ↓ 60.6 2,061 1

Subquery Scan on *SELECT* 2 (cost=897.76..1,606.16 rows=34 width=257) (actual time=1.809..43.194 rows=2,061 loops=1)

18. 3.418 42.744 ↓ 60.6 2,061 1

Nested Loop (cost=897.76..1,605.82 rows=34 width=257) (actual time=1.808..42.744 rows=2,061 loops=1)

  • Join Filter: (m_1.user_handle = u_1.user_handle)
19. 1.125 22.838 ↓ 29.0 2,061 1

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

20. 1.010 3.164 ↓ 22.9 2,061 1

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

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

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

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 281kB
23. 0.517 0.794 ↓ 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.285..0.794 rows=2,401 loops=1)

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

  • Index Cond: (plan_id = 'bank-of-america'::text)
25. 18.549 18.549 ↑ 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.009..0.009 rows=1 loops=2,061)

  • Index Cond: ((plan_id = 'bank-of-america'::text) AND (user_handle = m_1.user_handle))
26. 16.488 16.488 ↑ 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.008..0.008 rows=1 loops=2,061)

  • Index Cond: (user_handle = pu_1.user_handle)
27. 0.004 0.240 ↓ 3.7 11 1

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

28. 0.018 0.236 ↓ 3.7 11 1

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

29. 0.009 0.130 ↓ 1.4 11 1

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

30. 0.044 0.044 ↓ 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.040..0.044 rows=11 loops=1)

  • Index Cond: (plan_id = 'bank-of-america'::text)
31. 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)
32. 0.088 0.088 ↑ 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.008..0.008 rows=1 loops=11)

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