explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dxQx

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 10,489.105 ↓ 0.0 0 1

Group (cost=80,291.60..80,291.62 rows=1 width=122) (actual time=10,489.105..10,489.105 rows=0 loops=1)

  • Group Key: pessoa.codigo, matricula.matricula, curso.nome, unidadeensino.nome
  • Buffers: shared hit=2646210 read=1449
  • I/O Timings: read=580.093
2. 0.068 10,489.103 ↓ 0.0 0 1

Sort (cost=80,291.60..80,291.61 rows=1 width=122) (actual time=10,489.103..10,489.103 rows=0 loops=1)

  • Sort Key: pessoa.codigo, matricula.matricula, curso.nome, unidadeensino.nome
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2646210 read=1449
  • I/O Timings: read=580.093
3. 274.454 10,489.035 ↓ 0.0 0 1

Hash Join (cost=8,972.66..80,291.59 rows=1 width=122) (actual time=10,489.035..10,489.035 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 2) AND ((SubPlan 4) >= CURRENT_DATE) AND ((SubPlan 6) <= (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: 154712
  • Buffers: shared hit=2646204 read=1449
  • I/O Timings: read=580.093
4. 392.530 10,214.550 ↓ 154,712.0 154,712 1

Nested Loop (cost=8,971.64..80,290.57 rows=1 width=159) (actual time=264.842..10,214.550 rows=154,712 loops=1)

  • Buffers: shared hit=2646203 read=1449
  • I/O Timings: read=580.093
5. 367.621 9,512.596 ↓ 154,712.0 154,712 1

Nested Loop (cost=8,971.49..80,290.40 rows=1 width=139) (actual time=264.819..9,512.596 rows=154,712 loops=1)

  • Buffers: shared hit=2336779 read=1449
  • I/O Timings: read=580.093
6. 262.973 8,680.839 ↓ 154,712.0 154,712 1

Nested Loop (cost=8,971.22..80,290.11 rows=1 width=93) (actual time=264.785..8,680.839 rows=154,712 loops=1)

  • Buffers: shared hit=1872592 read=1449
  • I/O Timings: read=580.093
7. 1,082.329 7,025.458 ↓ 154,712.0 154,712 1

Hash Join (cost=8,970.80..80,289.30 rows=1 width=52) (actual time=264.725..7,025.458 rows=154,712 loops=1)

  • Hash Cond: (((matricula.matricula)::text = (matriculaperiodo.matricula)::text) AND ((SubPlan 1) = matriculaperiodo.codigo))
  • Buffers: shared hit=1253505 read=1421
  • I/O Timings: read=572.180
8. 110.085 110.085 ↑ 1.0 154,763 1

Seq Scan on matricula (cost=0.00..5,118.63 rows=154,763 width=38) (actual time=0.021..110.085 rows=154,763 loops=1)

  • Buffers: shared hit=3571
9. 124.686 262.494 ↑ 1.0 157,552 1

Hash (cost=6,607.52..6,607.52 rows=157,552 width=32) (actual time=262.494..262.494 rows=157,552 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 13126kB
  • Buffers: shared hit=5032
10. 137.808 137.808 ↑ 1.0 157,552 1

Seq Scan on matriculaperiodo (cost=0.00..6,607.52 rows=157,552 width=32) (actual time=0.029..137.808 rows=157,552 loops=1)

  • Buffers: shared hit=5032
11.          

SubPlan (for Hash Join)

12. 618.950 5,570.550 ↑ 1.0 1 309,475

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

  • Buffers: shared hit=1244902 read=1421
  • I/O Timings: read=572.180
13. 4,951.600 4,951.600 ↑ 1.0 1 309,475

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

  • Index Cond: ((matricula)::text = (matricula.matricula)::text)
  • Buffers: shared hit=1244902 read=1421
  • I/O Timings: read=572.180
14. 1,392.408 1,392.408 ↑ 1.0 1 154,712

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,712)

  • Index Cond: (codigo = matricula.aluno)
  • Buffers: shared hit=619087 read=28
  • I/O Timings: read=7.913
15. 464.136 464.136 ↑ 1.0 1 154,712

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,712)

  • Index Cond: (codigo = matricula.curso)
  • Buffers: shared hit=464187
16. 309.424 309.424 ↑ 1.0 1 154,712

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,712)

  • Index Cond: (codigo = matricula.unidadeensino)
  • Buffers: shared hit=309424
17. 0.006 0.031 ↑ 1.0 1 1

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

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

Seq Scan on configuracoes (cost=0.00..1.01 rows=1 width=17) (actual time=0.024..0.025 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,539.06 rows=12,814 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,630.00 rows=51,238 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 3)) 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=0.70..4.74 rows=1 width=8) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.70..51,747.05 rows=12,814 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,630.00 rows=51,238 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=0.28..0.94 rows=1 width=4) (never executed)

  • Index Cond: (codigo = horarioturmadia_1.horarioturma)
  • Filter: (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 AND ((turma = matriculaperiodo.turma) OR (SubPlan 5)))
32.          

SubPlan (for Index Scan)

33. 0.000 0.000 ↓ 0.0 0

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

  • Filter: ((turma = matriculaperiodo.turma) AND (turmaorigem = horarioturma_1.turma))
Planning time : 14.483 ms
Execution time : 10,489.945 ms