explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1O7Ep

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 27,288.826 ↑ 1.0 1 1

Limit (cost=30,079,910.97..30,079,911.08 rows=1 width=347) (actual time=27,288.825..27,288.826 rows=1 loops=1)

2. 0.002 27,288.824 ↑ 278.0 1 1

Unique (cost=30,079,910.97..30,079,942.94 rows=278 width=347) (actual time=27,288.824..27,288.824 rows=1 loops=1)

3. 2.537 27,288.822 ↑ 278.0 1 1

Sort (cost=30,079,910.97..30,079,911.66 rows=278 width=347) (actual time=27,288.822..27,288.822 rows=1 loops=1)

  • Sort Key: arquivo.dataupload DESC, arquivo.codigo, arquivo.codorigem, arquivo.nome, arquivo.apresentarportalcoordenador, arquivo.apresentarportalprofessor, arquivo.apresentarportalaluno, arquivo.descricao, arquivo.descricaoarquivo, arquivo.datadisponibilizacao, arquivo.dataindisponibilizacao, arquivo.manterdisponibilizacao, arquivo.controlardownload, arquivo.responsavelupload, arquivo.disciplina, arquivo.turma, arquivo.extensao, arquivo.apresentardeterminadoperiodo, arquivo.permitirarquivoresposta, arquivo.pastabasearquivo, arquivo.arquivoresposta, arquivo.professor, arquivo.niveleducacional, arquivo.cpfalunodocumentacao, arquivo.cpfrequerimento, arquivo.servidorarquivoonline, arquivo.indice, arquivo.agrupador, arquivo.indiceagrupador, arquivo.arquivoassinadodigitalmente, disciplina.codigo, disciplina.nome, turma.codigo, turma.identificadorturma, usuario.codigo, usuario.nome, professor.codigo, professor.nome, pessoa.codigo, pessoa.nome, curso.codigo, curso.nome, arquivo.arquivoassinadofuncionario, arquivo.arquivoassinadounidadeensino, arquivo.arquivoassinadounidadecertificadora
  • Sort Method: quicksort Memory: 297kB
4. 3.084 27,286.285 ↓ 1.9 534 1

Nested Loop Left Join (cost=1.13..30,079,899.68 rows=278 width=347) (actual time=133.188..27,286.285 rows=534 loops=1)

  • Join Filter: (arquivo.curso = curso.codigo)
  • Rows Removed by Join Filter: 29784
5. 1.086 27,281.599 ↓ 1.9 534 1

Nested Loop Left Join (cost=1.13..30,079,619.41 rows=278 width=323) (actual time=133.126..27,281.599 rows=534 loops=1)

6. 1.931 27,280.513 ↓ 1.9 534 1

Nested Loop Left Join (cost=0.71..30,077,593.78 rows=278 width=301) (actual time=133.120..27,280.513 rows=534 loops=1)

7. 1,133.960 27,278.048 ↓ 1.9 534 1

Nested Loop Left Join (cost=0.29..30,075,568.16 rows=278 width=271) (actual time=133.102..27,278.048 rows=534 loops=1)

  • Join Filter: (arquivo.responsavelupload = usuario.codigo)
  • Rows Removed by Join Filter: 16562440
8. 150.002 25,474.986 ↓ 1.9 534 1

Nested Loop Left Join (cost=0.29..29,942,108.26 rows=278 width=240) (actual time=116.813..25,474.986 rows=534 loops=1)

  • Join Filter: (arquivo.turma = turma.codigo)
  • Rows Removed by Join Filter: 2169679
9. 101.981 25,236.340 ↓ 1.9 534 1

Nested Loop Left Join (cost=0.29..29,924,682.43 rows=278 width=216) (actual time=114.469..25,236.340 rows=534 loops=1)

  • Join Filter: (arquivo.disciplina = disciplina.codigo)
  • Rows Removed by Join Filter: 1466364
10. 20.993 25,073.483 ↓ 1.9 534 1

Index Scan using ch_arquivo_situacao on arquivo (cost=0.29..29,913,172.28 rows=278 width=205) (actual time=113.186..25,073.483 rows=534 loops=1)

  • Index Cond: ((situacao)::text = 'AT'::text)
  • Filter: (apresentarportalprofessor AND ((niveleducacional)::text <> ''::text) AND ((origem)::text = 'IN'::text) AND ((professor = 119425) OR (SubPlan 1)))
  • Rows Removed by Filter: 3380
11.          

SubPlan (for Index Scan)

12. 67.374 25,052.490 ↓ 0.0 0 3,546

Result (cost=3,204.63..7,642.53 rows=1 width=0) (actual time=7.065..7.065 rows=0 loops=3,546)

  • One-Time Filter: (arquivo.professor IS NULL)
13. 7,173.518 24,985.116 ↓ 0.0 0 3,546

Hash Join (cost=3,204.63..7,642.53 rows=1 width=0) (actual time=7.046..7.046 rows=0 loops=3,546)

  • Hash Cond: (t.curso = c.codigo)
  • Join Filter: ((((arquivo.niveleducacional)::text = (c.niveleducacional)::text) AND (arquivo.curso IS NULL) AND (arquivo.turma IS NULL) AND (arquivo.disciplina IS NULL)) OR ((arquivo.curso = t.curso) AND (arquivo.turma IS NULL) AND (arquivo.disciplina IS NULL)) OR ((arquivo.curso = t.curso) AND (arquivo.turma = ht.turma) AND (arquivo.disciplina IS NULL)) OR ((arquivo.curso = t.curso) AND (arquivo.turma IS NULL) AND (arquivo.disciplina = htdi.disciplina)) OR ((arquivo.curso IS NULL) AND (arquivo.turma = ht.turma) AND (arquivo.disciplina = htdi.disciplina)) OR ((arquivo.curso IS NULL) AND (arquivo.turma = ht.turma) AND (arquivo.disciplina IS NULL)) OR ((arquivo.curso IS NULL) AND (arquivo.turma IS NULL) AND (arquivo.disciplina = htdi.disciplina)))
  • Rows Removed by Join Filter: 4617
14. 3,867.281 17,811.558 ↑ 1.2 4,617 3,546

Hash Join (cost=3,201.12..7,624.75 rows=5,437 width=12) (actual time=0.373..5.023 rows=4,617 loops=3,546)

  • Hash Cond: (ht.turma = t.codigo)
15. 3,315.387 13,942.872 ↑ 1.2 4,617 3,546

Hash Join (cost=3,026.82..7,436.16 rows=5,437 width=8) (actual time=0.372..3.932 rows=4,617 loops=3,546)

  • Hash Cond: (htd.horarioturma = ht.codigo)
16. 6,054.846 10,627.362 ↑ 1.2 4,617 3,546

Hash Join (cost=3,013.91..7,408.82 rows=5,437 width=8) (actual time=0.372..2.997 rows=4,617 loops=3,546)

  • Hash Cond: (htdi.horarioturmadia = htd.codigo)
17. 3,524.724 4,545.972 ↑ 1.2 4,617 3,546

Bitmap Heap Scan on horarioturmadiaitem htdi (cost=102.56..4,483.20 rows=5,437 width=8) (actual time=0.363..1.282 rows=4,617 loops=3,546)

  • Recheck Cond: (professor = 119425)
  • Heap Blocks: exact=2426484
18. 1,021.248 1,021.248 ↑ 1.0 5,337 3,546

Bitmap Index Scan on idx_horturdiaitem_professor (cost=0.00..101.20 rows=5,437 width=0) (actual time=0.288..0.288 rows=5,337 loops=3,546)

  • Index Cond: (professor = 119425)
19. 15.177 26.544 ↑ 1.0 74,371 1

Hash (cost=1,981.71..1,981.71 rows=74,371 width=8) (actual time=26.544..26.544 rows=74,371 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3930kB
20. 11.367 11.367 ↑ 1.0 74,371 1

Seq Scan on horarioturmadia htd (cost=0.00..1,981.71 rows=74,371 width=8) (actual time=0.034..11.367 rows=74,371 loops=1)

21. 0.061 0.123 ↑ 1.0 396 1

Hash (cost=7.96..7.96 rows=396 width=8) (actual time=0.123..0.123 rows=396 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
22. 0.062 0.062 ↑ 1.0 396 1

Seq Scan on horarioturma ht (cost=0.00..7.96 rows=396 width=8) (actual time=0.013..0.062 rows=396 loops=1)

23. 0.584 1.405 ↑ 1.0 4,147 1

Hash (cost=122.47..122.47 rows=4,147 width=8) (actual time=1.405..1.405 rows=4,147 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 226kB
24. 0.821 0.821 ↑ 1.0 4,147 1

Seq Scan on turma t (cost=0.00..122.47 rows=4,147 width=8) (actual time=0.009..0.821 rows=4,147 loops=1)

25. 0.013 0.040 ↑ 1.0 67 1

Hash (cost=2.67..2.67 rows=67 width=7) (actual time=0.040..0.040 rows=67 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
26. 0.027 0.027 ↑ 1.0 67 1

Seq Scan on curso c (cost=0.00..2.67 rows=67 width=7) (actual time=0.009..0.027 rows=67 loops=1)

27. 60.467 60.876 ↑ 1.0 2,746 534

Materialize (cost=0.00..66.19 rows=2,746 width=23) (actual time=0.001..0.114 rows=2,746 loops=534)

28. 0.409 0.409 ↑ 1.0 2,746 1

Seq Scan on disciplina (cost=0.00..52.46 rows=2,746 width=23) (actual time=0.013..0.409 rows=2,746 loops=1)

29. 87.718 88.644 ↑ 1.0 4,064 534

Materialize (cost=0.00..143.20 rows=4,147 width=24) (actual time=0.000..0.166 rows=4,064 loops=534)

30. 0.926 0.926 ↑ 1.0 4,147 1

Seq Scan on turma (cost=0.00..122.47 rows=4,147 width=24) (actual time=0.009..0.926 rows=4,147 loops=1)

31. 663.285 669.102 ↑ 1.0 31,017 534

Materialize (cost=0.00..1,266.80 rows=31,720 width=31) (actual time=0.000..1.253 rows=31,017 loops=534)

32. 5.817 5.817 ↑ 1.0 31,108 1

Seq Scan on usuario (cost=0.00..1,108.20 rows=31,720 width=31) (actual time=0.009..5.817 rows=31,108 loops=1)

33. 0.534 0.534 ↓ 0.0 0 534

Index Scan using pessoa_pkey on pessoa professor (cost=0.42..7.29 rows=1 width=30) (actual time=0.001..0.001 rows=0 loops=534)

  • Index Cond: (codigo = arquivo.professor)
34. 0.000 0.000 ↓ 0.0 0 534

Index Scan using pessoa_pkey on pessoa (cost=0.42..7.29 rows=1 width=30) (actual time=0.000..0.000 rows=0 loops=534)

  • Index Cond: (codigo = arquivo.pessoa)
35. 1.555 1.602 ↑ 1.2 57 534

Materialize (cost=0.00..3.00 rows=67 width=21) (actual time=0.000..0.003 rows=57 loops=534)

36. 0.047 0.047 ↑ 1.0 67 1

Seq Scan on curso (cost=0.00..2.67 rows=67 width=21) (actual time=0.025..0.047 rows=67 loops=1)

Planning time : 7.947 ms
Execution time : 27,290.398 ms