explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eBLC

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 45,214.529 ↓ 0.0 0 1

HashAggregate (cost=1,134,461.62..1,134,481.24 rows=1 width=123) (actual time=45,214.529..45,214.529 rows=0 loops=1)

  • Group Key: pessoa.codigo, pessoa.nome, pessoa.email, pessoa.email2, matricula.matricula, curso.nome, unidadeensino.nome
2. 485.457 45,214.527 ↓ 0.0 0 1

Nested Loop (cost=1.26..1,134,461.60 rows=1 width=123) (actual time=45,214.527..45,214.527 rows=0 loops=1)

  • Join Filter: ((unidadeensino.configuracoes = configuracoes.codigo) AND ((configuracoes.controlarsuspensaomatriculapendenciadocumentos AND (unidadeensino.codigo = 42) 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: 128927
3. 4,212.041 44,600.143 ↓ 128,927.0 128,927 1

Nested Loop (cost=1.26..1,134,343.03 rows=1 width=160) (actual time=0.859..44,600.143 rows=128,927 loops=1)

  • Join Filter: (matricula.unidadeensino = unidadeensino.codigo)
  • Rows Removed by Join Filter: 9540598
4. 17,304.819 31,492.139 ↓ 128,927.0 128,927 1

Nested Loop (cost=1.26..1,134,172.85 rows=1 width=140) (actual time=0.844..31,492.139 rows=128,927 loops=1)

  • Join Filter: (matricula.curso = curso.codigo)
  • Rows Removed by Join Filter: 50797238
5. 236.407 3,486.379 ↓ 128,927.0 128,927 1

Nested Loop (cost=1.26..1,134,147.00 rows=1 width=94) (actual time=0.618..3,486.379 rows=128,927 loops=1)

6. 696.724 2,476.410 ↓ 128,927.0 128,927 1

Merge Join (cost=0.84..1,134,146.16 rows=1 width=51) (actual time=0.581..2,476.410 rows=128,927 loops=1)

  • Merge Cond: ((matricula.matricula)::text = (matriculaperiodo.matricula)::text)
  • Join Filter: ((SubPlan 2) = matriculaperiodo.codigo)
  • Rows Removed by Join Filter: 2292
7. 147.786 147.786 ↑ 1.1 128,977 1

Index Scan using matricula_pkey on matricula (cost=0.42..11,043.96 rows=137,486 width=37) (actual time=0.005..147.786 rows=128,977 loops=1)

8. 188.491 188.491 ↓ 1.0 131,219 1

Index Scan using ch_matriculaperiodo_matricula on matriculaperiodo (cost=0.42..13,026.35 rows=131,107 width=31) (actual time=0.545..188.491 rows=131,219 loops=1)

9.          

SubPlan (forMerge Join)

10. 393.657 1,443.409 ↑ 1.0 1 131,219

Aggregate (cost=8.44..8.45 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=131,219)

11. 1,049.752 1,049.752 ↑ 1.0 1 131,219

Index Scan using idx_matriculaperiodo_matricula_ano_semestre on matriculaperiodo mp (cost=0.42..8.44 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=131,219)

  • Index Cond: ((matricula)::text = (matricula.matricula)::text)
12. 773.562 773.562 ↑ 1.0 1 128,927

Index Scan using pessoa_pkey on pessoa (cost=0.42..0.83 rows=1 width=47) (actual time=0.005..0.006 rows=1 loops=128,927)

  • Index Cond: (codigo = matricula.aluno)
13. 10,700.941 10,700.941 ↓ 1.0 395 128,927

Seq Scan on curso (cost=0.00..20.93 rows=393 width=54) (actual time=0.001..0.083 rows=395 loops=128,927)

14. 8,895.963 8,895.963 ↑ 5.4 75 128,927

Seq Scan on unidadeensino (cost=0.00..165.08 rows=408 width=24) (actual time=0.001..0.069 rows=75 loops=128,927)

15. 128.927 128.927 ↑ 1.0 1 128,927

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

16.          

SubPlan (forNested Loop)

17. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.42..12.77 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..12.77 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=15.23..55.46 rows=1 width=8) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=15.23..427,383.06 rows=10,623 width=8) (never executed)

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

Index Scan Backward using idx_horarioturmadia_data on horarioturmadia (cost=0.42..3,732.00 rows=42,460 width=12) (never executed)

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

Materialize (cost=14.81..114.23 rows=665 width=4) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Seq Scan on horarioturma (cost=14.81..110.91 rows=665 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)
24.          

SubPlan (forSeq Scan)

25. 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)
26. 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)
27. 0.000 0.000 ↓ 0.0 0

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

28. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=15.23..427,383.06 rows=10,623 width=8) (never executed)

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

Index Scan using idx_horarioturmadia_data on horarioturmadia horarioturmadia_1 (cost=0.42..3,732.00 rows=42,460 width=12) (never executed)

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

Materialize (cost=14.81..114.23 rows=665 width=4) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Seq Scan on horarioturma horarioturma_1 (cost=14.81..110.91 rows=665 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)
32.          

SubPlan (forSeq Scan)

33. 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)
34. 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)
35.          

SubPlan (forHashAggregate)

36. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=12.78..19.61 rows=1 width=38) (never executed)

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

Seq Scan on tipodocumento (cost=0.00..6.05 rows=205 width=42) (never executed)

38. 0.000 0.000 ↓ 0.0 0

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

39. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: ((matricula)::text = (matricula.matricula)::text)
  • Filter: (NOT entregue)