explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5Mon

Settings
# exclusive inclusive rows x rows loops node
1. 0.172 73,239.859 ↓ 3.5 138 1

Sort (cost=37,995,288.19..37,995,288.29 rows=40 width=952) (actual time=73,239.854..73,239.859 rows=138 loops=1)

  • Sort Key: abt.referencia_bico, (min(COALESCE((min((b_1.abt_vl_encerrante - b_1.abt_vl_abastecimento))), (COALESCE(min(abt.encerrante_inicial_abt) FILTER (WHERE ((abt.data_abt >= $6) OR (NOT (SubPlan 3)))), '0'::numeric)))) OVER (?)), (max(CASE WHEN ((((max(abt.encerrante_final_abt)) - (COALESCE(min(abt.encerrante_inicial_abt) FILTER (WHERE ((abt.data_abt >= $6) OR (NOT (SubPlan 3)))), '0'::numeric))) - (sum(abt.quantidade_afericao))) > (COALESCE(sum(b.abt_vl_volume_abastecido), '0'::numeric))) THEN ((max(abt.encerrante_final_abt)) - (COALESCE(sum(b.abt_vl_volume_abastecido), '0'::numeric))) ELSE (max(abt.encerrante_final_abt)) END) OVER (?)), abt.preco_bomba
  • Sort Method: quicksort Memory: 61kB
2.          

CTE caixa_meta

3. 0.014 0.045 ↑ 1.0 1 1

Aggregate (cost=42.06..42.07 rows=1 width=24) (actual time=0.044..0.045 rows=1 loops=1)

4. 0.031 0.031 ↑ 1.0 16 1

Index Scan using caixa_pkey on caixa cai_6 (cost=0.42..41.90 rows=16 width=12) (actual time=0.006..0.031 rows=16 loops=1)

  • Index Cond: (cai_cd_caixa = ANY ('{1506492,1506645,1506729,1506931,1506919,1507288,1507294,1506954,1507558,1507655,1507825,1507960,1507983,1508244,1508504,1508518}'::integer[]))
5. 0.565 73,239.687 ↓ 3.5 138 1

WindowAgg (cost=37,995,241.16..37,995,245.06 rows=40 width=952) (actual time=73,239.135..73,239.687 rows=138 loops=1)

6. 0.132 73,239.122 ↓ 3.5 138 1

Sort (cost=37,995,241.16..37,995,241.26 rows=40 width=728) (actual time=73,239.115..73,239.122 rows=138 loops=1)

  • Sort Key: abt.bico, abt.substituicao, abt.produto
  • Sort Method: quicksort Memory: 54kB
7. 0.083 73,238.990 ↓ 3.5 138 1

Group (cost=37,995,237.40..37,995,240.10 rows=40 width=728) (actual time=73,238.905..73,238.990 rows=138 loops=1)

  • Group Key: abt.referencia_bico, abt.descricao_produto, (sum(abt.quantidade_afericao)), (sum(abt.qtd_abastecimento)), abt.preco_bomba, (CASE WHEN (sum((abt.valor_venda_item - abt.valor_abastecimento)) > '0'::numeric) THEN sum((abt.valor_venda_item - abt.valor_abastecimento)) ELSE '0'::numeric END), (CASE WHEN (sum((abt.valor_venda_item - abt.valor_abastecimento)) < '0'::numeric) THEN abs(sum((abt.valor_venda_item - abt.valor_abastecimento))) ELSE '0'::numeric END), (COALESCE(sum(abt.valor_venda_item), '0'::numeric)), abt.substituicao, abt.descricao_bomba, (COALESCE(min(abt.encerrante_inicial_abt) FILTER (WHERE ((abt.data_abt >= $6) OR (NOT (SubPlan 3)))), '0'::numeric)), (COALESCE(sum(b_1.abt_vl_volume_abastecido), '0'::numeric)), (max(abt.encerrante_final_abt)), (COALESCE(sum(b.abt_vl_volume_abastecido), '0'::numeric)), abt.bico, abt.produto, (((((abt.bico)::character varying)::text || ((abt.substituicao)::character varying)::text) || ((abt.produto)::character varying)::text)), (min((b_1.abt_vl_encerrante - b_1.abt_vl_abastecimento)))
8. 0.513 73,238.907 ↓ 3.5 138 1

Sort (cost=37,995,237.40..37,995,237.50 rows=40 width=728) (actual time=73,238.901..73,238.907 rows=138 loops=1)

  • Sort Key: abt.referencia_bico, abt.descricao_produto, (sum(abt.quantidade_afericao)), (sum(abt.qtd_abastecimento)), abt.preco_bomba, (CASE WHEN (sum((abt.valor_venda_item - abt.valor_abastecimento)) > '0'::numeric) THEN sum((abt.valor_venda_item - abt.valor_abastecimento)) ELSE '0'::numeric END), (CASE WHEN (sum((abt.valor_venda_item - abt.valor_abastecimento)) < '0'::numeric) THEN abs(sum((abt.valor_venda_item - abt.valor_abastecimento))) ELSE '0'::numeric END), (COALESCE(sum(abt.valor_venda_item), '0'::numeric)), abt.substituicao, abt.descricao_bomba, (COALESCE(min(abt.encerrante_inicial_abt) FILTER (WHERE ((abt.data_abt >= $6) OR (NOT (SubPlan 3)))), '0'::numeric)), (COALESCE(sum(b_1.abt_vl_volume_abastecido), '0'::numeric)), (max(abt.encerrante_final_abt)), (COALESCE(sum(b.abt_vl_volume_abastecido), '0'::numeric)), abt.bico, abt.produto, (((((abt.bico)::character varying)::text || ((abt.substituicao)::character varying)::text) || ((abt.produto)::character varying)::text)), (min((b_1.abt_vl_encerrante - b_1.abt_vl_abastecimento)))
  • Sort Method: quicksort Memory: 54kB
9. 0.485 73,238.394 ↓ 3.5 138 1

Nested Loop (cost=35,959,228.77..37,995,236.33 rows=40 width=728) (actual time=68,101.828..73,238.394 rows=138 loops=1)

10. 0.445 68,303.029 ↓ 3.5 138 1

Nested Loop (cost=35,957,867.01..37,940,763.93 rows=40 width=632) (actual time=68,090.430..68,303.029 rows=138 loops=1)

11. 4.016 68,271.948 ↓ 3.5 138 1

GroupAggregate (cost=35,957,854.07..37,940,244.87 rows=40 width=604) (actual time=68,090.370..68,271.948 rows=138 loops=1)

  • Group Key: abt.referencia_bico, abt.descricao_produto, abt.preco_bomba, abt.substituicao, abt.descricao_bomba, abt.produto, abt.bico
12.          

Initplan (for GroupAggregate)

13. 0.008 0.054 ↑ 1.0 1 1

Aggregate (cost=41.94..41.95 rows=1 width=4) (actual time=0.054..0.054 rows=1 loops=1)

14. 0.046 0.046 ↑ 1.0 16 1

Index Scan using caixa_pkey on caixa caii (cost=0.42..41.90 rows=16 width=4) (actual time=0.017..0.046 rows=16 loops=1)

  • Index Cond: (cai_cd_caixa = ANY ('{1506492,1506645,1506729,1506931,1506919,1507288,1507294,1506954,1507558,1507655,1507825,1507960,1507983,1508244,1508504,1508518}'::integer[]))
15. 4.948 68,090.883 ↓ 75.9 3,036 1

Sort (cost=35,957,812.12..35,957,812.22 rows=40 width=580) (actual time=68,090.296..68,090.883 rows=3,036 loops=1)

  • Sort Key: abt.referencia_bico, abt.descricao_produto, abt.preco_bomba, abt.substituicao, abt.descricao_bomba, abt.produto, abt.bico
  • Sort Method: quicksort Memory: 661kB
16. 0.786 68,085.935 ↓ 75.9 3,036 1

Subquery Scan on abt (cost=3.11..35,957,811.06 rows=40 width=580) (actual time=0.257..68,085.935 rows=3,036 loops=1)

17. 0.340 68,085.149 ↓ 75.9 3,036 1

Append (cost=3.11..35,957,810.66 rows=40 width=612) (actual time=0.256..68,085.149 rows=3,036 loops=1)

18. 1.275 52.856 ↓ 607.2 3,036 1

Subquery Scan on *SELECT* 1 (cost=3.11..1,135.69 rows=5 width=192) (actual time=0.256..52.856 rows=3,036 loops=1)

19. 3.115 51.581 ↓ 607.2 3,036 1

Nested Loop (cost=3.11..1,135.62 rows=5 width=136) (actual time=0.254..51.581 rows=3,036 loops=1)

20. 0.825 45.430 ↓ 607.2 3,036 1

Nested Loop (cost=2.83..1,124.22 rows=5 width=91) (actual time=0.232..45.430 rows=3,036 loops=1)

21. 2.716 38.533 ↓ 607.2 3,036 1

Nested Loop (cost=2.41..1,111.57 rows=5 width=63) (actual time=0.097..38.533 rows=3,036 loops=1)

22. 2.134 32.781 ↓ 607.2 3,036 1

Nested Loop (cost=2.12..1,099.88 rows=5 width=51) (actual time=0.087..32.781 rows=3,036 loops=1)

23. 2.241 19.994 ↓ 710.2 3,551 1

Nested Loop (cost=1.56..1,086.51 rows=5 width=18) (actual time=0.074..19.994 rows=3,551 loops=1)

24. 0.414 3.193 ↓ 1,456.0 2,912 1

Nested Loop (cost=0.99..389.60 rows=2 width=12) (actual time=0.036..3.193 rows=2,912 loops=1)

25. 0.075 0.075 ↓ 16.0 16 1

Index Scan using caixa_pkey on caixa cai (cost=0.42..41.94 rows=1 width=12) (actual time=0.019..0.075 rows=16 loops=1)

  • Index Cond: (cai_cd_caixa = ANY ('{1506492,1506645,1506729,1506931,1506919,1507288,1507294,1506954,1507558,1507655,1507825,1507960,1507983,1508244,1508504,1508518}'::integer[]))
  • Filter: (cai_cd_unidade_negocio = 180)
26. 2.704 2.704 ↓ 22.8 182 16

Index Scan using venda_idx_caixa on venda ven (cost=0.56..347.59 rows=8 width=12) (actual time=0.009..0.169 rows=182 loops=16)

  • Index Cond: (ven_cd_caixa = cai.cai_cd_caixa)
  • Filter: ((ven_cd_unidade_negocio = 180) AND (ven_fl_cancelada = 'N'::bpchar))
  • Rows Removed by Filter: 1
27. 14.560 14.560 ↑ 412.0 1 2,912

Index Scan using venda_item_idx_venda_rebuild_1 on venda_item vei (cost=0.57..344.33 rows=412 width=14) (actual time=0.005..0.005 rows=1 loops=2,912)

  • Index Cond: (vei_cd_venda = ven.ven_cd_venda)
28. 10.653 10.653 ↑ 1.0 1 3,551

Index Scan using abastecimento_abt_cd_venda_item_key on abastecimento abt_1 (cost=0.57..2.67 rows=1 width=41) (actual time=0.003..0.003 rows=1 loops=3,551)

  • Index Cond: (abt_cd_venda_item = vei.vei_cd_venda_item)
  • Filter: (NOT abt_bl_afericao)
29. 3.036 3.036 ↑ 1.0 1 3,036

Index Scan using bico_pkey on bico bic (cost=0.28..2.34 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=3,036)

  • Index Cond: (bic_cd_bico = abt_1.abt_cd_bico)
30. 6.072 6.072 ↑ 1.0 1 3,036

Index Scan using produto_pkey on produto pro (cost=0.42..2.53 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=3,036)

  • Index Cond: (pro_cd_produto = bic.bic_cd_produto)
31. 3.036 3.036 ↑ 1.0 1 3,036

Index Scan using bomba_pkey on bomba bom (cost=0.28..2.28 rows=1 width=13) (actual time=0.001..0.001 rows=1 loops=3,036)

  • Index Cond: (bom_cd_bomba = bic.bic_cd_bomba)
32. 0.000 0.319 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=2.40..68.35 rows=1 width=218) (actual time=0.319..0.319 rows=0 loops=1)

33. 0.001 0.319 ↓ 0.0 0 1

Nested Loop (cost=2.40..68.33 rows=1 width=134) (actual time=0.319..0.319 rows=0 loops=1)

34. 0.000 0.318 ↓ 0.0 0 1

Nested Loop (cost=2.12..66.48 rows=1 width=85) (actual time=0.318..0.318 rows=0 loops=1)

35. 0.000 0.318 ↓ 0.0 0 1

Nested Loop (cost=1.70..63.84 rows=1 width=57) (actual time=0.318..0.318 rows=0 loops=1)

36. 0.033 0.318 ↓ 0.0 0 1

Nested Loop (cost=1.41..61.87 rows=1 width=49) (actual time=0.318..0.318 rows=0 loops=1)

  • Join Filter: (abt_2.abt_dt_abastecimento >= cai_1.cai_dt_movimento)
37. 0.015 0.135 ↓ 50.0 50 1

Nested Loop (cost=0.84..59.32 rows=1 width=16) (actual time=0.034..0.135 rows=50 loops=1)

38. 0.040 0.040 ↓ 16.0 16 1

Index Scan using caixa_pkey on caixa cai_1 (cost=0.42..41.94 rows=1 width=12) (actual time=0.010..0.040 rows=16 loops=1)

  • Index Cond: (cai_cd_caixa = ANY ('{1506492,1506645,1506729,1506931,1506919,1507288,1507294,1506954,1507558,1507655,1507825,1507960,1507983,1508244,1508504,1508518}'::integer[]))
  • Filter: (cai_cd_unidade_negocio = 180)
39. 0.080 0.080 ↑ 5.0 3 16

Index Scan using abastecimento_afericao_idx_aba_cd_caixa on abastecimento_afericao aba (cost=0.42..17.24 rows=15 width=8) (actual time=0.003..0.005 rows=3 loops=16)

  • Index Cond: (aba_cd_caixa = cai_1.cai_cd_caixa)
40. 0.150 0.150 ↓ 0.0 0 50

Index Scan using abastecimento_pkey on abastecimento abt_2 (cost=0.57..2.53 rows=1 width=45) (actual time=0.003..0.003 rows=0 loops=50)

  • Index Cond: (abt_cd_abastecimento = aba.aba_cd_abastecimento)
  • Filter: ((NOT abt_bl_afericao) AND (abt_cd_unidade_negocio = 180))
  • Rows Removed by Filter: 1
41. 0.000 0.000 ↓ 0.0 0

Index Scan using bico_pkey on bico bic_1 (cost=0.28..1.88 rows=1 width=20) (never executed)

  • Index Cond: (bic_cd_bico = abt_2.abt_cd_bico)
  • Filter: (bic_cd_unidade_negocio = 180)
42. 0.000 0.000 ↓ 0.0 0

Index Scan using produto_pkey on produto pro_1 (cost=0.42..2.64 rows=1 width=32) (never executed)

  • Index Cond: (pro_cd_produto = bic_1.bic_cd_produto)
43. 0.000 0.000 ↓ 0.0 0

Index Scan using bomba_pkey on bomba bom_1 (cost=0.28..1.85 rows=1 width=13) (never executed)

  • Index Cond: (bom_cd_bomba = bic_1.bic_cd_bomba)
44. 0.001 68,031.634 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=2.98..35,956,606.42 rows=34 width=321) (actual time=68,031.634..68,031.634 rows=0 loops=1)

45. 0.230 68,031.633 ↓ 0.0 0 1

Nested Loop Anti Join (cost=2.98..35,956,605.57 rows=34 width=153) (actual time=68,031.633..68,031.633 rows=0 loops=1)

  • Join Filter: (abt_3.abt_cd_bico = bic_2.bic_cd_bico)
  • Rows Removed by Join Filter: 2912
46. 2.238 68,025.861 ↑ 1.0 34 1

Nested Loop (cost=0.85..35,938,187.13 rows=34 width=93) (actual time=2,009.783..68,025.861 rows=34 loops=1)

  • Join Filter: (bic_2.bic_cd_bomba = bom_2.bom_cd_bomba)
  • Rows Removed by Join Filter: 33542
47. 98.175 68,021.991 ↑ 1.0 34 1

Nested Loop (cost=0.85..35,937,110.43 rows=34 width=88) (actual time=2,009.484..68,021.991 rows=34 loops=1)

  • Join Filter: (bic_2.bic_cd_produto = pro_2.pro_cd_produto)
  • Rows Removed by Join Filter: 1449704
48. 0.079 67,852.280 ↑ 1.0 34 1

Nested Loop (cost=0.85..35,776,137.85 rows=34 width=60) (actual time=1,998.321..67,852.280 rows=34 loops=1)

49. 0.058 2.651 ↑ 1.0 34 1

Nested Loop (cost=0.28..6,986.70 rows=34 width=24) (actual time=0.203..2.651 rows=34 loops=1)

50. 2.525 2.525 ↑ 1.0 34 1

Index Scan using bico_pkey on bico bic_2 (cost=0.28..6,986.01 rows=34 width=16) (actual time=0.154..2.525 rows=34 loops=1)

  • Filter: (bic_cd_unidade_negocio = 180)
  • Rows Removed by Filter: 6586
51. 0.068 0.068 ↑ 1.0 1 34

CTE Scan on caixa_meta cm (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=34)

52. 0.170 67,849.550 ↑ 1.0 1 34

Limit (cost=0.57..1,052,033.84 rows=1 width=36) (actual time=1,995.572..1,995.575 rows=1 loops=34)

53. 203.320 67,849.380 ↑ 1.0 1 34

GroupAggregate (cost=0.57..1,052,033.84 rows=1 width=36) (actual time=1,995.570..1,995.570 rows=1 loops=34)

  • Group Key: 0
54. 2,023.340 67,646.060 ↑ 1.0 6,899 34

Nested Loop (cost=0.57..1,051,945.15 rows=7,094 width=36) (actual time=0.750..1,989.590 rows=6,899 loops=34)

55. 962.812 10,917.944 ↓ 32.4 229,852 34

Nested Loop (cost=0.56..1,037,686.21 rows=7,094 width=1,399) (actual time=0.036..321.116 rows=229,852 loops=34)

56. 170.272 2,107.660 ↑ 2.4 1,304 34

Nested Loop (cost=0.00..19,093.91 rows=3,121 width=8) (actual time=0.024..61.990 rows=1,304 loops=34)

  • Join Filter: (cai_3.cai_ts_abertura < cm_2.min_ts_abertura)
  • Rows Removed by Join Filter: 7820
57. 1,937.388 1,937.388 ↑ 1.0 9,124 34

Seq Scan on caixa cai_3 (cost=0.00..18,883.24 rows=9,363 width=16) (actual time=0.004..56.982 rows=9,124 loops=34)

  • Filter: (cai_cd_unidade_negocio = 180)
  • Rows Removed by Filter: 588495
58. 0.000 0.000 ↑ 1.0 1 310,216

CTE Scan on caixa_meta cm_2 (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=310,216)

59. 7,847.472 7,847.472 ↓ 22.0 176 44,336

Index Scan using venda_idx_caixa on venda ven_2 (cost=0.56..326.29 rows=8 width=1,407) (actual time=0.007..0.177 rows=176 loops=44,336)

  • Index Cond: (ven_cd_caixa = cai_3.cai_cd_caixa)
  • Filter: ((ven_cd_unidade_negocio = 180) AND (ven_fl_cancelada = 'N'::bpchar))
  • Rows Removed by Filter: 1
60. 54,704.776 54,704.776 ↓ 0.0 0 7,814,968

Function Scan on jsonb_array_elements vei_2 (cost=0.01..2.00 rows=1 width=32) (actual time=0.007..0.007 rows=0 loops=7,814,968)

  • Filter: (((value ->> 'abt_cd_bico'::text))::integer = bic_2.bic_cd_bico)
  • Rows Removed by Filter: 1
61. 56.180 71.536 ↑ 6.8 42,639 34

Materialize (cost=0.00..13,468.72 rows=290,648 width=32) (actual time=0.001..2.104 rows=42,639 loops=34)

62. 15.356 15.356 ↑ 4.4 65,963 1

Seq Scan on produto pro_2 (cost=0.00..12,015.48 rows=290,648 width=32) (actual time=0.014..15.356 rows=65,963 loops=1)

63. 1.329 1.632 ↑ 2.0 988 34

Materialize (cost=0.00..57.14 rows=2,009 width=13) (actual time=0.001..0.048 rows=988 loops=34)

64. 0.303 0.303 ↑ 1.1 1,901 1

Seq Scan on bomba bom_2 (cost=0.00..47.09 rows=2,009 width=13) (actual time=0.009..0.303 rows=1,901 loops=1)

65. 0.189 5.542 ↓ 1.1 87 34

Materialize (cost=2.12..18,378.85 rows=78 width=4) (actual time=0.002..0.163 rows=87 loops=34)

66. 0.133 5.353 ↓ 8.7 679 1

Nested Loop (cost=2.12..18,378.46 rows=78 width=4) (actual time=0.052..5.353 rows=679 loops=1)

67. 0.492 3.024 ↓ 8.5 732 1

Nested Loop (cost=1.56..18,148.43 rows=86 width=4) (actual time=0.039..3.024 rows=732 loops=1)

68. 0.057 0.612 ↓ 17.8 640 1

Nested Loop (cost=0.99..5,604.14 rows=36 width=4) (actual time=0.029..0.612 rows=640 loops=1)

69. 0.005 0.030 ↑ 5.3 3 1

Nested Loop (cost=0.42..42.23 rows=16 width=4) (actual time=0.015..0.030 rows=3 loops=1)

70. 0.022 0.022 ↑ 5.3 3 1

Index Only Scan using caixa_pkey on caixa cai_2 (cost=0.42..41.90 rows=16 width=4) (actual time=0.013..0.022 rows=3 loops=1)

  • Index Cond: (cai_cd_caixa = ANY ('{1506492,1506645,1506729,1506931,1506919,1507288,1507294,1506954,1507558,1507655,1507825,1507960,1507983,1508244,1508504,1508518}'::integer[]))
  • Heap Fetches: 3
71. 0.003 0.003 ↑ 1.0 1 3

CTE Scan on caixa_meta cm_1 (cost=0.00..0.02 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=3)

72. 0.525 0.525 ↓ 26.6 213 3

Index Scan using venda_idx_caixa on venda ven_1 (cost=0.56..347.54 rows=8 width=8) (actual time=0.010..0.175 rows=213 loops=3)

  • Index Cond: (ven_cd_caixa = cai_2.cai_cd_caixa)
  • Filter: ((ven_cd_unidade_negocio = 180) AND (ven_fl_cancelada = 'N'::bpchar))
  • Rows Removed by Filter: 1
73. 1.920 1.920 ↑ 412.0 1 640

Index Scan using venda_item_idx_venda_rebuild_1 on venda_item vei_1 (cost=0.57..344.33 rows=412 width=8) (actual time=0.003..0.003 rows=1 loops=640)

  • Index Cond: (vei_cd_venda = ven_1.ven_cd_venda)
74. 2.196 2.196 ↑ 1.0 1 732

Index Scan using abastecimento_abt_cd_venda_item_key on abastecimento abt_3 (cost=0.57..2.67 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=732)

  • Index Cond: (abt_cd_venda_item = vei_1.vei_cd_venda_item)
75.          

SubPlan (for GroupAggregate)

76. 0.070 176.995 ↑ 1.0 1 7

Hash Join (cost=351.04..49,559.68 rows=1 width=0) (actual time=25.285..25.285 rows=1 loops=7)

  • Hash Cond: (vei_6.vei_cd_venda = ven_6.ven_cd_venda)
77. 0.036 175.126 ↑ 4,067.0 2 7

Nested Loop (cost=1.14..49,188.42 rows=8,134 width=4) (actual time=25.002..25.018 rows=2 loops=7)

78. 8.078 174.930 ↑ 4,067.0 2 7

Nested Loop (cost=0.57..26,886.87 rows=8,134 width=4) (actual time=24.983..24.990 rows=2 loops=7)

  • Join Filter: (abtc.abt_dt_abastecimento >= cm_5.min_dt_movimento)
  • Rows Removed by Join Filter: 5730
79. 0.000 0.000 ↑ 1.0 1 7

CTE Scan on caixa_meta cm_5 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=7)

80. 166.852 166.852 ↑ 4.3 5,732 7

Index Scan using abastecimento_idx_cd_bico on abastecimento abtc (cost=0.57..26,581.82 rows=24,402 width=8) (actual time=0.027..23.836 rows=5,732 loops=7)

  • Index Cond: (abt_cd_bico = abt.bico)
81. 0.160 0.160 ↑ 1.0 1 16

Index Scan using venda_item_pkey on venda_item vei_6 (cost=0.57..2.74 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=16)

  • Index Cond: (vei_cd_venda_item = abtc.abt_cd_venda_item)
82. 0.287 1.799 ↓ 1.1 334 7

Hash (cost=346.04..346.04 rows=309 width=4) (actual time=0.257..0.257 rows=334 loops=7)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
83. 1.512 1.512 ↓ 1.1 334 7

Index Scan using venda_idx_caixa on venda ven_6 (cost=0.56..346.04 rows=309 width=4) (actual time=0.011..0.216 rows=334 loops=7)

  • Index Cond: (ven_cd_caixa = abt.caixa)
84. 0.276 30.636 ↑ 1.0 1 138

Aggregate (cost=12.94..12.95 rows=1 width=32) (actual time=0.222..0.222 rows=1 loops=138)

85. 0.000 30.360 ↓ 0.0 0 138

Nested Loop (cost=2.12..12.93 rows=1 width=6) (actual time=0.220..0.220 rows=0 loops=138)

  • Join Filter: (cai_4.cai_ts_abertura > cm_3.max_ts_abertura)
  • Rows Removed by Join Filter: 0
86. 0.128 30.360 ↓ 0.0 0 138

Nested Loop (cost=2.12..12.90 rows=1 width=14) (actual time=0.219..0.220 rows=0 loops=138)

87. 0.000 30.222 ↓ 0.0 0 138

Nested Loop (cost=1.70..10.37 rows=1 width=14) (actual time=0.218..0.219 rows=0 loops=138)

88. 1.568 18.906 ↓ 17.0 17 138

Nested Loop (cost=1.14..7.61 rows=1 width=14) (actual time=0.036..0.137 rows=17 loops=138)

89. 5.658 5.658 ↓ 17.0 17 138

Index Scan using abt_unidade_dtfiscal_bico on abastecimento b (cost=0.57..4.83 rows=1 width=14) (actual time=0.023..0.041 rows=17 loops=138)

  • Index Cond: ((abt_cd_bico = abt.bico) AND (abt_cd_unidade_negocio = 180) AND (abt_dt_fiscal >= (max(abt.maior_data))) AND (abt_vl_encerrante < (max(abt.encerrante_final_abt))) AND (abt_cd_substituicao = abt.substituicao))
90. 11.680 11.680 ↑ 1.0 1 2,336

Index Scan using venda_item_pkey on venda_item vei_3 (cost=0.57..2.79 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=2,336)

  • Index Cond: (vei_cd_venda_item = b.abt_cd_venda_item)
  • Filter: (vei_cd_produto = abt.produto)
91. 11.435 11.435 ↓ 0.0 0 2,287

Index Scan using venda_pkey on venda ven_3 (cost=0.56..2.75 rows=1 width=12) (actual time=0.005..0.005 rows=0 loops=2,287)

  • Index Cond: (ven_cd_venda = vei_3.vei_cd_venda)
  • Filter: ((ven_cd_unidade_negocio = 180) AND (ven_fl_cancelada = 'N'::bpchar) AND (ven_cd_caixa <> ALL ('{1506492,1506645,1506729,1506931,1506919,1507288,1507294,1506954,1507558,1507655,1507825,1507960,1507983,1508244,1508504,1508518}'::integer[])))
  • Rows Removed by Filter: 1
92. 0.010 0.010 ↑ 1.0 1 2

Index Scan using caixa_pkey on caixa cai_4 (cost=0.42..2.53 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=2)

  • Index Cond: (cai_cd_caixa = ven_3.ven_cd_caixa)
  • Filter: (cai_cd_unidade_negocio = 180)
93. 0.002 0.002 ↑ 1.0 1 2

CTE Scan on caixa_meta cm_3 (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.001 rows=1 loops=2)

94. 0.552 4,934.880 ↑ 1.0 1 138

Aggregate (cost=1,361.76..1,361.77 rows=1 width=64) (actual time=35.759..35.760 rows=1 loops=138)

95. 10.488 4,934.328 ↓ 0.0 0 138

Nested Loop (cost=2.69..1,361.75 rows=1 width=19) (actual time=24.480..35.756 rows=0 loops=138)

  • Join Filter: (b_1.abt_dt_abastecimento < cm_4.min_dt_movimento)
  • Rows Removed by Join Filter: 142
96. 30.475 4,923.840 ↓ 142.0 142 138

Nested Loop Semi Join (cost=2.69..1,361.72 rows=1 width=23) (actual time=0.577..35.680 rows=142 loops=138)

  • Join Filter: ((cai_5.cai_cd_caixa = ven_5.ven_cd_caixa) AND (((vei_5.value ->> 'abt_ts_abastecimento'::text))::date >= cai_5.cai_dt_movimento))
  • Rows Removed by Join Filter: 0
97. 56.307 1,743.078 ↓ 142.0 142 138

Nested Loop (cost=2.12..397.83 rows=1 width=39) (actual time=0.375..12.631 rows=142 loops=138)

98. 264.546 1,435.200 ↓ 608.0 608 138

Nested Loop (cost=1.56..395.07 rows=1 width=20) (actual time=0.028..10.400 rows=608 loops=138)

99. 44.298 366.942 ↓ 1,456.0 2,912 138

Nested Loop (cost=0.99..389.60 rows=2 width=20) (actual time=0.009..2.659 rows=2,912 loops=138)

100. 6.900 6.900 ↓ 16.0 16 138

Index Scan using caixa_pkey on caixa cai_5 (cost=0.42..41.94 rows=1 width=12) (actual time=0.003..0.050 rows=16 loops=138)

  • Index Cond: (cai_cd_caixa = ANY ('{1506492,1506645,1506729,1506931,1506919,1507288,1507294,1506954,1507558,1507655,1507825,1507960,1507983,1508244,1508504,1508518}'::integer[]))
  • Filter: (cai_cd_unidade_negocio = 180)
101. 315.744 315.744 ↓ 22.8 182 2,208

Index Scan using venda_idx_caixa on venda ven_4 (cost=0.56..347.59 rows=8 width=12) (actual time=0.006..0.143 rows=182 loops=2,208)

  • Index Cond: (ven_cd_caixa = cai_5.cai_cd_caixa)
  • Filter: ((ven_cd_unidade_negocio = 180) AND (ven_fl_cancelada = 'N'::bpchar))
  • Rows Removed by Filter: 1
102. 803.712 803.712 ↓ 0.0 0 401,856

Index Scan using idx_tst_01_rebuild_1 on venda_item vei_4 (cost=0.57..2.72 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=401,856)

  • Index Cond: ((vei_cd_venda = ven_4.ven_cd_venda) AND (vei_cd_produto = abt.produto))
103. 251.571 251.571 ↓ 0.0 0 83,857

Index Scan using abastecimento_abt_cd_venda_item_key on abastecimento b_1 (cost=0.57..2.75 rows=1 width=35) (actual time=0.003..0.003 rows=0 loops=83,857)

  • Index Cond: (abt_cd_venda_item = vei_4.vei_cd_venda_item)
  • Filter: ((abt_cd_unidade_negocio = 180) AND (abt_cd_bico = abt.bico))
  • Rows Removed by Filter: 1
104. 270.939 3,150.287 ↑ 309.0 1 19,567

Nested Loop (cost=0.57..956.93 rows=309 width=36) (actual time=0.160..0.161 rows=1 loops=19,567)

105. 234.804 234.804 ↑ 16.3 19 19,567

Index Scan using venda_idx_caixa on venda ven_5 (cost=0.56..335.84 rows=309 width=1,403) (actual time=0.004..0.012 rows=19 loops=19,567)

  • Index Cond: (ven_cd_caixa = ven_4.ven_cd_caixa)
106. 2,644.544 2,644.544 ↓ 0.0 0 377,792

Function Scan on jsonb_array_elements vei_5 (cost=0.01..2.00 rows=1 width=32) (actual time=0.007..0.007 rows=0 loops=377,792)

  • Filter: (((value ->> 'abt_cd_bico'::text))::integer = abt.bico)
  • Rows Removed by Filter: 1
107. 0.000 0.000 ↑ 1.0 1 19,567

CTE Scan on caixa_meta cm_4 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=19,567)

Planning time : 14.984 ms
Execution time : 73,241.480 ms