explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UVIC

Settings
# exclusive inclusive rows x rows loops node
1. 1.655 65,984.076 ↓ 86.3 4,832 1

Subquery Scan on pc (cost=2,162.46..5,348.59 rows=56 width=744) (actual time=116.319..65,984.076 rows=4,832 loops=1)

2. 31.273 65,982.421 ↓ 86.3 4,832 1

GroupAggregate (cost=2,162.46..5,348.03 rows=56 width=772) (actual time=116.318..65,982.421 rows=4,832 loops=1)

  • Group Key: proc.sr_id, pa.sr_id, i.sr_id, proj.sr_id, pr.sr_id, ua.sr_id, poa.sr_id, pep.sr_id, m.sr_id
3. 23.758 100.270 ↓ 167.2 9,361 1

Sort (cost=2,162.46..2,162.60 rows=56 width=667) (actual time=92.914..100.270 rows=9,361 loops=1)

  • Sort Key: proc.sr_id, pa.sr_id, i.sr_id, proj.sr_id, pr.sr_id, ua.sr_id, poa.sr_id, pep.sr_id, m.sr_id
  • Sort Method: external merge Disk: 5,184kB
4. 2.521 76.512 ↓ 167.2 9,361 1

Hash Left Join (cost=1,106.10..2,160.83 rows=56 width=667) (actual time=32.615..76.512 rows=9,361 loops=1)

  • Hash Cond: ((proc.it_pep)::integer = pep.sr_id)
  • Join Filter: ((i.ch_rib)::bpchar <> '1'::bpchar)
5. 3.145 73.975 ↓ 167.2 9,361 1

Hash Left Join (cost=1,104.16..2,158.74 rows=56 width=667) (actual time=32.587..73.975 rows=9,361 loops=1)

  • Hash Cond: ((proc.it_poa)::integer = poa.sr_id)
6. 7.111 70.691 ↓ 167.2 9,361 1

Nested Loop Left Join (cost=1,080.91..2,135.35 rows=56 width=640) (actual time=32.436..70.691 rows=9,361 loops=1)

7. 5.200 54.219 ↓ 167.2 9,361 1

Nested Loop Left Join (cost=1,080.63..1,763.32 rows=56 width=602) (actual time=32.429..54.219 rows=9,361 loops=1)

8. 4.662 39.658 ↓ 167.2 9,361 1

Hash Right Join (cost=1,080.35..1,740.24 rows=56 width=598) (actual time=32.421..39.658 rows=9,361 loops=1)

  • Hash Cond: ((spi.it_processo)::integer = proc.sr_id)
9. 2.589 2.589 ↑ 1.5 8,120 1

Seq Scan on tb_processos_itens spi (cost=0.00..614.99 rows=11,835 width=17) (actual time=0.008..2.589 rows=8,120 loops=1)

  • Filter: ((ch_cancelado)::bpchar = '0'::bpchar)
  • Rows Removed by Filter: 6
10. 3.687 32.407 ↓ 210.1 4,832 1

Hash (cost=1,080.06..1,080.06 rows=23 width=585) (actual time=32.407..32.407 rows=4,832 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2,560kB
11. 2.158 28.720 ↓ 210.1 4,832 1

Nested Loop Left Join (cost=30.71..1,080.06 rows=23 width=585) (actual time=0.211..28.720 rows=4,832 loops=1)

12. 4.198 21.730 ↓ 210.1 4,832 1

Nested Loop Left Join (cost=30.43..1,071.85 rows=23 width=556) (actual time=0.199..21.730 rows=4,832 loops=1)

13. 1.534 12.700 ↓ 210.1 4,832 1

Hash Join (cost=30.15..926.72 rows=23 width=511) (actual time=0.188..12.700 rows=4,832 loops=1)

  • Hash Cond: ((proc.it_moeda)::integer = m.sr_id)
14. 2.408 11.150 ↓ 210.1 4,833 1

Nested Loop (cost=16.77..913.28 rows=23 width=469) (actual time=0.167..11.150 rows=4,833 loops=1)

15. 1.496 3.901 ↓ 201.7 4,841 1

Hash Join (cost=16.63..904.57 rows=24 width=405) (actual time=0.150..3.901 rows=4,841 loops=1)

  • Hash Cond: ((proc.it_projeto)::integer = proj.sr_id)
16. 2.289 2.289 ↓ 201.8 4,843 1

Seq Scan on tb_processos proc (cost=0.00..887.88 rows=24 width=360) (actual time=0.008..2.289 rows=4,843 loops=1)

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

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

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

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

19. 4.841 4.841 ↑ 1.0 1 4,841

Index Scan using tb_instituicoes_pkey on tb_instituicoes i (cost=0.14..0.36 rows=1 width=68) (actual time=0.001..0.001 rows=1 loops=4,841)

  • Index Cond: (sr_id = (proj.it_instituicao)::integer)
20. 0.004 0.016 ↑ 37.5 4 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
21. 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)

22. 4.832 4.832 ↑ 1.0 1 4,832

Index Scan using tb_processos_recepcao_it_processo_idx on tb_processos_recepcao pr (cost=0.28..6.30 rows=1 width=49) (actual time=0.001..0.001 rows=1 loops=4,832)

  • Index Cond: ((it_processo)::integer = proc.sr_id)
23. 4.832 4.832 ↑ 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.001..0.001 rows=1 loops=4,832)

  • Index Cond: (sr_id = (pr.it_analista)::integer)
24. 9.361 9.361 ↑ 1.0 1 9,361

Index Scan using tb_contratos_pkey on tb_contratos c (cost=0.28..0.41 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=9,361)

  • Index Cond: (sr_id = (spi.it_contrato)::integer)
25. 9.361 9.361 ↑ 1.0 1 9,361

Index Scan using tb_processos_agendamento_it_processo_idx on tb_processos_agendamento pa (cost=0.28..6.63 rows=1 width=42) (actual time=0.001..0.001 rows=1 loops=9,361)

  • Index Cond: ((it_processo)::integer = proc.sr_id)
26. 0.074 0.139 ↑ 1.2 625 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 44kB
27. 0.065 0.065 ↑ 1.2 625 1

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

28. 0.007 0.016 ↓ 1.2 50 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
29. 0.009 0.009 ↓ 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.009 rows=50 loops=1)

30.          

SubPlan (for GroupAggregate)

31. 1.164 27,711.348 ↓ 0.0 0 1,164

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

32. 27,710.184 27,710.184 ↓ 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=23.806..23.806 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
33. 8.195 38,139.530 ↓ 0.0 0 8,195

Limit (cost=0.42..28.38 rows=1 width=22) (actual time=4.653..4.654 rows=0 loops=8,195)

34. 38,131.335 38,131.335 ↓ 0.0 0 8,195

Index Scan Backward using tb_cotacoes_dolar_dt_data_idx on tb_cotacoes_dolar cd (cost=0.42..5,817.16 rows=208 width=22) (actual time=4.653..4.653 rows=0 loops=8,195)

  • Index Cond: ((dt_data)::date < (c.dt_data)::date)
  • Filter: ((it_moeda)::integer = (spi.it_moeda)::integer)
  • Rows Removed by Filter: 25,170
Planning time : 5.459 ms
Execution time : 65,985.956 ms