explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aBqE

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 10,101.348 ↓ 0.0 0 1

Group (cost=80,228.53..80,257.07 rows=1 width=154) (actual time=10,101.348..10,101.348 rows=0 loops=1)

  • Group Key: pessoa.codigo, matricula.matricula, curso.nome, unidadeensino.nome
  • Buffers: shared hit=2645292 read=1379 dirtied=30
  • I/O Timings: read=274.403
2. 0.074 10,101.347 ↓ 0.0 0 1

Sort (cost=80,228.53..80,228.53 rows=1 width=122) (actual time=10,101.347..10,101.347 rows=0 loops=1)

  • Sort Key: pessoa.codigo, matricula.matricula, curso.nome, unidadeensino.nome
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2645292 read=1379 dirtied=30
  • I/O Timings: read=274.403
3. 273.869 10,101.273 ↓ 0.0 0 1

Hash Join (cost=8,969.49..80,228.52 rows=1 width=122) (actual time=10,101.273..10,101.273 rows=0 loops=1)

  • Hash Cond: (unidadeensino.configuracoes = configuracoes.codigo)
  • Join Filter: ((configuracoes.controlarsuspensaomatriculapendenciadocumentos AND (unidadeensino.codigo = 17) AND ((matricula.situacao)::text = 'AT'::text) AND ((NOT matricula.matriculasuspensa) OR (matricula.matriculasuspensa IS NULL)) AND (SubPlan 3) AND ((SubPlan 5) >= CURRENT_DATE) AND ((SubPlan 7) <= (CURRENT_DATE - configuracoes.nrdiasquartoavisoriscosuspensao)) AND (matricula.dataenvionotificacao3 IS NOT NULL) AND (matricula.dataenvionotificacao4 IS NULL)) OR (((CURRENT_DATE - configuracoes.periodicidadequartoavisoriscosuspensao) = matricula.dataenvionotificacao4) AND (configuracoes.nrdiasterceiroavisoriscosuspensao IS NOT NULL) AND (configuracoes.nrdiasquartoavisoriscosuspensao > 0)))
  • Rows Removed by Join Filter: 154648
  • Buffers: shared hit=2645286 read=1379 dirtied=30
  • I/O Timings: read=274.403
4. 406.459 9,827.375 ↓ 154,648.0 154,648 1

Nested Loop (cost=8,968.46..80,227.49 rows=1 width=159) (actual time=250.492..9,827.375 rows=154,648 loops=1)

  • Buffers: shared hit=2645285 read=1379 dirtied=30
  • I/O Timings: read=274.403
5. 318.443 9,111.620 ↓ 154,648.0 154,648 1

Nested Loop (cost=8,968.32..80,227.33 rows=1 width=139) (actual time=250.466..9,111.620 rows=154,648 loops=1)

  • Buffers: shared hit=2335989 read=1379 dirtied=30
  • I/O Timings: read=274.403
6. 220.806 8,329.233 ↓ 154,648.0 154,648 1

Nested Loop (cost=8,968.05..80,227.04 rows=1 width=93) (actual time=250.433..8,329.233 rows=154,648 loops=1)

  • Buffers: shared hit=1871994 read=1379 dirtied=30
  • I/O Timings: read=274.403
7. 1,098.851 6,716.595 ↓ 154,648.0 154,648 1

Hash Join (cost=8,967.62..80,226.22 rows=1 width=52) (actual time=250.376..6,716.595 rows=154,648 loops=1)

  • Hash Cond: (((matricula.matricula)::text = (matriculaperiodo.matricula)::text) AND ((SubPlan 2) = matriculaperiodo.codigo))
  • Buffers: shared hit=1253135 read=1379 dirtied=30
  • I/O Timings: read=274.403
8. 110.860 110.860 ↓ 1.0 154,699 1

Seq Scan on matricula (cost=0.00..5,107.49 rows=154,649 width=38) (actual time=0.020..110.860 rows=154,699 loops=1)

  • Buffers: shared hit=3561
9. 119.210 247.985 ↓ 1.0 157,482 1

Hash (cost=6,606.25..6,606.25 rows=157,425 width=32) (actual time=247.984..247.985 rows=157,482 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 13121kB
  • Buffers: shared hit=5032
10. 128.775 128.775 ↓ 1.0 157,482 1

Seq Scan on matriculaperiodo (cost=0.00..6,606.25 rows=157,425 width=32) (actual time=0.023..128.775 rows=157,482 loops=1)

  • Buffers: shared hit=5032
11.          

SubPlan (for Hash Join)

12. 618.694 5,258.899 ↑ 1.0 1 309,347

Aggregate (cost=8.44..8.45 rows=1 width=4) (actual time=0.017..0.017 rows=1 loops=309,347)

  • Buffers: shared hit=1244542 read=1379 dirtied=30
  • I/O Timings: read=274.403
13. 4,640.205 4,640.205 ↑ 1.0 1 309,347

Index Scan using idx_matriculaperiodo_matricula_ano_semestre on matriculaperiodo mp (cost=0.42..8.44 rows=1 width=4) (actual time=0.014..0.015 rows=1 loops=309,347)

  • Index Cond: ((matricula)::text = (matricula.matricula)::text)
  • Buffers: shared hit=1244542 read=1379 dirtied=30
  • I/O Timings: read=274.403
14. 1,391.832 1,391.832 ↑ 1.0 1 154,648

Index Scan using pessoa_pkey on pessoa (cost=0.42..0.81 rows=1 width=45) (actual time=0.009..0.009 rows=1 loops=154,648)

  • Index Cond: (codigo = matricula.aluno)
  • Buffers: shared hit=618859
15. 463.944 463.944 ↑ 1.0 1 154,648

Index Scan using curso_pkey on curso (cost=0.27..0.29 rows=1 width=54) (actual time=0.003..0.003 rows=1 loops=154,648)

  • Index Cond: (codigo = matricula.curso)
  • Buffers: shared hit=463995
16. 309.296 309.296 ↑ 1.0 1 154,648

Index Scan using unidadeensino_pkey on unidadeensino (cost=0.14..0.16 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=154,648)

  • Index Cond: (codigo = matricula.unidadeensino)
  • Buffers: shared hit=309296
17. 0.007 0.029 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=17) (actual time=0.029..0.029 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
18. 0.022 0.022 ↑ 1.0 1 1

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

  • Buffers: shared hit=1
19.          

SubPlan (for Hash Join)

20. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.42..20.49 rows=1 width=14) (never executed)

21. 0.000 0.000 ↓ 0.0 0

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

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

Limit (cost=1.90..8.27 rows=1 width=8) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.90..81,521.00 rows=12,809 width=8) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Index Scan Backward using idx_horarioturmadia_data on horarioturmadia (cost=0.42..3,637.74 rows=51,221 width=12) (never executed)

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

Index Scan using horarioturma_pkey on horarioturma (cost=1.48..1.52 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)
26.          

SubPlan (for Index Scan)

27. 0.000 0.000 ↓ 0.0 0

Seq Scan on turmaagrupada (cost=0.00..1.20 rows=1 width=4) (never executed)

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

Limit (cost=1.90..8.27 rows=1 width=8) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.90..81,521.00 rows=12,809 width=8) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_horarioturmadia_data on horarioturmadia horarioturmadia_1 (cost=0.42..3,637.74 rows=51,221 width=12) (never executed)

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

Index Scan using horarioturma_pkey on horarioturma horarioturma_1 (cost=1.48..1.52 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)
32.          

SubPlan (for Index Scan)

33. 0.000 0.000 ↓ 0.0 0

Seq Scan on turmaagrupada turmaagrupada_1 (cost=0.00..1.20 rows=1 width=4) (never executed)

  • Filter: (turma = matriculaperiodo.turma)
34.          

SubPlan (for Group)

35. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=20.28..28.52 rows=2 width=37) (never executed)

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

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

37. 0.000 0.000 ↓ 0.0 0

Hash (cost=20.25..20.25 rows=2 width=4) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on documetacaomatricula (cost=4.45..20.25 rows=2 width=4) (never executed)

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

Bitmap Index Scan on idx_documetacaomatricula_matricula (cost=0.00..4.45 rows=4 width=0) (never executed)

  • Index Cond: ((matricula)::text = (matricula.matricula)::text)
Planning time : 17.975 ms
Execution time : 10,102.450 ms