explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DBKB

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 29,569.978 ↑ 17.0 1 1

Unique (cost=157,061.94..157,063.05 rows=17 width=503) (actual time=29,569.973..29,569.978 rows=1 loops=1)

  • Buffers: shared hit=35974778
2. 0.066 29,569.973 ↑ 5.7 3 1

Sort (cost=157,061.94..157,061.98 rows=17 width=503) (actual time=29,569.973..29,569.973 rows=3 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, atividadediscursiva.qtddiasaposinicioliberar, atividadediscursiva.qtddiasparaconclusao, disciplina.nome, disciplina.codigo, ((SubPlan 1))
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=35974778
3. 0.083 29,569.907 ↑ 5.7 3 1

Hash Left Join (cost=601.91..157,061.59 rows=17 width=503) (actual time=8,535.634..29,569.907 rows=3 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: 30
  • Buffers: shared hit=35974778
4. 0.013 29,558.583 ↑ 10.7 33 1

Nested Loop Left Join (cost=6.33..132,135.75 rows=353 width=502) (actual time=8,527.118..29,558.583 rows=33 loops=1)

  • Buffers: shared hit=35972956
5. 1.072 29,558.504 ↑ 10.7 33 1

Nested Loop (cost=6.04..129,198.79 rows=353 width=482) (actual time=8,527.110..29,558.504 rows=33 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: 3467
  • Buffers: shared hit=35972887
6. 0.932 0.932 ↓ 50.0 50 1

Seq Scan on atividadediscursiva (cost=0.00..173.09 rows=1 width=383) (actual time=0.526..0.932 rows=50 loops=1)

  • Filter: (((ano)::text = '2020'::text) AND ((semestre)::text = '1'::text))
  • Rows Removed by Filter: 2388
  • Buffers: shared hit=137
7. 6,751.250 29,527.700 ↑ 106.9 70 50

Nested Loop (cost=6.04..120,096.45 rows=7,480 width=113) (actual time=7.575..590.554 rows=70 loops=50)

  • Join Filter: ((turma.codigo = matriculaperiodoturmadisciplina.turma) OR (SubPlan 2))
  • Rows Removed by Join Filter: 89684
  • Buffers: shared hit=35969550
8. 0.300 6.750 ↓ 6.0 6 50

Nested Loop (cost=6.04..192.35 rows=1 width=112) (actual time=0.027..0.135 rows=6 loops=50)

  • Buffers: shared hit=4150
9. 0.200 5.550 ↓ 6.0 6 50

Nested Loop (cost=5.75..191.46 rows=1 width=86) (actual time=0.023..0.111 rows=6 loops=50)

  • Buffers: shared hit=3250
10. 0.250 4.450 ↓ 6.0 6 50

Nested Loop (cost=5.47..183.15 rows=1 width=48) (actual time=0.021..0.089 rows=6 loops=50)

  • Buffers: shared hit=2350
11. 0.350 2.400 ↓ 6.0 6 50

Nested Loop (cost=5.18..182.70 rows=1 width=41) (actual time=0.015..0.048 rows=6 loops=50)

  • Buffers: shared hit=1450
12. 0.450 0.850 ↓ 6.0 6 50

Bitmap Heap Scan on matriculaperiodoturmadisciplina (cost=4.76..174.25 rows=1 width=29) (actual time=0.010..0.017 rows=6 loops=50)

  • Recheck Cond: ((matricula)::text = '2010060017'::text)
  • Filter: (((ano)::text = '2020'::text) AND ((semestre)::text = '1'::text))
  • Heap Blocks: exact=100
  • Buffers: shared hit=250
13. 0.400 0.400 ↑ 7.3 6 50

Bitmap Index Scan on idx_matriculaperiodoturmadisciplina_matricula (cost=0.00..4.75 rows=44 width=0) (actual time=0.008..0.008 rows=6 loops=50)

  • Index Cond: ((matricula)::text = '2010060017'::text)
  • Buffers: shared hit=150
14. 1.200 1.200 ↑ 1.0 1 300

Index Scan using matriculaperiodo_pkey on matriculaperiodo (cost=0.42..8.44 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=300)

  • Index Cond: (codigo = matriculaperiodoturmadisciplina.matriculaperiodo)
  • Buffers: shared hit=1200
15. 1.800 1.800 ↑ 1.0 1 300

Index Scan using matricula_pkey on matricula (cost=0.29..0.44 rows=1 width=16) (actual time=0.005..0.006 rows=1 loops=300)

  • Index Cond: ((matricula)::text = (matriculaperiodo.matricula)::text)
  • Buffers: shared hit=900
16. 0.900 0.900 ↑ 1.0 1 300

Index Scan using disciplina_pkey on disciplina (cost=0.28..8.30 rows=1 width=38) (actual time=0.002..0.003 rows=1 loops=300)

  • Index Cond: (codigo = matriculaperiodoturmadisciplina.disciplina)
  • Buffers: shared hit=900
17. 0.900 0.900 ↑ 1.0 1 300

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

  • Index Cond: (codigo = matricula.aluno)
  • Buffers: shared hit=900
18. 332.700 332.700 ↑ 1.0 14,959 300

Seq Scan on turma (cost=0.00..408.59 rows=14,959 width=5) (actual time=0.001..1.109 rows=14,959 loops=300)

  • Buffers: shared hit=77700
19.          

SubPlan (for Nested Loop)

20. 13,462.200 22,437.000 ↓ 3.7 11 4,487,400

Bitmap Heap Scan on turmaagrupada (cost=4.30..11.64 rows=3 width=4) (actual time=0.002..0.005 rows=11 loops=4,487,400)

  • Recheck Cond: (turma = matriculaperiodoturmadisciplina.turma)
  • Heap Blocks: exact=26912900
  • Buffers: shared hit=35887700
21. 8,974.800 8,974.800 ↓ 3.7 11 4,487,400

Bitmap Index Scan on idx_turmaagrupada_turma (cost=0.00..4.30 rows=3 width=0) (actual time=0.002..0.002 rows=11 loops=4,487,400)

  • Index Cond: (turma = matriculaperiodoturmadisciplina.turma)
  • Buffers: shared hit=8974800
22.          

SubPlan (for Nested Loop)

23. 28.800 28.800 ↓ 0.0 0 3,200

Seq Scan on disciplinaequivalente (cost=0.00..2.35 rows=1 width=4) (actual time=0.008..0.009 rows=0 loops=3,200)

  • Filter: (disciplina = atividadediscursiva.disciplina)
  • Rows Removed by Filter: 109
  • Buffers: shared hit=3200
24. 0.066 0.066 ↓ 0.0 0 33

Index Scan using unique_atividadediscursiva_matriculaperiodoturmadisciplina on atividadediscursivarespostaaluno (cost=0.29..8.31 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=33)

  • Index Cond: ((atividadediscursiva = atividadediscursiva.codigo) AND (matriculaperiodoturmadisciplina = matriculaperiodoturmadisciplina.codigo))
  • Buffers: shared hit=69
25. 2.861 6.453 ↑ 1.0 14,959 1

Hash (cost=408.59..408.59 rows=14,959 width=35) (actual time=6.453..6.453 rows=14,959 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 1013kB
  • Buffers: shared hit=259
26. 3.592 3.592 ↑ 1.0 14,959 1

Seq Scan on turma turmaavaliacao (cost=0.00..408.59 rows=14,959 width=35) (actual time=0.002..3.592 rows=14,959 loops=1)

  • Buffers: shared hit=259
27.          

SubPlan (for Hash Left Join)

28. 0.006 4.788 ↑ 1.0 1 3

Aggregate (cost=704.80..704.81 rows=1 width=4) (actual time=1.596..1.596 rows=1 loops=3)

  • Buffers: shared hit=1563
29. 4.782 4.782 ↑ 1.0 1 3

Seq Scan on atividadediscursivainteracao (cost=0.00..704.79 rows=1 width=4) (actual time=1.284..1.594 rows=1 loops=3)

  • Filter: ((NOT interacaojalida) AND (atividadediscursivarespostaaluno = atividadediscursivarespostaaluno.codigo) AND ((interagidopor)::text = 'PROFESSOR'::text))
  • Rows Removed by Filter: 12296
  • Buffers: shared hit=1563
30. 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)
31. 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)
32. 0.000 0.000 ↓ 0.0 0

Seq Scan on turmaagrupada turmaagrupada_2 (cost=0.00..24.18 rows=1,318 width=8) (never executed)

33. 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)
34. 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)
35. 0.000 0.000 ↓ 0.0 0

Seq Scan on turmaagrupada turmaagrupada_4 (cost=0.00..24.18 rows=1,318 width=8) (never executed)

36. 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)
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.000 0.000 ↓ 0.0 0

Seq Scan on turmaagrupada turmaagrupada_6 (cost=0.00..24.18 rows=1,318 width=8) (never executed)

Planning time : 7.845 ms
Execution time : 29,570.576 ms