explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0bor

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 7,759.564 ↓ 0.0 0 1

HashAggregate (cost=612,405.79..612,427.74 rows=1 width=125) (actual time=7,759.564..7,759.564 rows=0 loops=1)

  • Group Key: pessoa.codigo, pessoa.nome, pessoa.email, pessoa.email2, matricula.matricula, curso.nome, unidadeensino.nome
2. 489.601 7,759.562 ↓ 0.0 0 1

Nested Loop (cost=10,042.70..612,405.77 rows=1 width=125) (actual time=7,759.562..7,759.562 rows=0 loops=1)

  • Join Filter: ((configuracoes.codigo = unidadeensino.configuracoes) AND ((configuracoes.controlarsuspensaomatriculapendenciadocumentos AND (unidadeensino.codigo = 68) 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: 140795
3. 206.845 6,988.371 ↓ 140,795.0 140,795 1

Nested Loop (cost=10,042.56..612,282.71 rows=1 width=159) (actual time=266.482..6,988.371 rows=140,795 loops=1)

4. 238.421 6,359.141 ↓ 140,795.0 140,795 1

Nested Loop (cost=10,042.28..612,282.40 rows=1 width=112) (actual time=266.461..6,359.141 rows=140,795 loops=1)

5. 970.109 4,994.360 ↓ 140,795.0 140,795 1

Hash Join (cost=10,041.86..612,281.56 rows=1 width=68) (actual time=266.428..4,994.360 rows=140,795 loops=1)

  • Hash Cond: (((matricula.matricula)::text = (matriculaperiodo.matricula)::text) AND ((SubPlan 2) = matriculaperiodo.codigo))
6. 97.343 97.343 ↓ 1.0 140,845 1

Seq Scan on matricula (cost=0.00..6,413.74 rows=140,774 width=37) (actual time=0.019..97.343 rows=140,845 loops=1)

7. 97.712 265.588 ↓ 1.0 143,184 1

Hash (cost=7,895.21..7,895.21 rows=143,110 width=48) (actual time=265.588..265.588 rows=143,184 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 12430kB
8. 95.343 167.876 ↓ 1.0 143,184 1

Nested Loop (cost=0.00..7,895.21 rows=143,110 width=48) (actual time=0.016..167.876 rows=143,184 loops=1)

9. 0.003 0.003 ↑ 1.0 1 1

Seq Scan on configuracoes (cost=0.00..1.01 rows=1 width=17) (actual time=0.002..0.003 rows=1 loops=1)

10. 72.530 72.530 ↓ 1.0 143,184 1

Seq Scan on matriculaperiodo (cost=0.00..6,463.10 rows=143,110 width=31) (actual time=0.012..72.530 rows=143,184 loops=1)

11.          

SubPlan (forHash Join)

12. 281.640 3,661.320 ↑ 1.0 1 281,640

Aggregate (cost=8.44..8.45 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=281,640)

13. 3,379.680 3,379.680 ↑ 1.0 1 281,640

Index Scan using idx_matriculaperiodo_matricula_ano_semestre on matriculaperiodo mp (cost=0.42..8.44 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=281,640)

  • Index Cond: ((matricula)::text = (matricula.matricula)::text)
14. 1,126.360 1,126.360 ↑ 1.0 1 140,795

Index Scan using pessoa_pkey on pessoa (cost=0.42..0.83 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=140,795)

  • Index Cond: (codigo = matricula.aluno)
15. 422.385 422.385 ↑ 1.0 1 140,795

Index Scan using curso_pkey on curso (cost=0.27..0.29 rows=1 width=55) (actual time=0.003..0.003 rows=1 loops=140,795)

  • Index Cond: (codigo = matricula.curso)
16. 281.590 281.590 ↑ 1.0 1 140,795

Index Scan using unidadeensino_pkey on unidadeensino (cost=0.14..0.17 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=140,795)

  • Index Cond: (codigo = matricula.unidadeensino)
17.          

SubPlan (forNested Loop)

18. 0.000 0.000 ↓ 0.0 0

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

19. 0.000 0.000 ↓ 0.0 0

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

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

Limit (cost=15.23..57.58 rows=1 width=8) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=15.23..494,146.79 rows=11,669 width=8) (never executed)

  • Join Filter: (horarioturmadia.horarioturma = horarioturma.codigo)
22. 0.000 0.000 ↓ 0.0 0

Index Scan Backward using idx_horarioturmadia_data on horarioturmadia (cost=0.42..3,744.29 rows=46,627 width=12) (never executed)

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

Materialize (cost=14.81..121.34 rows=701 width=4) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Seq Scan on horarioturma (cost=14.81..117.84 rows=701 width=4) (never executed)

  • 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)
25.          

SubPlan (forSeq Scan)

26. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on turmaagrupada (cost=4.23..14.79 rows=10 width=4) (never executed)

  • Recheck Cond: (turma = matriculaperiodo.turma)
27. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_turmaagrupada_turma (cost=0.00..4.23 rows=10 width=0) (never executed)

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

Limit (cost=15.23..57.58 rows=1 width=8) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=15.23..494,146.79 rows=11,669 width=8) (never executed)

  • Join Filter: (horarioturmadia_1.horarioturma = horarioturma_1.codigo)
30. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_horarioturmadia_data on horarioturmadia horarioturmadia_1 (cost=0.42..3,744.29 rows=46,627 width=12) (never executed)

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

Materialize (cost=14.81..121.34 rows=701 width=4) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Seq Scan on horarioturma horarioturma_1 (cost=14.81..117.84 rows=701 width=4) (never executed)

  • 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)
33.          

SubPlan (forSeq Scan)

34. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on turmaagrupada turmaagrupada_1 (cost=4.23..14.79 rows=10 width=4) (never executed)

  • Recheck Cond: (turma = matriculaperiodo.turma)
35. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_turmaagrupada_turma (cost=0.00..4.23 rows=10 width=0) (never executed)

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

SubPlan (forHashAggregate)

37. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=14.98..21.94 rows=1 width=37) (never executed)

  • Hash Cond: (tipodocumento.codigo = documetacaomatricula.tipodedocumento)
38. 0.000 0.000 ↓ 0.0 0

Seq Scan on tipodocumento (cost=0.00..6.15 rows=215 width=41) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Hash (cost=14.97..14.97 rows=1 width=4) (never executed)

40. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: ((matricula)::text = (matricula.matricula)::text)
  • Filter: (NOT entregue)
Planning time : 8.659 ms
Execution time : 7,759.882 ms