explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EWtQ

Settings
# exclusive inclusive rows x rows loops node
1. 67.719 956,904.153 ↑ 4,418.5 22,334 1

Unique (cost=129,211,841,846.34..129,218,996,314.25 rows=98,682,316 width=535) (actual time=956,831.034..956,904.153 rows=22,334 loops=1)

2. 1,052.321 956,836.434 ↑ 1,972.5 50,028 1

Sort (cost=129,211,841,846.34..129,212,088,552.13 rows=98,682,316 width=535) (actual time=956,831.031..956,836.434 rows=50,028 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: 41893kB
3. 34.748 955,784.113 ↑ 1,972.5 50,028 1

Hash Left Join (cost=593.73..129,151,517,632.20 rows=98,682,316 width=535) (actual time=151,057.842..955,784.113 rows=50,028 loops=1)

  • Hash Cond: ((matriculaperiodoturmadisciplina.codigo = calendarioatividadematricula.matriculaperiodoturmadisciplina) AND (atividadediscursiva.codigo = (calendarioatividadematricula.codorigem)::integer))
4. 89.796 955,599.273 ↑ 1,972.5 50,028 1

Hash Left Join (cost=592.55..128,329,740,644.53 rows=98,682,316 width=519) (actual time=151,057.821..955,599.273 rows=50,028 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 4 or hashed SubPlan 5)) OR ((matriculaperiodoturmadisciplina.turmapratica IS NOT NULL) AND turmaavaliacao.turmaagrupada AND (alternatives: SubPlan 6 or hashed SubPlan 7)) OR ((matriculaperiodoturmadisciplina.turmateorica IS NOT NULL) AND turmaavaliacao.turmaagrupada AND (alternatives: SubPlan 8 or hashed SubPlan 9)))) OR (((atividadediscursiva.publicoalvo)::text = 'ALUNO'::text) AND (atividadediscursiva.matriculaperiodoturmadisciplina = matriculaperiodoturmadisciplina.codigo)))
  • Rows Removed by Filter: 57234
5. 240.927 955,502.566 ↑ 19,222.4 107,262 1

Merge Left Join (cost=2.28..56,203,206,489.76 rows=2,061,835,566 width=518) (actual time=151,051.313..955,502.566 rows=107,262 loops=1)

  • Merge Cond: (atividadediscursiva.codigo = atividadediscursivarespostaaluno.atividadediscursiva)
  • Join Filter: (atividadediscursivarespostaaluno.matriculaperiodoturmadisciplina = matriculaperiodoturmadisciplina.codigo)
  • Rows Removed by Join Filter: 1448512
6. 3,773.068 955,170.380 ↑ 19,222.4 107,262 1

Nested Loop (cost=1.99..55,715,662,499.44 rows=2,061,835,566 width=497) (actual time=151,029.041..955,170.380 rows=107,262 loops=1)

  • Join Filter: (((NOT turma.turmaagrupada) AND (atividadediscursiva.disciplina = matriculaperiodoturmadisciplina.disciplina)) OR (turma.turmaagrupada AND ((SubPlan 3) OR (atividadediscursiva.disciplina = matriculaperiodoturmadisciplina.disciplina))))
  • Rows Removed by Join Filter: 43112913
7. 4.912 4.912 ↓ 1.9 795 1

Index Scan using atividadediscursiva_pkey on atividadediscursiva (cost=0.28..1,128.45 rows=423 width=398) (actual time=1.663..4.912 rows=795 loops=1)

  • Filter: (((ano)::text = '2020'::text) AND ((semestre)::text = '1'::text))
  • Rows Removed by Filter: 2389
8. 2,107.452 731,908.800 ↑ 1,897.8 54,365 795

Materialize (cost=1.71..1,651,233,646.12 rows=103,175,558 width=113) (actual time=0.607..920.640 rows=54,365 loops=795)

9. 310,680.146 729,801.348 ↑ 1,897.8 54,365 1

Nested Loop (cost=1.71..1,648,904,135.33 rows=103,175,558 width=113) (actual time=482.384..729,801.348 rows=54,365 loops=1)

  • Join Filter: ((turma.codigo = matriculaperiodoturmadisciplina.turma) OR (SubPlan 2))
  • Rows Removed by Join Filter: 401033065
10. 6.350 1,043.077 ↓ 1.9 26,757 1

Nested Loop (cost=1.71..123,492.96 rows=13,765 width=112) (actual time=472.964..1,043.077 rows=26,757 loops=1)

11. 24.808 929.699 ↓ 1.9 26,757 1

Nested Loop (cost=1.42..111,359.46 rows=13,765 width=86) (actual time=472.954..929.699 rows=26,757 loops=1)

12. 28.331 824.620 ↓ 1.9 26,757 1

Nested Loop (cost=1.14..106,888.31 rows=13,765 width=48) (actual time=472.945..824.620 rows=26,757 loops=1)

13. 42.007 635.747 ↓ 1.9 26,757 1

Nested Loop (cost=0.84..102,108.38 rows=13,765 width=41) (actual time=472.927..635.747 rows=26,757 loops=1)

14. 513.469 513.469 ↓ 1.9 26,757 1

Index Scan using matriculaperiodoturmadisciplina_pkey on matriculaperiodoturmadisciplina (cost=0.42..81,346.13 rows=13,765 width=29) (actual time=472.909..513.469 rows=26,757 loops=1)

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

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

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

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

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

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

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

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

  • Index Cond: (codigo = matricula.aluno)
19. 17,013.813 17,017.452 ↑ 1.0 14,990 26,757

Materialize (cost=0.00..477.85 rows=14,990 width=5) (actual time=0.000..0.636 rows=14,990 loops=26,757)

20. 3.639 3.639 ↑ 1.0 14,990 1

Seq Scan on turma (cost=0.00..402.90 rows=14,990 width=5) (actual time=0.006..3.639 rows=14,990 loops=1)

21.          

SubPlan (for Nested Loop)

22. 0.000 401,060.673 ↑ 3.0 1 401,060,673

Bitmap Heap Scan on turmaagrupada (cost=4.30..11.64 rows=3 width=4) (actual time=0.001..0.001 rows=1 loops=401,060,673)

  • Recheck Cond: (turma = matriculaperiodoturmadisciplina.turma)
  • Heap Blocks: exact=309073204
23. 401,060.673 401,060.673 ↑ 3.0 1 401,060,673

Bitmap Index Scan on idx_turmaagrupada_turma (cost=0.00..4.30 rows=3 width=0) (actual time=0.001..0.001 rows=1 loops=401,060,673)

  • Index Cond: (turma = matriculaperiodoturmadisciplina.turma)
24.          

SubPlan (for Nested Loop)

25. 219,483.600 219,483.600 ↓ 0.0 0 21,948,360

Seq Scan on disciplinaequivalente (cost=0.00..2.40 rows=1 width=4) (actual time=0.009..0.010 rows=0 loops=21,948,360)

  • Filter: (disciplina = atividadediscursiva.disciplina)
  • Rows Removed by Filter: 112
26. 68.969 91.259 ↓ 36.1 1,498,920 1

Materialize (cost=0.29..10,226.45 rows=41,564 width=29) (actual time=0.009..91.259 rows=1,498,920 loops=1)

27. 22.290 22.290 ↑ 1.0 41,520 1

Index Scan using unique_atividadediscursiva_matriculaperiodoturmadisciplina on atividadediscursivarespostaaluno (cost=0.29..10,122.54 rows=41,564 width=29) (actual time=0.007..22.290 rows=41,520 loops=1)

28. 2.428 6.485 ↑ 1.0 14,990 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 1015kB
29. 4.057 4.057 ↑ 1.0 14,990 1

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

30.          

SubPlan (for Hash Left Join)

31. 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)
32. 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)
33. 0.166 0.166 ↑ 1.0 1,333 1

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

34. 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)
35. 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)
36. 0.125 0.125 ↑ 1.0 1,333 1

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

37. 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)
38. 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)
39. 0.135 0.135 ↑ 1.0 1,333 1

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

40. 0.004 0.008 ↑ 7.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
41. 0.004 0.004 ↑ 1.0 7 1

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

42.          

SubPlan (for Hash Left Join)

43. 0.000 150.084 ↑ 1.0 1 50,028

Aggregate (cost=8.31..8.32 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=50,028)

44. 150.084 150.084 ↓ 0.0 0 50,028

Index Scan using idx_atividadediscursivainteracao_atividadediscursivarespostaalu on atividadediscursivainteracao (cost=0.29..8.30 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=50,028)

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