explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rM3H

Settings
# exclusive inclusive rows x rows loops node
1. 0.122 2,627.357 ↑ 1.0 100 1

Limit (cost=10,900.04..10,900.29 rows=100 width=429) (actual time=2,627.229..2,627.357 rows=100 loops=1)

2. 89.553 2,627.235 ↑ 2.0 100 1

Sort (cost=10,900.04..10,900.54 rows=200 width=429) (actual time=2,627.228..2,627.235 rows=100 loops=1)

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

GroupAggregate (cost=10,809.25..10,892.39 rows=200 width=429) (actual time=2,005.362..2,537.682 rows=106,195 loops=1)

  • Group Key: "*SELECT* 1".user_handle
4. 714.610 2,168.418 ↓ 63.4 107,226 1

Sort (cost=10,809.25..10,813.48 rows=1,692 width=255) (actual time=2,005.334..2,168.418 rows=107,226 loops=1)

  • Sort Key: "*SELECT* 1".user_handle
  • Sort Method: external merge Disk: 19648kB
5. 12.586 1,453.808 ↓ 63.4 107,226 1

Append (cost=1,897.76..10,718.52 rows=1,692 width=255) (actual time=22.821..1,453.808 rows=107,226 loops=1)

6. 26.123 1,397.272 ↓ 63.5 105,154 1

Subquery Scan on *SELECT* 1 (cost=1,897.76..9,008.18 rows=1,655 width=255) (actual time=22.820..1,397.272 rows=105,154 loops=1)

7. 35.832 1,371.149 ↓ 63.5 105,154 1

Gather (cost=1,897.76..8,991.63 rows=1,655 width=255) (actual time=22.820..1,371.149 rows=105,154 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 651.695 1,335.317 ↓ 50.8 35,051 3

Nested Loop (cost=897.76..7,826.13 rows=690 width=255) (actual time=10.002..1,335.317 rows=35,051 loops=3)

  • Join Filter: ((m.user_handle = u.user_handle) AND ((u.first_name ~~* '%a%'::text) OR (u.last_name ~~* '%a%'::text) OR (u.email ~~* '%a%'::text) OR (pu.note ~~* '
9. 620.323 683.607 ↓ 42.4 35,051 3

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

10. 28.412 63.268 ↓ 19.4 35,051 3

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

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

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

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

Hash (cost=866.95..866.95 rows=2,397 width=68) (actual time=1.553..1.554 rows=2,401 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 283kB
13. 0.573 0.821 ↓ 1.0 2,401 3

Bitmap Heap Scan on teams t (cost=86.99..866.95 rows=2,397 width=68) (actual time=0.255..0.821 rows=2,401 loops=3)

  • Recheck Cond: (plan_id = 'bank-of-america'::text)
  • Heap Blocks: exact=38
14. 0.248 0.248 ↓ 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.248..0.248 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.74 rows=1 width=83) (actual time=0.015..0.015 rows=1 loops=105,154)

  • Index Cond: (user_handle = pu.user_handle)
17. 0.520 43.649 ↓ 60.6 2,061 1

Subquery Scan on *SELECT* 2 (cost=897.76..1,606.87 rows=34 width=257) (actual time=1.842..43.649 rows=2,061 loops=1)

18. 1.607 43.129 ↓ 60.6 2,061 1

Nested Loop (cost=897.76..1,606.53 rows=34 width=257) (actual time=1.840..43.129 rows=2,061 loops=1)

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

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

20. 1.122 3.468 ↓ 22.9 2,061 1

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

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

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

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

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

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

  • Recheck Cond: (plan_id = 'bank-of-america'::text)
  • Heap Blocks: exact=38
24. 0.304 0.304 ↓ 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.304..0.304 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. 18.549 18.549 ↑ 1.0 1 2,061

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

  • Index Cond: (user_handle = pu_1.user_handle)
  • Filter: ((first_name ~~* '%a%'::text) OR (last_name ~~* '%a%'::text) OR (email ~~* '%a%'::text) OR (pu_1.note ~~* '%a%'::text))
27. 0.004 0.301 ↓ 3.7 11 1

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

28. 0.026 0.297 ↓ 3.7 11 1

Nested Loop (cost=1.26..103.44 rows=3 width=255) (actual time=0.081..0.297 rows=11 loops=1)

29. 0.010 0.139 ↓ 1.4 11 1

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

30. 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.036..0.041 rows=11 loops=1)

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

  • Index Cond: (user_handle = a.user_handle)
32. 0.132 0.132 ↑ 1.0 1 11

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

  • 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))
Planning time : 6.666 ms
Execution time : 2,631.491 ms