explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wWdL

Settings
# exclusive inclusive rows x rows loops node
1. 0.438 3,736.425 ↑ 3.3 2,077 1

Unique (cost=69,752,648.21..69,752,734.94 rows=6,938 width=79) (actual time=3,735.883..3,736.425 rows=2,077 loops=1)

2. 0.860 3,735.987 ↑ 3.3 2,077 1

Sort (cost=69,752,648.21..69,752,665.56 rows=6,938 width=79) (actual time=3,735.883..3,735.987 rows=2,077 loops=1)

  • Sort Key: disciplina.codigo, disciplina.nome, (min(horarioturmadiaitem.data)), (max(horarioturmadiaitem.data))
  • Sort Method: quicksort Memory: 366kB
3. 0.000 3,735.127 ↑ 3.3 2,077 1

Finalize GroupAggregate (cost=142,685.10..69,752,205.56 rows=6,938 width=79) (actual time=959.966..3,735.127 rows=2,077 loops=1)

  • Group Key: disciplina.codigo
4. 142.611 3,746.015 ↑ 2.5 5,656 1

Gather Merge (cost=142,685.10..69,752,032.11 rows=13,876 width=47) (actual time=959.819..3,746.015 rows=5,656 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 56.206 3,603.404 ↑ 3.7 1,885 3 / 3

Partial GroupAggregate (cost=141,685.08..69,749,430.45 rows=6,938 width=47) (actual time=934.641..3,603.404 rows=1,885 loops=3)

  • Group Key: disciplina.codigo
6. 554.039 3,547.198 ↑ 1.6 458,443 3 / 3

Nested Loop (cost=141,685.08..69,743,787.01 rows=743,208 width=43) (actual time=934.576..3,547.198 rows=458,443 loops=3)

7. 535.052 1,617.830 ↑ 1.6 458,443 3 / 3

Merge Left Join (cost=141,595.48..147,342.66 rows=743,208 width=55) (actual time=934.388..1,617.830 rows=458,443 loops=3)

  • Merge Cond: ((disciplina.codigo = professortitulardisciplinaturma.disciplina) AND (turma.codigo = professortitulardisciplinaturma.turma))
  • Join Filter: ((((curso.periodicidade)::text = 'AN'::text) AND ((professortitulardisciplinaturma.ano)::text = (matriculaperiodo.ano)::text)) OR (((curso.periodicidade)::text = 'SE'::text) AND ((professortitulardisciplinaturma.ano)::text = (matriculaperiodo.ano)::text) AND ((professortitulardisciplinaturma.semestre)::text = (matriculaperiodo.semestre)::text)) OR ((curso.periodicidade)::text = 'IN'::text))
  • Rows Removed by Join Filter: 1628950
8. 294.848 994.883 ↑ 1.6 458,443 3 / 3

Sort (cost=139,550.02..141,408.04 rows=743,208 width=59) (actual time=923.920..994.883 rows=458,443 loops=3)

  • Sort Key: disciplina.codigo, turma.codigo
  • Sort Method: external merge Disk: 29048kB
  • Worker 0: Sort Method: external merge Disk: 31616kB
  • Worker 1: Sort Method: external merge Disk: 31512kB
9. 146.017 700.035 ↑ 1.6 458,443 3 / 3

Parallel Hash Join (cost=26,748.99..67,074.58 rows=743,208 width=59) (actual time=183.625..700.035 rows=458,443 loops=3)

  • Hash Cond: (historico.matriculaperiodo = matriculaperiodo.codigo)
10. 100.631 485.590 ↑ 1.6 458,443 3 / 3

Hash Join (cost=19,313.81..57,688.45 rows=743,208 width=58) (actual time=114.424..485.590 rows=458,443 loops=3)

  • Hash Cond: (horarioturmadiaitem.disciplina = disciplina.codigo)
11. 215.978 382.344 ↑ 1.2 594,567 3 / 3

Parallel Hash Join (cost=19,069.70..55,492.51 rows=743,208 width=23) (actual time=111.756..382.344 rows=594,567 loops=3)

  • Hash Cond: (horarioturmadiaitem.horarioturmadia = horarioturmadia.codigo)
12. 55.096 55.096 ↑ 1.2 594,567 3 / 3

Parallel Seq Scan on horarioturmadiaitem (cost=0.00..30,539.08 rows=743,208 width=16) (actual time=0.006..55.096 rows=594,567 loops=3)

13. 31.487 111.270 ↑ 1.2 152,264 3 / 3

Parallel Hash (cost=16,690.58..16,690.58 rows=190,330 width=15) (actual time=111.270..111.270 rows=152,264 loops=3)

  • Buckets: 524288 Batches: 1 Memory Usage: 25568kB
14. 40.415 79.783 ↑ 1.2 152,264 3 / 3

Hash Join (cost=1,589.24..16,690.58 rows=190,330 width=15) (actual time=22.057..79.783 rows=152,264 loops=3)

  • Hash Cond: (horarioturmadia.horarioturma = horarioturma.codigo)
15. 17.428 17.428 ↑ 1.2 152,264 3 / 3

Parallel Seq Scan on horarioturmadia (cost=0.00..12,484.30 rows=190,330 width=8) (actual time=0.007..17.428 rows=152,264 loops=3)

16. 3.063 21.940 ↑ 1.0 16,821 3 / 3

Hash (cost=1,378.97..1,378.97 rows=16,821 width=15) (actual time=21.940..21.940 rows=16,821 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1045kB
17. 2.107 18.877 ↑ 1.0 16,821 3 / 3

Nested Loop (cost=848.33..1,378.97 rows=16,821 width=15) (actual time=10.446..18.877 rows=16,821 loops=3)

18. 0.004 0.620 ↑ 1.0 1 3 / 3

Nested Loop (cost=0.86..16.92 rows=1 width=7) (actual time=0.617..0.620 rows=1 loops=3)

19. 0.008 0.435 ↑ 1.0 1 3 / 3

Nested Loop (cost=0.72..16.75 rows=1 width=8) (actual time=0.432..0.435 rows=1 loops=3)

20. 0.030 0.030 ↑ 1.0 1 3 / 3

Index Scan using historico_pkey on historico (cost=0.43..8.45 rows=1 width=17) (actual time=0.028..0.030 rows=1 loops=3)

  • Index Cond: (codigo = 1624691)
21. 0.397 0.397 ↑ 1.0 1 3 / 3

Index Scan using matricula_pkey on matricula (cost=0.29..8.31 rows=1 width=13) (actual time=0.397..0.397 rows=1 loops=3)

  • Index Cond: ((matricula)::text = (historico.matricula)::text)
22. 0.181 0.181 ↑ 1.0 1 3 / 3

Index Scan using curso_pkey on curso (cost=0.14..0.16 rows=1 width=7) (actual time=0.181..0.181 rows=1 loops=3)

  • Index Cond: (codigo = matricula.curso)
23. 4.779 16.150 ↑ 1.0 16,821 3 / 3

Hash Join (cost=847.47..1,193.85 rows=16,821 width=8) (actual time=9.826..16.150 rows=16,821 loops=3)

  • Hash Cond: (horarioturma.turma = turma.codigo)
24. 1.703 1.703 ↑ 1.0 16,821 3 / 3

Seq Scan on horarioturma (cost=0.00..302.21 rows=16,821 width=8) (actual time=0.017..1.703 rows=16,821 loops=3)

25. 3.929 9.668 ↑ 1.0 17,932 3 / 3

Hash (cost=623.32..623.32 rows=17,932 width=4) (actual time=9.668..9.668 rows=17,932 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 887kB
26. 5.739 5.739 ↑ 1.0 17,932 3 / 3

Seq Scan on turma (cost=0.00..623.32 rows=17,932 width=4) (actual time=0.014..5.739 rows=17,932 loops=3)

27. 1.433 2.615 ↑ 1.0 6,938 3 / 3

Hash (cost=157.38..157.38 rows=6,938 width=39) (actual time=2.615..2.615 rows=6,938 loops=3)

  • Buckets: 8192 Batches: 1 Memory Usage: 551kB
28. 1.182 1.182 ↑ 1.0 6,938 3 / 3

Seq Scan on disciplina (cost=0.00..157.38 rows=6,938 width=39) (actual time=0.018..1.182 rows=6,938 loops=3)

29. 25.046 68.428 ↑ 1.3 101,446 3 / 3

Parallel Hash (cost=5,850.08..5,850.08 rows=126,808 width=9) (actual time=68.428..68.428 rows=101,446 loops=3)

  • Buckets: 524288 Batches: 1 Memory Usage: 18432kB
30. 43.382 43.382 ↑ 1.3 101,446 3 / 3

Parallel Seq Scan on matriculaperiodo (cost=0.00..5,850.08 rows=126,808 width=9) (actual time=0.116..43.382 rows=101,446 loops=3)

31. 83.869 87.895 ↓ 80.4 1,818,633 3 / 3

Sort (cost=2,045.46..2,102.04 rows=22,633 width=21) (actual time=10.457..87.895 rows=1,818,633 loops=3)

  • Sort Key: professortitulardisciplinaturma.disciplina, professortitulardisciplinaturma.turma
  • Sort Method: quicksort Memory: 2537kB
  • Worker 0: Sort Method: quicksort Memory: 2537kB
  • Worker 1: Sort Method: quicksort Memory: 2537kB
32. 4.026 4.026 ↑ 1.0 22,633 3 / 3

Seq Scan on professortitulardisciplinaturma (cost=0.00..408.40 rows=22,633 width=21) (actual time=0.107..4.026 rows=22,633 loops=3)

  • Filter: titular
  • Rows Removed by Filter: 2307
33. 458.443 1,375.329 ↑ 1.0 1 1,375,329 / 3

Bitmap Heap Scan on pessoa (cost=89.60..93.63 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1,375,329)

  • Recheck Cond: ((professortitulardisciplinaturma.professor = codigo) OR (codigo = horarioturmadiaitem.professor))
  • Filter: (((professortitulardisciplinaturma.codigo IS NOT NULL) AND (professortitulardisciplinaturma.professor = codigo)) OR ((professortitulardisciplinaturma.codigo IS NULL) AND (codigo = horarioturmadiaitem.professor)))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=502780
34. 0.000 916.886 ↓ 0.0 0 1,375,329 / 3

BitmapOr (cost=89.60..89.60 rows=2 width=0) (actual time=0.002..0.002 rows=0 loops=1,375,329)

35. 458.443 458.443 ↓ 0.0 0 1,375,329 / 3

Bitmap Index Scan on pessoa_pkey (cost=0.00..0.71 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1,375,329)

  • Index Cond: (professortitulardisciplinaturma.professor = codigo)
36. 458.443 458.443 ↑ 1.0 1 1,375,329 / 3

Bitmap Index Scan on pessoa_pkey (cost=0.00..0.43 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1,375,329)

  • Index Cond: (codigo = horarioturmadiaitem.professor)
Planning time : 13.193 ms
Execution time : 3,752.700 ms