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. 0.000 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. 168.618 10,810.212 ↑ 3.7 1,885 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. 1,662.117 10,641.594 ↑ 1.6 458,443 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. 1,605.156 4,853.490 ↑ 1.6 458,443 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. 884.544 2,984.649 ↑ 1.6 458,443 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. 438.051 2,100.105 ↑ 1.6 458,443 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. 301.893 1,456.770 ↑ 1.6 458,443 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. 647.934 1,147.032 ↑ 1.2 594,567 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. 165.288 165.288 ↑ 1.2 594,567 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. 94.461 333.810 ↑ 1.2 152,264 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. 121.245 239.349 ↑ 1.2 152,264 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. 52.284 52.284 ↑ 1.2 152,264 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. 9.189 65.820 ↑ 1.0 16,821 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. 6.321 56.631 ↑ 1.0 16,821 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.012 1.860 ↑ 1.0 1 3

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

19. 0.024 1.305 ↑ 1.0 1 3

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

20. 0.090 0.090 ↑ 1.0 1 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. 1.191 1.191 ↑ 1.0 1 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.543 0.543 ↑ 1.0 1 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. 14.337 48.450 ↑ 1.0 16,821 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. 5.109 5.109 ↑ 1.0 16,821 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. 11.787 29.004 ↑ 1.0 17,932 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. 17.217 17.217 ↑ 1.0 17,932 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. 4.299 7.845 ↑ 1.0 6,938 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. 3.546 3.546 ↑ 1.0 6,938 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. 75.138 205.284 ↑ 1.3 101,446 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. 130.146 130.146 ↑ 1.3 101,446 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. 251.607 263.685 ↓ 80.4 1,818,633 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. 12.078 12.078 ↑ 1.0 22,633 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. 1,375.329 4,125.987 ↑ 1.0 1 1,375,329

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 2,750.658 ↓ 0.0 0 1,375,329

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

35. 1,375.329 1,375.329 ↓ 0.0 0 1,375,329

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. 1,375.329 1,375.329 ↑ 1.0 1 1,375,329

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