explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8Yw0

Settings
# exclusive inclusive rows x rows loops node
1. 4,584.838 34,605.004 ↓ 258.1 1,842,500 1

Hash Join (cost=200,183.24..424,649.08 rows=7,138 width=121) (actual time=3,319.921..34,605.004 rows=1,842,500 loops=1)

  • Hash Cond: (t.codigo = hturma.turma)
2. 1,906.048 29,471.610 ↓ 162.8 1,842,500 1

Nested Loop (cost=180,637.86..400,985.54 rows=11,316 width=125) (actual time=2,771.331..29,471.610 rows=1,842,500 loops=1)

3. 3,038.330 22,038.062 ↓ 162.8 1,842,500 1

Nested Loop (cost=180,637.43..391,442.58 rows=11,316 width=100) (actual time=2,771.315..22,038.062 rows=1,842,500 loops=1)

  • Join Filter: ((mp.matricula)::text = (m.matricula)::text)
4. 1,791.566 6,102.232 ↓ 162.8 1,842,500 1

Hash Join (cost=180,637.01..386,182.05 rows=11,316 width=101) (actual time=2,771.286..6,102.232 rows=1,842,500 loops=1)

  • Hash Cond: ((t.codigo = ht_1.turma) AND (h.disciplina = d.codigo))
5. 1,365.569 3,618.595 ↓ 1.5 1,938,948 1

Hash Join (cost=149,815.42..225,052.40 rows=1,301,949 width=45) (actual time=2,079.067..3,618.595 rows=1,938,948 loops=1)

  • Hash Cond: ((mp.matricula)::text = (h.matricula)::text)
6. 131.431 183.202 ↓ 1.5 131,083 1

Hash Join (cost=306.05..6,142.96 rows=89,021 width=25) (actual time=7.000..183.202 rows=131,083 loops=1)

  • Hash Cond: (mp.turma = t.codigo)
7. 44.822 44.822 ↓ 1.0 141,038 1

Seq Scan on matriculaperiodo mp (cost=0.00..4,065.66 rows=140,966 width=17) (actual time=0.014..44.822 rows=141,038 loops=1)

8. 1.007 6.949 ↓ 1.0 2,745 1

Hash (cost=271.77..271.77 rows=2,742 width=8) (actual time=6.949..6.949 rows=2,745 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 108kB
9. 2.717 5.942 ↓ 1.0 2,745 1

Merge Join (cost=0.56..271.77 rows=2,742 width=8) (actual time=0.046..5.942 rows=2,745 loops=1)

  • Merge Cond: (t.codigo = ht.turma)
10. 1.745 1.745 ↑ 1.0 4,340 1

Index Only Scan using turma_pkey on turma t (cost=0.28..145.41 rows=4,342 width=4) (actual time=0.022..1.745 rows=4,340 loops=1)

  • Heap Fetches: 1145
11. 1.480 1.480 ↓ 1.0 2,745 1

Index Only Scan using ch_horarioturma_turma_ano_semestre on horarioturma ht (cost=0.28..81.41 rows=2,742 width=4) (actual time=0.021..1.480 rows=2,745 loops=1)

  • Heap Fetches: 1667
12. 844.228 2,069.824 ↓ 1.0 1,957,394 1

Hash (cost=113,692.26..113,692.26 rows=1,950,889 width=20) (actual time=2,069.824..2,069.824 rows=1,957,394 loops=1)

  • Buckets: 262144 Batches: 2 Memory Usage: 52692kB
13. 1,225.596 1,225.596 ↓ 1.0 1,957,394 1

Seq Scan on historico h (cost=0.00..113,692.26 rows=1,950,889 width=20) (actual time=0.063..1,225.596 rows=1,957,394 loops=1)

  • Filter: ((situacao)::text <> 'CS'::text)
  • Rows Removed by Filter: 390217
14. 21.989 692.071 ↑ 4.6 43,137 1

Hash (cost=27,841.12..27,841.12 rows=198,698 width=64) (actual time=692.071..692.071 rows=43,137 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 4137kB
15. 30.856 670.082 ↑ 4.6 43,137 1

Hash Join (cost=20,141.57..27,841.12 rows=198,698 width=64) (actual time=617.197..670.082 rows=43,137 loops=1)

  • Hash Cond: (hti.disciplina = d.codigo)
16. 136.118 635.920 ↑ 4.6 43,137 1

HashAggregate (cost=19,953.11..21,940.09 rows=198,698 width=12) (actual time=613.875..635.920 rows=43,137 loops=1)

  • Group Key: ht_1.turma, hti.disciplina
17. 303.159 499.802 ↓ 1.0 198,850 1

Hash Join (cost=6,626.45..17,966.13 rows=198,698 width=12) (actual time=151.266..499.802 rows=198,850 loops=1)

  • Hash Cond: (hti.horarioturmadia = htd.codigo)
18. 45.460 45.460 ↓ 1.0 198,850 1

Seq Scan on horarioturmadiaitem hti (cost=0.00..6,868.98 rows=198,698 width=12) (actual time=0.003..45.460 rows=198,850 loops=1)

19. 42.804 151.183 ↓ 1.0 136,185 1

Hash (cost=4,925.56..4,925.56 rows=136,071 width=8) (actual time=151.183..151.183 rows=136,185 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 5320kB
20. 80.350 108.379 ↓ 1.0 136,185 1

Hash Join (cost=85.69..4,925.56 rows=136,071 width=8) (actual time=1.652..108.379 rows=136,185 loops=1)

  • Hash Cond: (htd.horarioturma = ht_1.codigo)
21. 26.395 26.395 ↓ 1.0 136,185 1

Seq Scan on horarioturmadia htd (cost=0.00..2,628.71 rows=136,071 width=8) (actual time=0.003..26.395 rows=136,185 loops=1)

22. 0.864 1.634 ↓ 1.0 2,745 1

Hash (cost=51.42..51.42 rows=2,742 width=8) (actual time=1.634..1.634 rows=2,745 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 108kB
23. 0.770 0.770 ↓ 1.0 2,745 1

Seq Scan on horarioturma ht_1 (cost=0.00..51.42 rows=2,742 width=8) (actual time=0.006..0.770 rows=2,745 loops=1)

24. 1.834 3.306 ↑ 1.0 5,265 1

Hash (cost=122.65..122.65 rows=5,265 width=48) (actual time=3.306..3.306 rows=5,265 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 420kB
25. 1.472 1.472 ↑ 1.0 5,265 1

Seq Scan on disciplina d (cost=0.00..122.65 rows=5,265 width=48) (actual time=0.006..1.472 rows=5,265 loops=1)

26. 12,897.500 12,897.500 ↑ 1.0 1 1,842,500

Index Scan using matricula_pkey on matricula m (cost=0.42..0.45 rows=1 width=25) (actual time=0.007..0.007 rows=1 loops=1,842,500)

  • Index Cond: ((matricula)::text = (h.matricula)::text)
27. 5,527.500 5,527.500 ↑ 1.0 1 1,842,500

Index Scan using pessoa_pkey on pessoa p (cost=0.42..0.83 rows=1 width=29) (actual time=0.003..0.003 rows=1 loops=1,842,500)

  • Index Cond: (codigo = m.aluno)
28. 0.898 548.556 ↑ 1.0 2,730 1

Hash (cost=19,511.15..19,511.15 rows=2,739 width=12) (actual time=548.556..548.556 rows=2,730 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 118kB
29. 0.854 547.658 ↑ 1.0 2,730 1

Subquery Scan on hturma (cost=19,456.37..19,511.15 rows=2,739 width=12) (actual time=545.899..547.658 rows=2,730 loops=1)

30. 81.746 546.804 ↑ 1.0 2,730 1

HashAggregate (cost=19,456.37..19,483.76 rows=2,739 width=8) (actual time=545.899..546.804 rows=2,730 loops=1)

  • Group Key: ht_2.turma
31. 269.013 465.058 ↓ 1.0 198,850 1

Hash Join (cost=6,626.45..17,966.13 rows=198,698 width=8) (actual time=151.304..465.058 rows=198,850 loops=1)

  • Hash Cond: (hti_1.horarioturmadia = htd_1.codigo)
32. 44.821 44.821 ↓ 1.0 198,850 1

Seq Scan on horarioturmadiaitem hti_1 (cost=0.00..6,868.98 rows=198,698 width=8) (actual time=0.006..44.821 rows=198,850 loops=1)

33. 44.254 151.224 ↓ 1.0 136,185 1

Hash (cost=4,925.56..4,925.56 rows=136,071 width=8) (actual time=151.224..151.224 rows=136,185 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 5320kB
34. 78.895 106.970 ↓ 1.0 136,185 1

Hash Join (cost=85.69..4,925.56 rows=136,071 width=8) (actual time=1.624..106.970 rows=136,185 loops=1)

  • Hash Cond: (htd_1.horarioturma = ht_2.codigo)
35. 26.475 26.475 ↓ 1.0 136,185 1

Seq Scan on horarioturmadia htd_1 (cost=0.00..2,628.71 rows=136,071 width=8) (actual time=0.005..26.475 rows=136,185 loops=1)

36. 0.793 1.600 ↓ 1.0 2,745 1

Hash (cost=51.42..51.42 rows=2,742 width=8) (actual time=1.600..1.600 rows=2,745 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 108kB
37. 0.807 0.807 ↓ 1.0 2,745 1

Seq Scan on horarioturma ht_2 (cost=0.00..51.42 rows=2,742 width=8) (actual time=0.006..0.807 rows=2,745 loops=1)

Planning time : 10.803 ms
Execution time : 34,980.675 ms