explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BfQq

Settings
# exclusive inclusive rows x rows loops node
1. 3.080 87,538.567 ↓ 1,257.2 6,286 1

Unique (cost=584,351.66..584,351.80 rows=5 width=75) (actual time=87,535.005..87,538.567 rows=6,286 loops=1)

2. 6.474 87,535.487 ↓ 1,291.0 6,455 1

Sort (cost=584,351.66..584,351.68 rows=5 width=75) (actual time=87,535.004..87,535.487 rows=6,455 loops=1)

  • Sort Key: tb_rd.id_veiculo, tb_trecho_viagem.id_trecho_viagem, tb_viagem.id_viagem, tb_viagem.vl_ordem, tb_veiculo.vl_capacidade, tb_trecho_viagem.vl_total_manipulado, tb_veiculo.cd_identificador, tb_rd.cd_rd, (dt_inicio(tb_trecho_viagem.*)), c.nm_coletor
  • Sort Method: quicksort Memory: 1,100kB
3. 6,445.815 87,529.013 ↓ 1,291.0 6,455 1

Nested Loop (cost=18.98..584,351.61 rows=5 width=75) (actual time=77,274.028..87,529.013 rows=6,455 loops=1)

  • Join Filter: ((tb_rd.id_veiculo)::integer = (tb_veiculo.id_veiculo)::integer)
  • Rows Removed by Join Filter: 67,848,505
4. 4.142 4.142 ↑ 1.0 10,512 1

Seq Scan on tb_veiculo (cost=0.00..327.12 rows=10,512 width=19) (actual time=0.005..4.142 rows=10,512 loops=1)

5. 4,477.814 81,079.056 ↓ 1,291.0 6,455 10,512

Materialize (cost=18.98..583,234.85 rows=5 width=92) (actual time=0.491..7.713 rows=6,455 loops=10,512)

6. 3.430 76,601.242 ↓ 1,291.0 6,455 1

Nested Loop (cost=18.98..583,234.82 rows=5 width=92) (actual time=5,158.921..76,601.242 rows=6,455 loops=1)

7. 21.703 74,338.992 ↓ 1.4 16,732 1

Nested Loop (cost=18.55..526,336.05 rows=12,261 width=44) (actual time=12.928..74,338.992 rows=16,732 loops=1)

8. 8.527 73,561.089 ↓ 1.1 9,950 1

Nested Loop Left Join (cost=18.11..502,750.99 rows=9,157 width=42) (actual time=10.352..73,561.089 rows=9,950 loops=1)

9. 32.366 32.366 ↓ 1.1 9,804 1

Index Scan using ind_tb_rd_idfilial_dtini_idveiculo_idrd on tb_rd (cost=0.43..6,814.22 rows=9,157 width=18) (actual time=4.718..32.366 rows=9,804 loops=1)

  • Index Cond: (((id_filial)::integer = 55) AND (dt_inicio_terminal >= '2020-03-29 23:59:59'::timestamp without time zone) AND (dt_inicio_terminal <= '2020-04-30 23:59:01-03'::timestamp with time zone))
10. 12.960 73,520.196 ↓ 0.0 0 9,804

Nested Loop Left Join (cost=17.68..54.15 rows=1 width=28) (actual time=6.601..7.499 rows=0 loops=9,804)

11. 20.588 73,392.744 ↓ 0.0 0 9,804

Nested Loop Left Join (cost=17.39..53.84 rows=1 width=8) (actual time=6.588..7.486 rows=0 loops=9,804)

12. 894.620 72,480.972 ↓ 0.0 0 9,804

Nested Loop (cost=16.96..53.30 rows=1 width=8) (actual time=6.509..7.393 rows=0 loops=9,804)

  • Join Filter: (dt_fim(tv.*) = (max(dt_fim(tv2.*))))
  • Rows Removed by Join Filter: 3
13. 17,352.096 19,490.352 ↓ 1.5 3 9,804

Nested Loop Left Join (cost=1.29..21.39 rows=2 width=49) (actual time=1.477..1.988 rows=3 loops=9,804)

  • Filter: ((dt_fim(tv.*) >= e.dt_inicio) AND (dt_inicio(tv.*) < COALESCE(e.dt_fim, now())))
  • Rows Removed by Filter: 0
14. 500.004 500.004 ↓ 0.0 0 9,804

Index Scan using ix03f_tb_equipe on tb_equipe e (cost=0.43..2.02 rows=4 width=24) (actual time=0.047..0.051 rows=0 loops=9,804)

  • Index Cond: ((tb_rd.id_rd)::integer = (id_rd)::integer)
15. 26.218 1,638.252 ↓ 2.0 8 4,158

Nested Loop Left Join (cost=0.86..2.77 rows=4 width=45) (actual time=0.365..0.394 rows=8 loops=4,158)

16. 561.330 561.330 ↓ 1.5 3 4,158

Index Scan using ix01f_tb_viagem on tb_viagem v (cost=0.43..0.52 rows=2 width=8) (actual time=0.132..0.135 rows=3 loops=4,158)

  • Index Cond: ((e.id_rd)::integer = (id_rd)::integer)
17. 21.664 1,050.704 ↑ 2.3 3 10,832

Append (cost=0.43..1.06 rows=7 width=45) (actual time=0.092..0.097 rows=3 loops=10,832)

18. 1,007.376 1,007.376 ↑ 1.0 3 10,832

Index Scan using ix01f_tb_trecho_viagem on tb_trecho_viagem tv (cost=0.43..0.49 rows=3 width=98) (actual time=0.090..0.093 rows=3 loops=10,832)

  • Index Cond: ((v.id_viagem)::integer = (id_viagem)::integer)
19. 21.664 21.664 ↓ 0.0 0 10,832

Index Scan using ix01f_tbtrechoviagemu20191111 on tb_trecho_viagem_clust_until_20191111 tv_1 (cost=0.43..0.54 rows=4 width=36) (actual time=0.002..0.002 rows=0 loops=10,832)

  • Index Cond: ((v.id_viagem)::integer = (id_viagem)::integer)
20. 48,156.240 52,096.000 ↑ 1.0 1 32,560

Aggregate (cost=15.67..15.68 rows=1 width=8) (actual time=1.600..1.600 rows=1 loops=32,560)

21. 146.968 3,939.760 ↓ 2.8 11 32,560

Nested Loop (cost=0.86..14.66 rows=4 width=41) (actual time=0.008..0.121 rows=11 loops=32,560)

22. 976.800 976.800 ↓ 1.5 3 32,560

Index Scan using ix01f_tb_viagem on tb_viagem v2 (cost=0.43..2.87 rows=2 width=4) (actual time=0.003..0.030 rows=3 loops=32,560)

  • Index Cond: ((id_rd)::integer = (e.id_rd)::integer)
23. 208.592 2,815.992 ↑ 2.3 3 104,296

Append (cost=0.43..5.83 rows=7 width=45) (actual time=0.017..0.027 rows=3 loops=104,296)

24. 2,398.808 2,398.808 ↑ 1.0 3 104,296

Index Scan using ix01f_tb_trecho_viagem on tb_trecho_viagem tv2 (cost=0.43..2.89 rows=3 width=98) (actual time=0.016..0.023 rows=3 loops=104,296)

  • Index Cond: ((id_viagem)::integer = (v2.id_viagem)::integer)
25. 208.592 208.592 ↓ 0.0 0 104,296

Index Scan using ix01f_tbtrechoviagemu20191111 on tb_trecho_viagem_clust_until_20191111 tv2_1 (cost=0.43..2.91 rows=4 width=36) (actual time=0.002..0.002 rows=0 loops=104,296)

  • Index Cond: ((id_viagem)::integer = (v2.id_viagem)::integer)
26. 891.184 891.184 ↑ 1.0 1 4,088

Index Scan using pk00_tb_equipe_coletor on tb_equipe_coletor ec (cost=0.43..0.54 rows=1 width=8) (actual time=0.190..0.218 rows=1 loops=4,088)

  • Index Cond: ((e.id_equipe)::integer = (id_equipe)::integer)
  • Filter: (vl_ordem = 1)
  • Rows Removed by Filter: 2
27. 114.492 114.492 ↑ 1.0 1 4,089

Index Scan using pk00_tb_coletor on tb_coletor c (cost=0.29..0.31 rows=1 width=28) (actual time=0.028..0.028 rows=1 loops=4,089)

  • Index Cond: ((ec.id_coletor)::integer = (id_coletor)::integer)
28. 756.200 756.200 ↑ 1.0 2 9,950

Index Scan using ix01f_tb_viagem on tb_viagem (cost=0.43..2.56 rows=2 width=10) (actual time=0.071..0.076 rows=2 loops=9,950)

  • Index Cond: ((id_rd)::integer = (tb_rd.id_rd)::integer)
29. 50.196 2,258.820 ↓ 0.0 0 16,732

Append (cost=0.43..4.62 rows=2 width=52) (actual time=0.132..0.135 rows=0 loops=16,732)

30. 2,175.160 2,175.160 ↓ 0.0 0 16,732

Index Scan using ix01f_tb_trecho_viagem on tb_trecho_viagem (cost=0.43..2.03 rows=1 width=106) (actual time=0.129..0.130 rows=0 loops=16,732)

  • Index Cond: ((id_viagem)::integer = (tb_viagem.id_viagem)::integer)
  • Filter: ((vl_total_manipulado >= 0) AND (vl_total_manipulado <= 30,000) AND ((tp_trecho)::smallint = 2) AND (dt_inicio(tb_trecho_viagem.*) > '2020-03-31 23:59:59-03'::timestamp with time zone) AND (dt_inicio(tb_trecho_viagem.*) < '2020-04-30 23:59:01-03'::timestamp with time zone))
  • Rows Removed by Filter: 2
31. 33.464 33.464 ↓ 0.0 0 16,732

Index Scan using ix01f_tbtrechoviagemu20191111 on tb_trecho_viagem_clust_until_20191111 tb_trecho_viagem_1 (cost=0.43..2.59 rows=1 width=44) (actual time=0.002..0.002 rows=0 loops=16,732)

  • Index Cond: ((id_viagem)::integer = (tb_viagem.id_viagem)::integer)
  • Filter: ((vl_total_manipulado >= 0) AND (vl_total_manipulado <= 30,000) AND ((tp_trecho)::smallint = 2) AND (dt_inicio((tb_trecho_viagem_1.*)::tb_trecho_viagem) > '2020-03-31 23:59:59-03'::timestamp with time zone) AND (dt_inicio((tb_trecho_viagem_1.*)::tb_trecho_viagem) < '2020-04-30 23:59:01-03'::timestamp with time zone))
Planning time : 97.223 ms
Execution time : 87,541.061 ms