explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7WFo : Optimization for: plan #ACqB

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=52,412,156.91..52,412,157.04 rows=51 width=231) (never executed)

  • Sort Key: (max(a.fecha))
2. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=52,412,154.45..52,412,155.47 rows=51 width=231) (never executed)

  • Group Key: a.oficina, a.localidad
3. 0.000 0.000 ↓ 0.0

Sort (cost=52,412,154.45..52,412,154.57 rows=51 width=231) (never executed)

  • Sort Key: a.oficina, a.localidad
4. 0.000 0.000 ↓ 0.0

Subquery Scan on a (cost=52,412,151.85..52,412,153 rows=51 width=231) (never executed)

5. 0.000 0.000 ↓ 0.0

Unique (cost=52,412,151.85..52,412,152.49 rows=51 width=239) (never executed)

6. 0.000 0.000 ↓ 0.0

Sort (cost=52,412,151.85..52,412,151.98 rows=51 width=239) (never executed)

  • Sort Key: ((t1.fecha_hora)::date), o.oficina, l.localidad, ((((SubPlan 1) / (SubPlan 2)) * 100))
7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..52,412,150.41 rows=51 width=239) (never executed)

8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.14..32.12 rows=6 width=231) (never executed)

9. 0.000 0.000 ↓ 0.0

Index Scan using oficina_pkey on oficina o (cost=0.14..27.31 rows=224 width=57) (never executed)

10. 0.000 0.000 ↓ 0.0

Materialize (cost=0..1.45 rows=1 width=182) (never executed)

11. 0.000 0.000 ↓ 0.0

Seq Scan on localidad l (cost=0..1.45 rows=1 width=182) (never executed)

  • Filter: (l.circunscripcion_id = 4)
12. 0.000 0.000 ↓ 0.0

Index Scan using idx_e79767628a8639b7 on turno t1 (cost=0.42..8,690,718.34 rows=8 width=12) (never executed)

  • Index Cond: (t1.oficina_id = o.id)
  • Filter: ((((SubPlan 3) / (SubPlan 4)) * 100) = 100)
13.          

SubPlan (for Index Scan)

14. 0.000 0.000 ↓ 0.0

Aggregate (cost=2,625.54..2,625.55 rows=1 width=8) (never executed)

15. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on turno t2_2 (cost=32.84..2,625.54 rows=2 width=0) (never executed)

  • Filter: ((t2_2.persona_id IS NOT NULL) AND ((t2_2.fecha_hora)::date = (t1.fecha_hora)::date))
16. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_e79767628a8639b7 (cost=0..32.84 rows=1,655 width=0) (never executed)

  • Index Cond: (t2_2.oficina_id = t1.oficina_id)
17. 0.000 0.000 ↓ 0.0

Aggregate (cost=2,625.56..2,625.57 rows=1 width=8) (never executed)

18. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on turno t2_3 (cost=32.84..2,625.54 rows=8 width=0) (never executed)

  • Filter: ((t2_3.fecha_hora)::date = (t1.fecha_hora)::date)
19. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_e79767628a8639b7 (cost=0..32.84 rows=1,655 width=0) (never executed)

  • Index Cond: (t2_3.oficina_id = t1.oficina_id)
20.          

SubPlan (for Nested Loop)

21. 0.000 0.000 ↓ 0.0

Aggregate (cost=2,625.54..2,625.55 rows=1 width=8) (never executed)

22. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on turno t2 (cost=32.84..2,625.54 rows=2 width=0) (never executed)

  • Filter: ((t2.persona_id IS NOT NULL) AND ((t2.fecha_hora)::date = (t1.fecha_hora)::date))
23. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_e79767628a8639b7 (cost=0..32.84 rows=1,655 width=0) (never executed)

  • Index Cond: (t2.oficina_id = t1.oficina_id)
24. 0.000 0.000 ↓ 0.0

Aggregate (cost=2,625.56..2,625.57 rows=1 width=8) (never executed)

25. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on turno t2_1 (cost=32.84..2,625.54 rows=8 width=0) (never executed)

  • Filter: ((t2_1.fecha_hora)::date = (t1.fecha_hora)::date)
26. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_e79767628a8639b7 (cost=0..32.84 rows=1,655 width=0) (never executed)

  • Index Cond: (t2_1.oficina_id = t1.oficina_id)