explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4MWU

Settings
# exclusive inclusive rows x rows loops node
1. 0.054 43,811.409 ↓ 1.3 67 1

Sort (cost=11,854,206.95..11,854,207.08 rows=52 width=952) (actual time=43,811.407..43,811.409 rows=67 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 >= $2) 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 >= $2) 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: 42kB
2.          

CTE caixa_meta

3. 0.009 0.021 ↑ 1.0 1 1

Aggregate (cost=2.65..2.66 rows=1 width=24) (actual time=0.021..0.021 rows=1 loops=1)

4. 0.012 0.012 ↑ 1.0 1 1

Index Scan using caixa_pkey on caixa cai_6 (cost=0.42..2.64 rows=1 width=12) (actual time=0.012..0.012 rows=1 loops=1)

  • Index Cond: (cai_cd_caixa = 1506645)
5. 0.278 43,811.355 ↓ 1.3 67 1

WindowAgg (cost=11,854,197.73..11,854,202.80 rows=52 width=952) (actual time=43,811.105..43,811.355 rows=67 loops=1)

6. 0.050 43,811.077 ↓ 1.3 67 1

Sort (cost=11,854,197.73..11,854,197.86 rows=52 width=728) (actual time=43,811.074..43,811.077 rows=67 loops=1)

  • Sort Key: abt.bico, abt.substituicao, abt.produto
  • Sort Method: quicksort Memory: 40kB
7. 0.043 43,811.027 ↓ 1.3 67 1

Group (cost=11,854,192.74..11,854,196.25 rows=52 width=728) (actual time=43,810.985..43,811.027 rows=67 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 >= $2) 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.225 43,810.984 ↓ 1.3 67 1

Sort (cost=11,854,192.74..11,854,192.87 rows=52 width=728) (actual time=43,810.981..43,810.984 rows=67 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 >= $2) 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: 40kB
9. 0.363 43,810.759 ↓ 1.3 67 1

Nested Loop (cost=4,090,589.43..11,854,191.26 rows=52 width=728) (actual time=745.162..43,810.759 rows=67 loops=1)

10. 0.294 1,481.136 ↓ 1.3 67 1

Nested Loop (cost=3,995,993.44..6,935,197.04 rows=52 width=632) (actual time=743.244..1,481.136 rows=67 loops=1)

11. 1.264 878.445 ↓ 1.3 67 1

GroupAggregate (cost=3,988,893.57..6,566,001.60 rows=52 width=604) (actual time=742.391..878.445 rows=67 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.003 0.009 ↑ 1.0 1 1

Aggregate (cost=2.65..2.65 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=1)

14. 0.006 0.006 ↑ 1.0 1 1

Index Scan using caixa_pkey on caixa caii (cost=0.42..2.64 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (cai_cd_caixa = 1506645)
15. 1.237 742.505 ↓ 11.9 621 1

Sort (cost=3,988,890.91..3,988,891.04 rows=52 width=580) (actual time=742.357..742.505 rows=621 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: 148kB
16. 0.301 741.268 ↓ 11.9 621 1

Subquery Scan on abt (cost=3.11..3,988,889.43 rows=52 width=580) (actual time=0.134..741.268 rows=621 loops=1)

17. 0.130 740.967 ↓ 11.9 621 1

Append (cost=3.11..3,988,888.91 rows=52 width=612) (actual time=0.133..740.967 rows=621 loops=1)

18. 0.441 15.739 ↓ 36.4 618 1

Subquery Scan on *SELECT* 1 (cost=3.11..3,450.62 rows=17 width=192) (actual time=0.132..15.739 rows=618 loops=1)

19. 1.082 15.298 ↓ 36.4 618 1

Nested Loop (cost=3.11..3,450.37 rows=17 width=136) (actual time=0.130..15.298 rows=618 loops=1)

20. 0.613 12.980 ↓ 36.4 618 1

Nested Loop (cost=2.83..3,411.63 rows=17 width=91) (actual time=0.111..12.980 rows=618 loops=1)

21. 0.889 10.513 ↓ 36.4 618 1

Nested Loop (cost=2.41..3,368.62 rows=17 width=63) (actual time=0.097..10.513 rows=618 loops=1)

22. 0.466 8.388 ↓ 36.4 618 1

Nested Loop (cost=2.12..3,328.88 rows=17 width=51) (actual time=0.083..8.388 rows=618 loops=1)

23. 0.654 4.582 ↓ 35.2 668 1

Nested Loop (cost=1.56..3,278.06 rows=19 width=18) (actual time=0.067..4.582 rows=668 loops=1)

24. 0.150 0.953 ↓ 74.4 595 1

Nested Loop (cost=0.99..350.31 rows=8 width=12) (actual time=0.051..0.953 rows=595 loops=1)

25. 0.024 0.024 ↑ 1.0 1 1

Index Scan using caixa_pkey on caixa cai (cost=0.42..2.65 rows=1 width=12) (actual time=0.023..0.024 rows=1 loops=1)

  • Index Cond: (cai_cd_caixa = 1506645)
  • Filter: (cai_cd_unidade_negocio = 180)
26. 0.779 0.779 ↓ 74.4 595 1

Index Scan using venda_idx_caixa on venda ven (cost=0.56..347.59 rows=8 width=12) (actual time=0.025..0.779 rows=595 loops=1)

  • Index Cond: (ven_cd_caixa = 1506645)
  • Filter: ((ven_cd_unidade_negocio = 180) AND (ven_fl_cancelada = 'N'::bpchar))
  • Rows Removed by Filter: 3
27. 2.975 2.975 ↑ 412.0 1 595

Index Scan using venda_item_idx_venda_rebuild_1 on venda_item vei (cost=0.57..361.85 rows=412 width=14) (actual time=0.005..0.005 rows=1 loops=595)

  • Index Cond: (vei_cd_venda = ven.ven_cd_venda)
28. 3.340 3.340 ↑ 1.0 1 668

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.005..0.005 rows=1 loops=668)

  • Index Cond: (abt_cd_venda_item = vei.vei_cd_venda_item)
  • Filter: (NOT abt_bl_afericao)
29. 1.236 1.236 ↑ 1.0 1 618

Index Scan using bico_pkey on bico bic (cost=0.28..2.34 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=618)

  • Index Cond: (bic_cd_bico = abt_1.abt_cd_bico)
30. 1.854 1.854 ↑ 1.0 1 618

Index Scan using produto_pkey on produto pro (cost=0.42..2.53 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=618)

  • Index Cond: (pro_cd_produto = bic.bic_cd_produto)
31. 1.236 1.236 ↑ 1.0 1 618

Index Scan using bomba_pkey on bomba bom (cost=0.28..2.28 rows=1 width=13) (actual time=0.002..0.002 rows=1 loops=618)

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

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

33. 0.001 0.272 ↓ 0.0 0 1

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

  • Join Filter: (abt_2.abt_dt_abastecimento >= cai_1.cai_dt_movimento)
34. 0.000 0.271 ↓ 0.0 0 1

Nested Loop (cost=1.97..57.85 rows=1 width=90) (actual time=0.271..0.271 rows=0 loops=1)

35. 0.000 0.271 ↓ 0.0 0 1

Nested Loop (cost=1.69..56.00 rows=1 width=85) (actual time=0.271..0.271 rows=0 loops=1)

36. 0.001 0.271 ↓ 0.0 0 1

Nested Loop (cost=1.27..53.36 rows=1 width=57) (actual time=0.271..0.271 rows=0 loops=1)

37. 0.040 0.270 ↓ 0.0 0 1

Nested Loop (cost=0.99..51.39 rows=1 width=45) (actual time=0.270..0.270 rows=0 loops=1)

38. 0.038 0.038 ↓ 3.7 48 1

Index Scan using abastecimento_afericao_idx_aba_cd_caixa on abastecimento_afericao aba (cost=0.42..15.15 rows=13 width=8) (actual time=0.017..0.038 rows=48 loops=1)

  • Index Cond: (aba_cd_caixa = 1506645)
39. 0.192 0.192 ↓ 0.0 0 48

Index Scan using abastecimento_pkey on abastecimento abt_2 (cost=0.57..2.79 rows=1 width=45) (actual time=0.004..0.004 rows=0 loops=48)

  • 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
40. 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)
41. 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)
42. 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)
43. 0.000 0.000 ↓ 0.0 0

Index Scan using caixa_pkey on caixa cai_1 (cost=0.42..2.65 rows=1 width=12) (never executed)

  • Index Cond: (cai_cd_caixa = 1506645)
  • Filter: (cai_cd_unidade_negocio = 180)
44. 0.008 724.825 ↑ 11.3 3 1

Subquery Scan on *SELECT* 3 (cost=1,051.65..3,985,377.50 rows=34 width=321) (actual time=51.632..724.825 rows=3 loops=1)

45. 0.294 724.817 ↑ 11.3 3 1

Nested Loop Anti Join (cost=1,051.65..3,985,376.65 rows=34 width=153) (actual time=51.629..724.817 rows=3 loops=1)

  • Join Filter: (abt_3.abt_cd_bico = bic_2.bic_cd_bico)
  • Rows Removed by Join Filter: 3744
46. 0.079 718.437 ↑ 1.0 34 1

Nested Loop (cost=1,049.53..3,984,653.89 rows=34 width=93) (actual time=45.606..718.437 rows=34 loops=1)

47. 0.062 2.930 ↑ 1.0 34 1

Nested Loop (cost=0.98..7,154.70 rows=34 width=57) (actual time=0.274..2.930 rows=34 loops=1)

48. 0.063 2.732 ↑ 1.0 34 1

Nested Loop (cost=0.70..7,076.46 rows=34 width=52) (actual time=0.262..2.732 rows=34 loops=1)

49. 0.058 2.431 ↑ 1.0 34 1

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

50. 2.339 2.339 ↑ 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.229..2.339 rows=34 loops=1)

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

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

52. 0.238 0.238 ↑ 1.0 1 34

Index Scan using produto_pkey on produto pro_2 (cost=0.42..2.64 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=34)

  • Index Cond: (pro_cd_produto = bic_2.bic_cd_produto)
53. 0.136 0.136 ↑ 1.0 1 34

Index Scan using bomba_pkey on bomba bom_2 (cost=0.28..2.30 rows=1 width=13) (actual time=0.004..0.004 rows=1 loops=34)

  • Index Cond: (bom_cd_bomba = bic_2.bic_cd_bomba)
54. 0.102 715.428 ↑ 1.0 1 34

Limit (cost=1,048.55..116,985.25 rows=1 width=36) (actual time=21.040..21.042 rows=1 loops=34)

55. 2.108 715.326 ↑ 1.0 1 34

GroupAggregate (cost=1,048.55..116,985.25 rows=1 width=36) (actual time=21.039..21.039 rows=1 loops=34)

  • Group Key: 0
56. 43.894 713.218 ↑ 10.4 76 34

Nested Loop (cost=1,048.55..116,975.39 rows=788 width=36) (actual time=1.620..20.977 rows=76 loops=34)

57. 9.248 105.502 ↓ 3.0 2,369 34

Nested Loop (cost=1,048.54..115,391.51 rows=788 width=1,399) (actual time=1.183..3.103 rows=2,369 loops=34)

58. 0.442 41.038 ↑ 24.8 14 34

Nested Loop (cost=1,047.97..2,142.59 rows=347 width=8) (actual time=1.177..1.207 rows=14 loops=34)

59. 0.034 0.034 ↑ 1.0 1 34

CTE Scan on caixa_meta cm_2 (cost=0.00..0.02 rows=1 width=16) (actual time=0.000..0.001 rows=1 loops=34)

60. 2.006 40.562 ↑ 24.8 14 34

Bitmap Heap Scan on caixa cai_3 (cost=1,047.97..2,139.10 rows=347 width=20) (actual time=1.166..1.193 rows=14 loops=34)

  • Recheck Cond: ((cai_cd_unidade_negocio = 180) AND (cai_dt_movimento >= (cm_2.min_dt_movimento - 2)) AND (cai_dt_movimento <= (cm_2.max_dt_movimento + 2)))
  • Filter: (cai_ts_abertura < cm_2.min_ts_abertura)
  • Rows Removed by Filter: 18
  • Heap Blocks: exact=1088
61. 7.752 38.556 ↓ 0.0 0 34

BitmapAnd (cost=1,047.97..1,047.97 rows=1,040 width=0) (actual time=1.134..1.134 rows=0 loops=34)

62. 22.202 22.202 ↑ 1.0 9,127 34

Bitmap Index Scan on caixa_idx_unidade_negocio (cost=0.00..99.25 rows=9,363 width=0) (actual time=0.653..0.653 rows=9,127 loops=34)

  • Index Cond: (cai_cd_unidade_negocio = 180)
63. 8.602 8.602 ↑ 16.2 4,103 34

Bitmap Index Scan on caixa_idx_data_pdv_turno (cost=0.00..946.05 rows=66,402 width=0) (actual time=0.253..0.253 rows=4,103 loops=34)

  • Index Cond: ((cai_dt_movimento >= (cm_2.min_dt_movimento - 2)) AND (cai_dt_movimento <= (cm_2.max_dt_movimento + 2)))
64. 55.216 55.216 ↓ 21.1 169 476

Index Scan using venda_idx_caixa on venda ven_2 (cost=0.56..326.29 rows=8 width=1,407) (actual time=0.006..0.116 rows=169 loops=476)

  • 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: 2
65. 563.822 563.822 ↓ 0.0 0 80,546

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=80,546)

  • Filter: (((value ->> 'abt_cd_bico'::text))::integer = bic_2.bic_cd_bico)
  • Rows Removed by Filter: 1
66. 0.225 6.086 ↓ 55.5 111 34

Materialize (cost=2.12..721.75 rows=2 width=4) (actual time=0.002..0.179 rows=111 loops=34)

67. 0.127 5.861 ↓ 309.0 618 1

Nested Loop (cost=2.12..721.74 rows=2 width=4) (actual time=0.055..5.861 rows=618 loops=1)

68. 0.260 3.730 ↓ 334.0 668 1

Nested Loop (cost=1.56..716.39 rows=2 width=4) (actual time=0.044..3.730 rows=668 loops=1)

69. 0.000 1.685 ↓ 595.0 595 1

Nested Loop (cost=0.99..350.42 rows=1 width=4) (actual time=0.028..1.685 rows=595 loops=1)

70. 0.109 0.551 ↓ 595.0 595 1

Nested Loop (cost=0.56..347.77 rows=1 width=8) (actual time=0.016..0.551 rows=595 loops=1)

  • Join Filter: ((ven_1.ven_dt_fiscal >= (cm_1.min_dt_movimento - 2)) AND (ven_1.ven_dt_fiscal <= (cm_1.max_dt_movimento + 2)))
71. 0.001 0.001 ↑ 1.0 1 1

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

72. 0.441 0.441 ↓ 74.4 595 1

Index Scan using venda_idx_caixa on venda ven_1 (cost=0.56..347.59 rows=8 width=12) (actual time=0.012..0.441 rows=595 loops=1)

  • Index Cond: (ven_cd_caixa = 1506645)
  • Filter: ((ven_cd_unidade_negocio = 180) AND (ven_fl_cancelada = 'N'::bpchar))
  • Rows Removed by Filter: 3
73. 1.190 1.190 ↑ 1.0 1 595

Index Only Scan using caixa_pkey on caixa cai_2 (cost=0.42..2.64 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=595)

  • Index Cond: (cai_cd_caixa = 1506645)
  • Heap Fetches: 595
74. 1.785 1.785 ↑ 412.0 1 595

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

  • Index Cond: (vei_cd_venda = ven_1.ven_cd_venda)
75. 2.004 2.004 ↑ 1.0 1 668

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=668)

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

SubPlan (for GroupAggregate)

77. 0.036 134.667 ↑ 1.0 1 3

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

  • Hash Cond: (vei_6.vei_cd_venda = ven_6.ven_cd_venda)
78. 0.025 133.428 ↑ 2,711.3 3 3

Nested Loop (cost=1.14..49,188.42 rows=8,134 width=4) (actual time=44.366..44.476 rows=3 loops=3)

79. 2.451 133.059 ↑ 2,711.3 3 3

Nested Loop (cost=0.57..26,886.87 rows=8,134 width=4) (actual time=44.316..44.353 rows=3 loops=3)

  • Join Filter: (abtc_1.abt_dt_abastecimento >= cm_5.min_dt_movimento)
  • Rows Removed by Join Filter: 3810
80. 0.000 0.000 ↑ 1.0 1 3

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=3)

81. 130.608 130.608 ↑ 6.4 3,813 3

Index Scan using abastecimento_idx_cd_bico on abastecimento abtc_1 (cost=0.57..26,581.82 rows=24,402 width=8) (actual time=0.018..43.536 rows=3,813 loops=3)

  • Index Cond: (abt_cd_bico = abt.bico)
82. 0.344 0.344 ↑ 1.0 1 8

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

  • Index Cond: (vei_cd_venda_item = abtc_1.abt_cd_venda_item)
83. 0.210 1.203 ↓ 1.9 598 3

Hash (cost=346.04..346.04 rows=309 width=4) (actual time=0.401..0.401 rows=598 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
84. 0.993 0.993 ↓ 1.9 598 3

Index Scan using venda_idx_caixa on venda ven_6 (cost=0.56..346.04 rows=309 width=4) (actual time=0.012..0.331 rows=598 loops=3)

  • Index Cond: (ven_cd_caixa = abt.caixa)
85. 0.536 602.397 ↑ 1.0 1 67

Aggregate (cost=7,099.87..7,099.88 rows=1 width=32) (actual time=8.991..8.991 rows=1 loops=67)

86. 0.536 601.861 ↓ 10.0 10 67

Nested Loop (cost=6,962.08..7,099.87 rows=1 width=6) (actual time=7.620..8.983 rows=10 loops=67)

  • Join Filter: (cai_4.cai_ts_abertura > cm_3.max_ts_abertura)
  • Rows Removed by Join Filter: 1
87. 0.715 601.325 ↓ 11.0 11 67

Nested Loop (cost=6,962.08..7,099.83 rows=1 width=14) (actual time=7.494..8.975 rows=11 loops=67)

88. 1.063 599.114 ↓ 11.0 11 67

Nested Loop (cost=6,961.65..7,097.30 rows=1 width=14) (actual time=7.486..8.942 rows=11 loops=67)

89. 1.191 569.299 ↓ 27.0 27 67

Nested Loop (cost=6,961.09..7,094.57 rows=1 width=14) (actual time=7.353..8.497 rows=27 loops=67)

90. 45.359 510.540 ↓ 27.0 27 67

Bitmap Heap Scan on abastecimento b (cost=6,960.52..7,091.78 rows=1 width=14) (actual time=7.220..7.620 rows=27 loops=67)

  • Recheck Cond: ((abt_cd_bico = abt.bico) AND (abt_cd_unidade_negocio = 180) AND (abt_dt_fiscal >= (max(abt.maior_data))))
  • Filter: ((abt_vl_encerrante < (max(abt.encerrante_final_abt))) AND (abt_cd_substituicao = abt.substituicao))
  • Rows Removed by Filter: 82
  • Heap Blocks: exact=6520
91. 73.968 465.181 ↓ 0.0 0 67

BitmapAnd (cost=6,960.52..6,960.52 rows=117 width=0) (actual time=6.943..6.943 rows=0 loops=67)

92. 373.458 373.458 ↓ 2.2 52,688 67

Bitmap Index Scan on abastecimento_idx_cd_bico (cost=0.00..257.28 rows=24,402 width=0) (actual time=5.574..5.574 rows=52,688 loops=67)

  • Index Cond: (abt_cd_bico = abt.bico)
93. 17.755 17.755 ↑ 166.6 3,091 67

Bitmap Index Scan on abastecimento_abt_cd_unidade_negocio_abt_dt_fiscal_idx (cost=0.00..6,702.99 rows=514,922 width=0) (actual time=0.265..0.265 rows=3,091 loops=67)

  • Index Cond: ((abt_cd_unidade_negocio = 180) AND (abt_dt_fiscal >= (max(abt.maior_data))))
94. 57.568 57.568 ↑ 1.0 1 1,799

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

  • Index Cond: (vei_cd_venda_item = b.abt_cd_venda_item)
  • Filter: (vei_cd_produto = abt.produto)
95. 28.752 28.752 ↓ 0.0 0 1,797

Index Scan using venda_pkey on venda ven_3 (cost=0.56..2.74 rows=1 width=12) (actual time=0.016..0.016 rows=0 loops=1,797)

  • Index Cond: (ven_cd_venda = vei_3.vei_cd_venda)
  • Filter: ((ven_cd_caixa <> 1506645) AND (ven_cd_unidade_negocio = 180) AND (ven_fl_cancelada = 'N'::bpchar))
  • Rows Removed by Filter: 1
96. 1.496 1.496 ↑ 1.0 1 748

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

  • Index Cond: (cai_cd_caixa = ven_3.ven_cd_caixa)
  • Filter: (cai_cd_unidade_negocio = 180)
97. 0.000 0.000 ↑ 1.0 1 748

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

98. 0.268 42,329.260 ↑ 1.0 1 67

Aggregate (cost=94,595.99..94,596.00 rows=1 width=64) (actual time=631.779..631.780 rows=1 loops=67)

99. 2.094 42,328.992 ↓ 0.0 0 67

Nested Loop (cost=353.16..94,595.98 rows=1 width=19) (actual time=617.123..631.776 rows=0 loops=67)

  • Join Filter: (b_1.abt_dt_abastecimento < cm_4.min_dt_movimento)
  • Rows Removed by Join Filter: 19
100. 2.928 42,325.642 ↓ 19.0 19 67

Nested Loop Semi Join (cost=353.16..94,595.95 rows=1 width=23) (actual time=106.432..631.726 rows=19 loops=67)

  • Join Filter: (abtc.abt_dt_abastecimento >= cai_5.cai_dt_movimento)
  • Rows Removed by Join Filter: 2
101. 1.777 178.890 ↓ 19.0 19 67

Nested Loop (cost=2.12..375.39 rows=1 width=35) (actual time=0.293..2.670 rows=19 loops=67)

102. 2.122 169.577 ↓ 19.0 19 67

Nested Loop (cost=1.70..372.73 rows=1 width=35) (actual time=0.288..2.531 rows=19 loops=67)

103. 22.378 133.799 ↓ 126.0 126 67

Nested Loop (cost=1.13..369.97 rows=1 width=12) (actual time=0.043..1.997 rows=126 loops=67)

104. 31.691 31.691 ↓ 74.4 595 67

Index Scan using venda_idx_caixa on venda ven_4 (cost=0.56..347.59 rows=8 width=12) (actual time=0.012..0.473 rows=595 loops=67)

  • Index Cond: (ven_cd_caixa = 1506645)
  • Filter: ((ven_cd_unidade_negocio = 180) AND (ven_fl_cancelada = 'N'::bpchar))
  • Rows Removed by Filter: 3
105. 79.730 79.730 ↓ 0.0 0 39,865

Index Scan using idx_tst_01_rebuild_1 on venda_item vei_4 (cost=0.57..2.79 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=39,865)

  • Index Cond: ((vei_cd_venda = ven_4.ven_cd_venda) AND (vei_cd_produto = abt.produto))
106. 33.656 33.656 ↓ 0.0 0 8,414

Index Scan using abastecimento_abt_cd_venda_item_key on abastecimento b_1 (cost=0.57..2.76 rows=1 width=35) (actual time=0.004..0.004 rows=0 loops=8,414)

  • 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
107. 7.536 7.536 ↑ 1.0 1 1,256

Index Scan using caixa_pkey on caixa cai_5 (cost=0.42..2.65 rows=1 width=12) (actual time=0.005..0.006 rows=1 loops=1,256)

  • Index Cond: (cai_cd_caixa = 1506645)
  • Filter: (cai_cd_unidade_negocio = 180)
108. 690.397 42,143.824 ↑ 1.0 1 1,256

Hash Join (cost=351.04..94,220.55 rows=1 width=12) (actual time=33.553..33.554 rows=1 loops=1,256)

  • Hash Cond: (vei_5.vei_cd_venda = ven_5.ven_cd_venda)
109. 7,849.265 41,453.024 ↑ 3.8 6,405 1,256

Nested Loop (cost=1.14..93,806.59 rows=24,402 width=12) (actual time=0.015..33.004 rows=6,405 loops=1,256)

110. 9,408.696 9,408.696 ↑ 3.8 6,421 1,256

Index Scan using abastecimento_idx_cd_bico on abastecimento abtc (cost=0.57..26,581.82 rows=24,402 width=12) (actual time=0.009..7.491 rows=6,421 loops=1,256)

  • Index Cond: (abt_cd_bico = abt.bico)
111. 24,195.063 24,195.063 ↑ 1.0 1 8,065,021

Index Scan using venda_item_pkey on venda_item vei_5 (cost=0.57..2.75 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=8,065,021)

  • Index Cond: (vei_cd_venda_item = abtc.abt_cd_venda_item)
112. 0.072 0.403 ↓ 1.9 598 1

Hash (cost=346.04..346.04 rows=309 width=8) (actual time=0.402..0.403 rows=598 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
113. 0.331 0.331 ↓ 1.9 598 1

Index Scan using venda_idx_caixa on venda ven_5 (cost=0.56..346.04 rows=309 width=8) (actual time=0.007..0.331 rows=598 loops=1)

  • Index Cond: (ven_cd_caixa = 1506645)
114. 1.256 1.256 ↑ 1.0 1 1,256

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

Planning time : 16.165 ms
Execution time : 43,812.504 ms