explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S925

Settings
# exclusive inclusive rows x rows loops node
1. 15.377 66,097.948 ↓ 161.5 9,365 1

Subquery Scan on pc (cost=7,287.39..668,284.58 rows=58 width=780) (actual time=70.876..66,097.948 rows=9,365 loops=1)

  • Filter: ((pc.it_empresa)::integer = 1)
  • Rows Removed by Filter: 41
2. 49.415 65,998.286 ↑ 1.2 9,406 1

WindowAgg (cost=7,287.39..662,828.82 rows=11,528 width=752) (actual time=70.848..65,998.286 rows=9,406 loops=1)

3. 19.209 54.442 ↑ 1.2 9,406 1

Sort (cost=7,287.39..7,316.21 rows=11,528 width=649) (actual time=47.528..54.442 rows=9,406 loops=1)

  • Sort Key: spi.it_processo
  • Sort Method: external merge Disk: 5,072kB
4. 2.278 35.233 ↑ 1.2 9,406 1

Hash Left Join (cost=2,173.14..3,160.17 rows=11,528 width=649) (actual time=18.976..35.233 rows=9,406 loops=1)

  • Hash Cond: ((proc.it_pep)::integer = pep.sr_id)
  • Join Filter: ((i.ch_rib)::bpchar <> '1'::bpchar)
5. 2.437 32.939 ↑ 1.2 9,406 1

Hash Left Join (cost=2,171.20..3,127.18 rows=11,528 width=653) (actual time=18.948..32.939 rows=9,406 loops=1)

  • Hash Cond: ((proc.it_poa)::integer = poa.sr_id)
6. 3.152 30.361 ↑ 1.2 9,406 1

Hash Left Join (cost=2,147.95..3,073.50 rows=11,528 width=630) (actual time=18.799..30.361 rows=9,406 loops=1)

  • Hash Cond: (proc.sr_id = (pa.it_processo)::integer)
7. 2.433 25.098 ↑ 1.2 9,406 1

Hash Left Join (cost=1,760.85..2,528.39 rows=11,528 width=596) (actual time=16.663..25.098 rows=9,406 loops=1)

  • Hash Cond: ((spi.it_contrato)::integer = c.sr_id)
8. 4.923 21.126 ↑ 1.2 9,406 1

Hash Right Join (cost=1,344.37..2,081.62 rows=11,528 width=592) (actual time=15.095..21.126 rows=9,406 loops=1)

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

Seq Scan on tb_processos_itens spi (cost=0.00..585.39 rows=11,839 width=19) (actual time=0.005..1.122 rows=8,126 loops=1)

10. 2.244 15.081 ↓ 1.0 4,859 1

Hash (cost=1,285.09..1,285.09 rows=4,742 width=573) (actual time=15.081..15.081 rows=4,859 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 2,492kB
11. 1.509 12.837 ↓ 1.0 4,859 1

Hash Left Join (cost=1,068.81..1,285.09 rows=4,742 width=573) (actual time=4.499..12.837 rows=4,859 loops=1)

  • Hash Cond: ((pr.it_analista)::integer = ua.sr_id)
12. 1.308 11.078 ↓ 1.0 4,859 1

Hash Join (cost=971.73..1,175.53 rows=4,742 width=548) (actual time=4.243..11.078 rows=4,859 loops=1)

  • Hash Cond: ((proc.it_moeda)::integer = m.sr_id)
13. 1.444 9.759 ↓ 1.0 4,860 1

Hash Join (cost=958.36..1,149.44 rows=4,742 width=510) (actual time=4.229..9.759 rows=4,860 loops=1)

  • Hash Cond: ((proj.it_instituicao)::integer = i.sr_id)
14. 1.427 8.282 ↑ 1.0 4,868 1

Hash Join (cost=953.21..1,130.47 rows=4,870 width=446) (actual time=4.193..8.282 rows=4,868 loops=1)

  • Hash Cond: ((proc.it_projeto)::integer = proj.sr_id)
15. 2.103 6.787 ↑ 1.0 4,870 1

Hash Right Join (cost=936.58..1,100.59 rows=4,870 width=401) (actual time=4.121..6.787 rows=4,870 loops=1)

  • Hash Cond: ((pr.it_processo)::integer = proc.sr_id)
16. 0.577 0.577 ↓ 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.006..0.577 rows=4,870 loops=1)

17. 1.851 4.107 ↑ 1.0 4,870 1

Hash (cost=875.70..875.70 rows=4,870 width=360) (actual time=4.107..4.107 rows=4,870 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 1,963kB
18. 2.256 2.256 ↑ 1.0 4,870 1

Seq Scan on tb_processos proc (cost=0.00..875.70 rows=4,870 width=360) (actual time=0.005..2.256 rows=4,870 loops=1)

19. 0.022 0.068 ↓ 1.0 118 1

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

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

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

21. 0.010 0.033 ↑ 1.0 51 1

Hash (cost=4.51..4.51 rows=51 width=68) (actual time=0.033..0.033 rows=51 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
22. 0.023 0.023 ↑ 1.0 51 1

Seq Scan on tb_instituicoes i (cost=0.00..4.51 rows=51 width=68) (actual time=0.003..0.023 rows=51 loops=1)

23. 0.003 0.011 ↑ 37.5 4 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.008 0.008 ↑ 37.5 4 1

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

25. 0.075 0.250 ↑ 2.7 458 1

Hash (cost=81.48..81.48 rows=1,248 width=29) (actual time=0.250..0.250 rows=458 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 45kB
26. 0.175 0.175 ↑ 2.7 458 1

Seq Scan on tb_usuarios ua (cost=0.00..81.48 rows=1,248 width=29) (actual time=0.005..0.175 rows=458 loops=1)

27. 0.456 1.539 ↑ 1.0 4,377 1

Hash (cost=361.77..361.77 rows=4,377 width=8) (actual time=1.539..1.539 rows=4,377 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 235kB
28. 1.083 1.083 ↑ 1.0 4,377 1

Seq Scan on tb_contratos c (cost=0.00..361.77 rows=4,377 width=8) (actual time=0.004..1.083 rows=4,377 loops=1)

29. 0.879 2.111 ↓ 1.0 4,870 1

Hash (cost=326.49..326.49 rows=4,849 width=38) (actual time=2.111..2.111 rows=4,870 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 426kB
30. 1.232 1.232 ↓ 1.0 4,870 1

Seq Scan on tb_processos_agendamento pa (cost=0.00..326.49 rows=4,849 width=38) (actual time=0.005..1.232 rows=4,870 loops=1)

31. 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
32. 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.005..0.066 rows=625 loops=1)

33. 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
34. 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.005..0.009 rows=50 loops=1)

35.          

SubPlan (for WindowAgg)

36. 1.171 28,050.134 ↓ 0.0 0 1,171

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

37. 28,048.963 28,048.963 ↓ 0.0 0 1,171

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.953..23.953 rows=0 loops=1,171)

  • Index Cond: ((dt_data)::date < now())
  • Filter: ((it_moeda)::integer = (spi.it_moeda)::integer)
  • Rows Removed by Filter: 103,172
38. 6.935 37,844.295 ↓ 0.0 0 6,935

Limit (cost=0.42..28.38 rows=1 width=22) (actual time=5.457..5.457 rows=0 loops=6,935)

39. 37,837.360 37,837.360 ↓ 0.0 0 6,935

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=5.456..5.456 rows=0 loops=6,935)

  • Index Cond: ((dt_data)::date < (c.dt_data)::date)
  • Filter: ((it_moeda)::integer = (spi.it_moeda)::integer)
  • Rows Removed by Filter: 29,743
40.          

SubPlan (for Subquery Scan)

41. 0.000 84.285 ↑ 1.0 1 9,365

Limit (cost=0.83..91.58 rows=1 width=40) (actual time=0.009..0.009 rows=1 loops=9,365)

42. 9.530 84.285 ↑ 154.0 1 9,365

Nested Loop (cost=0.83..13,974.84 rows=154 width=40) (actual time=0.009..0.009 rows=1 loops=9,365)

43. 46.825 46.825 ↑ 416.0 1 9,365

Index Scan Backward using tb_cotacoes_dolar_dt_data_idx on tb_cotacoes_dolar a (cost=0.42..5,921.38 rows=416 width=22) (actual time=0.005..0.005 rows=1 loops=9,365)

  • Index Cond: ((dt_data)::date < (pc.dt_criacao)::date)
  • Filter: (((it_moeda)::integer = (pc.it_moeda)::integer) OR (1 = (pc.it_moeda)::integer))
  • Rows Removed by Filter: 0
44. 27.930 27.930 ↑ 1.0 1 9,310

Index Scan using tb_cotacoes_dolar_dt_data_idx on tb_cotacoes_dolar b (cost=0.42..19.35 rows=1 width=22) (actual time=0.003..0.003 rows=1 loops=9,310)

  • Index Cond: ((dt_data)::date = (a.dt_data)::date)
  • Filter: ((it_moeda)::integer = 0)
  • Rows Removed by Filter: 2
Planning time : 5.482 ms
Execution time : 66,099.760 ms