explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LPTu

Settings
# exclusive inclusive rows x rows loops node
1. 16.647 5,732.460 ↑ 2.6 22,701 1

Unique (cost=77,588,540.48..77,592,884.46 rows=59,917 width=538) (actual time=5,713.888..5,732.460 rows=22,701 loops=1)

2. 202.293 5,715.813 ↑ 2.6 22,701 1

Sort (cost=77,588,540.48..77,588,690.27 rows=59,917 width=538) (actual time=5,713.887..5,715.813 rows=22,701 loops=1)

  • Sort Key: turmaavaliacao.identificadorturma, pessoa.nome, matricula.matricula, matricula.situacao, pessoa.codigo, matriculaperiodo.situacaomatriculaperiodo, atividadediscursivarespostaaluno.codigo, turmaavaliacao.codigo, turmaavaliacao.anual, turmaavaliacao.semestral, atividadediscursivarespostaaluno.situacaorespostaatividadediscursiva, matriculaperiodoturmadisciplina.codigo, matriculaperiodoturmadisciplina.matriculaperiodo, atividadediscursiva.codigo, atividadediscursiva.artefatoentrega, atividadediscursiva.enunciado, atividadediscursiva.dataliberacao, atividadediscursiva.datalimiteentrega, calendarioatividadematricula.datainicio, calendarioatividadematricula.datafim, atividadediscursiva.qtddiasaposinicioliberar, atividadediscursiva.qtddiasparaconclusao, atividadediscursiva.definicaodataentregaatividadediscursiva, disciplina.nome, disciplina.codigo, ((SubPlan 1))
  • Sort Method: quicksort Memory: 19,244kB
3. 15.303 5,513.520 ↑ 2.6 22,701 1

Hash Left Join (cost=612.71..77,583,785.86 rows=59,917 width=538) (actual time=534.105..5,513.520 rows=22,701 loops=1)

  • Hash Cond: ((matriculaperiodoturmadisciplina.codigo = calendarioatividadematricula.matriculaperiodoturmadisciplina) AND (atividadediscursiva.codigo = (calendarioatividadematricula.codorigem)::integer))
4. 40.160 5,475.507 ↑ 2.6 22,701 1

Hash Left Join (cost=611.53..77,084,825.86 rows=59,917 width=522) (actual time=534.081..5,475.507 rows=22,701 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 (alternatives: SubPlan 3 or hashed SubPlan 4)) OR ((matriculaperiodoturmadisciplina.turmapratica IS NOT NULL) AND turmaavaliacao.turmaagrupada AND (alternatives: SubPlan 5 or hashed SubPlan 6)) OR ((matriculaperiodoturmadisciplina.turmateorica IS NOT NULL) AND turmaavaliacao.turmaagrupada AND (alternatives: SubPlan 7 or hashed SubPlan 8)))) OR (((atividadediscursiva.publicoalvo)::text = 'ALUNO'::text) AND (atividadediscursiva.matriculaperiodoturmadisciplina = matriculaperiodoturmadisciplina.codigo)))
  • Rows Removed by Filter: 17,349
5. 25.664 5,427.005 ↑ 31.3 40,050 1

Merge Left Join (cost=21.26..33,251,173.63 rows=1,253,028 width=521) (actual time=525.684..5,427.005 rows=40,050 loops=1)

  • Merge Cond: (matriculaperiodoturmadisciplina.codigo = atividadediscursivarespostaaluno.matriculaperiodoturmadisciplina)
  • Join Filter: (atividadediscursivarespostaaluno.atividadediscursiva = atividadediscursiva.codigo)
  • Rows Removed by Join Filter: 36,874
6. 3,396.285 5,363.839 ↑ 31.3 40,050 1

Nested Loop (cost=20.97..33,238,905.99 rows=1,253,028 width=500) (actual time=496.126..5,363.839 rows=40,050 loops=1)

  • Join Filter: (((NOT turma.turmaagrupada) AND (atividadediscursiva.disciplina = matriculaperiodoturmadisciplina.disciplina)) OR (turma.turmaagrupada AND ((SubPlan 2) OR (atividadediscursiva.disciplina = matriculaperiodoturmadisciplina.disciplina))))
  • Rows Removed by Join Filter: 21,766,905
7. 21.241 1,031.059 ↑ 2.1 26,757 1

Nested Loop (cost=2.56..865,835.34 rows=55,340 width=113) (actual time=494.983..1,031.059 rows=26,757 loops=1)

8. 9.303 983.061 ↑ 2.1 26,757 1

Nested Loop (cost=2.28..405,683.24 rows=55,340 width=116) (actual time=494.979..983.061 rows=26,757 loops=1)

9. 17.681 920.244 ↓ 1.9 26,757 1

Nested Loop (cost=1.99..128,697.51 rows=13,835 width=113) (actual time=494.973..920.244 rows=26,757 loops=1)

10. 5.138 849.049 ↓ 1.9 26,757 1

Nested Loop (cost=1.70..116,502.30 rows=13,835 width=87) (actual time=494.963..849.049 rows=26,757 loops=1)

11. 7.638 790.397 ↓ 1.9 26,757 1

Nested Loop (cost=1.42..112,009.45 rows=13,835 width=49) (actual time=494.955..790.397 rows=26,757 loops=1)

12. 28.270 729.245 ↓ 1.9 26,757 1

Nested Loop (cost=1.14..106,467.42 rows=13,835 width=48) (actual time=494.947..729.245 rows=26,757 loops=1)

13. 23.768 593.947 ↓ 1.9 26,757 1

Nested Loop (cost=0.84..101,663.18 rows=13,835 width=41) (actual time=494.930..593.947 rows=26,757 loops=1)

14. 516.665 516.665 ↓ 1.9 26,757 1

Index Scan using matriculaperiodoturmadisciplina_pkey on matriculaperiodoturmadisciplina (cost=0.42..80,869.43 rows=13,835 width=29) (actual time=494.911..516.665 rows=26,757 loops=1)

  • Filter: (((ano)::text = '2020'::text) AND ((semestre)::text = '1'::text))
  • Rows Removed by Filter: 992,728
15. 53.514 53.514 ↑ 1.0 1 26,757

Index Scan using matriculaperiodo_pkey on matriculaperiodo (cost=0.42..1.49 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=26,757)

  • Index Cond: (codigo = matriculaperiodoturmadisciplina.matriculaperiodo)
16. 107.028 107.028 ↑ 1.0 1 26,757

Index Scan using matricula_pkey on matricula (cost=0.29..0.34 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=26,757)

  • Index Cond: ((matricula)::text = (matriculaperiodo.matricula)::text)
17. 53.514 53.514 ↑ 1.0 1 26,757

Index Scan using turma_pkey on turma turma_mptd (cost=0.29..0.39 rows=1 width=5) (actual time=0.001..0.002 rows=1 loops=26,757)

  • Index Cond: (codigo = matriculaperiodoturmadisciplina.turma)
18. 53.514 53.514 ↑ 1.0 1 26,757

Index Scan using disciplina_pkey on disciplina (cost=0.28..0.31 rows=1 width=38) (actual time=0.001..0.002 rows=1 loops=26,757)

  • Index Cond: (codigo = matriculaperiodoturmadisciplina.disciplina)
19. 53.514 53.514 ↑ 1.0 1 26,757

Index Scan using pessoa_pkey on pessoa (cost=0.29..0.87 rows=1 width=30) (actual time=0.002..0.002 rows=1 loops=26,757)

  • Index Cond: (codigo = matricula.aluno)
20. 26.757 53.514 ↑ 4.0 1 26,757

Append (cost=0.29..19.98 rows=4 width=4) (actual time=0.001..0.002 rows=1 loops=26,757)

21. 26.757 26.757 ↑ 1.0 1 26,757

Index Only Scan using turma_pkey on turma turma_1 (cost=0.29..8.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=26,757)

  • Index Cond: (codigo = matriculaperiodoturmadisciplina.turma)
  • Heap Fetches: 26,757
22. 0.000 0.000 ↓ 0.0 0 26,757

Result (cost=4.30..11.64 rows=3 width=4) (actual time=0.000..0.000 rows=0 loops=26,757)

  • One-Time Filter: turma_mptd.turmaagrupada
23. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on turmaagrupada (cost=4.30..11.64 rows=3 width=4) (never executed)

  • Recheck Cond: (turma = matriculaperiodoturmadisciplina.turma)
24. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (turma = matriculaperiodoturmadisciplina.turma)
25. 26.757 26.757 ↑ 1.0 1 26,757

Index Scan using turma_pkey on turma (cost=0.29..8.30 rows=1 width=5) (actual time=0.001..0.001 rows=1 loops=26,757)

  • Index Cond: (codigo = turma_1.codigo)
26. 935.864 936.495 ↓ 1.7 815 26,757

Materialize (cost=18.41..347.82 rows=479 width=401) (actual time=0.000..0.035 rows=815 loops=26,757)

27. 0.544 0.631 ↓ 1.7 815 1

Bitmap Heap Scan on atividadediscursiva (cost=18.41..345.42 rows=479 width=401) (actual time=0.114..0.631 rows=815 loops=1)

  • Recheck Cond: ((ano)::text = '2020'::text)
  • Filter: ((semestre)::text = '1'::text)
  • Heap Blocks: exact=170
28. 0.087 0.087 ↓ 1.0 815 1

Bitmap Index Scan on ch_atividadediscursiva_ano (cost=0.00..18.29 rows=801 width=0) (actual time=0.087..0.087 rows=815 loops=1)

  • Index Cond: ((ano)::text = '2020'::text)
29.          

SubPlan (for Nested Loop)

30. 0.000 0.000 ↓ 0.0 0

Seq Scan on disciplinaequivalente (cost=0.00..2.40 rows=1 width=4) (never executed)

  • Filter: (disciplina = atividadediscursiva.disciplina)
31. 11.306 37.502 ↓ 1.9 78,700 1

Materialize (cost=0.29..8,336.60 rows=42,121 width=29) (actual time=0.010..37.502 rows=78,700 loops=1)

32. 26.196 26.196 ↑ 1.0 41,890 1

Index Scan using ch_atividadediscursivarespostaaluno_matriculaperiodoturmadiscip on atividadediscursivarespostaaluno (cost=0.29..8,231.30 rows=42,121 width=29) (actual time=0.009..26.196 rows=41,890 loops=1)

33. 2.901 7.854 ↑ 1.0 14,990 1

Hash (cost=402.90..402.90 rows=14,990 width=35) (actual time=7.854..7.854 rows=14,990 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 1,015kB
34. 4.953 4.953 ↑ 1.0 14,990 1

Seq Scan on turma turmaavaliacao (cost=0.00..402.90 rows=14,990 width=35) (actual time=0.007..4.953 rows=14,990 loops=1)

35.          

SubPlan (for Hash Left Join)

36. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on turmaagrupada turmaagrupada_1 (cost=4.30..11.65 rows=1 width=0) (never executed)

  • Recheck Cond: (turmaorigem = turmaavaliacao.codigo)
  • Filter: (turma = matriculaperiodoturmadisciplina.turma)
37. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (turmaorigem = turmaavaliacao.codigo)
38. 0.170 0.170 ↑ 1.0 1,333 1

Seq Scan on turmaagrupada turmaagrupada_2 (cost=0.00..24.33 rows=1,333 width=8) (actual time=0.005..0.170 rows=1,333 loops=1)

39. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on turmaagrupada turmaagrupada_3 (cost=4.30..11.65 rows=1 width=0) (never executed)

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

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

  • Index Cond: (turmaorigem = turmaavaliacao.codigo)
41. 0.145 0.145 ↑ 1.0 1,333 1

Seq Scan on turmaagrupada turmaagrupada_4 (cost=0.00..24.33 rows=1,333 width=8) (actual time=0.004..0.145 rows=1,333 loops=1)

42. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on turmaagrupada turmaagrupada_5 (cost=4.30..11.65 rows=1 width=0) (never executed)

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

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

  • Index Cond: (turmaorigem = turmaavaliacao.codigo)
44. 0.173 0.173 ↑ 1.0 1,333 1

Seq Scan on turmaagrupada turmaagrupada_6 (cost=0.00..24.33 rows=1,333 width=8) (actual time=0.003..0.173 rows=1,333 loops=1)

45. 0.003 0.009 ↑ 7.0 1 1

Hash (cost=1.07..1.07 rows=7 width=23) (actual time=0.009..0.009 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
46. 0.006 0.006 ↑ 1.0 7 1

Seq Scan on calendarioatividadematricula (cost=0.00..1.07 rows=7 width=23) (actual time=0.003..0.006 rows=7 loops=1)

47.          

SubPlan (for Hash Left Join)

48. 0.000 22.701 ↑ 1.0 1 22,701

Aggregate (cost=8.31..8.32 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=22,701)

49. 22.701 22.701 ↓ 0.0 0 22,701

Index Scan using ch_atividadediscursivainteracao_atividadediscursivarespostaalun on atividadediscursivainteracao (cost=0.29..8.30 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=22,701)

  • Index Cond: (atividadediscursivarespostaaluno = atividadediscursivarespostaaluno.codigo)
  • Filter: ((NOT interacaojalida) AND ((interagidopor)::text = 'PROFESSOR'::text))
  • Rows Removed by Filter: 0
Planning time : 10.129 ms
Execution time : 5,734.233 ms