explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CEbG

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=22,395,619.50..47,154,377.61 rows=622,828,566 width=428) (actual rows= loops=)

  • Hash Cond: (aluno_2012.co_ies = depara_oficial.co_ies)
2.          

CTE canonical_courses

3. 0.000 0.000 ↓ 0.0

Function Scan on dblink courses (cost=0.00..10.00 rows=1,000 width=100) (actual rows= loops=)

4.          

CTE aluno_2012

5. 0.000 0.000 ↓ 0.0

Unique (cost=1,100,878.42..1,180,195.24 rows=1,175,064 width=272) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Sort (cost=1,100,878.42..1,103,816.08 rows=1,175,064 width=272) (actual rows= loops=)

  • Sort Key: aluno.co_aluno, aluno.co_ies, (unaccent(lower((aluno.no_curso)::text))), curso.co_ocde_area_geral, aluno.co_grau_academico, aluno.ds_grau_academico, aluno.co_modalidade_ensino, (CASE WHEN ((aluno.ds_modalidade_ensino)::text = 'A D (...)
7. 0.000 0.000 ↓ 0.0

Hash Join (cost=8,964.75..897,488.66 rows=1,175,064 width=272) (actual rows= loops=)

  • Hash Cond: (aluno.co_curso = curso.co_curso)
8. 0.000 0.000 ↓ 0.0

Hash Join (cost=5,775.82..857,578.98 rows=1,175,064 width=227) (actual rows= loops=)

  • Hash Cond: (aluno.co_curso_polo = local_oferta.co_curso_polo)
9. 0.000 0.000 ↓ 0.0

Seq Scan on dm_aluno_2012 aluno (cost=0.00..813,150.90 rows=7,173,766 width=211) (actual rows= loops=)

  • Filter: (co_categoria_administrativa = ANY ('{4,5,7}'::bigint[]))
10. 0.000 0.000 ↓ 0.0

Hash (cost=3,858.70..3,858.70 rows=153,370 width=32) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Seq Scan on dm_local_oferta_2012 local_oferta (cost=0.00..3,858.70 rows=153,370 width=32) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Hash (cost=2,787.30..2,787.30 rows=32,130 width=32) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Seq Scan on dm_curso_2012 curso (cost=0.00..2,787.30 rows=32,130 width=32) (actual rows= loops=)

14.          

CTE aluno_2013

15. 0.000 0.000 ↓ 0.0

Unique (cost=1,336,962.81..1,424,136.57 rows=1,291,463 width=250) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Sort (cost=1,336,962.81..1,340,191.47 rows=1,291,463 width=250) (actual rows= loops=)

  • Sort Key: aluno_1.co_aluno, aluno_1.co_ies, (unaccent(lower((aluno_1.no_curso)::text))), curso_1.co_ocde_area_geral, aluno_1.co_grau_academico, aluno_1.ds_grau_academico, aluno_1.co_modalidade_ensino, aluno_1.ds_modalidade_ensino, aluno_1.c (...)
17. 0.000 0.000 ↓ 0.0

Hash Join (cost=10,301.81..1,117,591.61 rows=1,291,463 width=250) (actual rows= loops=)

  • Hash Cond: (aluno_1.co_curso = curso_1.co_curso)
18. 0.000 0.000 ↓ 0.0

Hash Join (cost=6,993.33..1,077,153.57 rows=1,291,463 width=226) (actual rows= loops=)

  • Hash Cond: (aluno_1.co_curso_polo = local_oferta_1.co_curso_polo)
19. 0.000 0.000 ↓ 0.0

Seq Scan on dm_aluno_2013 aluno_1 (cost=0.00..1,028,372.52 rows=7,699,490 width=210) (actual rows= loops=)

  • Filter: (co_categoria_administrativa = ANY ('{4,5,7}'::bigint[]))
20. 0.000 0.000 ↓ 0.0

Hash (cost=4,930.37..4,930.37 rows=165,037 width=32) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Seq Scan on dm_local_oferta_2013 local_oferta_1 (cost=0.00..4,930.37 rows=165,037 width=32) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash (cost=2,905.99..2,905.99 rows=32,199 width=32) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Seq Scan on dm_curso_2013 curso_1 (cost=0.00..2,905.99 rows=32,199 width=32) (actual rows= loops=)

24.          

CTE aluno_2014

25. 0.000 0.000 ↓ 0.0

Unique (cost=1,478,852.09..1,586,178.64 rows=1,590,023 width=237) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Sort (cost=1,478,852.09..1,482,827.15 rows=1,590,023 width=237) (actual rows= loops=)

  • Sort Key: aluno_2.co_aluno, aluno_2.co_ies, (unaccent(lower((aluno_2.no_curso)::text))), curso_2.co_ocde_area_geral, aluno_2.co_grau_academico, aluno_2.ds_grau_academico, aluno_2.co_modalidade_ensino, aluno_2.ds_modalidade_ensino, aluno_2.c (...)
27. 0.000 0.000 ↓ 0.0

Hash Join (cost=10,924.36..1,212,592.82 rows=1,590,023 width=237) (actual rows= loops=)

  • Hash Cond: (aluno_2.co_curso = curso_2.co_curso)
28. 0.000 0.000 ↓ 0.0

Hash Join (cost=7,457.87..1,167,388.24 rows=1,590,023 width=229) (actual rows= loops=)

  • Hash Cond: (aluno_2.co_curso_polo = local_oferta_2.co_curso_polo)
29. 0.000 0.000 ↓ 0.0

Seq Scan on dm_aluno_2014 aluno_2 (cost=0.00..1,112,047.69 rows=8,528,652 width=213) (actual rows= loops=)

  • Filter: (co_categoria_administrativa = ANY ('{4,5,7}'::bigint[]))
30. 0.000 0.000 ↓ 0.0

Hash (cost=5,254.61..5,254.61 rows=176,261 width=32) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Seq Scan on dm_local_oferta_2014 local_oferta_2 (cost=0.00..5,254.61 rows=176,261 width=32) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Hash (cost=3,051.77..3,051.77 rows=33,177 width=32) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Seq Scan on dm_curso_2014 curso_2 (cost=0.00..3,051.77 rows=33,177 width=32) (actual rows= loops=)

34.          

CTE aluno_2015

35. 0.000 0.000 ↓ 0.0

Unique (cost=1,541,627.37..1,651,301.10 rows=1,624,796 width=234) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Sort (cost=1,541,627.37..1,545,689.36 rows=1,624,796 width=234) (actual rows= loops=)

  • Sort Key: aluno_3.co_aluno, aluno_3.co_ies, (unaccent(lower((aluno_3.no_curso)::text))), curso_3.co_ocde_area_geral, aluno_3.co_grau_academico, aluno_3.ds_grau_academico, aluno_3.co_modalidade_ensino, aluno_3.ds_modalidade_ensino, aluno_3.c (...)
37. 0.000 0.000 ↓ 0.0

Hash Join (cost=11,896.75..1,269,290.82 rows=1,624,796 width=234) (actual rows= loops=)

  • Hash Cond: (aluno_3.co_curso = curso_3.co_curso)
38. 0.000 0.000 ↓ 0.0

Hash Join (cost=8,394.33..1,223,137.50 rows=1,624,796 width=225) (actual rows= loops=)

  • Hash Cond: (aluno_3.co_curso_polo = local_oferta_3.co_curso_polo)
39. 0.000 0.000 ↓ 0.0

Seq Scan on dm_aluno_2015 aluno_3 (cost=0.00..1,165,554.24 rows=8,784,260 width=209) (actual rows= loops=)

  • Filter: (co_categoria_administrativa = ANY ('{4,5,7}'::bigint[]))
40. 0.000 0.000 ↓ 0.0

Hash (cost=5,908.59..5,908.59 rows=198,859 width=32) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Seq Scan on dm_local_oferta_2015 local_oferta_3 (cost=0.00..5,908.59 rows=198,859 width=32) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Hash (cost=3,079.41..3,079.41 rows=33,841 width=32) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Seq Scan on dm_curso_2015 curso_3 (cost=0.00..3,079.41 rows=33,841 width=32) (actual rows= loops=)

44.          

CTE aluno_2016

45. 0.000 0.000 ↓ 0.0

Unique (cost=1,616,262.46..1,737,920.41 rows=1,802,340 width=233) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Sort (cost=1,616,262.46..1,620,768.31 rows=1,802,340 width=233) (actual rows= loops=)

  • Sort Key: aluno_4.co_aluno, aluno_4.co_ies, (unaccent(lower((aluno_4.no_curso)::text))), curso_4.co_ocde_area_geral, aluno_4.co_grau_academico, aluno_4.ds_grau_academico, aluno_4.co_modalidade_ensino, aluno_4.ds_modalidade_ensino, aluno_4.c (...)
47. 0.000 0.000 ↓ 0.0

Hash Join (cost=12,399.67..1,312,818.36 rows=1,802,340 width=233) (actual rows= loops=)

  • Hash Cond: (aluno_4.co_curso = curso_4.co_curso)
48. 0.000 0.000 ↓ 0.0

Hash Join (cost=8,761.94..1,261,869.21 rows=1,802,340 width=224) (actual rows= loops=)

  • Hash Cond: (aluno_4.co_curso_polo = local_oferta_4.co_curso_polo)
49. 0.000 0.000 ↓ 0.0

Seq Scan on dm_aluno_2016 aluno_4 (cost=0.00..1,201,182.73 rows=9,040,302 width=208) (actual rows= loops=)

  • Filter: (co_categoria_administrativa = ANY ('{4,5,7}'::bigint[]))
50. 0.000 0.000 ↓ 0.0

Hash (cost=6,162.53..6,162.53 rows=207,953 width=32) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Seq Scan on dm_local_oferta_2016 local_oferta_4 (cost=0.00..6,162.53 rows=207,953 width=32) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Hash (cost=3,203.99..3,203.99 rows=34,699 width=32) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Seq Scan on dm_curso_2016 curso_4 (cost=0.00..3,203.99 rows=34,699 width=32) (actual rows= loops=)

54.          

CTE aluno_2017

55. 0.000 0.000 ↓ 0.0

Unique (cost=1,374,553.38..1,518,685.32 rows=2,135,288 width=337) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Sort (cost=1,374,553.38..1,379,891.60 rows=2,135,288 width=337) (actual rows= loops=)

  • Sort Key: ((aluno_5.co_aluno)::bigint), ((aluno_5.co_ies)::bigint), (unaccent(lower(curso_5.no_curso))), ((curso_5.co_ocde_area_geral)::bigint), aluno_5.tp_grau_academico, (CASE WHEN (aluno_5.tp_grau_academico = 1) THEN 'Bacharelado'::text (...)
57. 0.000 0.000 ↓ 0.0

Hash Join (cost=19,682.71..958,226.56 rows=2,135,288 width=337) (actual rows= loops=)

  • Hash Cond: (aluno_5.co_curso = curso_5.co_curso)
58. 0.000 0.000 ↓ 0.0

Hash Join (cost=17,524.63..686,488.37 rows=2,135,288 width=108) (actual rows= loops=)

  • Hash Cond: (aluno_5.co_curso_polo = local_oferta_5.co_curso_polo)
59. 0.000 0.000 ↓ 0.0

Seq Scan on dm_aluno_2017 aluno_5 (cost=0.00..613,440.17 rows=9,112,183 width=96) (actual rows= loops=)

  • Filter: (tp_categoria_administrativa = ANY ('{4,5,7}'::integer[]))
60. 0.000 0.000 ↓ 0.0

Hash (cost=13,062.06..13,062.06 rows=357,006 width=24) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Seq Scan on dm_local_oferta_2017 local_oferta_5 (cost=0.00..13,062.06 rows=357,006 width=24) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Hash (cost=1,712.48..1,712.48 rows=35,648 width=37) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Seq Scan on dm_curso_2017 curso_5 (cost=0.00..1,712.48 rows=35,648 width=37) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13,297,119.94..16,136,641.06 rows=48,094,870 width=420) (actual rows= loops=)

  • Hash Cond: (aluno_2012.no_curso = canonical_courses.clean_name)
65. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13,297,087.44..14,429,240.67 rows=9,618,974 width=384) (actual rows= loops=)

  • Hash Cond: (aluno_2012.co_uf_nascimento = uf_nasc.id)
66. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13,297,085.83..14,380,182.30 rows=9,618,974 width=389) (actual rows= loops=)

  • Hash Cond: (aluno_2012.co_municipio_nascimento = municipios_nasc.id)
67. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13,296,894.00..14,247,729.58 rows=9,618,974 width=384) (actual rows= loops=)

  • Hash Cond: (aluno_2012.co_uf_curso = uf_curso.id)
68. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13,296,892.40..14,198,671.21 rows=9,618,974 width=389) (actual rows= loops=)

  • Hash Cond: (aluno_2012.co_municipio_curso = municipios_curso.id)
69. 0.000 0.000 ↓ 0.0

Unique (cost=13,296,700.57..13,970,028.75 rows=9,618,974 width=384) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Sort (cost=13,296,700.57..13,320,748.00 rows=9,618,974 width=384) (actual rows= loops=)

  • Sort Key: aluno_2012.co_aluno, aluno_2012.co_ies, aluno_2012.no_curso, aluno_2012.co_ocde_area_geral, aluno_2012.co_grau_academico, aluno_2012.ds_grau_academico, aluno_2012.co_modalidade_ensino, aluno_2012.ds_mod (...)
71. 0.000 0.000 ↓ 0.0

Append (cost=9,556,588.31..10,264,742.15 rows=9,618,974 width=384) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Result (cost=9,556,588.31..10,173,992.41 rows=7,483,686 width=2,320) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Unique (cost=9,556,588.31..10,080,446.33 rows=7,483,686 width=2,292) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Sort (cost=9,556,588.31..9,575,297.53 rows=7,483,686 width=2,292) (actual rows= loops=)

  • Sort Key: aluno_2012.co_aluno, aluno_2012.co_ies, aluno_2012.no_curso, aluno_2012.co_ocde_area_geral, aluno_2012.co_grau_academico, aluno_2012.ds_grau_academico, aluno_2012.co_modalidade_e (...)
75. 0.000 0.000 ↓ 0.0

Append (cost=0.00..224,510.58 rows=7,483,686 width=2,292) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

CTE Scan on aluno_2012 (cost=0.00..23,501.28 rows=1,175,064 width=2,292) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

CTE Scan on aluno_2013 (cost=0.00..25,829.26 rows=1,291,463 width=2,776) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

CTE Scan on aluno_2014 (cost=0.00..31,800.46 rows=1,590,023 width=3,260) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

CTE Scan on aluno_2015 (cost=0.00..32,495.92 rows=1,624,796 width=3,260) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

CTE Scan on aluno_2016 (cost=0.00..36,046.80 rows=1,802,340 width=3,260) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 6 (cost=0.00..90,749.74 rows=2,135,288 width=384) (actual rows= loops=)

82. 0.000 0.000 ↓ 0.0

CTE Scan on aluno_2017 (cost=0.00..42,705.76 rows=2,135,288 width=364) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Hash (cost=122.48..122.48 rows=5,548 width=17) (actual rows= loops=)

84. 0.000 0.000 ↓ 0.0

Seq Scan on municipios municipios_curso (cost=0.00..122.48 rows=5,548 width=17) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

Hash (cost=1.27..1.27 rows=27 width=7) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

Seq Scan on uf uf_curso (cost=0.00..1.27 rows=27 width=7) (actual rows= loops=)

87. 0.000 0.000 ↓ 0.0

Hash (cost=122.48..122.48 rows=5,548 width=17) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

Seq Scan on municipios municipios_nasc (cost=0.00..122.48 rows=5,548 width=17) (actual rows= loops=)

89. 0.000 0.000 ↓ 0.0

Hash (cost=1.27..1.27 rows=27 width=7) (actual rows= loops=)

90. 0.000 0.000 ↓ 0.0

Seq Scan on uf uf_nasc (cost=0.00..1.27 rows=27 width=7) (actual rows= loops=)

91. 0.000 0.000 ↓ 0.0

Hash (cost=20.00..20.00 rows=1,000 width=100) (actual rows= loops=)

92. 0.000 0.000 ↓ 0.0

CTE Scan on canonical_courses (cost=0.00..20.00 rows=1,000 width=100) (actual rows= loops=)

93. 0.000 0.000 ↓ 0.0

Hash (cost=39.90..39.90 rows=2,590 width=16) (actual rows= loops=)

94. 0.000 0.000 ↓ 0.0

Seq Scan on depara_oficial (cost=0.00..39.90 rows=2,590 width=16) (actual rows= loops=)