explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UGbX

Settings
# exclusive inclusive rows x rows loops node
1. 0.034 125.877 ↑ 131.3 15 1

Sort (cost=31,925.18..31,930.10 rows=1,969 width=128) (actual time=125.874..125.877 rows=15 loops=1)

  • Sort Key: anon_2.anon_1_coach_alerts_active_time, anon_2.anon_1_coach_alerts_created_at
  • Sort Method: quicksort Memory: 28kB
2. 0.007 125.843 ↑ 131.3 15 1

Subquery Scan on anon_2 (cost=31,778.06..31,817.44 rows=1,969 width=128) (actual time=125.824..125.843 rows=15 loops=1)

3. 0.049 125.836 ↑ 131.3 15 1

HashAggregate (cost=31,778.06..31,797.75 rows=1,969 width=230) (actual time=125.822..125.836 rows=15 loops=1)

  • Group Key: coach_alerts.id, coach_alerts.created_at, coach_alerts.updated_at, coach_alerts.user_id, coach_alerts.provider_id, coach_alerts.worker_id, coach_alerts.process_ref_name, coach_al
4. 0.010 125.787 ↑ 131.3 15 1

Append (cost=27,242.35..31,699.30 rows=1,969 width=230) (actual time=123.037..125.787 rows=15 loops=1)

5. 0.008 121.853 ↓ 0.0 0 1

HashAggregate (cost=27,242.35..27,261.41 rows=1,906 width=230) (actual time=121.853..121.853 rows=0 loops=1)

  • Group Key: coach_alerts.id, coach_alerts.created_at, coach_alerts.updated_at, coach_alerts.user_id, coach_alerts.provider_id, coach_alerts.worker_id, coach_alerts.process_ref_na
6. 0.002 121.845 ↓ 0.0 0 1

Append (cost=12,822.54..27,166.11 rows=1,906 width=230) (actual time=121.845..121.845 rows=0 loops=1)

7. 0.005 91.763 ↓ 0.0 0 1

Sort (cost=12,822.54..12,826.99 rows=1,781 width=193) (actual time=91.763..91.763 rows=0 loops=1)

  • Sort Key: coach_alerts.active_time
  • Sort Method: quicksort Memory: 25kB
8. 0.015 91.758 ↓ 0.0 0 1

Nested Loop (cost=5.45..12,726.38 rows=1,781 width=193) (actual time=91.758..91.758 rows=0 loops=1)

  • Join Filter: ((roles.role = coach_alerts.role) OR (coach_alerts.role IS NULL))
  • Rows Removed by Join Filter: 12
9. 1.877 91.713 ↑ 116.3 6 1

Nested Loop (cost=1.14..12,679.61 rows=698 width=209) (actual time=74.147..91.713 rows=6 loops=1)

10. 4.926 4.926 ↓ 1.0 2,426 1

Nested Loop (cost=0.71..3,332.59 rows=2,335 width=32) (actual time=0.074..4.926 rows=2,426 loops=1)

  • -> Index Scan using ix_worker_interest_groups_worker_id on worker_interest_groups (cost=0.28..8.30 rows=1 width=53) (actual time=0.033..0.034 row
  • Index Cond: (worker_id = '711f8ede-b002-4906-9bc7-bbfcf2b823c4'::uuid)
  • -> Index Scan using ix_user_interest_groups_group_id on user_interest_groups (cost=0.42..3306.80 rows=1749 width=53) (actual time=0.036..3.289 ro
  • Index Cond: (group_id = worker_interest_groups.group_id)
11. 84.910 84.910 ↓ 0.0 0 2,426

Index Scan using ix_coach_alerts_user_id on coach_alerts (cost=0.43..3.96 rows=4 width=193) (actual time=0.035..0.035 rows=0 loops=2,426)

  • Index Cond: (user_id = user_interest_groups.user_id)
  • Filter: ((finished_time IS NULL) AND (worker_id IS NULL) AND (process_ref_name IS NULL) AND (priority > 0) AND (active_time < '2020-01-13 23:00:01.
  • Rows Removed by Filter: 24
12. 0.011 0.030 ↑ 1.5 2 6

Materialize (cost=4.31..15.37 rows=3 width=30) (actual time=0.003..0.005 rows=2 loops=6)

13. 0.010 0.019 ↑ 1.5 2 1

Bitmap Heap Scan on roles (cost=4.31..15.35 rows=3 width=30) (actual time=0.016..0.019 rows=2 loops=1)

  • Recheck Cond: (worker_id = '711f8ede-b002-4906-9bc7-bbfcf2b823c4'::uuid)
  • Heap Blocks: exact=1
14. 0.009 0.009 ↑ 1.5 2 1

Bitmap Index Scan on ix_roles_worker_id (cost=0.00..4.31 rows=3 width=0) (actual time=0.009..0.009 rows=2 loops=1)

  • Index Cond: (worker_id = '711f8ede-b002-4906-9bc7-bbfcf2b823c4'::uuid)
15. 0.016 30.080 ↓ 0.0 0 1

Sort (cost=14,319.74..14,320.05 rows=125 width=193) (actual time=30.080..30.080 rows=0 loops=1)

  • Sort Key: coach_alerts_1.active_time
  • Sort Method: quicksort Memory: 25kB
16. 0.000 30.064 ↓ 0.0 0 1

Nested Loop (cost=1,009.65..14,315.38 rows=125 width=193) (actual time=30.064..30.064 rows=0 loops=1)

  • Join Filter: ((roles_1.role = coach_alerts_1.role) OR (coach_alerts_1.role IS NULL))
17. 4.874 30.121 ↓ 0.0 0 1

Gather (cost=1,005.34..14,297.82 rows=49 width=209) (actual time=30.062..30.121 rows=0 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
18. 0.002 25.247 ↓ 0.0 0 2 / 2

Nested Loop (cost=5.34..13,292.92 rows=29 width=209) (actual time=25.247..25.247 rows=0 loops=2)

19. 25.245 25.245 ↓ 0.0 0 2 / 2

Parallel Seq Scan on direct_worker_interests (cost=0.00..3,742.35 rows=20 width=32) (actual time=25.245..25.245 rows=0 loops=2)

  • Filter: (worker_id = '711f8ede-b002-4906-9bc7-bbfcf2b823c4'::uuid)
  • Rows Removed by Filter: 81616
20. 0.000 0.000 ↓ 0.0 0 / 2

Bitmap Heap Scan on coach_alerts coach_alerts_1 (cost=5.34..477.49 rows=4 width=193) (never executed)

  • Recheck Cond: (user_id = direct_worker_interests.user_id)
  • Filter: ((finished_time IS NULL) AND (worker_id IS NULL) AND (process_ref_name IS NULL) AND (priority > 0) AND (active_time < '2020-01-13 23:
21. 0.000 0.000 ↓ 0.0 0 / 2

Bitmap Index Scan on ix_coach_alerts_user_id (cost=0.00..5.34 rows=121 width=0) (never executed)

  • Index Cond: (user_id = direct_worker_interests.user_id)
22. 0.000 0.000 ↓ 0.0 0

Materialize (cost=4.31..15.37 rows=3 width=30) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on roles roles_1 (cost=4.31..15.35 rows=3 width=30) (never executed)

  • Recheck Cond: (worker_id = '711f8ede-b002-4906-9bc7-bbfcf2b823c4'::uuid)
24. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ix_roles_worker_id (cost=0.00..4.31 rows=3 width=0) (never executed)

  • Index Cond: (worker_id = '711f8ede-b002-4906-9bc7-bbfcf2b823c4'::uuid)
25. 0.003 0.066 ↓ 0.0 0 1

Nested Loop (cost=5.03..142.17 rows=5 width=193) (actual time=0.066..0.066 rows=0 loops=1)

  • Join Filter: ((roles_2.role = coach_alerts_2.role) OR (coach_alerts_2.role IS NULL))
26. 0.006 0.019 ↑ 1.5 2 1

Bitmap Heap Scan on roles roles_2 (cost=4.31..15.35 rows=3 width=30) (actual time=0.018..0.019 rows=2 loops=1)

  • Recheck Cond: (worker_id = '711f8ede-b002-4906-9bc7-bbfcf2b823c4'::uuid)
  • Heap Blocks: exact=1
27. 0.013 0.013 ↑ 1.5 2 1

Bitmap Index Scan on ix_roles_worker_id (cost=0.00..4.31 rows=3 width=0) (actual time=0.013..0.013 rows=2 loops=1)

  • Index Cond: (worker_id = '711f8ede-b002-4906-9bc7-bbfcf2b823c4'::uuid)
28. 0.005 0.044 ↓ 0.0 0 2

Materialize (cost=0.71..126.74 rows=2 width=209) (actual time=0.022..0.022 rows=0 loops=2)

29. 0.005 0.039 ↓ 0.0 0 1

Nested Loop (cost=0.71..126.73 rows=2 width=209) (actual time=0.039..0.039 rows=0 loops=1)

30. 0.009 0.009 ↑ 1.0 1 1

Index Scan using ix_provider_workers_worker_id on provider_workers (cost=0.28..8.30 rows=1 width=32) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: (worker_id = '711f8ede-b002-4906-9bc7-bbfcf2b823c4'::uuid)
31. 0.025 0.025 ↓ 0.0 0 1

Index Scan using ix_coach_alerts_provider_id on coach_alerts coach_alerts_2 (cost=0.43..118.42 rows=1 width=193) (actual time=0.025..0.025 rows=0 loops=1)

  • Index Cond: ((provider_id = provider_workers.provider_id) AND (provider_id IS NOT NULL))
  • Filter: ((finished_time IS NULL) AND (priority > 0) AND (active_time < '2020-01-13 23:00:01.023325'::timestamp without time zone) AND ((worker_id = '711f8ede-b
  • Rows Removed by Filter: 9
32. 3.012 3.858 ↑ 3.9 15 1

Bitmap Heap Scan on coach_alerts coach_alerts_3 (cost=48.86..4,256.97 rows=58 width=193) (actual time=1.116..3.858 rows=15 loops=1)

  • Recheck Cond: (worker_id = '711f8ede-b002-4906-9bc7-bbfcf2b823c4'::uuid)
  • Filter: ((finished_time IS NULL) AND (priority > 0) AND (active_time < '2020-01-13 23:00:01.022988'::timestamp without time zone))
  • Rows Removed by Filter: 2036
  • Heap Blocks: exact=1404
33. 0.846 0.846 ↓ 2.0 2,191 1

Bitmap Index Scan on ix_coach_alerts_worker_id (cost=0.00..48.85 rows=1,122 width=0) (actual time=0.846..0.846 rows=2,191 loops=1)

  • Index Cond: (worker_id = '711f8ede-b002-4906-9bc7-bbfcf2b823c4'::uuid)
Planning time : 4.096 ms
Execution time : 126.337 ms