explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Hg3W

Settings
# exclusive inclusive rows x rows loops node
1. 40.834 236,119.573 ↓ 70.0 4,832 1

Hash Left Join (cost=1,067.67..61,455.59 rows=69 width=772) (actual time=9.300..236,119.573 rows=4,832 loops=1)

  • Hash Cond: ((proc.it_pep)::integer = pep.sr_id)
  • Join Filter: ((i.ch_rib)::bpchar <> '1'::bpchar)
2. 6.422 112.833 ↓ 70.0 4,832 1

Hash Left Join (cost=1,065.72..1,583.05 rows=69 width=646) (actual time=7.949..112.833 rows=4,832 loops=1)

  • Hash Cond: ((proc.it_poa)::integer = poa.sr_id)
3. 12.237 106.270 ↓ 70.0 4,832 1

Nested Loop Left Join (cost=1,042.47..1,559.62 rows=69 width=623) (actual time=7.799..106.270 rows=4,832 loops=1)

4. 10.943 74.705 ↓ 70.0 4,832 1

Nested Loop Left Join (cost=1,042.19..1,231.13 rows=69 width=577) (actual time=7.792..74.705 rows=4,832 loops=1)

5. 6.657 39.602 ↓ 70.0 4,832 1

Hash Join (cost=1,041.91..1,206.50 rows=69 width=552) (actual time=7.779..39.602 rows=4,832 loops=1)

  • Hash Cond: ((proc.it_moeda)::integer = m.sr_id)
6. 6.124 32.922 ↓ 70.0 4,833 1

Hash Join (cost=1,028.54..1,192.94 rows=69 width=514) (actual time=7.747..32.922 rows=4,833 loops=1)

  • Hash Cond: ((proj.it_instituicao)::integer = i.sr_id)
7. 6.788 26.743 ↓ 68.2 4,841 1

Hash Join (cost=1,022.92..1,187.12 rows=71 width=450) (actual time=7.671..26.743 rows=4,841 loops=1)

  • Hash Cond: ((proc.it_projeto)::integer = proj.sr_id)
8. 10.533 19.840 ↓ 68.2 4,843 1

Hash Right Join (cost=1,006.29..1,170.29 rows=71 width=405) (actual time=7.543..19.840 rows=4,843 loops=1)

  • Hash Cond: ((pr.it_processo)::integer = proc.sr_id)
9. 1.786 1.786 ↓ 1.0 4,870 1

Seq Scan on tb_processos_recepcao pr (cost=0.00..151.32 rows=4,832 width=45) (actual time=0.005..1.786 rows=4,870 loops=1)

10. 3.918 7.521 ↓ 68.2 4,843 1

Hash (cost=1,005.40..1,005.40 rows=71 width=364) (actual time=7.521..7.521 rows=4,843 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,978kB
11. 3.603 3.603 ↓ 68.2 4,843 1

Seq Scan on tb_processos proc (cost=0.00..1,005.40 rows=71 width=364) (actual time=0.008..3.603 rows=4,843 loops=1)

  • Filter: ((it_empresa)::integer = 1)
  • Rows Removed by Filter: 27
12. 0.047 0.115 ↓ 1.0 118 1

Hash (cost=15.17..15.17 rows=117 width=49) (actual time=0.115..0.115 rows=118 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
13. 0.068 0.068 ↓ 1.0 118 1

Seq Scan on tb_projetos proj (cost=0.00..15.17 rows=117 width=49) (actual time=0.007..0.068 rows=118 loops=1)

14. 0.020 0.055 ↑ 1.4 51 1

Hash (cost=4.72..4.72 rows=72 width=68) (actual time=0.055..0.055 rows=51 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
15. 0.035 0.035 ↑ 1.4 51 1

Seq Scan on tb_instituicoes i (cost=0.00..4.72 rows=72 width=68) (actual time=0.005..0.035 rows=51 loops=1)

16. 0.011 0.023 ↑ 37.5 4 1

Hash (cost=11.50..11.50 rows=150 width=42) (actual time=0.023..0.023 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 0.012 0.012 ↑ 37.5 4 1

Seq Scan on tb_moedas m (cost=0.00..11.50 rows=150 width=42) (actual time=0.010..0.012 rows=4 loops=1)

18. 24.160 24.160 ↑ 1.0 1 4,832

Index Scan using tb_usuarios_pkey on tb_usuarios ua (cost=0.28..0.36 rows=1 width=29) (actual time=0.005..0.005 rows=1 loops=4,832)

  • Index Cond: (sr_id = (pr.it_analista)::integer)
19. 19.328 19.328 ↑ 1.0 1 4,832

Index Scan using tb_processos_agendamento_it_processo_idx on tb_processos_agendamento pa (cost=0.28..4.75 rows=1 width=50) (actual time=0.004..0.004 rows=1 loops=4,832)

  • Index Cond: ((it_processo)::integer = proc.sr_id)
20. 0.075 0.141 ↑ 1.2 625 1

Hash (cost=14.22..14.22 rows=722 width=27) (actual time=0.141..0.141 rows=625 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 44kB
21. 0.066 0.066 ↑ 1.2 625 1

Seq Scan on tb_poas poa (cost=0.00..14.22 rows=722 width=27) (actual time=0.004..0.066 rows=625 loops=1)

22. 0.010 0.018 ↓ 1.2 50 1

Hash (cost=1.42..1.42 rows=42 width=36) (actual time=0.017..0.018 rows=50 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
23. 0.008 0.008 ↓ 1.2 50 1

Seq Scan on tb_pep_costcenter pep (cost=0.00..1.42 rows=42 width=36) (actual time=0.004..0.008 rows=50 loops=1)

24.          

SubPlan (for Hash Left Join)

25. 50.554 235,965.888 ↑ 1.0 1 4,832

Aggregate (cost=867.56..867.57 rows=1 width=32) (actual time=48.834..48.834 rows=1 loops=4,832)

26. 5,798.400 5,798.400 ↑ 1.0 2 4,832

Seq Scan on tb_processos_itens spi (cost=0.00..644.59 rows=2 width=13) (actual time=0.782..1.200 rows=2 loops=4,832)

  • Filter: (((it_processo)::integer = proc.sr_id) AND ((ch_cancelado)::bpchar = '0'::bpchar))
  • Rows Removed by Filter: 8,124
27.          

SubPlan (for Aggregate)

28. 1.164 28,330.596 ↓ 0.0 0 1,164

Limit (cost=0.42..28.33 rows=1 width=22) (actual time=24.339..24.339 rows=0 loops=1,164)

29. 28,329.432 28,329.432 ↓ 0.0 0 1,164

Index Scan Backward using tb_cotacoes_dolar_dt_data_idx on tb_cotacoes_dolar cd1 (cost=0.42..17,442.64 rows=625 width=22) (actual time=24.338..24.338 rows=0 loops=1,164)

  • Index Cond: ((dt_data)::date < now())
  • Filter: ((it_moeda)::integer = (spi.it_moeda)::integer)
  • Rows Removed by Filter: 103,793
30. 6.929 201,786.338 ↓ 0.0 0 6,929

Limit (cost=0.70..83.14 rows=1 width=22) (actual time=29.121..29.122 rows=0 loops=6,929)

31. 49,507.705 201,779.409 ↓ 0.0 0 6,929

Nested Loop (cost=0.70..17,147.66 rows=208 width=22) (actual time=29.121..29.121 rows=0 loops=6,929)

  • Join Filter: ((cd.dt_data)::date < (c.dt_data)::date)
  • Rows Removed by Join Filter: 32,761
32. 152,271.704 152,271.704 ↓ 52.5 32,796 6,929

Index Scan Backward using tb_cotacoes_dolar_dt_data_idx on tb_cotacoes_dolar cd (cost=0.42..17,129.99 rows=625 width=22) (actual time=13.231..21.976 rows=32,796 loops=6,929)

  • Filter: ((it_moeda)::integer = (spi.it_moeda)::integer)
  • Rows Removed by Filter: 76,436
33. 0.000 0.000 ↑ 1.0 1 227,240,938

Materialize (cost=0.28..8.31 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=227,240,938)

34. 13.885 13.885 ↑ 1.0 1 2,777

Index Scan using tb_contratos_pkey on tb_contratos c (cost=0.28..8.30 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=2,777)

  • Index Cond: (sr_id = (spi.it_contrato)::integer)
Planning time : 5.802 ms
Execution time : 236,121.167 ms