explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6h5

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 101.870 ↓ 12.0 12 1

Unique (cost=12,835.54..12,835.56 rows=1 width=113) (actual time=101.858..101.870 rows=12 loops=1)

2. 0.058 101.858 ↓ 12.0 12 1

Sort (cost=12,835.54..12,835.54 rows=1 width=113) (actual time=101.856..101.858 rows=12 loops=1)

  • Sort Key: turma.identificadorturma, disciplina.nome, arquivo.descricaoarquivo, arquivo.datadisponibilizacao, professor.nome, turma.codigo, disciplina.codigo, professor.codigo
  • Sort Method: quicksort Memory: 28kB
3. 0.011 101.800 ↓ 12.0 12 1

Nested Loop Left Join (cost=8,888.75..12,835.53 rows=1 width=113) (actual time=75.620..101.800 rows=12 loops=1)

4. 0.013 101.741 ↓ 12.0 12 1

Nested Loop Left Join (cost=8,888.33..12,829.34 rows=1 width=88) (actual time=75.601..101.741 rows=12 loops=1)

5. 7.730 101.680 ↓ 12.0 12 1

Merge Left Join (cost=8,888.05..12,829.00 rows=1 width=79) (actual time=75.576..101.680 rows=12 loops=1)

  • Merge Cond: (arquivo.disciplina = "*SELECT* 1".disciplina)
  • Filter: ((((("*SELECT* 1".anovigente)::text = ''::text) AND (("*SELECT* 1".semestrevigente)::text = ''::text) AND (NOT arquivo.manterdisponibilizacao)) OR arquivo.manterdisponibilizacao) AND ((arquivo.disciplina = 3726) OR (arquivo.disciplina = "*SELECT* 1".disciplina)) AND (((arquivo.turma IS NULL) AND (arquivo.professor IS NULL)) OR ((arquivo.professor IS NULL) AND (arquivo.turma = "*SELECT* 1".turma)) OR ((arquivo.professor = "*SELECT* 1".professor) AND (arquivo.turma = "*SELECT* 1".turma)) OR ((arquivo.professor = "*SELECT* 1".professor) AND (arquivo.turma IS NULL))))
  • Rows Removed by Filter: 34757
6. 13.244 79.119 ↓ 40.9 34,769 1

Merge Join (cost=0.94..3,939.72 rows=850 width=84) (actual time=0.083..79.119 rows=34,769 loops=1)

  • Merge Cond: (arquivo.disciplina = disciplina.codigo)
7. 63.855 63.855 ↓ 4.1 34,770 1

Index Scan using idx_arquivo_teste_arquivo on arquivo (cost=0.42..36,591.06 rows=8,461 width=44) (actual time=0.066..63.855 rows=34,770 loops=1)

  • Index Cond: (datadisponibilizacao <= CURRENT_TIMESTAMP)
  • Filter: CASE WHEN ((NOT manterdisponibilizacao) AND (NOT apresentardeterminadoperiodo)) THEN false WHEN ((NOT manterdisponibilizacao) AND apresentardeterminadoperiodo) THEN ((dataindisponibilizacao >= CURRENT_TIMESTAMP) OR (dataindisponibilizacao IS NULL)) WHEN (manterdisponibilizacao AND (NOT apresentardeterminadoperiodo)) THEN true ELSE NULL::boolean END
  • Rows Removed by Filter: 62
8. 2.020 2.020 ↑ 1.0 5,266 1

Index Scan using disciplina_pkey on disciplina (cost=0.28..239.47 rows=5,309 width=48) (actual time=0.009..2.020 rows=5,266 loops=1)

9. 0.033 14.831 ↑ 2.0 1 1

Sort (cost=8,887.11..8,887.12 rows=2 width=14) (actual time=14.801..14.831 rows=1 loops=1)

  • Sort Key: "*SELECT* 1".disciplina
  • Sort Method: quicksort Memory: 25kB
10. 0.002 14.798 ↑ 2.0 1 1

Append (cost=8,821.37..8,887.10 rows=2 width=14) (actual time=14.670..14.798 rows=1 loops=1)

11. 0.001 14.672 ↑ 1.0 1 1

Subquery Scan on *SELECT* 1 (cost=8,821.37..8,821.40 rows=1 width=14) (actual time=14.669..14.672 rows=1 loops=1)

12. 0.004 14.671 ↑ 1.0 1 1

Unique (cost=8,821.37..8,821.39 rows=1 width=15) (actual time=14.667..14.671 rows=1 loops=1)

13. 0.018 14.667 ↓ 4.0 4 1

Sort (cost=8,821.37..8,821.38 rows=1 width=15) (actual time=14.666..14.667 rows=4 loops=1)

  • Sort Key: horarioturma.turma, turma_1.turmaagrupada, horarioturmadiaitem.disciplina, horarioturmadiaitem.professor, horarioturma.anovigente, horarioturma.semestrevigente
  • Sort Method: quicksort Memory: 25kB
14. 0.000 14.649 ↓ 4.0 4 1

Nested Loop (cost=7,977.67..8,821.36 rows=1 width=15) (actual time=14.431..14.649 rows=4 loops=1)

  • Join Filter: (((NOT turma_1.turmaagrupada) AND (horarioturmadiaitem.disciplina = 3726)) OR (turma_1.turmaagrupada AND (hashed SubPlan 1)))
15. 0.017 14.310 ↑ 3.4 57 1

Nested Loop (cost=7,970.20..8,034.32 rows=191 width=11) (actual time=14.266..14.310 rows=57 loops=1)

16. 0.002 14.254 ↑ 4.0 1 1

Nested Loop (cost=7,969.78..8,017.17 rows=4 width=11) (actual time=14.253..14.254 rows=1 loops=1)

17. 0.004 14.243 ↑ 6.0 1 1

Nested Loop (cost=7,969.50..8,015.14 rows=6 width=9) (actual time=14.243..14.243 rows=1 loops=1)

18. 0.009 14.228 ↑ 6.0 1 1

HashAggregate (cost=7,969.22..7,969.28 rows=6 width=4) (actual time=14.228..14.228 rows=1 loops=1)

  • Group Key: matriculaperiodoturmadisciplina.turmapratica
19. 0.003 14.219 ↑ 6.0 1 1

Append (cost=0.43..7,969.20 rows=6 width=4) (actual time=0.092..14.219 rows=1 loops=1)

20. 0.009 0.009 ↓ 0.0 0 1

Index Scan using idx_mptd_turmapratica on matriculaperiodoturmadisciplina (cost=0.43..8.45 rows=1 width=4) (actual time=0.009..0.009 rows=0 loops=1)

  • Index Cond: (turmapratica IS NOT NULL)
  • Filter: (((matricula)::text = '053DT0000656'::text) AND (disciplina = 3726))
21. 0.007 0.007 ↓ 0.0 0 1

Index Scan using idx_mptd_turmateorica on matriculaperiodoturmadisciplina matriculaperiodoturmadisciplina_1 (cost=0.43..8.45 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: (turmateorica IS NOT NULL)
  • Filter: (((matricula)::text = '053DT0000656'::text) AND (disciplina = 3726))
22. 5.220 5.220 ↑ 1.0 1 1

Index Scan using unique_matriculaperiodoturmadisciplina_disc_matriculaperi_turma on matriculaperiodoturmadisciplina matriculaperiodoturmadisciplina_2 (cost=0.43..1,945.75 rows=1 width=4) (actual time=0.075..5.220 rows=1 loops=1)

  • Index Cond: (disciplina = 3726)
  • Filter: ((turmateorica IS NULL) AND (turmapratica IS NULL) AND ((matricula)::text = '053DT0000656'::text))
  • Rows Removed by Filter: 3013
23. 0.001 2.688 ↓ 0.0 0 1

Nested Loop (cost=0.71..2,002.15 rows=1 width=4) (actual time=2.687..2.688 rows=0 loops=1)

24. 0.002 2.687 ↓ 0.0 0 1

Nested Loop (cost=0.43..2,001.65 rows=1 width=4) (actual time=2.687..2.687 rows=0 loops=1)

  • Join Filter: (matriculaperiodoturmadisciplina_3.turmapratica = turmaagrupada.turma)
25. 2.679 2.679 ↑ 1.0 1 1

Index Scan using unique_matriculaperiodoturmadisciplina_disc_matriculaperi_turma on matriculaperiodoturmadisciplina matriculaperiodoturmadisciplina_3 (cost=0.43..1,945.75 rows=1 width=4) (actual time=0.039..2.679 rows=1 loops=1)

  • Index Cond: (disciplina = 3726)
  • Filter: ((matricula)::text = '053DT0000656'::text)
  • Rows Removed by Filter: 3013
26. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on turmaagrupada (cost=0.00..30.40 rows=2,040 width=8) (actual time=0.006..0.006 rows=0 loops=1)

27. 0.000 0.000 ↓ 0.0 0

Index Scan using turma_pkey on turma turma_2 (cost=0.28..0.50 rows=1 width=4) (never executed)

  • Index Cond: (codigo = turmaagrupada.turmaorigem)
  • Filter: ((situacao)::text = 'AB'::text)
28. 0.000 2.648 ↓ 0.0 0 1

Nested Loop (cost=0.71..2,002.15 rows=1 width=4) (actual time=2.648..2.648 rows=0 loops=1)

29. 0.002 2.648 ↓ 0.0 0 1

Nested Loop (cost=0.43..2,001.65 rows=1 width=4) (actual time=2.648..2.648 rows=0 loops=1)

  • Join Filter: (matriculaperiodoturmadisciplina_4.turmateorica = turmaagrupada_1.turma)
30. 2.644 2.644 ↑ 1.0 1 1

Index Scan using unique_matriculaperiodoturmadisciplina_disc_matriculaperi_turma on matriculaperiodoturmadisciplina matriculaperiodoturmadisciplina_4 (cost=0.43..1,945.75 rows=1 width=4) (actual time=0.033..2.644 rows=1 loops=1)

  • Index Cond: (disciplina = 3726)
  • Filter: ((matricula)::text = '053DT0000656'::text)
  • Rows Removed by Filter: 3013
31. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on turmaagrupada turmaagrupada_1 (cost=0.00..30.40 rows=2,040 width=8) (actual time=0.002..0.002 rows=0 loops=1)

32. 0.000 0.000 ↓ 0.0 0

Index Scan using turma_pkey on turma turma_3 (cost=0.28..0.50 rows=1 width=4) (never executed)

  • Index Cond: (codigo = turmaagrupada_1.turmaorigem)
  • Filter: ((situacao)::text = 'AB'::text)
33. 0.000 3.644 ↓ 0.0 0 1

Nested Loop (cost=0.71..2,002.15 rows=1 width=4) (actual time=3.644..3.644 rows=0 loops=1)

34. 0.001 3.644 ↓ 0.0 0 1

Nested Loop (cost=0.43..2,001.65 rows=1 width=4) (actual time=3.644..3.644 rows=0 loops=1)

  • Join Filter: (matriculaperiodoturmadisciplina_5.turma = turmaagrupada_2.turma)
35. 3.641 3.641 ↑ 1.0 1 1

Index Scan using unique_matriculaperiodoturmadisciplina_disc_matriculaperi_turma on matriculaperiodoturmadisciplina matriculaperiodoturmadisciplina_5 (cost=0.43..1,945.75 rows=1 width=4) (actual time=0.042..3.641 rows=1 loops=1)

  • Index Cond: (disciplina = 3726)
  • Filter: ((turmateorica IS NULL) AND (turmapratica IS NULL) AND ((matricula)::text = '053DT0000656'::text))
  • Rows Removed by Filter: 3013
36. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on turmaagrupada turmaagrupada_2 (cost=0.00..30.40 rows=2,040 width=8) (actual time=0.002..0.002 rows=0 loops=1)

37. 0.000 0.000 ↓ 0.0 0

Index Scan using turma_pkey on turma turma_4 (cost=0.28..0.50 rows=1 width=4) (never executed)

  • Index Cond: (codigo = turmaagrupada_2.turmaorigem)
  • Filter: ((situacao)::text = 'AB'::text)
38. 0.011 0.011 ↑ 1.0 1 1

Index Scan using turma_pkey on turma turma_1 (cost=0.28..7.63 rows=1 width=5) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: (codigo = matriculaperiodoturmadisciplina.turmapratica)
39. 0.009 0.009 ↑ 1.0 1 1

Index Scan using ch_horarioturma_turma_ano_semestre on horarioturma (cost=0.28..0.33 rows=1 width=10) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (turma = turma_1.codigo)
40. 0.039 0.039 ↓ 1.1 57 1

Index Scan using fk_horarioturmadia_horarioturma on horarioturmadia (cost=0.42..3.75 rows=54 width=8) (actual time=0.010..0.039 rows=57 loops=1)

  • Index Cond: (horarioturma = horarioturma.codigo)
41. 0.315 0.342 ↓ 0.0 0 57

Index Scan using idx_horturdiaitem_horturmdia on horarioturmadiaitem (cost=3.94..4.09 rows=1 width=12) (actual time=0.005..0.006 rows=0 loops=57)

  • Index Cond: (horarioturmadia = horarioturmadia.codigo)
  • Filter: ((disciplina = 3726) OR (hashed SubPlan 1))
  • Rows Removed by Filter: 1
42.          

SubPlan (forIndex Scan)

43. 0.003 0.027 ↑ 3.0 1 1

HashAggregate (cost=3.49..3.52 rows=3 width=4) (actual time=0.027..0.027 rows=1 loops=1)

  • Group Key: (3726)
44. 0.001 0.024 ↑ 3.0 1 1

Append (cost=0.00..3.48 rows=3 width=4) (actual time=0.001..0.024 rows=1 loops=1)

45. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

46. 0.014 0.014 ↓ 0.0 0 1

Seq Scan on disciplinaequivalente (cost=0.00..1.71 rows=1 width=4) (actual time=0.014..0.014 rows=0 loops=1)

  • Filter: (equivalente = 3726)
  • Rows Removed by Filter: 57
47. 0.008 0.008 ↓ 0.0 0 1

Seq Scan on disciplinaequivalente disciplinaequivalente_1 (cost=0.00..1.71 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)

  • Filter: (disciplina = 3726)
  • Rows Removed by Filter: 57
48. 0.001 0.124 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=1.00..65.69 rows=1 width=14) (actual time=0.124..0.124 rows=0 loops=1)

49. 0.000 0.123 ↓ 0.0 0 1

Nested Loop (cost=1.00..65.68 rows=1 width=15) (actual time=0.123..0.123 rows=0 loops=1)

50. 0.001 0.123 ↓ 0.0 0 1

Nested Loop (cost=0.57..58.10 rows=1 width=12) (actual time=0.123..0.123 rows=0 loops=1)

51. 0.122 0.122 ↓ 0.0 0 1

Index Scan using ch_turmadisciplina_disciplina on turmadisciplina (cost=0.29..49.80 rows=1 width=8) (actual time=0.122..0.122 rows=0 loops=1)

  • Index Cond: (disciplina = 3726)
  • Filter: ((definicoestutoriaonline)::text = 'DINAMICA'::text)
  • Rows Removed by Filter: 68
52. 0.000 0.000 ↓ 0.0 0

Index Only Scan using turma_pkey on turma turma_5 (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: (codigo = turmadisciplina.turma)
  • Heap Fetches: 0
53. 0.000 0.000 ↓ 0.0 0

Index Scan using matriculaperiodoturmadisciplina_turma_disciplina_matricula on matriculaperiodoturmadisciplina matriculaperiodoturmadisciplina_6 (cost=0.43..7.57 rows=1 width=14) (never executed)

  • Index Cond: ((turma = turma_5.codigo) AND (disciplina = 3726) AND ((matricula)::text = '053DT0000656'::text))
  • Filter: ((professor IS NOT NULL) AND ((modalidadedisciplina)::text = 'ON_LINE'::text))
54. 0.048 0.048 ↑ 1.0 1 12

Index Scan using turma_pkey on turma (cost=0.28..0.35 rows=1 width=13) (actual time=0.004..0.004 rows=1 loops=12)

  • Index Cond: (arquivo.turma = codigo)
55. 0.048 0.048 ↑ 1.0 1 12

Index Scan using pessoa_pkey on pessoa professor (cost=0.42..6.19 rows=1 width=29) (actual time=0.004..0.004 rows=1 loops=12)

  • Index Cond: (arquivo.professor = codigo)
Planning time : 11.112 ms
Execution time : 102.354 ms