explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WTEW

Settings
# exclusive inclusive rows x rows loops node
1. 74.297 13,209.101 ↓ 147.0 147 1

Unique (cost=5,535.34..5,535.39 rows=1 width=156) (actual time=13,098.571..13,209.101 rows=147 loops=1)

2. 332.807 13,134.804 ↓ 33,222.0 33,222 1

Sort (cost=5,535.34..5,535.35 rows=1 width=156) (actual time=13,098.564..13,134.804 rows=33,222 loops=1)

  • Sort Key: turmaavaliacao.identificadorturma, pessoa.nome, matricula.matricula, matricula.situacao, pessoa.codigo, turmaavaliacao.codigo, disciplina.nome, matriculaperiodo.situacaomatriculaperiodo, atividadediscursivarespostaaluno.codigo, atividadediscursivarespostaaluno.situacaorespostaatividadediscursiva, matriculaperiodoturmadisciplina.codigo, matriculaperiodoturmadisciplina.ano, matriculaperiodoturmadisciplina.semestre, matriculaperiodoturmadisciplina.disciplina, ((SubPlan 1))
  • Sort Method: quicksort Memory: 10361kB
3. 301.446 12,801.997 ↓ 33,222.0 33,222 1

Nested Loop Left Join (cost=290.13..5,535.33 rows=1 width=156) (actual time=17.798..12,801.997 rows=33,222 loops=1)

4. 79.840 341.299 ↓ 33,222.0 33,222 1

Nested Loop (cost=289.85..5,443.58 rows=1 width=119) (actual time=17.070..341.299 rows=33,222 loops=1)

5. 1.213 90.057 ↓ 147.0 147 1

Nested Loop (cost=273.09..5,422.27 rows=1 width=132) (actual time=17.028..90.057 rows=147 loops=1)

6. 1.427 87.227 ↓ 147.0 147 1

Nested Loop (cost=272.81..5,421.97 rows=1 width=102) (actual time=16.998..87.227 rows=147 loops=1)

  • Join Filter: (((matricula.gradecurricularatual = historico.matrizcurricular) AND ((historico.historicocursandoporcorrespondenciaapostransferencia IS NULL) OR (NOT historico.historicocursandoporcorrespondenciaapostransferencia)) AND ((historico.transferenciamatrizcurricularmatricula IS NULL) OR ((historico.transferenciamatrizcurricularmatricula IS NOT NULL) AND (NOT (SubPlan 3))))) OR ((matricula.gradecurricularatual <> historico.matrizcurricular) AND historico.historicocursandoporcorrespondenciaapostransferencia AND (historico.transferenciamatrizcurricularmatricula IS NOT NULL) AND (historico.disciplina = (SubPlan 4)) AND ((NOT historico.historicoporequivalencia) OR (historico.historicoporequivalencia IS NULL))) OR ((matricula.gradecurricularatual <> historico.matrizcurricular) AND historico.historicoequivalente AND (SubPlan 5)) OR ((historico.matrizcurricular = matriculaperiodo.gradecurricular) AND (matricula.gradecurricularatual <> historico.matrizcurricular) AND historico.historicodisciplinafazpartecomposicao AND (NOT (SubPlan 6))))
  • Rows Removed by Join Filter: 2
7. 3.384 79.806 ↓ 148.0 148 1

Hash Left Join (cost=272.39..5,267.87 rows=1 width=110) (actual time=16.939..79.806 rows=148 loops=1)

  • Hash Cond: (atividadediscursiva.turma = turmaavaliacao.codigo)
  • Filter: ((((atividadediscursiva.publicoalvo)::text = 'TURMA'::text) AND (((NOT turmaavaliacao.turmaagrupada) AND (NOT turmaavaliacao.subturma) AND (turmaavaliacao.codigo = matriculaperiodoturmadisciplina.turma)) OR ((matriculaperiodoturmadisciplina.turmapratica IS NOT NULL) AND turmaavaliacao.subturma AND ((turmaavaliacao.tiposubturma)::text = 'PRATICA'::text) AND (turmaavaliacao.codigo = matriculaperiodoturmadisciplina.turmapratica)) OR ((matriculaperiodoturmadisciplina.turmateorica IS NOT NULL) AND turmaavaliacao.subturma AND ((turmaavaliacao.tiposubturma)::text = 'TEORICA'::text) AND (turmaavaliacao.codigo = matriculaperiodoturmadisciplina.turmateorica)) OR (turmaavaliacao.subturma AND ((turmaavaliacao.tiposubturma)::text = 'GERAL'::text) AND (turmaavaliacao.codigo = matriculaperiodoturmadisciplina.turma)) OR (turmaavaliacao.turmaagrupada AND (NOT turmaavaliacao.subturma) AND (alternatives: SubPlan 7 or hashed SubPlan 8)) OR ((matriculaperiodoturmadisciplina.turmapratica IS NOT NULL) AND turmaavaliacao.turmaagrupada AND (alternatives: SubPlan 9 or hashed SubPlan 10)) OR ((matriculaperiodoturmadisciplina.turmateorica IS NOT NULL) AND turmaavaliacao.turmaagrupada AND (alternatives: SubPlan 11 or hashed SubPlan 12)))) OR (((atividadediscursiva.publicoalvo)::text = 'ALUNO'::text) AND (atividadediscursiva.matriculaperiodoturmadisciplina = matriculaperiodoturmadisciplina.codigo)))
  • Rows Removed by Filter: 2
8. 0.497 55.339 ↓ 9.4 150 1

Nested Loop (cost=20.59..5,016.03 rows=16 width=108) (actual time=1.287..55.339 rows=150 loops=1)

9. 0.019 0.019 ↑ 1.0 1 1

Index Scan using atividadediscursiva_pkey on atividadediscursiva (cost=0.28..8.29 rows=1 width=14) (actual time=0.015..0.019 rows=1 loops=1)

  • Index Cond: (codigo = 265)
10. 1.238 54.823 ↓ 9.4 150 1

Nested Loop (cost=20.32..5,007.58 rows=16 width=94) (actual time=1.266..54.823 rows=150 loops=1)

11. 1.952 51.335 ↓ 9.4 150 1

Nested Loop (cost=20.03..4,995.02 rows=16 width=68) (actual time=1.249..51.335 rows=150 loops=1)

12. 1.495 44.583 ↓ 9.4 150 1

Nested Loop (cost=19.74..4,950.81 rows=16 width=57) (actual time=1.219..44.583 rows=150 loops=1)

13. 7.795 40.678 ↓ 7.1 241 1

Nested Loop (cost=19.46..4,940.49 rows=34 width=57) (actual time=1.207..40.678 rows=241 loops=1)

14. 6.812 16.539 ↓ 2.0 2,724 1

Nested Loop (cost=19.16..4,487.92 rows=1,379 width=42) (actual time=1.169..16.539 rows=2,724 loops=1)

15. 0.004 0.040 ↑ 2.0 1 1

Unique (cost=1.40..1.41 rows=2 width=4) (actual time=0.035..0.040 rows=1 loops=1)

16. 0.011 0.036 ↑ 2.0 1 1

Sort (cost=1.40..1.41 rows=2 width=4) (actual time=0.032..0.036 rows=1 loops=1)

  • Sort Key: disciplinaequivalente.equivalente
  • Sort Method: quicksort Memory: 25kB
17. 0.004 0.025 ↑ 2.0 1 1

Append (cost=0.00..1.39 rows=2 width=4) (actual time=0.021..0.025 rows=1 loops=1)

18. 0.017 0.017 ↓ 0.0 0 1

Seq Scan on disciplinaequivalente (cost=0.00..1.35 rows=1 width=4) (actual time=0.016..0.017 rows=0 loops=1)

  • Filter: (disciplina = 1244)
  • Rows Removed by Filter: 28
19. 0.004 0.004 ↑ 1.0 1 1

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

20. 8.940 9.687 ↓ 4.0 2,724 1

Bitmap Heap Scan on matriculaperiodoturmadisciplina (cost=17.76..2,236.37 rows=689 width=38) (actual time=1.125..9.687 rows=2,724 loops=1)

  • Recheck Cond: (disciplina = disciplinaequivalente.equivalente)
  • Heap Blocks: exact=2047
21. 0.747 0.747 ↓ 4.6 3,167 1

Bitmap Index Scan on ch_matriculaperiodoturmadisciplina_disciplina (cost=0.00..17.59 rows=689 width=0) (actual time=0.746..0.747 rows=3,167 loops=1)

  • Index Cond: (disciplina = disciplinaequivalente.equivalente)
22. 16.344 16.344 ↓ 0.0 0 2,724

Index Scan using matriculaperiodo_pkey on matriculaperiodo (cost=0.29..0.33 rows=1 width=23) (actual time=0.006..0.006 rows=0 loops=2,724)

  • Index Cond: (codigo = matriculaperiodoturmadisciplina.matriculaperiodo)
  • Filter: (((ano)::text = '2019'::text) AND ((semestre)::text = '2'::text))
  • Rows Removed by Filter: 1
23. 2.410 2.410 ↑ 1.0 1 241

Index Scan using turma_pkey on turma (cost=0.28..0.30 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=241)

  • Index Cond: (codigo = matriculaperiodoturmadisciplina.turma)
  • Filter: (unidadeensino = 3)
  • Rows Removed by Filter: 0
24. 4.800 4.800 ↑ 1.0 1 150

Index Scan using matricula_pkey on matricula (cost=0.29..2.76 rows=1 width=23) (actual time=0.032..0.032 rows=1 loops=150)

  • Index Cond: ((matricula)::text = (matriculaperiodo.matricula)::text)
25. 2.250 2.250 ↑ 1.0 1 150

Index Scan using pessoa_pkey on pessoa (cost=0.29..0.79 rows=1 width=34) (actual time=0.015..0.015 rows=1 loops=150)

  • Index Cond: (codigo = matricula.aluno)
26. 6.751 15.533 ↑ 1.0 5,463 1

Hash (cost=183.02..183.02 rows=5,502 width=36) (actual time=15.531..15.533 rows=5,463 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 438kB
27. 8.782 8.782 ↑ 1.0 5,463 1

Seq Scan on turma turmaavaliacao (cost=0.00..183.02 rows=5,502 width=36) (actual time=0.009..8.782 rows=5,463 loops=1)

28.          

SubPlan (for Hash Left Join)

29. 1.350 5.550 ↑ 1.0 1 150

Bitmap Heap Scan on turmaagrupada (cost=8.94..12.95 rows=1 width=0) (actual time=0.037..0.037 rows=1 loops=150)

  • Recheck Cond: ((turma = matriculaperiodoturmadisciplina.turma) AND (turmaorigem = turmaavaliacao.codigo))
  • Heap Blocks: exact=148
30. 1.220 4.200 ↓ 0.0 0 150

BitmapAnd (cost=8.94..8.94 rows=1 width=0) (actual time=0.028..0.028 rows=0 loops=150)

31. 1.500 1.500 ↑ 4.0 1 150

Bitmap Index Scan on idx_turmaagrupada_turma (cost=0.00..4.31 rows=4 width=0) (actual time=0.010..0.010 rows=1 loops=150)

  • Index Cond: (turma = matriculaperiodoturmadisciplina.turma)
32. 1.480 1.480 ↓ 1.3 17 148

Bitmap Index Scan on idx_turmaagrupada_turmaorigem (cost=0.00..4.38 rows=13 width=0) (actual time=0.010..0.010 rows=17 loops=148)

  • Index Cond: (turmaorigem = turmaavaliacao.codigo)
33. 0.000 0.000 ↓ 0.0 0

Seq Scan on turmaagrupada turmaagrupada_1 (cost=0.00..68.57 rows=4,057 width=8) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on turmaagrupada turmaagrupada_2 (cost=8.94..12.95 rows=1 width=0) (never executed)

  • Recheck Cond: ((turma = matriculaperiodoturmadisciplina.turmapratica) AND (turmaorigem = turmaavaliacao.codigo))
35. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=8.94..8.94 rows=1 width=0) (never executed)

36. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (turma = matriculaperiodoturmadisciplina.turmapratica)
37. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_turmaagrupada_turmaorigem (cost=0.00..4.38 rows=13 width=0) (never executed)

  • Index Cond: (turmaorigem = turmaavaliacao.codigo)
38. 0.000 0.000 ↓ 0.0 0

Seq Scan on turmaagrupada turmaagrupada_3 (cost=0.00..68.57 rows=4,057 width=8) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on turmaagrupada turmaagrupada_4 (cost=8.94..12.95 rows=1 width=0) (never executed)

  • Recheck Cond: ((turma = matriculaperiodoturmadisciplina.turmateorica) AND (turmaorigem = turmaavaliacao.codigo))
40. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=8.94..8.94 rows=1 width=0) (never executed)

41. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (turma = matriculaperiodoturmadisciplina.turmateorica)
42. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_turmaagrupada_turmaorigem (cost=0.00..4.38 rows=13 width=0) (never executed)

  • Index Cond: (turmaorigem = turmaavaliacao.codigo)
43. 0.000 0.000 ↓ 0.0 0

Seq Scan on turmaagrupada turmaagrupada_5 (cost=0.00..68.57 rows=4,057 width=8) (never executed)

44. 3.552 3.552 ↑ 1.0 1 148

Index Scan using index_historico_matriculaperiodoturmadisciplina on historico (cost=0.42..0.70 rows=1 width=49) (actual time=0.021..0.024 rows=1 loops=148)

  • Index Cond: (matriculaperiodoturmadisciplina = matriculaperiodoturmadisciplina.codigo)
  • Filter: ((((historicocursandoporcorrespondenciaapostransferencia IS NULL) OR (NOT historicocursandoporcorrespondenciaapostransferencia)) AND ((transferenciamatrizcurricularmatricula IS NULL) OR (transferenciamatrizcurricularmatricula IS NOT NULL))) OR (historicocursandoporcorrespondenciaapostransferencia AND (transferenciamatrizcurricularmatricula IS NOT NULL) AND ((NOT historicoporequivalencia) OR (historicoporequivalencia IS NULL))) OR historicoequivalente OR historicodisciplinafazpartecomposicao)
45.          

SubPlan (for Nested Loop)

46. 0.000 0.000 ↓ 0.0 0

Limit (cost=28.81..32.84 rows=1 width=4) (never executed)

47. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on historico his (cost=28.81..32.84 rows=1 width=4) (never executed)

  • Recheck Cond: (((matricula)::text = (historico.matricula)::text) AND (disciplina = historico.disciplina))
  • Filter: (historicocursandoporcorrespondenciaapostransferencia AND (matrizcurricular <> matricula.gradecurricularatual) AND ((anohistorico)::text = (historico.anohistorico)::text) AND ((semestrehistorico)::text = (historico.semestrehistorico)::text) AND (transferenciamatrizcurricularmatricula = historico.transferenciamatrizcurricularmatricula))
48. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=28.81..28.81 rows=1 width=0) (never executed)

49. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_historico_matricula_gradedisciplina (cost=0.00..4.67 rows=33 width=0) (never executed)

  • Index Cond: ((matricula)::text = (historico.matricula)::text)
50. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ch_historico_disciplina (cost=0.00..23.89 rows=995 width=0) (never executed)

  • Index Cond: (disciplina = historico.disciplina)
51. 0.051 2.442 ↑ 1.0 1 3

Limit (cost=28.81..32.84 rows=1 width=4) (actual time=0.807..0.814 rows=1 loops=3)

52. 0.057 2.391 ↑ 1.0 1 3

Bitmap Heap Scan on historico his_1 (cost=28.81..32.84 rows=1 width=4) (actual time=0.797..0.797 rows=1 loops=3)

  • Recheck Cond: (((matricula)::text = (historico.matricula)::text) AND (disciplina = historico.disciplina))
  • Filter: (((historicocursandoporcorrespondenciaapostransferencia IS NULL) OR (NOT historicocursandoporcorrespondenciaapostransferencia)) AND ((anohistorico)::text = (historico.anohistorico)::text) AND ((semestrehistorico)::text = (historico.semestrehistorico)::text) AND (transferenciamatrizcurricularmatricula = historico.transferenciamatrizcurricularmatricula) AND (matrizcurricular = matricula.gradecurricularatual))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=4
53. 0.054 2.334 ↓ 0.0 0 3

BitmapAnd (cost=28.81..28.81 rows=1 width=0) (actual time=0.777..0.778 rows=0 loops=3)

54. 0.174 0.174 ↓ 3.8 127 3

Bitmap Index Scan on idx_historico_matricula_gradedisciplina (cost=0.00..4.67 rows=33 width=0) (actual time=0.057..0.058 rows=127 loops=3)

  • Index Cond: ((matricula)::text = (historico.matricula)::text)
55. 2.106 2.106 ↓ 3.3 3,288 3

Bitmap Index Scan on ch_historico_disciplina (cost=0.00..23.89 rows=995 width=0) (actual time=0.702..0.702 rows=3,288 loops=3)

  • Index Cond: (disciplina = historico.disciplina)
56. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=81.23..85.28 rows=1 width=0) (never executed)

57. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=52.42..52.43 rows=1 width=25) (never executed)

  • Group Key: (his_2.matricula)::text, (his_2.anohistorico)::text, (his_2.semestrehistorico)::text, his_2.disciplina, his_2.transferenciamatrizcurricularmatricula
58. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on historico his_2 (cost=48.39..52.41 rows=1 width=25) (never executed)

  • Recheck Cond: (((matricula)::text = (historico.matricula)::text) AND (matrizcurricular = matricula.gradecurricularatual))
  • Filter: ((historicocursandoporcorrespondenciaapostransferencia IS NULL) OR (NOT historicocursandoporcorrespondenciaapostransferencia))
59. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=48.39..48.39 rows=1 width=0) (never executed)

60. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_historico_matricula_gradedisciplina (cost=0.00..4.67 rows=33 width=0) (never executed)

  • Index Cond: ((matricula)::text = (historico.matricula)::text)
61. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ch_historico_gc (cost=0.00..43.47 rows=2,006 width=0) (never executed)

  • Index Cond: (matrizcurricular = matricula.gradecurricularatual)
62. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on historico hist (cost=28.81..32.84 rows=1 width=25) (never executed)

  • Recheck Cond: (((matricula)::text = (historico.matricula)::text) AND (disciplina = his_2.disciplina))
  • Filter: (historicoporequivalencia AND (historico.mapaequivalenciadisciplina = mapaequivalenciadisciplina) AND (numeroagrupamentoequivalenciadisciplina = historico.numeroagrupamentoequivalenciadisciplina) AND ((his_2.anohistorico)::text = (anohistorico)::text) AND ((his_2.semestrehistorico)::text = (semestrehistorico)::text) AND (his_2.transferenciamatrizcurricularmatricula = transferenciamatrizcurricularmatricula))
63. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=28.81..28.81 rows=1 width=0) (never executed)

64. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_historico_matricula_gradedisciplina (cost=0.00..4.67 rows=33 width=0) (never executed)

  • Index Cond: ((matricula)::text = (historico.matricula)::text)
65. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ch_historico_disciplina (cost=0.00..23.89 rows=995 width=0) (never executed)

  • Index Cond: (disciplina = his_2.disciplina)
66. 0.000 0.000 ↓ 0.0 0

Index Only Scan using unq_historico_disciplina_matrper_matrperturm_grade_gd_gcgod on historico his_3 (cost=0.42..8.45 rows=1 width=4) (never executed)

  • Index Cond: ((disciplina = historico.disciplina) AND (matriculaperiodo = historico.matriculaperiodo) AND (matrizcurricular = matricula.gradecurricularatual))
  • Heap Fetches: 0
67. 1.617 1.617 ↑ 1.0 1 147

Index Scan using disciplina_pkey on disciplina (cost=0.28..0.30 rows=1 width=34) (actual time=0.011..0.011 rows=1 loops=147)

  • Index Cond: (codigo = matriculaperiodoturmadisciplina.disciplina)
68. 171.402 171.402 ↓ 4.0 226 147

Index Scan using ch_turmadisciplina_disciplina on turmadisciplina (cost=16.76..20.76 rows=56 width=23) (actual time=0.021..1.166 rows=226 loops=147)

  • Index Cond: (disciplina = CASE WHEN (NOT COALESCE(matriculaperiodoturmadisciplina.disciplinafazpartecomposicao, false)) THEN disciplina.codigo ELSE (SubPlan 2) END)
  • Filter: ((((definicoestutoriaonline)::text = 'DIMANICA'::text) AND (matriculaperiodoturmadisciplina.professor = 106894)) OR ((definicoestutoriaonline)::text = 'PROGRAMACAO_DE_AULA'::text))
69.          

SubPlan (for Index Scan)

70. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.43..16.47 rows=1 width=4) (never executed)

71. 0.000 0.000 ↓ 0.0 0

Index Scan using gradedisciplinacomposta_pkey on gradedisciplinacomposta (cost=0.15..8.17 rows=1 width=4) (never executed)

  • Index Cond: (codigo = matriculaperiodoturmadisciplina.gradedisciplinacomposta)
72. 0.000 0.000 ↓ 0.0 0

Index Scan using gradedisciplina_pkey on gradedisciplina (cost=0.29..8.30 rows=1 width=8) (never executed)

  • Index Cond: (codigo = gradedisciplinacomposta.gradedisciplina)
73. 232.554 232.554 ↓ 0.0 0 33,222

Index Scan using unique_atividadediscursiva_matriculaperiodoturmadisciplina on atividadediscursivarespostaaluno (cost=0.28..0.30 rows=1 width=33) (actual time=0.007..0.007 rows=0 loops=33,222)

  • Index Cond: ((atividadediscursiva = 265) AND (matriculaperiodoturmadisciplina = matriculaperiodoturmadisciplina.codigo))
74.          

SubPlan (for Nested Loop Left Join)

75. 265.776 11,926.698 ↑ 1.0 1 33,222

Aggregate (cost=91.42..91.43 rows=1 width=8) (actual time=0.358..0.359 rows=1 loops=33,222)

76. 11,660.922 11,660.922 ↓ 0.0 0 33,222

Seq Scan on atividadediscursivainteracao (cost=0.00..91.42 rows=1 width=4) (actual time=0.351..0.351 rows=0 loops=33,222)

  • Filter: ((NOT interacaojalida) AND (atividadediscursivarespostaaluno = atividadediscursivarespostaaluno.codigo) AND ((interagidopor)::text = 'ALUNO'::text))
  • Rows Removed by Filter: 1246
Planning time : 12.816 ms
Execution time : 13,210.191 ms