explain.depesz.com

PostgreSQL's explain analyze made readable

Result: d4gN

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 24,479.733 ↓ 0.0 0 1

HashAggregate (cost=382,657.54..382,664.28 rows=1 width=126) (actual time=24,479.733..24,479.733 rows=0 loops=1)

  • Group Key: pessoa.codigo, pessoa.nome, pessoa.email, pessoa.email2, matricula.matricula, curso.nome, unidadeensino.nome
2. 1,154.011 24,479.730 ↓ 0.0 0 1

Nested Loop (cost=1.26..382,657.53 rows=1 width=126) (actual time=24,479.730..24,479.730 rows=0 loops=1)

  • Join Filter: ((unidadeensino.configuracoes = configuracoes.codigo) AND ((configuracoes.controlarsuspensaomatriculapendenciadocumentos AND (unidadeensino.codigo = 41) AND ((matricula.situacao)::text = 'AT'::text) AND ((NOT matricula.matriculasuspensa) OR (matricula.matriculasuspensa IS NULL)) AND (SubPlan 3) AND ((SubPlan 5) >= ('now'::cstring)::date) AND ((SubPlan 7) <= (('now'::cstring)::date - configuracoes.nrdiasquartoavisoriscosuspensao)) AND (matricula.dataenvionotificacao3 IS NOT NULL) AND (matricula.dataenvionotificacao4 IS NULL)) OR (((('now'::cstring)::date - configuracoes.periodicidadequartoavisoriscosuspensao) = matricula.dataenvionotificacao4) AND (configuracoes.nrdiasterceiroavisoriscosuspensao IS NOT NULL) AND (configuracoes.nrdiasquartoavisoriscosuspensao > 0))))
  • Rows Removed by Join Filter: 136847
3. 3,430.224 23,188.872 ↓ 136,847.0 136,847 1

Nested Loop (cost=1.26..382,558.27 rows=1 width=163) (actual time=0.200..23,188.872 rows=136,847 loops=1)

  • Join Filter: (matricula.unidadeensino = unidadeensino.codigo)
  • Rows Removed by Join Filter: 10400372
4. 12,045.191 18,937.566 ↓ 136,847.0 136,847 1

Nested Loop (cost=1.26..382,550.54 rows=1 width=143) (actual time=0.167..18,937.566 rows=136,847 loops=1)

  • Join Filter: (matricula.curso = curso.codigo)
  • Rows Removed by Join Filter: 57065199
5. 213.372 3,334.353 ↓ 136,847.0 136,847 1

Nested Loop (cost=1.26..382,523.14 rows=1 width=96) (actual time=0.078..3,334.353 rows=136,847 loops=1)

6. 784.835 2,436.746 ↓ 136,847.0 136,847 1

Merge Join (cost=0.84..382,522.59 rows=1 width=51) (actual time=0.061..2,436.746 rows=136,847 loops=1)

  • Merge Cond: ((matricula.matricula)::text = (matriculaperiodo.matricula)::text)
  • Join Filter: ((SubPlan 2) = matriculaperiodo.codigo)
  • Rows Removed by Join Filter: 2352
7. 136.214 136.214 ↑ 1.0 136,897 1

Index Scan using matricula_pkey on matricula (cost=0.42..5,899.46 rows=136,897 width=37) (actual time=0.013..136.214 rows=136,897 loops=1)

8. 123.707 123.707 ↑ 1.0 139,199 1

Index Scan using ch_matriculaperiodo_matricula on matriculaperiodo (cost=0.42..5,330.57 rows=139,199 width=31) (actual time=0.010..123.707 rows=139,199 loops=1)

9.          

SubPlan (forMerge Join)

10. 139.199 1,391.990 ↑ 1.0 1 139,199

Aggregate (cost=2.64..2.65 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=139,199)

11. 1,252.791 1,252.791 ↑ 1.0 1 139,199

Index Scan using idx_matriculaperiodo_matricula_ano_semestre on matriculaperiodo mp (cost=0.42..2.64 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=139,199)

  • Index Cond: ((matricula)::text = (matricula.matricula)::text)
12. 684.235 684.235 ↑ 1.0 1 136,847

Index Scan using pessoa_pkey on pessoa (cost=0.42..0.54 rows=1 width=49) (actual time=0.005..0.005 rows=1 loops=136,847)

  • Index Cond: (codigo = matricula.aluno)
13. 3,558.022 3,558.022 ↑ 1.0 418 136,847

Seq Scan on curso (cost=0.00..22.18 rows=418 width=55) (actual time=0.001..0.026 rows=418 loops=136,847)

14. 821.082 821.082 ↑ 1.0 77 136,847

Seq Scan on unidadeensino (cost=0.00..6.77 rows=77 width=24) (actual time=0.001..0.006 rows=77 loops=136,847)

15. 136.847 136.847 ↑ 1.0 1 136,847

Seq Scan on configuracoes (cost=0.00..1.01 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=136,847)

16.          

SubPlan (forNested Loop)

17. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.42..4.35 rows=1 width=13) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_documetacaomatricula_matricula on documetacaomatricula dm (cost=0.42..4.35 rows=1 width=13) (never executed)

  • Index Cond: ((matricula.matricula)::text = (matricula)::text)
  • Filter: ((NOT entregue) AND gerarsuspensaomatricula)
19. 0.000 0.000 ↓ 0.0 0

Limit (cost=9.85..47.90 rows=1 width=8) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=9.85..426,429.95 rows=11,207 width=8) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Index Scan Backward using idx_horarioturmadia_data on horarioturmadia (cost=0.42..1,737.05 rows=44,795 width=12) (never executed)

  • Index Cond: (data >= matriculaperiodo.data)
22. 0.000 0.000 ↓ 0.0 0

Index Scan using horarioturma_pkey on horarioturma (cost=9.43..9.47 rows=1 width=4) (never executed)

  • Index Cond: (codigo = horarioturmadia.horarioturma)
  • Filter: (((turma = matriculaperiodo.turma) OR (hashed SubPlan 4)) AND CASE curso.periodicidade WHEN 'SE'::text THEN (((anovigente)::text = (matriculaperiodo.ano)::text) AND ((semestrevigente)::text = (matriculaperiodo.semestre)::text)) WHEN 'AN'::text THEN ((anovigente)::text = (matriculaperiodo.ano)::text) ELSE true END)
23.          

SubPlan (forIndex Scan)

24. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_turmaagrupada_turma on turmaagrupada (cost=0.15..9.13 rows=10 width=4) (never executed)

  • Index Cond: (turma = matriculaperiodo.turma)
25. 0.000 0.000 ↓ 0.0 0

Limit (cost=9.85..47.90 rows=1 width=8) (never executed)

26. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=9.85..426,429.95 rows=11,207 width=8) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_horarioturmadia_data on horarioturmadia horarioturmadia_1 (cost=0.42..1,737.05 rows=44,795 width=12) (never executed)

  • Index Cond: (data >= matriculaperiodo.data)
28. 0.000 0.000 ↓ 0.0 0

Index Scan using horarioturma_pkey on horarioturma horarioturma_1 (cost=9.43..9.47 rows=1 width=4) (never executed)

  • Index Cond: (codigo = horarioturmadia_1.horarioturma)
  • Filter: (((turma = matriculaperiodo.turma) OR (hashed SubPlan 6)) AND CASE curso.periodicidade WHEN 'SE'::text THEN (((anovigente)::text = (matriculaperiodo.ano)::text) AND ((semestrevigente)::text = (matriculaperiodo.semestre)::text)) WHEN 'AN'::text THEN ((anovigente)::text = (matriculaperiodo.ano)::text) ELSE true END)
29.          

SubPlan (forIndex Scan)

30. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_turmaagrupada_turma on turmaagrupada turmaagrupada_1 (cost=0.15..9.13 rows=10 width=4) (never executed)

  • Index Cond: (turma = matriculaperiodo.turma)
31.          

SubPlan (forHashAggregate)

32. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.57..6.72 rows=1 width=38) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_documetacaomatricula_matricula on documetacaomatricula (cost=0.42..4.35 rows=1 width=4) (never executed)

  • Index Cond: ((matricula)::text = (matricula.matricula)::text)
  • Filter: (NOT entregue)
34. 0.000 0.000 ↓ 0.0 0

Index Scan using tipodocumento_pkey on tipodocumento (cost=0.14..2.37 rows=1 width=42) (never executed)

  • Index Cond: (codigo = documetacaomatricula.tipodedocumento)
Planning time : 322.095 ms
Execution time : 24,495.750 ms