explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9y8z : slow_query

Settings
# exclusive inclusive rows x rows loops node
1. 17.466 87,447.150 ↓ 21.7 738 1

Sort (cost=5,696,646.25..5,696,646.33 rows=34 width=12,642) (actual time=87,447.090..87,447.150 rows=738 loops=1)

  • Sort Key: pessoa.nome, edu_matriculaperiodo.aluno_id, edu_componente.sigla, edu_matriculadiario.diario_id
  • Sort Method: quicksort Memory: 1512kB
2. 12.568 87,429.684 ↓ 21.7 738 1

HashAggregate (cost=5,696,645.04..5,696,645.38 rows=34 width=12,642) (actual time=87,426.842..87,429.684 rows=738 loops=1)

  • Group Key: edu_matriculadiario.id, edu_matriculaperiodo.id, edu_aluno.id, edu_matriz.id, edu_estruturacurso.id, edu_turma.id, t9.id, t10.id, edu_situacaomatriculaperiodo.id, edu_diario.id, edu_componentecurricular.id, edu_componente.id, t14.id, pessoa.nome
3. 1.660 87,417.116 ↓ 21.7 738 1

Nested Loop (cost=1,036,942.17..5,696,643.77 rows=34 width=12,642) (actual time=20,467.519..87,417.116 rows=738 loops=1)

  • Join Filter: (edu_aluno.pessoa_fisica_id = pessoa.id)
4. 653.450 87,412.504 ↓ 21.7 738 1

Hash Semi Join (cost=1,036,941.75..5,696,625.54 rows=34 width=12,620) (actual time=20,467.488..87,412.504 rows=738 loops=1)

  • Hash Cond: (edu_matriculadiario.diario_id = u0.id)
5. 5,433.227 86,758.891 ↓ 1.3 5,536,982 1

Nested Loop (cost=1,036,902.26..5,685,264.05 rows=4,312,998 width=12,620) (actual time=4,123.637..86,758.891 rows=5,536,982 loops=1)

6. 6,069.896 70,251.700 ↓ 1.3 5,536,982 1

Hash Join (cost=1,036,901.84..3,421,467.82 rows=4,312,998 width=12,616) (actual time=4,123.575..70,251.700 rows=5,536,982 loops=1)

  • Hash Cond: (edu_diario.estrutura_curso_id = t14.id)
7. 8,098.133 64,181.774 ↓ 1.3 5,536,982 1

Hash Left Join (cost=1,036,900.55..3,362,162.80 rows=4,312,998 width=11,903) (actual time=4,123.531..64,181.774 rows=5,536,982 loops=1)

  • Hash Cond: (edu_componentecurricular.componente_id = edu_componente.id)
8. 8,457.395 56,072.409 ↓ 1.3 5,536,982 1

Hash Left Join (cost=1,035,945.09..3,280,338.63 rows=4,312,998 width=11,775) (actual time=4,112.271..56,072.409 rows=5,536,982 loops=1)

  • Hash Cond: (edu_diario.componente_curricular_id = edu_componentecurricular.id)
9. 4,484.151 47,594.024 ↓ 1.3 5,536,982 1

Hash Join (cost=1,034,386.48..3,192,520.06 rows=4,312,998 width=11,720) (actual time=4,091.250..47,594.024 rows=5,536,982 loops=1)

  • Hash Cond: (edu_matriculaperiodo.situacao_id = edu_situacaomatriculaperiodo.id)
10. 6,504.408 43,109.864 ↓ 1.3 5,536,982 1

Hash Left Join (cost=1,034,384.91..3,133,214.77 rows=4,312,998 width=11,697) (actual time=4,091.231..43,109.864 rows=5,536,982 loops=1)

  • Hash Cond: (edu_turma.matriz_id = t9.id)
11. 6,249.958 36,603.815 ↓ 1.3 5,536,982 1

Hash Left Join (cost=1,034,310.99..3,073,837.13 rows=4,312,998 width=10,822) (actual time=4,089.578..36,603.815 rows=5,536,982 loops=1)

  • Hash Cond: (edu_aluno.matriz_id = edu_matriz.id)
12. 16,779.660 30,352.141 ↓ 1.3 5,536,982 1

Hash Join (cost=1,034,237.07..3,014,498.30 rows=4,312,998 width=9,947) (actual time=4,087.845..30,352.141 rows=5,536,982 loops=1)

  • Hash Cond: (edu_matriculadiario.matricula_periodo_id = edu_matriculaperiodo.id)
13. 5,592.285 10,158.661 ↓ 1.3 5,536,982 1

Hash Join (cost=25,619.99..526,434.51 rows=4,312,998 width=631) (actual time=144.653..10,158.661 rows=5,536,982 loops=1)

  • Hash Cond: (edu_matriculadiario.diario_id = edu_diario.id)
14. 1,428.061 4,422.971 ↓ 1.3 5,536,982 1

Merge Left Join (cost=8.90..338,124.20 rows=4,312,998 width=47) (actual time=0.022..4,422.971 rows=5,536,982 loops=1)

  • Merge Cond: (edu_matriculadiario.id = edu_falta.matricula_diario_id)
15. 1,252.276 1,252.276 ↑ 1.0 4,312,998 1

Index Scan using edu_matriculadiario_pkey on edu_matriculadiario (cost=0.43..154,007.09 rows=4,312,998 width=43) (actual time=0.010..1,252.276 rows=4,312,998 loops=1)

16. 1,742.634 1,742.634 ↑ 1.0 3,856,937 1

Index Scan using edu_falta_5198b01b on edu_falta (cost=0.43..125,122.91 rows=3,856,937 width=8) (actual time=0.008..1,742.634 rows=3,856,937 loops=1)

17. 77.271 143.405 ↑ 1.0 226,315 1

Hash (cost=5,985.15..5,985.15 rows=226,315 width=584) (actual time=143.405..143.405 rows=226,315 loops=1)

  • Buckets: 1024 Batches: 64 Memory Usage: 497kB
18. 66.134 66.134 ↑ 1.0 226,315 1

Seq Scan on edu_diario (cost=0.00..5,985.15 rows=226,315 width=584) (actual time=0.010..66.134 rows=226,315 loops=1)

19. 1,399.000 3,413.820 ↑ 1.0 620,558 1

Hash (cost=293,036.11..293,036.11 rows=620,558 width=9,316) (actual time=3,413.820..3,413.820 rows=620,558 loops=1)

  • Buckets: 1024 Batches: 2048 Memory Usage: 269kB
20. 422.993 2,014.820 ↑ 1.0 620,558 1

Nested Loop Left Join (cost=5.22..293,036.11 rows=620,558 width=9,316) (actual time=0.035..2,014.820 rows=620,558 loops=1)

21. 416.745 971.269 ↑ 1.0 620,558 1

Merge Join (cost=4.93..93,348.34 rows=620,558 width=8,594) (actual time=0.019..971.269 rows=620,558 loops=1)

  • Merge Cond: (edu_aluno.id = edu_matriculaperiodo.aluno_id)
22. 175.065 175.065 ↑ 1.0 167,954 1

Index Scan using edu_aluno_pkey on edu_aluno (cost=0.42..45,308.10 rows=167,954 width=8,542) (actual time=0.008..175.065 rows=167,954 loops=1)

23. 379.459 379.459 ↑ 1.0 620,558 1

Index Scan using edu_matriculaperiodo_3bc25354 on edu_matriculaperiodo (cost=0.42..39,876.17 rows=620,558 width=52) (actual time=0.007..379.459 rows=620,558 loops=1)

24. 620.558 620.558 ↑ 1.0 1 620,558

Index Scan using edu_turma_pkey on edu_turma (cost=0.29..0.31 rows=1 width=722) (actual time=0.001..0.001 rows=1 loops=620,558)

  • Index Cond: (edu_matriculaperiodo.turma_id = id)
25. 0.814 1.716 ↑ 1.0 1,205 1

Hash (cost=58.86..58.86 rows=1,205 width=875) (actual time=1.716..1.716 rows=1,205 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 405kB
26. 0.738 0.902 ↑ 1.0 1,205 1

Hash Left Join (cost=1.29..58.86 rows=1,205 width=875) (actual time=0.043..0.902 rows=1,205 loops=1)

  • Hash Cond: (edu_matriz.estrutura_id = edu_estruturacurso.id)
27. 0.142 0.142 ↑ 1.0 1,205 1

Seq Scan on edu_matriz (cost=0.00..41.05 rows=1,205 width=162) (actual time=0.012..0.142 rows=1,205 loops=1)

28. 0.009 0.022 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=713) (actual time=0.022..0.022 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
29. 0.013 0.013 ↑ 1.0 13 1

Seq Scan on edu_estruturacurso (cost=0.00..1.13 rows=13 width=713) (actual time=0.009..0.013 rows=13 loops=1)

30. 0.801 1.641 ↑ 1.0 1,205 1

Hash (cost=58.86..58.86 rows=1,205 width=875) (actual time=1.641..1.641 rows=1,205 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 405kB
31. 0.740 0.840 ↑ 1.0 1,205 1

Hash Left Join (cost=1.29..58.86 rows=1,205 width=875) (actual time=0.022..0.840 rows=1,205 loops=1)

  • Hash Cond: (t9.estrutura_id = t10.id)
32. 0.087 0.087 ↑ 1.0 1,205 1

Seq Scan on edu_matriz t9 (cost=0.00..41.05 rows=1,205 width=162) (actual time=0.001..0.087 rows=1,205 loops=1)

33. 0.008 0.013 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=713) (actual time=0.013..0.013 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
34. 0.005 0.005 ↑ 1.0 13 1

Seq Scan on edu_estruturacurso t10 (cost=0.00..1.13 rows=13 width=713) (actual time=0.001..0.005 rows=13 loops=1)

35. 0.002 0.009 ↑ 1.0 25 1

Hash (cost=1.25..1.25 rows=25 width=23) (actual time=0.009..0.009 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
36. 0.007 0.007 ↑ 1.0 25 1

Seq Scan on edu_situacaomatriculaperiodo (cost=0.00..1.25 rows=25 width=23) (actual time=0.005..0.007 rows=25 loops=1)

37. 10.803 20.990 ↑ 1.0 46,116 1

Hash (cost=982.16..982.16 rows=46,116 width=55) (actual time=20.990..20.990 rows=46,116 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 3968kB
38. 10.187 10.187 ↑ 1.0 46,116 1

Seq Scan on edu_componentecurricular (cost=0.00..982.16 rows=46,116 width=55) (actual time=0.021..10.187 rows=46,116 loops=1)

39. 6.555 11.232 ↑ 1.0 22,465 1

Hash (cost=674.65..674.65 rows=22,465 width=128) (actual time=11.232..11.232 rows=22,465 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 3654kB
40. 4.677 4.677 ↑ 1.0 22,465 1

Seq Scan on edu_componente (cost=0.00..674.65 rows=22,465 width=128) (actual time=0.022..4.677 rows=22,465 loops=1)

41. 0.010 0.030 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=713) (actual time=0.030..0.030 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
42. 0.020 0.020 ↑ 1.0 13 1

Seq Scan on edu_estruturacurso t14 (cost=0.00..1.13 rows=13 width=713) (actual time=0.018..0.020 rows=13 loops=1)

43. 11,073.964 11,073.964 ↑ 1.0 1 5,536,982

Index Only Scan using pessoa_fisica_pkey on pessoa_fisica (cost=0.42..0.51 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=5,536,982)

  • Index Cond: (pessoa_ptr_id = edu_aluno.pessoa_fisica_id)
  • Heap Fetches: 5536982
44. 0.003 0.163 ↓ 18.0 18 1

Hash (cost=39.48..39.48 rows=1 width=4) (actual time=0.163..0.163 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
45. 0.148 0.160 ↓ 18.0 18 1

Bitmap Heap Scan on edu_diario u0 (cost=4.49..39.48 rows=1 width=4) (actual time=0.030..0.160 rows=18 loops=1)

  • Recheck Cond: (turma_id = 23462)
  • Filter: (id = ANY ('{103872,103905,103778,103783,103943,103772,103879,103820,103792,103911,103889,103795,103809,103866,103836,103930,103870,103935}'::integer[]))
  • Heap Blocks: exact=4
46. 0.012 0.012 ↓ 2.0 18 1

Bitmap Index Scan on edu_diario_5ce9797a (cost=0.00..4.49 rows=9 width=0) (actual time=0.012..0.012 rows=18 loops=1)

  • Index Cond: (turma_id = 23462)
47. 2.952 2.952 ↑ 1.0 1 738

Index Scan using pessoa_pkey on pessoa (cost=0.42..0.52 rows=1 width=30) (actual time=0.004..0.004 rows=1 loops=738)

  • Index Cond: (id = pessoa_fisica.pessoa_ptr_id)