explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fAA7

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 27,956.294 ↑ 1.0 1 1

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

2. 0.001 27,956.284 ↑ 278.0 1 1

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

3. 2.562 27,956.283 ↑ 278.0 1 1

Sort (cost=30,079,910.97..30,079,911.66 rows=278 width=347) (actual time=27,956.283..27,956.283 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.224 27,953.721 ↓ 1.9 534 1

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

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

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

6. 2.331 27,947.693 ↓ 1.9 534 1

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

7. 1,161.955 27,944.828 ↓ 1.9 534 1

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

  • Join Filter: (arquivo.responsavelupload = usuario.codigo)
  • Rows Removed by Join Filter: 16562440
8. 153.474 26,087.071 ↓ 1.9 534 1

Nested Loop Left Join (cost=0.29..29,942,108.26 rows=278 width=240) (actual time=110.970..26,087.071 rows=534 loops=1)

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

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

  • Join Filter: (arquivo.disciplina = disciplina.codigo)
  • Rows Removed by Join Filter: 1466364
10. 23.333 25,678.643 ↓ 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=107.537..25,678.643 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. 74.466 25,655.310 ↓ 0.0 0 3,546

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

  • One-Time Filter: (arquivo.professor IS NULL)
13. 7,290.537 25,580.844 ↓ 0.0 0 3,546

Hash Join (cost=3,204.63..7,642.53 rows=1 width=0) (actual time=7.214..7.214 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,778.667 18,290.268 ↑ 1.2 4,617 3,546

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

  • Hash Cond: (ht.turma = t.codigo)
15. 3,357.948 14,510.232 ↑ 1.2 4,617 3,546

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

  • Hash Cond: (htd.horarioturma = ht.codigo)
16. 6,156.488 11,152.170 ↑ 1.2 4,617 3,546

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

  • Hash Cond: (htdi.horarioturmadia = htd.codigo)
17. 3,928.968 4,971.492 ↑ 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.369..1.402 rows=4,617 loops=3,546)

  • Recheck Cond: (professor = 119425)
  • Heap Blocks: exact=2426484
18. 1,042.524 1,042.524 ↑ 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.294..0.294 rows=5,337 loops=3,546)

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

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

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

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

21. 0.055 0.114 ↑ 1.0 396 1

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

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

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

23. 0.591 1.369 ↑ 1.0 4,147 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 226kB
24. 0.778 0.778 ↑ 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.778 rows=4,147 loops=1)

25. 0.013 0.039 ↑ 1.0 67 1

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

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

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

27. 63.655 64.080 ↑ 1.0 2,746 534

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

28. 0.425 0.425 ↑ 1.0 2,746 1

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

29. 90.480 91.314 ↑ 1.0 4,064 534

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

30. 0.834 0.834 ↑ 1.0 4,147 1

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

31. 689.458 695.802 ↑ 1.0 31,017 534

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

32. 6.344 6.344 ↑ 1.0 31,108 1

Seq Scan on usuario (cost=0.00..1,108.20 rows=31,720 width=31) (actual time=0.010..6.344 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.560 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.042 0.042 ↑ 1.0 67 1

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

Planning time : 9.028 ms
Execution time : 27,957.695 ms