explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 824G

Settings
# exclusive inclusive rows x rows loops node
1. 5.729 118,823.140 ↓ 215.7 647 1

Group (cost=9,529.57..9,529.67 rows=3 width=242) (actual time=63,083.469..118,823.140 rows=647 loops=1)

  • Group Key: aula.datainicio, pessoa.nome, disciplina.nome, historico.codigo, matricula.matricula, mfc.conceitonota, configuracaoacademico.quantidadecasasdecimaispermitiraposvirgula
2. 4.620 63,017.543 ↓ 215.7 647 1

Sort (cost=9,529.57..9,529.58 rows=3 width=230) (actual time=63,016.864..63,017.543 rows=647 loops=1)

  • Sort Key: aula.datainicio, pessoa.nome, disciplina.nome, historico.codigo, matricula.matricula, mfc.conceitonota, configuracaoacademico.quantidadecasasdecimaispermitiraposvirgula
  • Sort Method: quicksort Memory: 176kB
3. 3.191 63,012.923 ↓ 215.7 647 1

Nested Loop Left Join (cost=6,506.07..9,529.55 rows=3 width=230) (actual time=224.633..63,012.923 rows=647 loops=1)

4. 2.830 63,009.085 ↓ 215.7 647 1

Nested Loop (cost=6,505.93..9,529.04 rows=3 width=116) (actual time=224.626..63,009.085 rows=647 loops=1)

5. 0.937 600.267 ↓ 218.0 654 1

Nested Loop (cost=6,505.93..9,528.97 rows=3 width=112) (actual time=161.638..600.267 rows=654 loops=1)

6. 0.020 0.020 ↑ 1.0 1 1

Index Only Scan using turma_pkey on turma (cost=0.28..4.30 rows=1 width=4) (actual time=0.016..0.020 rows=1 loops=1)

  • Index Cond: (codigo = 80482)
  • Heap Fetches: 1
7. 3.659 599.310 ↓ 218.0 654 1

Nested Loop (cost=6,505.64..9,524.64 rows=3 width=116) (actual time=161.618..599.310 rows=654 loops=1)

8. 2.716 582.571 ↓ 218.0 654 1

Nested Loop (cost=6,505.21..9,523.04 rows=3 width=120) (actual time=161.595..582.571 rows=654 loops=1)

9. 2.939 572.661 ↓ 218.0 654 1

Nested Loop (cost=6,504.79..9,519.61 rows=3 width=99) (actual time=161.577..572.661 rows=654 loops=1)

10. 10.820 563.182 ↓ 218.0 654 1

Nested Loop (cost=6,504.51..9,518.71 rows=3 width=59) (actual time=161.562..563.182 rows=654 loops=1)

11. 4.636 547.130 ↓ 218.0 654 1

Hash Join (cost=6,504.38..9,518.26 rows=3 width=59) (actual time=161.531..547.130 rows=654 loops=1)

  • Hash Cond: ((historico.matricula)::text = (matricula.matricula)::text)
  • 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 2))))) OR ((matricula.gradecurricularatual <> historico.matrizcurricular) AND historico.historicocursandoporcorrespondenciaapostransferencia AND (historico.transferenciamatrizcurricularmatricula IS NOT NULL) AND (historico.disciplina = (SubPlan 3)) AND ((NOT historico.historicoporequivalencia) OR (historico.historicoporequivalencia IS NULL))) OR ((matricula.gradecurricularatual <> historico.matrizcurricular) AND historico.historicoequivalente AND (SubPlan 4)) OR ((historico.matrizcurricular = matriculaperiodo.gradecurricular) AND (matricula.gradecurricularatual <> historico.matrizcurricular) AND historico.historicodisciplinafazpartecomposicao AND (NOT (SubPlan 5))))
12. 1.690 381.649 ↓ 1.4 934 1

Nested Loop (cost=0.85..3,012.96 rows=676 width=84) (actual time=0.034..381.649 rows=934 loops=1)

13. 0.781 0.781 ↓ 1.1 46 1

Index Scan using ch_matriculaperiodo_turma on matriculaperiodo (cost=0.42..163.05 rows=42 width=12) (actual time=0.016..0.781 rows=46 loops=1)

  • Index Cond: (turma = 80482)
  • Filter: ((situacao)::text = ANY ('{PF,CO,AT}'::text[]))
14. 379.178 379.178 ↑ 1.1 20 46

Index Scan using idx_historico_matriculaperiodo on historico (cost=0.43..67.65 rows=21 width=76) (actual time=2.669..8.243 rows=20 loops=46)

  • Index Cond: (matriculaperiodo = matriculaperiodo.codigo)
  • Filter: ((matriculaperiodoturmadisciplina IS NOT NULL) AND (historicodisciplinacomposta OR (gradedisciplina IS NOT NULL)))
15. 42.960 160.845 ↓ 1.0 81,583 1

Hash (cost=5,488.57..5,488.57 rows=81,196 width=22) (actual time=160.845..160.845 rows=81,583 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5566kB
16. 117.885 117.885 ↓ 1.0 81,583 1

Seq Scan on matricula (cost=0.00..5,488.57 rows=81,196 width=22) (actual time=0.015..117.885 rows=81,583 loops=1)

  • Filter: ((situacao)::text = 'AT'::text)
  • Rows Removed by Filter: 72772
17.          

SubPlan (for Hash Join)

18. 0.000 0.000 ↓ 0.0 0

Limit (cost=28.12..32.14 rows=1 width=4) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on historico his (cost=28.12..32.14 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))
20. 0.000 0.000 ↓ 0.0 0

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

21. 0.000 0.000 ↓ 0.0 0

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

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

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

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

Limit (cost=28.12..32.14 rows=1 width=4) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on historico his_1 (cost=28.12..32.14 rows=1 width=4) (never executed)

  • 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))
25. 0.000 0.000 ↓ 0.0 0

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

26. 0.000 0.000 ↓ 0.0 0

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

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

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

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

Nested Loop Semi Join (cost=28.55..119.60 rows=1 width=0) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Index Scan using ch_historico_matricula on historico hist (cost=0.43..87.45 rows=1 width=24) (never executed)

  • Index Cond: ((matricula)::text = (historico.matricula)::text)
  • Filter: (historicoporequivalencia AND (historico.mapaequivalenciadisciplina = mapaequivalenciadisciplina) AND (numeroagrupamentoequivalenciadisciplina = historico.numeroagrupamentoequivalenciadisciplina))
30. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on historico his_2 (cost=28.12..32.14 rows=1 width=24) (never executed)

  • Recheck Cond: (((matricula)::text = (historico.matricula)::text) AND (disciplina = hist.disciplina))
  • Filter: (((historicocursandoporcorrespondenciaapostransferencia IS NULL) OR (NOT historicocursandoporcorrespondenciaapostransferencia)) AND (matrizcurricular = matricula.gradecurricularatual) AND ((hist.anohistorico)::text = (anohistorico)::text) AND ((hist.semestrehistorico)::text = (semestrehistorico)::text) AND (hist.transferenciamatrizcurricularmatricula = transferenciamatrizcurricularmatricula))
31. 0.000 0.000 ↓ 0.0 0

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

32. 0.000 0.000 ↓ 0.0 0

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

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

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

  • Index Cond: (disciplina = hist.disciplina)
34. 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.43..4.45 rows=1 width=4) (never executed)

  • Index Cond: ((disciplina = historico.disciplina) AND (matriculaperiodo = historico.matriculaperiodo) AND (matrizcurricular = matricula.gradecurricularatual))
  • Heap Fetches: 0
35. 5.232 5.232 ↑ 1.0 1 654

Index Scan using configuracaoacademico_pkey on configuracaoacademico (cost=0.13..0.15 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=654)

  • Index Cond: (codigo = historico.configuracaoacademico)
36. 6.540 6.540 ↑ 1.0 1 654

Index Scan using disciplina_pkey on disciplina (cost=0.28..0.30 rows=1 width=48) (actual time=0.010..0.010 rows=1 loops=654)

  • Index Cond: (codigo = historico.disciplina)
37. 7.194 7.194 ↑ 1.0 1 654

Index Scan using pessoa_pkey on pessoa (cost=0.42..1.14 rows=1 width=29) (actual time=0.011..0.011 rows=1 loops=654)

  • Index Cond: (codigo = matricula.aluno)
38. 13.080 13.080 ↑ 1.0 1 654

Index Scan using matriculaperiodoturmadisciplina_pkey on matriculaperiodoturmadisciplina (cost=0.43..0.53 rows=1 width=4) (actual time=0.020..0.020 rows=1 loops=654)

  • Index Cond: (codigo = historico.matriculaperiodoturmadisciplina)
  • Filter: ((turmapratica IS NULL) AND (turmateorica IS NULL))
39. 62,405.988 62,405.988 ↑ 1.0 1 654

Function Scan on periodoauladisciplinaaluno aula (cost=0.00..0.02 rows=1 width=4) (actual time=95.421..95.422 rows=1 loops=654)

  • Filter: ((disciplina_codigo IS NOT NULL) AND (datainicio >= '2016-08-04'::date) AND (datainicio <= '2020-01-31'::date))
  • Rows Removed by Filter: 0
40. 0.647 0.647 ↓ 0.0 0 647

Index Scan using pk_configuracaoacademiconotaconceito on configuracaoacademiconotaconceito mfc (cost=0.14..0.16 rows=1 width=122) (actual time=0.001..0.001 rows=0 loops=647)

  • Index Cond: (codigo = historico.mediafinalconceito)
41.          

SubPlan (for Group)

42. 55,799.868 55,799.868 ↑ 1.0 1 647

Function Scan on periodoauladisciplinaaluno (cost=0.00..0.01 rows=1 width=4) (actual time=86.243..86.244 rows=1 loops=647)

Planning time : 10.558 ms
Execution time : 118,824.391 ms