explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vVF8 : Optimization for: Top Query Lenta - Maio; plan #UX3X

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.084 6.931 ↑ 1.1 33 1

Sort (cost=328.61..328.70 rows=36 width=161) (actual time=6.929..6.931 rows=33 loops=1)

  • Sort Key: classejudi0_.ds_classe_judicial
  • Sort Method: quicksort Memory: 33kB
2. 0.021 6.847 ↑ 1.1 33 1

Nested Loop Semi Join (cost=1.24..327.68 rows=36 width=161) (actual time=0.286..6.847 rows=33 loops=1)

3. 0.106 0.106 ↑ 1.0 35 1

Index Scan using tb_classe_judicial_in01 on tb_classe_judicial classejudi0_ (cost=0.28..8.68 rows=36 width=161) (actual time=0.018..0.106 rows=35 loops=1)

  • Index Cond: (id_fluxo IS NOT NULL)
  • Filter: (((in_ativo)::bpchar = 'S'::bpchar) AND ((in_inicial)::bpchar = 'S'::bpchar) AND ((in_recursal)::bpchar = 'N'::bpchar))
  • Rows Removed by Filter: 21
4. 0.035 6.720 ↑ 30.0 1 35

Nested Loop (cost=0.97..60.17 rows=30 width=8) (actual time=0.192..0.192 rows=1 loops=35)

5. 0.088 6.335 ↑ 2.0 1 35

Nested Loop (cost=0.55..45.68 rows=2 width=16) (actual time=0.180..0.181 rows=1 loops=35)

  • Join Filter: ((SubPlan 1) OR (SubPlan 2))
  • Rows Removed by Join Filter: 0
6. 0.035 0.210 ↑ 1.0 1 35

Nested Loop (cost=0.55..0.83 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=35)

7. 0.070 0.070 ↑ 1.0 1 35

Index Only Scan using tb_classe_judicial_pk on tb_classe_judicial classejudi1_ (cost=0.28..0.51 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=35)

  • Index Cond: (id_classe_judicial = (classejudi0_.id_classe_judicial)::integer)
  • Heap Fetches: 0
8. 0.105 0.105 ↑ 1.0 1 35

Index Scan using tb_classe_aplicacao_uk on tb_classe_aplicacao classeapli2_ (cost=0.28..0.32 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=35)

  • Index Cond: (((id_classe_judicial)::integer = (classejudi1_.id_classe_judicial)::integer) AND ((id_aplicacao_classe)::integer = 1))
  • Filter: ((in_ativo)::bpchar = 'S'::bpchar)
9. 0.035 0.035 ↑ 2.0 1 35

Seq Scan on tb_competencia competenci4_ (cost=0.00..1.06 rows=2 width=4) (actual time=0.001..0.001 rows=1 loops=35)

  • Filter: ((in_ativo)::bpchar = 'S'::bpchar)
  • Rows Removed by Filter: 0
10.          

SubPlan (forNested Loop)

11. 0.043 5.994 ↑ 22.0 1 37

Nested Loop (cost=6.80..23.15 rows=22 width=4) (actual time=0.162..0.162 rows=1 loops=37)

12. 0.152 5.846 ↑ 22.0 1 37

Hash Join (cost=6.80..21.81 rows=22 width=4) (actual time=0.158..0.158 rows=1 loops=37)

  • Hash Cond: ((orgaojulga8_.id_orgao_julgador)::integer = (orgaojulga7_.id_orgao_julgador)::integer)
13. 0.070 0.070 ↑ 96.0 1 35

Seq Scan on tb_orgao_julgador orgaojulga8_ (cost=0.00..13.11 rows=96 width=4) (actual time=0.002..0.002 rows=1 loops=35)

  • Filter: (((in_ativo)::bpchar = 'S'::bpchar) AND ((id_jurisdicao)::integer = 24) AND ((id_aplicacao_classe)::integer = (classeapli2_.id_aplicacao_classe)::integer))
  • Rows Removed by Filter: 1
14. 1.110 5.624 ↓ 3.7 206 37

Hash (cost=6.11..6.11 rows=55 width=8) (actual time=0.152..0.152 rows=206 loops=37)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
15. 4.107 4.514 ↓ 3.7 206 37

Bitmap Heap Scan on tb_org_julg_competencia orgaojulga7_ (cost=2.65..6.11 rows=55 width=8) (actual time=0.018..0.122 rows=206 loops=37)

  • Recheck Cond: ((id_competencia)::integer = (competenci4_.id_competencia)::integer)
  • Filter: ((CURRENT_DATE >= (dt_inicio)::timestamp without time zone) AND (((dt_fim)::timestamp without time zone >= CURRENT_DATE) OR (dt_fim IS NULL)))
  • Rows Removed by Filter: 36
  • Heap Blocks: exact=70
16. 0.407 0.407 ↓ 3.7 242 37

Bitmap Index Scan on tb_org_julg_competencia_in02 (cost=0.00..2.63 rows=65 width=0) (actual time=0.011..0.011 rows=242 loops=37)

  • Index Cond: ((id_competencia)::integer = (competenci4_.id_competencia)::integer)
17. 0.070 0.105 ↑ 1.0 1 35

Materialize (cost=0.00..1.07 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=35)

18. 0.035 0.035 ↑ 1.0 1 35

Seq Scan on tb_competencia competenci6_ (cost=0.00..1.06 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=35)

  • Filter: ((id_competencia)::integer = (competenci4_.id_competencia)::integer)
19. 0.002 0.008 ↓ 0.0 0 2

Nested Loop (cost=0.30..13.45 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=2)

20. 0.000 0.006 ↓ 0.0 0 2

Nested Loop (cost=0.30..12.38 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=2)

21. 0.006 0.006 ↓ 0.0 0 2

Index Scan using tb_orgao_jlgador_colgiado_in05 on tb_orgao_julgador_colgiado orgaojulga11_ (cost=0.14..4.17 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=2)

  • Index Cond: ((id_jurisdicao)::integer = 24)
  • Filter: (((in_ativo)::bpchar = 'S'::bpchar) AND ((id_aplicacao_classe)::integer = (classeapli2_.id_aplicacao_classe)::integer))
22. 0.000 0.000 ↓ 0.0 0

Index Scan using tb_org_julg_clgiado_compt_in03 on tb_org_julg_clgiado_compet orgaojulga10_ (cost=0.15..4.18 rows=1 width=8) (never executed)

  • Index Cond: (((id_orgao_julgador_colegiado)::integer = (orgaojulga11_.id_orgao_julgador_colegiado)::integer) AND ((id_competencia)::integer = (competenci4_.id_competencia)::integer))
  • Filter: ((CURRENT_DATE >= (dt_inicio)::timestamp without time zone) AND (((dt_fim)::timestamp without time zone >= CURRENT_DATE) OR (dt_fim IS NULL)))
23. 0.000 0.000 ↓ 0.0 0

Seq Scan on tb_competencia competenci9_ (cost=0.00..1.06 rows=1 width=4) (never executed)

  • Filter: ((id_competencia)::integer = (competenci4_.id_competencia)::integer)
24. 0.350 0.350 ↑ 161.0 1 35

Index Only Scan using tb_competencia_cl_assunto_in06 on tb_competencia_cl_assunto competenci3_ (cost=0.41..5.64 rows=161 width=8) (actual time=0.010..0.010 rows=1 loops=35)

  • Index Cond: ((id_competencia = (competenci4_.id_competencia)::integer) AND (id_classe_aplicacao = (classeapli2_.id_classe_aplicacao)::integer))
  • Heap Fetches: 0
Planning time : 2.463 ms
Execution time : 7.167 ms