explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ok1W : monitoramento

Settings
# exclusive inclusive rows x rows loops node
1. 320.763 28,280.069 ↓ 106.0 106 1

Result (cost=114,925.11..114,927.10 rows=1 width=800) (actual time=27,961.668..28,280.069 rows=106 loops=1)

2.          

CTE with_veiculos_com_rastreamento

3. 15.181 40.197 ↓ 1.0 134 1

Unique (cost=0.42..4,437.28 rows=133 width=4) (actual time=0.044..40.197 rows=134 loops=1)

4. 25.016 25.016 ↑ 1.0 155,249 1

Index Only Scan using idx_veiculo_ficha_diaria_cd_veiculo on veiculo_ficha_diaria vfd (cost=0.42..4,049.15 rows=155,249 width=4) (actual time=0.043..25.016 rows=155,249 loops=1)

  • Heap Fetches: 0
5.          

CTE with_veiculo_ficha_diaria

6. 203.187 869.816 ↑ 1.0 155,249 1

WindowAgg (cost=50,043.69..54,701.16 rows=155,249 width=311) (actual time=595.857..869.816 rows=155,249 loops=1)

7. 309.524 666.629 ↑ 1.0 155,249 1

Sort (cost=50,043.69..50,431.81 rows=155,249 width=291) (actual time=595.828..666.629 rows=155,249 loops=1)

  • Sort Key: vfd_1.cd_veiculo, (COALESCE(vfd_1.dt_evento, vfd_1.dt_posicao)) DESC, vfd_1.cd_ficha DESC
  • Sort Method: external merge Disk: 46,152kB
8. 64.199 357.105 ↑ 1.0 155,249 1

Hash Left Join (cost=6,116.96..15,430.44 rows=155,249 width=291) (actual time=125.795..357.105 rows=155,249 loops=1)

  • Hash Cond: (vfd_1.cd_cidade_posicao = ci.cd_cidade)
9. 122.990 290.092 ↑ 1.0 155,249 1

Hash Left Join (cost=5,930.41..14,836.06 rows=155,249 width=279) (actual time=122.942..290.092 rows=155,249 loops=1)

  • Hash Cond: (vfd_1.cd_veiculo = wud.cd_veiculo)
10. 44.197 44.197 ↑ 1.0 155,249 1

Seq Scan on veiculo_ficha_diaria vfd_1 (cost=0.00..8,489.49 rows=155,249 width=247) (actual time=0.018..44.197 rows=155,249 loops=1)

11. 0.038 122.905 ↑ 1.5 134 1

Hash (cost=5,927.91..5,927.91 rows=200 width=36) (actual time=122.905..122.905 rows=134 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
12. 0.036 122.867 ↑ 1.5 134 1

Subquery Scan on wud (cost=5,924.73..5,927.91 rows=200 width=36) (actual time=122.742..122.867 rows=134 loops=1)

13. 0.064 122.831 ↑ 1.5 134 1

Unique (cost=5,924.73..5,925.91 rows=200 width=48) (actual time=122.739..122.831 rows=134 loops=1)

14. 0.222 122.767 ↓ 1.5 349 1

Sort (cost=5,924.73..5,925.32 rows=235 width=48) (actual time=122.738..122.767 rows=349 loops=1)

  • Sort Key: wvcr.cd_veiculo, (((((c_2.dt_emissao)::text || ' '::text) || (c_2.hr_emissao)::text))::timestamp without time zone) DESC, (0)
  • Sort Method: quicksort Memory: 52kB
15. 0.300 122.545 ↓ 1.5 349 1

Nested Loop (cost=5,250.22..5,915.48 rows=235 width=48) (actual time=121.787..122.545 rows=349 loops=1)

16. 0.419 121.896 ↓ 1.5 349 1

HashAggregate (cost=5,249.93..5,252.28 rows=235 width=44) (actual time=121.777..121.896 rows=349 loops=1)

  • Group Key: c_2.cd_ctrc, (NULL::integer), (NULL::integer), (((((c_2.dt_emissao)::text || ' '::text) || (c_2.hr_emissao)::text))::timestamp without time zone), (0), wvcr.cd_veiculo, c_2.cd_cidade_origem, c_2.cd_cidade_destino, c_2.cd_pessoa_motorista, c_2.dt_emissao
17. 0.070 121.477 ↓ 1.5 349 1

Append (cost=0.71..5,244.05 rows=235 width=44) (actual time=0.099..121.477 rows=349 loops=1)

18. 0.356 42.260 ↓ 2.2 134 1

Nested Loop (cost=0.71..1,064.04 rows=60 width=44) (actual time=0.098..42.260 rows=134 loops=1)

19. 0.157 41.100 ↓ 1.0 134 1

Nested Loop (cost=0.29..951.89 rows=133 width=8) (actual time=0.056..41.100 rows=134 loops=1)

20. 40.273 40.273 ↓ 1.0 134 1

CTE Scan on with_veiculos_com_rastreamento wvcr (cost=0.00..2.66 rows=133 width=4) (actual time=0.045..40.273 rows=134 loops=1)

21. 0.670 0.670 ↑ 1.0 1 134

Index Scan using pk_veiculo on veiculo v_2 (cost=0.29..7.14 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=134)

  • Index Cond: (cd_veiculo = wvcr.cd_veiculo)
22. 0.804 0.804 ↑ 1.0 1 134

Index Scan using pk_ctrc on ctrc c_2 (cost=0.42..0.83 rows=1 width=28) (actual time=0.006..0.006 rows=1 loops=134)

  • Index Cond: (cd_ctrc = v_2.cd_ultimo_ctrc)
23. 3.602 78.195 ↑ 1.0 133 1

Unique (cost=3,111.48..3,167.61 rows=133 width=44) (actual time=71.576..78.195 rows=133 loops=1)

24. 27.151 74.593 ↓ 2.0 22,845 1

Sort (cost=3,111.48..3,139.54 rows=11,226 width=44) (actual time=71.572..74.593 rows=22,845 loops=1)

  • Sort Key: wvcr_1.cd_veiculo, (((((vr_3.dt_embarque)::text || ' '::text) || (COALESCE(vr_3.hr_embarque, '00:00:00'::time without time zone))::text))::timestamp without time zone) DESC
  • Sort Method: quicksort Memory: 2,553kB
25. 26.185 47.442 ↓ 2.0 22,845 1

Hash Join (cost=4.32..2,356.27 rows=11,226 width=44) (actual time=0.100..47.442 rows=22,845 loops=1)

  • Hash Cond: (vr_3.cd_veiculo = wvcr_1.cd_veiculo)
26. 21.201 21.201 ↓ 2.0 22,880 1

Seq Scan on viagem_receita vr_3 (cost=0.00..1,972.12 rows=11,479 width=32) (actual time=0.019..21.201 rows=22,880 loops=1)

  • Filter: ((cd_ctrc IS NULL) AND CASE WHEN ((COALESCE(vl_diaria, '0'::numeric) > '0'::numeric) AND (COALESCE(vl_frete, '0'::numeric) = '0'::numeric)) THEN false ELSE true END)
  • Rows Removed by Filter: 27,395
27. 0.035 0.056 ↓ 1.0 134 1

Hash (cost=2.66..2.66 rows=133 width=4) (actual time=0.056..0.056 rows=134 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
28. 0.021 0.021 ↓ 1.0 134 1

CTE Scan on with_veiculos_com_rastreamento wvcr_1 (cost=0.00..2.66 rows=133 width=4) (actual time=0.001..0.021 rows=134 loops=1)

29. 0.052 0.952 ↓ 2.0 82 1

Nested Loop (cost=0.58..1,008.88 rows=42 width=44) (actual time=0.056..0.952 rows=82 loops=1)

30. 0.071 0.498 ↓ 1.0 134 1

Nested Loop (cost=0.29..951.89 rows=133 width=8) (actual time=0.040..0.498 rows=134 loops=1)

31. 0.025 0.025 ↓ 1.0 134 1

CTE Scan on with_veiculos_com_rastreamento wvcr_2 (cost=0.00..2.66 rows=133 width=4) (actual time=0.002..0.025 rows=134 loops=1)

32. 0.402 0.402 ↑ 1.0 1 134

Index Scan using pk_veiculo on veiculo v_3 (cost=0.29..7.14 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=134)

  • Index Cond: (cd_veiculo = wvcr_2.cd_veiculo)
33. 0.402 0.402 ↑ 1.0 1 134

Index Scan using pk_ordem_carregamento on ordem_carregamento oc_1 (cost=0.29..0.43 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=134)

  • Index Cond: (cd_ordem = v_3.cd_ultima_ordem)
34. 0.349 0.349 ↑ 1.0 1 349

Index Only Scan using pk_veiculo on veiculo v_1 (cost=0.29..2.81 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=349)

  • Index Cond: (cd_veiculo = wvcr.cd_veiculo)
  • Heap Fetches: 0
35. 1.321 2.814 ↑ 1.0 5,758 1

Hash (cost=114.58..114.58 rows=5,758 width=20) (actual time=2.814..2.814 rows=5,758 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 354kB
36. 1.493 1.493 ↑ 1.0 5,758 1

Seq Scan on cidade ci (cost=0.00..114.58 rows=5,758 width=20) (actual time=0.016..1.493 rows=5,758 loops=1)

37.          

CTE with_veiculo_conjunto

38. 0.200 16.918 ↑ 1.3 227 1

WindowAgg (cost=745.86..753.38 rows=301 width=20) (actual time=16.716..16.918 rows=227 loops=1)

39. 0.177 16.718 ↑ 1.3 227 1

Sort (cost=745.86..746.61 rows=301 width=12) (actual time=16.697..16.718 rows=227 loops=1)

  • Sort Key: vc.cd_veiculo, (COALESCE((vc.nr_ordem)::integer, 0)), vc.cd_veiculo_conjunto
  • Sort Method: quicksort Memory: 35kB
40. 2.758 16.541 ↑ 1.3 227 1

Hash Join (cost=16.66..733.46 rows=301 width=12) (actual time=0.613..16.541 rows=227 loops=1)

  • Hash Cond: (vc.cd_veiculo = vp_2.cd_veiculo)
41. 13.625 13.625 ↓ 2.5 20,603 1

Seq Scan on veiculo_conjunto vc (cost=0.00..694.43 rows=8,229 width=10) (actual time=0.016..13.625 rows=20,603 loops=1)

  • Filter: ((dt_alteracao <= CURRENT_DATE) AND (COALESCE(dt_alteracao_final, CURRENT_DATE) >= CURRENT_DATE))
  • Rows Removed by Filter: 4,083
42. 0.083 0.158 ↑ 1.0 474 1

Hash (cost=10.74..10.74 rows=474 width=4) (actual time=0.158..0.158 rows=474 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
43. 0.075 0.075 ↑ 1.0 474 1

Seq Scan on veiculo_proprio vp_2 (cost=0.00..10.74 rows=474 width=4) (actual time=0.002..0.075 rows=474 loops=1)

44.          

Initplan (for Result)

45. 0.001 0.006 ↓ 0.0 0 1

Limit (cost=0.00..1.20 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=1)

46. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on grupo_veiculo_pessoa gvp_ (cost=0.00..1.20 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1)

  • Filter: (cd_pessoa = 1)
  • Rows Removed by Filter: 16
47. 0.970 27,958.240 ↓ 106.0 106 1

Sort (cost=55,032.09..55,032.10 rows=1 width=754) (actual time=27,958.181..27,958.240 rows=106 loops=1)

  • Sort Key: wvfd.dt_evento DESC
  • Sort Method: quicksort Memory: 106kB
48. 4.368 27,957.270 ↓ 106.0 106 1

Nested Loop Left Join (cost=47,946.54..55,032.08 rows=1 width=754) (actual time=24,155.381..27,957.270 rows=106 loops=1)

  • Join Filter: (wuae.cd_viagem = vi.cd_viagem)
49. 39.793 27,939.016 ↓ 106.0 106 1

Nested Loop Left Join (cost=45,613.48..52,698.88 rows=1 width=485) (actual time=24,141.480..27,939.016 rows=106 loops=1)

  • Join Filter: (wuav.cd_viagem = vi.cd_viagem)
  • Rows Removed by Join Filter: 368,246
50. 0.679 24,171.521 ↓ 106.0 106 1

Nested Loop Left Join (cost=42,165.31..49,249.37 rows=1 width=445) (actual time=23,713.419..24,171.521 rows=106 loops=1)

  • Join Filter: (wdf.cd_pessoa = wvfd.cd_pessoa_motorista)
  • Rows Removed by Join Filter: 2,220
51. 2.134 24,167.768 ↓ 106.0 106 1

Nested Loop Left Join (cost=42,136.13..49,219.49 rows=1 width=441) (actual time=23,713.069..24,167.768 rows=106 loops=1)

  • Join Filter: (wdv.cd_pessoa = wvfd.cd_pessoa_motorista)
  • Rows Removed by Join Filter: 15,310
52. 0.527 24,051.472 ↓ 106.0 106 1

Nested Loop Left Join (cost=42,061.27..49,111.20 rows=1 width=433) (actual time=23,711.389..24,051.472 rows=106 loops=1)

53. 0.482 24,050.415 ↓ 106.0 106 1

Nested Loop Left Join (cost=42,060.99..49,110.90 rows=1 width=425) (actual time=23,711.378..24,050.415 rows=106 loops=1)

54. 62.053 24,049.615 ↓ 106.0 106 1

Nested Loop Left Join (cost=42,060.70..49,110.51 rows=1 width=404) (actual time=23,711.354..24,049.615 rows=106 loops=1)

55. 0.413 23,987.138 ↓ 106.0 106 1

Nested Loop Left Join (cost=42,051.82..49,093.61 rows=1 width=395) (actual time=23,711.193..23,987.138 rows=106 loops=1)

  • Join Filter: (wvfd_2.cd_ficha = wvfd.cd_ficha)
56. 0.469 23,945.067 ↓ 106.0 106 1

Nested Loop Left Join (cost=38,496.93..45,538.64 rows=1 width=391) (actual time=23,669.635..23,945.067 rows=106 loops=1)

  • Join Filter: (p.cd_pessoa = COALESCE(ct.cd_pessoa_matriz, oc.cd_pessoa_matriz, ve.cd_pessoa_transportadora_doc))
  • Rows Removed by Join Filter: 212
57. 0.485 23,944.386 ↓ 106.0 106 1

Nested Loop Left Join (cost=38,470.95..45,512.58 rows=1 width=371) (actual time=23,669.549..23,944.386 rows=106 loops=1)

58. 0.609 23,943.795 ↓ 106.0 106 1

Nested Loop Left Join (cost=38,470.66..45,507.93 rows=1 width=367) (actual time=23,669.543..23,943.795 rows=106 loops=1)

59. 0.441 23,942.444 ↓ 106.0 106 1

Nested Loop Left Join (cost=38,470.24..45,502.01 rows=1 width=348) (actual time=23,669.527..23,942.444 rows=106 loops=1)

60. 0.488 23,941.897 ↓ 106.0 106 1

Nested Loop Left Join (cost=38,470.10..45,501.85 rows=1 width=349) (actual time=23,669.521..23,941.897 rows=106 loops=1)

61. 0.392 23,940.985 ↓ 106.0 106 1

Nested Loop Left Join (cost=38,469.82..45,501.16 rows=1 width=337) (actual time=23,669.508..23,940.985 rows=106 loops=1)

62. 0.453 23,940.063 ↓ 106.0 106 1

Nested Loop Left Join (cost=38,469.68..45,501.00 rows=1 width=338) (actual time=23,669.499..23,940.063 rows=106 loops=1)

63. 0.775 23,938.762 ↓ 106.0 106 1

Nested Loop Left Join (cost=38,469.39..45,500.32 rows=1 width=326) (actual time=23,669.481..23,938.762 rows=106 loops=1)

64. 0.785 23,936.609 ↓ 106.0 106 1

Hash Left Join (cost=38,469.10..45,496.60 rows=1 width=297) (actual time=23,669.445..23,936.609 rows=106 loops=1)

  • Hash Cond: (vcav.cd_tipo_veiculo = tvc.cd_tipo_veiculo)
  • Join Filter: (((tvc.cd_tipo_veiculo_1 = vcar1.cd_tipo_veiculo) OR (tvc.cd_tipo_veiculo_1 IS NULL)) AND ((tvc.cd_tipo_veiculo_2 = vcar2.cd_tipo_veiculo) OR (tvc.cd_tipo_veiculo_2 IS NULL)) AND ((tvc.cd_tipo_veiculo_3 = vcar3.cd_tipo_veiculo) OR (tvc.cd_tipo_veiculo_3 IS NULL)))
  • Rows Removed by Join Filter: 218
65. 31.319 23,935.801 ↓ 106.0 106 1

Nested Loop Left Join (cost=38,467.97..45,495.44 rows=1 width=304) (actual time=23,669.401..23,935.801 rows=106 loops=1)

  • Join Filter: (wvfd_1.cd_ficha = wvfd.cd_ficha)
  • Rows Removed by Join Filter: 296,519
66. 0.964 23,379.040 ↓ 106.0 106 1

Nested Loop Left Join (cost=38.73..7,059.20 rows=1 width=272) (actual time=23,310.792..23,379.040 rows=106 loops=1)

67. 9.511 23,376.486 ↓ 106.0 106 1

Nested Loop (cost=38.59..7,059.02 rows=1 width=270) (actual time=23,310.757..23,376.486 rows=106 loops=1)

  • Join Filter: (vp.cd_veiculo = vcavp.cd_veiculo)
  • Rows Removed by Join Filter: 50,138
68. 5.852 69.875 ↑ 1.0 474 1

Nested Loop Left Join (cost=17.54..2,342.22 rows=474 width=24) (actual time=18.355..69.875 rows=474 loops=1)

  • Join Filter: (wvc1.cd_veiculo = vcav.cd_veiculo)
  • Rows Removed by Join Filter: 49,192
69. 5.562 58.809 ↑ 1.0 474 1

Nested Loop Left Join (cost=17.25..2,304.61 rows=474 width=20) (actual time=17.947..58.809 rows=474 loops=1)

  • Join Filter: (wvc2.cd_veiculo = vcav.cd_veiculo)
  • Rows Removed by Join Filter: 43,989
70. 1.913 48.507 ↑ 1.0 474 1

Nested Loop Left Join (cost=16.96..2,266.99 rows=474 width=16) (actual time=17.469..48.507 rows=474 loops=1)

  • Join Filter: (wvc3.cd_veiculo = vcav.cd_veiculo)
  • Rows Removed by Join Filter: 14,190
71. 15.794 27.634 ↑ 1.0 474 1

Hash Join (cost=16.66..2,229.37 rows=474 width=12) (actual time=0.284..27.634 rows=474 loops=1)

  • Hash Cond: (vcav.cd_veiculo = vcavp.cd_veiculo)
72. 11.615 11.615 ↑ 1.0 70,550 1

Seq Scan on veiculo vcav (cost=0.00..2,027.50 rows=70,550 width=8) (actual time=0.012..11.615 rows=70,550 loops=1)

73. 0.128 0.225 ↑ 1.0 474 1

Hash (cost=10.74..10.74 rows=474 width=4) (actual time=0.225..0.225 rows=474 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
74. 0.097 0.097 ↑ 1.0 474 1

Seq Scan on veiculo_proprio vcavp (cost=0.00..10.74 rows=474 width=4) (actual time=0.014..0.097 rows=474 loops=1)

75. 1.794 18.960 ↓ 15.0 30 474

Materialize (cost=0.29..23.40 rows=2 width=8) (actual time=0.036..0.040 rows=30 loops=474)

76. 0.039 17.166 ↓ 15.0 30 1

Nested Loop Left Join (cost=0.29..23.39 rows=2 width=8) (actual time=16.764..17.166 rows=30 loops=1)

77. 17.007 17.007 ↓ 15.0 30 1

CTE Scan on with_veiculo_conjunto wvc3 (cost=0.00..6.77 rows=2 width=8) (actual time=16.734..17.007 rows=30 loops=1)

  • Filter: (rank = 3)
  • Rows Removed by Filter: 197
78. 0.120 0.120 ↑ 1.0 1 30

Index Scan using pk_veiculo on veiculo vcar3 (cost=0.29..8.31 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=30)

  • Index Cond: (cd_veiculo = wvc3.cd_veiculo_conjunto)
79. 4.313 4.740 ↓ 46.5 93 474

Materialize (cost=0.29..23.40 rows=2 width=8) (actual time=0.000..0.010 rows=93 loops=474)

80. 0.101 0.427 ↓ 46.5 93 1

Nested Loop Left Join (cost=0.29..23.39 rows=2 width=8) (actual time=0.025..0.427 rows=93 loops=1)

81. 0.047 0.047 ↓ 46.5 93 1

CTE Scan on with_veiculo_conjunto wvc2 (cost=0.00..6.77 rows=2 width=8) (actual time=0.002..0.047 rows=93 loops=1)

  • Filter: (rank = 2)
  • Rows Removed by Filter: 134
82. 0.279 0.279 ↑ 1.0 1 93

Index Scan using pk_veiculo on veiculo vcar2 (cost=0.29..8.31 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=93)

  • Index Cond: (cd_veiculo = wvc2.cd_veiculo_conjunto)
83. 4.860 5.214 ↓ 52.0 104 474

Materialize (cost=0.29..23.40 rows=2 width=8) (actual time=0.000..0.011 rows=104 loops=474)

84. 0.104 0.354 ↓ 52.0 104 1

Nested Loop Left Join (cost=0.29..23.39 rows=2 width=8) (actual time=0.024..0.354 rows=104 loops=1)

85. 0.042 0.042 ↓ 52.0 104 1

CTE Scan on with_veiculo_conjunto wvc1 (cost=0.00..6.77 rows=2 width=8) (actual time=0.001..0.042 rows=104 loops=1)

  • Filter: (rank = 1)
  • Rows Removed by Filter: 123
86. 0.208 0.208 ↑ 1.0 1 104

Index Scan using pk_veiculo on veiculo vcar1 (cost=0.29..8.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=104)

  • Index Cond: (cd_veiculo = wvc1.cd_veiculo_conjunto)
87. 6.203 23,297.100 ↓ 53.0 106 474

Materialize (cost=21.04..4,702.58 rows=2 width=262) (actual time=5.008..49.150 rows=106 loops=474)

88. 21.805 23,290.897 ↓ 53.0 106 1

Nested Loop (cost=21.04..4,702.57 rows=2 width=262) (actual time=2,373.902..23,290.897 rows=106 loops=1)

  • Join Filter: (vp.cd_veiculo = wvfd.cd_veiculo)
  • Rows Removed by Join Filter: 55,236
89. 5.543 19.257 ↓ 413.0 413 1

Nested Loop (cost=21.04..1,199.77 rows=1 width=64) (actual time=0.233..19.257 rows=413 loops=1)

90. 2.445 4.215 ↓ 2.0 413 1

Hash Join (cost=20.75..32.59 rows=206 width=48) (actual time=0.219..4.215 rows=413 loops=1)

  • Hash Cond: (vp.cd_veiculo = vp_1.cd_veiculo)
91. 1.578 1.578 ↑ 1.0 413 1

Seq Scan on veiculo_proprio vp (cost=0.00..10.74 rows=413 width=44) (actual time=0.009..1.578 rows=413 loops=1)

  • Filter: (dt_baixa IS NULL)
  • Rows Removed by Filter: 61
92. 0.098 0.192 ↓ 2.0 474 1

Hash (cost=17.79..17.79 rows=237 width=4) (actual time=0.192..0.192 rows=474 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
93. 0.094 0.094 ↓ 2.0 474 1

Seq Scan on veiculo_proprio vp_1 (cost=5.86..17.79 rows=237 width=4) (actual time=0.011..0.094 rows=474 loops=1)

  • Filter: CASE WHEN ($9 IS NOT NULL) THEN (hashed SubPlan 6) ELSE true END
94.          

SubPlan (for Seq Scan)

95. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..5.72 rows=55 width=4) (never executed)

  • Join Filter: (gv_.cd_grupo = gvv_.cd_grupo)
96. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..2.25 rows=1 width=8) (never executed)

  • Join Filter: (gv_.cd_grupo = gvp__1.cd_grupo)
97. 0.000 0.000 ↓ 0.0 0

Seq Scan on grupo_veiculo_pessoa gvp__1 (cost=0.00..1.20 rows=1 width=4) (never executed)

  • Filter: (cd_pessoa = 1)
98. 0.000 0.000 ↓ 0.0 0

Seq Scan on grupo_veiculo gv_ (cost=0.00..1.02 rows=2 width=4) (never executed)

  • Filter: (id_ativo = 1)
99. 0.000 0.000 ↓ 0.0 0

Seq Scan on grupo_veiculo_veiculo gvv_ (cost=0.00..2.10 rows=110 width=8) (never executed)

100. 9.499 9.499 ↑ 1.0 1 413

Index Scan using pk_veiculo on veiculo ve (cost=0.29..5.67 rows=1 width=16) (actual time=0.023..0.023 rows=1 loops=413)

  • Index Cond: (cd_veiculo = vp.cd_veiculo)
101. 23,249.835 23,249.835 ↑ 5.8 134 413

CTE Scan on with_veiculo_ficha_diaria wvfd (cost=0.00..3,493.10 rows=776 width=198) (actual time=1.447..56.295 rows=134 loops=413)

  • Filter: (rank = 1)
  • Rows Removed by Filter: 155,115
102. 1.590 1.590 ↑ 1.0 1 106

Index Scan using pk_rastreadora_veiculo on rastreadora_veiculo rv (cost=0.14..0.18 rows=1 width=10) (actual time=0.015..0.015 rows=1 loops=106)

  • Index Cond: ((cd_pessoa = wvfd.cd_pessoa_rastreadora) AND (cd_veiculo = ve.cd_veiculo))
103. 250.116 525.442 ↓ 14.0 2,798 106

HashAggregate (cost=38,429.24..38,431.74 rows=200 width=36) (actual time=3.370..4.957 rows=2,798 loops=106)

  • Group Key: wvfd_1.cd_ficha
104. 24.090 275.326 ↑ 7.3 72,105 1

Hash Left Join (cost=9,526.91..24,678.47 rows=523,839 width=36) (actual time=192.442..275.326 rows=72,105 loops=1)

  • Hash Cond: (vr_1.cd_viagem_receita = vrs.cd_viagem_receita)
105. 20.814 229.477 ↑ 7.3 72,105 1

Hash Join (cost=8,153.78..21,930.15 rows=523,839 width=32) (actual time=170.459..229.477 rows=72,105 loops=1)

  • Hash Cond: (v.cd_viagem = vr_1.cd_viagem)
106. 51.547 171.601 ↑ 6.5 5,766 1

Hash Join (cost=5,804.59..11,128.90 rows=37,260 width=8) (actual time=133.301..171.601 rows=5,766 loops=1)

  • Hash Cond: (v.cd_veiculo = wvfd_1.cd_veiculo)
  • Join Filter: (v.dt_saida < (wvfd_1.dt_evento)::date)
  • Rows Removed by Join Filter: 171,688
107. 0.824 0.824 ↑ 1.0 144 1

Seq Scan on viagem v (cost=0.00..91.76 rows=144 width=12) (actual time=0.447..0.824 rows=144 loops=1)

  • Filter: (dt_chegada IS NULL)
  • Rows Removed by Filter: 3,432
108. 74.421 119.230 ↑ 1.0 155,249 1

Hash (cost=3,104.98..3,104.98 rows=155,249 width=16) (actual time=119.230..119.230 rows=155,249 loops=1)

  • Buckets: 131,072 Batches: 4 Memory Usage: 2,903kB
109. 44.809 44.809 ↑ 1.0 155,249 1

CTE Scan on with_veiculo_ficha_diaria wvfd_1 (cost=0.00..3,104.98 rows=155,249 width=16) (actual time=0.012..44.809 rows=155,249 loops=1)

110. 14.133 37.062 ↑ 1.0 50,275 1

Hash (cost=1,720.75..1,720.75 rows=50,275 width=32) (actual time=37.061..37.062 rows=50,275 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,875kB
111. 22.929 22.929 ↑ 1.0 50,275 1

Seq Scan on viagem_receita vr_1 (cost=0.00..1,720.75 rows=50,275 width=32) (actual time=0.010..22.929 rows=50,275 loops=1)

112. 9.176 21.759 ↑ 1.0 40,939 1

Hash (cost=861.39..861.39 rows=40,939 width=12) (actual time=21.759..21.759 rows=40,939 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,363kB
113. 12.583 12.583 ↑ 1.0 40,939 1

Seq Scan on viagem_receita_saldo vrs (cost=0.00..861.39 rows=40,939 width=12) (actual time=0.010..12.583 rows=40,939 loops=1)

114. 0.005 0.023 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=25) (actual time=0.023..0.023 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
115. 0.018 0.018 ↑ 1.0 6 1

Seq Scan on tipo_veiculo_conjunto tvc (cost=0.00..1.06 rows=6 width=25) (actual time=0.016..0.018 rows=6 loops=1)

116. 1.378 1.378 ↑ 1.0 1 106

Index Scan using pk_pessoa on pessoa pm (cost=0.29..3.72 rows=1 width=29) (actual time=0.013..0.013 rows=1 loops=106)

  • Index Cond: (cd_pessoa = wvfd.cd_pessoa_motorista)
117. 0.848 0.848 ↑ 1.0 1 106

Index Scan using pk_cidade on cidade co (cost=0.28..0.69 rows=1 width=20) (actual time=0.008..0.008 rows=1 loops=106)

  • Index Cond: (cd_cidade = wvfd.cd_cidade_origem)
118. 0.530 0.530 ↑ 1.0 1 106

Index Scan using pk_uf on uf uo (cost=0.14..0.16 rows=1 width=7) (actual time=0.005..0.005 rows=1 loops=106)

  • Index Cond: (cd_uf = co.cd_uf)
119. 0.424 0.424 ↑ 1.0 1 106

Index Scan using pk_cidade on cidade cd (cost=0.28..0.69 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=106)

  • Index Cond: (cd_cidade = wvfd.cd_cidade_destino)
120. 0.106 0.106 ↑ 1.0 1 106

Index Scan using pk_uf on uf ud (cost=0.14..0.16 rows=1 width=7) (actual time=0.001..0.001 rows=1 loops=106)

  • Index Cond: (cd_uf = cd.cd_uf)
121. 0.742 0.742 ↑ 1.0 1 106

Index Scan using pk_ctrc on ctrc ct (cost=0.42..5.91 rows=1 width=23) (actual time=0.007..0.007 rows=1 loops=106)

  • Index Cond: (cd_ctrc = wvfd.cd_ctrc)
122. 0.106 0.106 ↓ 0.0 0 106

Index Scan using pk_ordem_carregamento on ordem_carregamento oc (cost=0.29..4.66 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=106)

  • Index Cond: (cd_ordem = wvfd.cd_ordem_carregamento)
123. 0.176 0.212 ↑ 1.0 3 106

Sort (cost=25.98..25.99 rows=3 width=36) (actual time=0.001..0.002 rows=3 loops=106)

  • Sort Key: (COALESCE(p.nm_fantasia, p.nm_pessoa))
  • Sort Method: quicksort Memory: 25kB
124. 0.008 0.036 ↑ 1.0 3 1

Nested Loop (cost=0.29..25.96 rows=3 width=36) (actual time=0.017..0.036 rows=3 loops=1)

125. 0.007 0.007 ↑ 1.0 3 1

Seq Scan on empresa_grupo eg (cost=0.00..1.04 rows=3 width=4) (actual time=0.005..0.007 rows=3 loops=1)

  • Filter: (id_ativo = 1)
126. 0.021 0.021 ↑ 1.0 1 3

Index Scan using pk_pessoa on pessoa p (cost=0.29..8.31 rows=1 width=47) (actual time=0.007..0.007 rows=1 loops=3)

  • Index Cond: (cd_pessoa = eg.cd_pessoa)
127. 0.106 41.658 ↓ 0.0 0 106

GroupAggregate (cost=3,554.88..3,554.95 rows=1 width=8) (actual time=0.393..0.393 rows=0 loops=106)

  • Group Key: wvfd_2.cd_ficha
128. 0.006 41.552 ↓ 0.0 0 106

Sort (cost=3,554.88..3,554.90 rows=7 width=8) (actual time=0.392..0.392 rows=0 loops=106)

  • Sort Key: wvfd_2.cd_ficha
  • Sort Method: quicksort Memory: 25kB
129. 0.001 41.546 ↓ 0.0 0 1

Nested Loop (cost=4.77..3,554.78 rows=7 width=8) (actual time=41.546..41.546 rows=0 loops=1)

130. 0.002 41.545 ↓ 0.0 0 1

Nested Loop (cost=4.48..3,156.55 rows=1 width=8) (actual time=41.544..41.545 rows=0 loops=1)

131. 41.543 41.543 ↓ 0.0 0 1

CTE Scan on with_veiculo_ficha_diaria wvfd_2 (cost=0.00..3,104.98 rows=1 width=16) (actual time=41.543..41.543 rows=0 loops=1)

  • Filter: ((cd_ctrc IS NULL) AND (cd_ordem_carregamento IS NULL) AND (cd_viagem_receita IS NULL))
  • Rows Removed by Filter: 155,249
132. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on viagem vi_1 (cost=4.48..51.56 rows=1 width=12) (never executed)

  • Recheck Cond: (cd_veiculo = wvfd_2.cd_veiculo)
  • Filter: ((dt_chegada IS NULL) AND (dt_saida <= (wvfd_2.dt_evento)::date))
133. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_viagem_cd_veiculo (cost=0.00..4.48 rows=26 width=0) (never executed)

  • Index Cond: (cd_veiculo = wvfd_2.cd_veiculo)
134. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_viagem_receita_dt_embarque_cd_viagem on viagem_receita vr_2 (cost=0.29..398.16 rows=7 width=8) (never executed)

  • Index Cond: (cd_viagem = vi_1.cd_viagem)
  • Filter: CASE WHEN ((COALESCE(vl_diaria, '0'::numeric) > '0'::numeric) AND (COALESCE(vl_frete, '0'::numeric) = '0'::numeric)) THEN false ELSE true END
135. 0.000 0.424 ↑ 1.0 1 106

Index Scan using pk_viagem_receita on viagem_receita vr (cost=8.89..16.90 rows=1 width=17) (actual time=0.004..0.004 rows=1 loops=106)

  • Index Cond: (cd_viagem_receita = COALESCE(wvfd.cd_viagem_receita, (max(vr_2.cd_viagem_receita)), (SubPlan 8)))
136.          

SubPlan (for Index Scan)

137. 0.360 61.020 ↑ 1.0 1 90

Result (cost=8.59..8.60 rows=1 width=4) (actual time=0.678..0.678 rows=1 loops=90)

138.          

Initplan (for Result)

139. 0.270 60.660 ↑ 1.0 1 90

Limit (cost=0.29..8.59 rows=1 width=4) (actual time=0.674..0.674 rows=1 loops=90)

140. 60.390 60.390 ↑ 370.0 1 90

Index Scan Backward using pk_viagem_receita on viagem_receita vr_ (cost=0.29..3,070.33 rows=370 width=4) (actual time=0.671..0.671 rows=1 loops=90)

  • Index Cond: (cd_viagem_receita IS NOT NULL)
  • Filter: (cd_veiculo = ve.cd_veiculo)
  • Rows Removed by Filter: 1,061
141. 0.318 0.318 ↑ 1.0 1 106

Index Scan using pk_pessoa on pessoa pp (cost=0.29..0.38 rows=1 width=29) (actual time=0.003..0.003 rows=1 loops=106)

  • Index Cond: (cd_pessoa = vr.cd_pessoa)
142. 0.530 0.530 ↑ 1.0 1 106

Index Scan using pk_viagem on viagem vi (cost=0.28..0.30 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=106)

  • Index Cond: (cd_viagem = vr.cd_viagem)
143. 12.296 114.162 ↓ 29.0 145 106

Subquery Scan on wdv (cost=74.85..108.23 rows=5 width=12) (actual time=0.012..1.077 rows=145 loops=106)

  • Filter: (wdv.rank = 1)
  • Rows Removed by Filter: 882
144. 86.814 101.866 ↑ 1.0 1,027 106

WindowAgg (cost=74.85..95.39 rows=1,027 width=20) (actual time=0.012..0.961 rows=1,027 loops=106)

145. 14.827 15.052 ↑ 1.0 1,027 106

Sort (cost=74.85..77.42 rows=1,027 width=12) (actual time=0.006..0.142 rows=1,027 loops=106)

  • Sort Key: ff.cd_pessoa, ff.dt_final DESC
  • Sort Method: quicksort Memory: 97kB
146. 0.225 0.225 ↑ 1.0 1,027 1

Seq Scan on funcionario_folga ff (cost=0.00..23.48 rows=1,027 width=12) (actual time=0.010..0.225 rows=1,027 loops=1)

  • Filter: (dt_final IS NOT NULL)
  • Rows Removed by Filter: 21
147. 0.424 3.074 ↓ 21.0 21 106

Subquery Scan on wdf (cost=29.18..29.86 rows=1 width=12) (actual time=0.006..0.029 rows=21 loops=106)

  • Filter: (wdf.rank = 1)
148. 2.014 2.650 ↑ 1.0 21 106

WindowAgg (cost=29.18..29.60 rows=21 width=20) (actual time=0.006..0.025 rows=21 loops=106)

149. 0.370 0.636 ↑ 1.0 21 106

Sort (cost=29.18..29.23 rows=21 width=12) (actual time=0.003..0.006 rows=21 loops=106)

  • Sort Key: ff_1.cd_pessoa, ff_1.dt_inicio DESC
  • Sort Method: quicksort Memory: 25kB
150. 0.266 0.266 ↑ 1.0 21 1

Seq Scan on funcionario_folga ff_1 (cost=0.00..28.72 rows=21 width=12) (actual time=0.087..0.266 rows=21 loops=1)

  • Filter: ((dt_final IS NULL) OR (dt_final > CURRENT_TIMESTAMP))
  • Rows Removed by Filter: 1,027
151. 334.960 3,727.702 ↓ 3,475.0 3,475 106

Subquery Scan on wuav (cost=3,448.17..3,449.50 rows=1 width=44) (actual time=3.749..35.167 rows=3,475 loops=106)

  • Filter: (wuav.rank = 1)
  • Rows Removed by Filter: 23,886
152. 2,633.782 3,392.742 ↓ 720.0 27,361 106

WindowAgg (cost=3,448.17..3,449.02 rows=38 width=68) (actual time=3.748..32.007 rows=27,361 loops=106)

153. 376.944 758.960 ↓ 720.0 27,361 106

Sort (cost=3,448.17..3,448.26 rows=38 width=60) (actual time=3.746..7.160 rows=27,361 loops=106)

  • Sort Key: cvi.cd_viagem, (COALESCE(cve.dt_abastecimento, c.dt_documento)) DESC, cvi.cd_caixa
  • Sort Method: quicksort Memory: 2,906kB
154. 27.314 382.016 ↓ 720.0 27,361 1

GroupAggregate (cost=3,446.03..3,447.17 rows=38 width=60) (actual time=351.253..382.016 rows=27,361 loops=1)

  • Group Key: cvi.cd_viagem, cvi.cd_caixa, cve.dt_abastecimento, c.cd_caixa, cve.id_medidor
155. 21.572 354.702 ↓ 726.8 27,619 1

Sort (cost=3,446.03..3,446.13 rows=38 width=29) (actual time=351.234..354.702 rows=27,619 loops=1)

  • Sort Key: cvi.cd_viagem, cvi.cd_caixa, cve.dt_abastecimento
  • Sort Method: quicksort Memory: 2,926kB
156. 8.155 333.130 ↓ 726.8 27,619 1

Nested Loop (cost=138.33..3,445.04 rows=38 width=29) (actual time=1.219..333.130 rows=27,619 loops=1)

157. 11.324 266.689 ↓ 277.6 29,143 1

Nested Loop (cost=138.04..3,411.27 rows=105 width=29) (actual time=1.197..266.689 rows=29,143 loops=1)

  • Join Filter: (cve.cd_caixa = c.cd_caixa)
158. 38.123 167.936 ↓ 212.7 29,143 1

Nested Loop (cost=137.62..3,330.84 rows=137 width=21) (actual time=1.182..167.936 rows=29,143 loops=1)

159. 36.652 56.657 ↓ 180.2 36,578 1

Hash Join (cost=137.20..3,235.19 rows=203 width=9) (actual time=1.146..56.657 rows=36,578 loops=1)

  • Hash Cond: (cp.cd_produto = pc.cd_produto)
160. 18.887 18.887 ↑ 1.0 130,198 1

Seq Scan on caixa_produto cp (cost=0.00..2,755.98 rows=130,198 width=13) (actual time=0.017..18.887 rows=130,198 loops=1)

161. 0.006 1.118 ↑ 1.0 9 1

Hash (cost=137.09..137.09 rows=9 width=4) (actual time=1.118..1.118 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
162. 1.112 1.112 ↑ 1.0 9 1

Seq Scan on produto_consumo pc (cost=0.00..137.09 rows=9 width=4) (actual time=0.023..1.112 rows=9 loops=1)

  • Filter: (id_combustivel = 1)
  • Rows Removed by Filter: 5,758
163. 73.156 73.156 ↑ 1.0 1 36,578

Index Scan using pk_caixa_veiculo on caixa_veiculo cve (cost=0.42..0.47 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=36,578)

  • Index Cond: (cd_caixa = cp.cd_caixa)
  • Filter: ((id_tanque_cheio = 1) AND (id_medidor = 1))
  • Rows Removed by Filter: 0
164. 87.429 87.429 ↑ 1.0 1 29,143

Index Scan using pk_caixa on caixa c (cost=0.42..0.57 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=29,143)

  • Index Cond: (cd_caixa = cp.cd_caixa)
165. 58.286 58.286 ↑ 1.0 1 29,143

Index Only Scan using pk_caixa_viagem on caixa_viagem cvi (cost=0.29..0.31 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=29,143)

  • Index Cond: (cd_caixa = c.cd_caixa)
  • Heap Fetches: 0
166. 0.000 13.886 ↓ 0.0 0 106

Subquery Scan on wuae (cost=2,333.06..2,333.10 rows=1 width=44) (actual time=0.131..0.131 rows=0 loops=106)

  • Filter: (wuae.rank = 1)
167. 0.106 13.886 ↓ 0.0 0 106

WindowAgg (cost=2,333.06..2,333.09 rows=1 width=68) (actual time=0.131..0.131 rows=0 loops=106)

168. 0.044 13.780 ↓ 0.0 0 106

Sort (cost=2,333.06..2,333.07 rows=1 width=60) (actual time=0.130..0.130 rows=0 loops=106)

  • Sort Key: cvi_1.cd_viagem, (COALESCE(cve_1.dt_abastecimento, c_1.dt_documento)) DESC, cvi_1.cd_caixa
  • Sort Method: quicksort Memory: 25kB
169. 0.003 13.736 ↓ 0.0 0 1

GroupAggregate (cost=2,333.02..2,333.05 rows=1 width=60) (actual time=13.736..13.736 rows=0 loops=1)

  • Group Key: cvi_1.cd_viagem, cvi_1.cd_caixa, cve_1.dt_abastecimento, c_1.cd_caixa, cve_1.id_medidor
170. 0.005 13.733 ↓ 0.0 0 1

Sort (cost=2,333.02..2,333.03 rows=1 width=29) (actual time=13.733..13.733 rows=0 loops=1)

  • Sort Key: cvi_1.cd_viagem, cvi_1.cd_caixa, cve_1.dt_abastecimento
  • Sort Method: quicksort Memory: 25kB
171. 0.001 13.728 ↓ 0.0 0 1

Nested Loop (cost=1.41..2,333.01 rows=1 width=29) (actual time=13.728..13.728 rows=0 loops=1)

172. 0.001 13.727 ↓ 0.0 0 1

Nested Loop (cost=1.13..2,332.70 rows=1 width=33) (actual time=13.727..13.727 rows=0 loops=1)

  • Join Filter: (cvi_1.cd_caixa = cp_1.cd_caixa)
173. 0.000 13.726 ↓ 0.0 0 1

Nested Loop (cost=0.71..2,332.14 rows=1 width=28) (actual time=13.726..13.726 rows=0 loops=1)

174. 0.002 13.726 ↓ 0.0 0 1

Nested Loop (cost=0.29..2,331.36 rows=1 width=20) (actual time=13.725..13.726 rows=0 loops=1)

175. 13.724 13.724 ↓ 0.0 0 1

Seq Scan on caixa_veiculo cve_1 (cost=0.00..2,327.04 rows=1 width=12) (actual time=13.724..13.724 rows=0 loops=1)

  • Filter: ((id_tanque_cheio = 1) AND (id_medidor = 2))
  • Rows Removed by Filter: 107,336
176. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_caixa_viagem on caixa_viagem cvi_1 (cost=0.29..4.31 rows=1 width=8) (never executed)

  • Index Cond: (cd_caixa = cve_1.cd_caixa)
  • Heap Fetches: 0
177. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_caixa on caixa c_1 (cost=0.42..0.78 rows=1 width=8) (never executed)

  • Index Cond: (cd_caixa = cvi_1.cd_caixa)
178. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_caixa_produto_cd_caixa on caixa_produto cp_1 (cost=0.42..0.52 rows=3 width=13) (never executed)

  • Index Cond: (cd_caixa = c_1.cd_caixa)
179. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_produto_consumo on produto_consumo pc_1 (cost=0.28..0.31 rows=1 width=4) (never executed)

  • Index Cond: (cd_produto = cp_1.cd_produto)
  • Filter: (id_combustivel = 1)
180.          

SubPlan (for Result)

181. 0.106 1.060 ↓ 0.0 0 106

Limit (cost=1.24..1.24 rows=1 width=45) (actual time=0.010..0.010 rows=0 loops=106)

182. 0.530 0.954 ↓ 0.0 0 106

Sort (cost=1.24..1.24 rows=1 width=45) (actual time=0.009..0.009 rows=0 loops=106)

  • Sort Key: vo_.dt_observacao DESC
  • Sort Method: quicksort Memory: 25kB
183. 0.424 0.424 ↓ 0.0 0 106

Seq Scan on veiculo_observacao vo_ (cost=0.00..1.23 rows=1 width=45) (actual time=0.004..0.004 rows=0 loops=106)

  • Filter: (cd_veiculo = vp.cd_veiculo)
  • Rows Removed by Filter: 18
Planning time : 28.651 ms
Execution time : 28,300.747 ms