explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yOZn

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.055 3,405.616 ↑ 1.0 1 1

Aggregate (cost=10,462,344.81..10,462,344.81 rows=1 width=8) (actual time=3,405.616..3,405.616 rows=1 loops=1)

2. 0.115 3,405.561 ↓ 118.0 118 1

Nested Loop (cost=39,810.64..10,462,344.81 rows=1 width=0) (actual time=771.277..3,405.561 rows=118 loops=1)

3. 0.089 3,404.974 ↓ 118.0 118 1

Nested Loop (cost=39,810.59..10,462,341.41 rows=1 width=4) (actual time=771.267..3,404.974 rows=118 loops=1)

4. 0.222 3,404.649 ↓ 118.0 118 1

Nested Loop (cost=39,810.53..10,462,337.68 rows=1 width=8) (actual time=771.262..3,404.649 rows=118 loops=1)

5. 193.666 3,404.191 ↓ 118.0 118 1

Nested Loop (cost=39,810.47..10,462,334.28 rows=1 width=12) (actual time=771.255..3,404.191 rows=118 loops=1)

6. 550.473 920.089 ↓ 1.6 572,609 1

HashAggregate (cost=39,810.39..40,892.88 rows=360,831 width=4) (actual time=758.134..920.089 rows=572,609 loops=1)

  • Group Key: punches.rota_id
7. 369.616 369.616 ↓ 1.1 1,603,036 1

Seq Scan on punches (cost=0.00..39,061.64 rows=1,497,503 width=4) (actual time=0.010..369.616 rows=1,603,036 loops=1)

  • Filter: (adjusted_at IS NULL)
  • Rows Removed by Filter: 564,696
8. 2,200.908 2,290.436 ↓ 0.0 0 572,609

Index Scan using rotas_pkey on rotas (cost=0.09..28.88 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=572,609)

  • Index Cond: (id = punches.rota_id)
  • Filter: ((locked_at IS NULL) AND (actual_job_ends_at IS NOT NULL) AND (time_verified_at IS NULL) AND (deleted_at IS NULL) AND ((status)::text = ANY ('{completed,disputed}'::text[])) AND ((status)::text <> 'draft'::text) AND ((((status)::text = 'completed'::text) AND ((alternatives: SubPlan 5 or hashed SubPlan 6) OR ((alternatives: SubPlan 7 or hashed SubPlan 8) AND (break_minutes = 0)))) OR (((status)::text = 'disputed'::text) AND (alternatives: SubPlan 9 or hashed SubPlan 10))) AND ((user_id = 4,642) OR (alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4)))
  • Rows Removed by Filter: 1
9.          

SubPlan (for Index Scan)

10. 32.275 32.275 ↑ 4.0 1 6,455

Index Scan using punches_rota_id_index on punches punches_1 (cost=0.09..6.34 rows=4 width=0) (actual time=0.005..0.005 rows=1 loops=6,455)

  • Index Cond: (rotas.id = rota_id)
  • Filter: ((latitude IS NULL) OR (longitude IS NULL) OR (has_time_violation IS TRUE) OR (has_location_violation IS TRUE) OR (has_punch_method_violation IS TRUE))
  • Rows Removed by Filter: 1
11. 0.000 0.000 ↓ 0.0 0

Seq Scan on punches punches_2 (cost=0.00..39,061.64 rows=1,405,327 width=4) (never executed)

  • Filter: ((latitude IS NULL) OR (longitude IS NULL) OR (has_time_violation IS TRUE) OR (has_location_violation IS TRUE) OR (has_punch_method_violation IS TRUE))
12. 7.743 7.743 ↓ 0.0 0 2,581

Index Scan using positions_pkey on positions positions_1 (cost=0.06..4.06 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=2,581)

  • Index Cond: (rotas.position_id = id)
  • Filter: ((is_break_required IS TRUE) AND (deleted_at IS NULL))
  • Rows Removed by Filter: 1
13. 0.000 0.000 ↓ 0.0 0

Seq Scan on positions positions_2 (cost=0.00..157.86 rows=63 width=4) (never executed)

  • Filter: ((is_break_required IS TRUE) AND (deleted_at IS NULL))
14. 6.552 6.552 ↑ 1.0 1 1,638

Index Scan using disputes_rota_id_index on disputes (cost=0.08..5.75 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=1,638)

  • Index Cond: (rotas.id = rota_id)
  • Filter: ((created_by_user_id IS NULL) AND (is_auto_disputed IS FALSE) AND (approved_at IS NULL) AND (declined_at IS NULL))
  • Rows Removed by Filter: 0
15. 0.000 0.000 ↓ 0.0 0

Seq Scan on disputes disputes_1 (cost=0.00..9,288.36 rows=227,038 width=4) (never executed)

  • Filter: ((created_by_user_id IS NULL) AND (is_auto_disputed IS FALSE) AND (approved_at IS NULL) AND (declined_at IS NULL))
16. 5.414 27.070 ↓ 0.0 0 5,414

Nested Loop Semi Join (cost=0.11..8.13 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=5,414)

17. 10.828 10.828 ↑ 1.0 1 5,414

Index Only Scan using locations_pkey on locations locations_1 (cost=0.06..4.06 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=5,414)

  • Index Cond: (id = rotas.location_id)
  • Heap Fetches: 5,670
18. 10.828 10.828 ↓ 0.0 0 5,414

Index Scan using employees_user_id_index on employees (cost=0.06..4.06 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=5,414)

  • Index Cond: (user_id = 4,642)
  • Filter: ((can_manage_rotas IS TRUE) AND (deleted_at IS NULL) AND (location_id = rotas.location_id))
  • Rows Removed by Filter: 1
19. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.12..8.13 rows=1 width=4) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Unique (cost=4.06..4.07 rows=1 width=4) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Sort (cost=4.06..4.07 rows=1 width=4) (never executed)

  • Sort Key: employees_1.location_id
22. 0.000 0.000 ↓ 0.0 0

Index Scan using employees_user_id_index on employees employees_1 (cost=0.06..4.06 rows=1 width=4) (never executed)

  • Index Cond: (user_id = 4,642)
  • Filter: ((can_manage_rotas IS TRUE) AND (deleted_at IS NULL))
23. 0.000 0.000 ↓ 0.0 0

Index Only Scan using locations_pkey on locations locations_2 (cost=0.06..4.06 rows=1 width=4) (never executed)

  • Index Cond: (id = employees_1.location_id)
  • Heap Fetches: 0
24. 0.000 15.888 ↓ 0.0 0 5,296

Nested Loop Semi Join (cost=0.11..8.13 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=5,296)

25. 5.296 5.296 ↑ 1.0 1 5,296

Index Only Scan using locations_pkey on locations locations_3 (cost=0.06..4.06 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=5,296)

  • Index Cond: (id = rotas.location_id)
  • Heap Fetches: 5,552
26. 10.592 10.592 ↓ 0.0 0 5,296

Index Scan using employees_user_id_index on employees employees_2 (cost=0.06..4.06 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=5,296)

  • Index Cond: (user_id = 4,642)
  • Filter: ((can_review_time IS TRUE) AND (deleted_at IS NULL) AND (location_id = rotas.location_id))
  • Rows Removed by Filter: 1
27. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.12..8.13 rows=1 width=4) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Unique (cost=4.06..4.07 rows=1 width=4) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Sort (cost=4.06..4.07 rows=1 width=4) (never executed)

  • Sort Key: employees_3.location_id
30. 0.000 0.000 ↓ 0.0 0

Index Scan using employees_user_id_index on employees employees_3 (cost=0.06..4.06 rows=1 width=4) (never executed)

  • Index Cond: (user_id = 4,642)
  • Filter: ((can_review_time IS TRUE) AND (deleted_at IS NULL))
31. 0.000 0.000 ↓ 0.0 0

Index Only Scan using locations_pkey on locations locations_4 (cost=0.06..4.06 rows=1 width=4) (never executed)

  • Index Cond: (id = employees_3.location_id)
  • Heap Fetches: 0
32. 0.236 0.236 ↑ 1.0 1 118

Index Only Scan using positions_pkey on positions (cost=0.06..3.39 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=118)

  • Index Cond: (id = rotas.position_id)
  • Heap Fetches: 118
33. 0.236 0.236 ↑ 1.0 1 118

Index Only Scan using locations_pkey on locations (cost=0.06..3.73 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=118)

  • Index Cond: (id = rotas.location_id)
  • Heap Fetches: 118
34. 0.472 0.472 ↑ 1.0 1 118

Index Only Scan using workers_id_unique on workers (cost=0.06..3.39 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=118)

  • Index Cond: (id = rotas.worker_id)
  • Heap Fetches: 73
Planning time : 1.944 ms
Execution time : 3,410.211 ms