explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UJ2E

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.031 79.369 ↑ 1.0 1 1

Limit (cost=8.60..46.66 rows=1 width=682) (actual time=79.340..79.369 rows=1 loops=1)

2. 0.153 79.338 ↑ 108,106.0 1 1

Nested Loop Left Join (cost=8.60..4,114,414.19 rows=108,106 width=682) (actual time=79.338..79.338 rows=1 loops=1)

3. 0.005 79.147 ↑ 108,106.0 1 1

Nested Loop Left Join (cost=7.45..3,185,513.38 rows=108,106 width=527) (actual time=79.147..79.147 rows=1 loops=1)

4. 0.007 79.025 ↑ 108,106.0 1 1

Nested Loop Left Join (cost=6.30..2,508,096.97 rows=108,106 width=491) (actual time=79.025..79.025 rows=1 loops=1)

5. 0.080 78.992 ↑ 108,106.0 1 1

Merge Left Join (cost=6.03..2,337,685.57 rows=108,106 width=476) (actual time=78.992..78.992 rows=1 loops=1)

  • Merge Cond: (tr.refo_id = ct.refo_id)
  • Join Filter: (((rht.rht_inicio)::text <= (tr.to_hora_transacao)::text) AND ((rht.rht_termino)::text >= (tr.to_hora_transacao)::text))
6. 0.002 22.722 ↑ 108,106.0 1 1

Nested Loop Left Join (cost=5.75..2,333,491.46 rows=108,106 width=468) (actual time=22.722..22.722 rows=1 loops=1)

7. 0.003 22.699 ↑ 108,106.0 1 1

Nested Loop Left Join (cost=4.62..1,845,979.15 rows=108,106 width=444) (actual time=22.699..22.699 rows=1 loops=1)

8. 0.003 22.688 ↑ 108,106.0 1 1

Nested Loop Left Join (cost=4.05..1,564,447.97 rows=108,106 width=443) (actual time=22.688..22.688 rows=1 loops=1)

9. 0.006 22.679 ↑ 108,106.0 1 1

Nested Loop Left Join (cost=3.78..1,417,983.96 rows=108,106 width=406) (actual time=22.679..22.679 rows=1 loops=1)

  • Join Filter: (co.co_id = tr.co_id)
  • Rows Removed by Join Filter: 18
10. 0.002 22.653 ↑ 108,106.0 1 1

Nested Loop Left Join (cost=3.78..1,302,804.24 rows=108,106 width=397) (actual time=22.653..22.653 rows=1 loops=1)

11. 0.049 22.645 ↑ 108,106.0 1 1

Merge Left Join (cost=3.50..1,136,682.00 rows=108,106 width=381) (actual time=22.644..22.645 rows=1 loops=1)

  • Merge Cond: (tr.refo_id = cl.refo_id)
  • Join Filter: ((rlt.co_id = tr.co_id) AND (rlt.rlt_terminal = tr.to_num_terminal))
12. 0.002 1.455 ↑ 108,106.0 1 1

Nested Loop Left Join (cost=3.35..1,134,005.18 rows=108,106 width=362) (actual time=1.455..1.455 rows=1 loops=1)

13. 0.003 1.450 ↑ 108,106.0 1 1

Nested Loop Left Join (cost=3.21..1,057,237.18 rows=108,106 width=355) (actual time=1.449..1.450 rows=1 loops=1)

14. 0.003 1.441 ↑ 108,106.0 1 1

Nested Loop Left Join (cost=2.92..885,871.46 rows=108,106 width=318) (actual time=1.441..1.441 rows=1 loops=1)

15. 0.531 1.432 ↑ 108,106.0 1 1

Merge Left Join (cost=2.63..713,603.57 rows=108,106 width=301) (actual time=1.432..1.432 rows=1 loops=1)

  • Merge Cond: (tr.refo_id = refo.refo_id)
16. 0.003 0.053 ↑ 108,106.0 1 1

Nested Loop Left Join (cost=1.73..705,791.67 rows=108,106 width=297) (actual time=0.053..0.053 rows=1 loops=1)

17. 0.002 0.046 ↑ 108,106.0 1 1

Nested Loop Left Join (cost=1.44..535,938.31 rows=108,106 width=259) (actual time=0.046..0.046 rows=1 loops=1)

18. 0.003 0.037 ↑ 108,106.0 1 1

Nested Loop Left Join (cost=1.01..338,423.21 rows=108,106 width=238) (actual time=0.037..0.037 rows=1 loops=1)

19. 0.026 0.026 ↑ 108,106.0 1 1

Index Scan using idx_to_refo_acelerado_venda_transacao on transacoes_operadora tr (cost=0.57..107,913.94 rows=108,106 width=222) (actual time=0.025..0.026 rows=1 loops=1)

  • Index Cond: ((refo_id = ANY ('{3287,3285,2605,3286,3283,3281}'::integer[])) AND (to_idt_acelerado = false) AND (to_data_venda >= '2019-07-01'::date) AND (to_data_venda <= '2019-09-10'::date) AND (to_tipo_transacao = 1))
  • Filter: (NOT to_idt_acelerado)
20. 0.008 0.008 ↑ 1.0 1 1

Index Scan using importacao_dados_at_pkey on importacao_dados_at ida (cost=0.43..2.13 rows=1 width=24) (actual time=0.008..0.008 rows=1 loops=1)

  • Index Cond: (ida_id = tr.ida_id)
21. 0.007 0.007 ↓ 0.0 0 1

Index Scan using to_rede_pkey on to_rede rede (cost=0.43..1.83 rows=1 width=29) (actual time=0.006..0.007 rows=0 loops=1)

  • Index Cond: (to_id = tr.to_id)
22. 0.004 0.004 ↓ 0.0 0 1

Index Scan using idx_to_moedas_id_idadata on to_moedas tm (cost=0.29..1.57 rows=1 width=46) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (to_id = tr.to_id)
23. 0.848 0.848 ↑ 4.5 2,392 1

Index Scan using rel_empresa_filial_operadora_pkey on rel_empresa_filial_operadora refo (cost=0.29..6,574.13 rows=10,682 width=8) (actual time=0.005..0.848 rows=2,392 loops=1)

24. 0.006 0.006 ↑ 1.0 1 1

Index Scan using rel_empresa_filial_operadora_pr_pkey on rel_empresa_filial_operadora_pr refopr (cost=0.29..1.59 rows=1 width=25) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (refopr_id = tr.refopr_id)
25. 0.006 0.006 ↑ 1.0 1 1

Index Scan using cad_empresa_filial_pkey on cad_empresa_filial cef (cost=0.29..1.59 rows=1 width=41) (actual time=0.005..0.006 rows=1 loops=1)

  • Index Cond: (cef_id = refo.cef_id)
26. 0.003 0.003 ↓ 0.0 0 1

Index Scan using cef_categoria_pkey on cef_categoria cct (cost=0.14..0.71 rows=1 width=15) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (cef.cef_id = cef_id)
27. 0.092 21.141 ↑ 2.5 181 1

Materialize (cost=0.15..2,196.36 rows=447 width=35) (actual time=0.027..21.141 rows=181 loops=1)

28. 10.989 21.049 ↑ 2.5 181 1

Nested Loop (cost=0.15..2,195.24 rows=447 width=35) (actual time=0.025..21.049 rows=181 loops=1)

  • Join Filter: (cl.cl_id = rlt.cl_id)
  • Rows Removed by Join Filter: 51221
29. 0.055 0.055 ↑ 2.7 115 1

Index Scan using idx_cad_loja_fk_refo_id on cad_loja cl (cost=0.15..64.87 rows=316 width=31) (actual time=0.009..0.055 rows=115 loops=1)

30. 9.870 10.005 ↑ 1.0 447 115

Materialize (cost=0.00..12.70 rows=447 width=20) (actual time=0.000..0.087 rows=447 loops=115)

31. 0.135 0.135 ↑ 1.0 447 1

Seq Scan on rel_loja_terminal rlt (cost=0.00..10.47 rows=447 width=20) (actual time=0.006..0.135 rows=447 loops=1)

32. 0.006 0.006 ↑ 1.0 1 1

Index Scan using cad_empresa_grupo_pkey on cad_empresa_grupo ceg (cost=0.28..1.54 rows=1 width=20) (actual time=0.005..0.006 rows=1 loops=1)

  • Index Cond: (ceg_id = cef.ceg_id)
33. 0.011 0.020 ↑ 3.8 19 1

Materialize (cost=0.00..2.08 rows=72 width=13) (actual time=0.006..0.020 rows=19 loops=1)

34. 0.009 0.009 ↑ 3.8 19 1

Seq Scan on cad_operadora co (cost=0.00..1.72 rows=72 width=13) (actual time=0.004..0.009 rows=19 loops=1)

35. 0.006 0.006 ↑ 1.0 1 1

Index Scan using empresa_operadora_bandeira_tipo_produto_pkey on empresa_operadora_bandeira_tipo_produto eobtp (cost=0.27..1.36 rows=1 width=41) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (eobtp_id = tr.eobtp_id)
36. 0.008 0.008 ↓ 0.0 0 1

Index Scan using to_cartao_estrangeiro_pkey on to_cartao_estrangeiro tce (cost=0.57..2.60 rows=1 width=9) (actual time=0.007..0.008 rows=0 loops=1)

  • Index Cond: (to_id = tr.to_id)
37. 0.002 0.021 ↑ 1.0 1 1

Nested Loop (cost=1.14..4.50 rows=1 width=48) (actual time=0.020..0.021 rows=1 loops=1)

38. 0.010 0.010 ↑ 1.0 1 1

Index Scan using conciliacao_cliente_at_idx_cca_to on conciliacao_cliente_at cca (cost=0.57..2.61 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: (to_id = tr.to_id)
  • Filter: (ida_id_to = tr.ida_id)
39. 0.009 0.009 ↑ 1.0 1 1

Index Scan using importacao_dados_cliente_item_at_pkey on importacao_dados_cliente_item_at idcia (cost=0.57..1.89 rows=1 width=24) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: (idcia_id = cca.idcia_id)
40. 0.168 56.190 ↑ 1.8 276 1

Materialize (cost=0.27..3,677.09 rows=490 width=28) (actual time=0.058..56.190 rows=276 loops=1)

41. 29.311 56.022 ↑ 1.8 276 1

Nested Loop (cost=0.27..3,675.87 rows=490 width=28) (actual time=0.056..56.022 rows=276 loops=1)

  • Join Filter: (ct.ct_id = rht.ct_id)
  • Rows Removed by Join Filter: 133772
42. 0.133 0.133 ↑ 1.8 274 1

Index Scan using idx_cad_turno_fk_refo_id on cad_turno ct (cost=0.27..61.24 rows=490 width=20) (actual time=0.008..0.133 rows=274 loops=1)

43. 26.430 26.578 ↑ 1.0 489 274

Materialize (cost=0.00..14.35 rows=490 width=24) (actual time=0.000..0.097 rows=489 loops=274)

44. 0.148 0.148 ↑ 1.0 490 1

Seq Scan on rel_horario_turno rht (cost=0.00..11.90 rows=490 width=24) (actual time=0.005..0.148 rows=490 loops=1)

45. 0.026 0.026 ↑ 1.0 1 1

Index Scan using cad_ajuste_conta_cac_banco_orig_cac_agencia_orig_cac_conta__key on cad_ajuste_conta cac (cost=0.28..1.58 rows=1 width=30) (actual time=0.026..0.026 rows=1 loops=1)

  • Index Cond: ((tr.to_num_banco = cac_banco_orig) AND (tr.to_num_agencia = cac_agencia_orig) AND (tr.to_num_cc = cac_conta_orig))
46. 0.007 0.117 ↑ 1.0 1 1

GroupAggregate (cost=1.15..6.25 rows=1 width=60) (actual time=0.116..0.117 rows=1 loops=1)

  • Group Key: po.refo_id, po.po_data_venda, po.co_id, cpa.to_id, cpa.cpa_data
47. 0.022 0.110 ↑ 1.0 1 1

Nested Loop (cost=1.15..6.22 rows=1 width=35) (actual time=0.108..0.110 rows=1 loops=1)

  • Join Filter: (po.po_id = cpa.po_id)
  • Rows Removed by Join Filter: 79
48. 0.015 0.015 ↑ 1.0 1 1

Index Scan using conciliacao_pagamento_at_idx_cpa_data_to_status on conciliacao_pagamento_at cpa (cost=0.57..2.80 rows=1 width=20) (actual time=0.014..0.015 rows=1 loops=1)

  • Index Cond: ((cpa_data = tr.to_data_venda) AND (to_id = tr.to_id) AND (cpa_status = 10))
49. 0.073 0.073 ↓ 40.0 80 1

Index Scan using idx_pocancelado_venda_refo_co on pagamentos_operadora po (cost=0.57..3.40 rows=2 width=31) (actual time=0.019..0.073 rows=80 loops=1)

  • Index Cond: ((po_data_venda = tr.to_data_venda) AND (refo_id = tr.refo_id) AND (co_id = tr.co_id))
  • Filter: ((NOT po_idt_acelerado) AND (po_tipo_transacao = 1) AND (po_data_pagto <= CURRENT_DATE))
50. 0.002 0.023 ↓ 0.0 0 1

GroupAggregate (cost=1.15..5.63 rows=1 width=28) (actual time=0.023..0.023 rows=0 loops=1)

  • Group Key: po_1.refo_id, po_1.po_data_venda, po_1.co_id, cpa_1.to_id, cpa_1.cpa_data
51. 0.004 0.021 ↓ 0.0 0 1

Nested Loop (cost=1.15..5.61 rows=1 width=28) (actual time=0.021..0.021 rows=0 loops=1)

  • Join Filter: (po_1.po_id = cpa_1.po_id)
  • Rows Removed by Join Filter: 1
52. 0.011 0.011 ↑ 1.0 1 1

Index Scan using idx_po_refo_co_venda_transacao_parcela on pagamentos_operadora po_1 (cost=0.57..2.80 rows=1 width=24) (actual time=0.010..0.011 rows=1 loops=1)

  • Index Cond: ((refo_id = tr.refo_id) AND (co_id = tr.co_id) AND (po_data_venda = tr.to_data_venda) AND (po_tipo_transacao = 3))
53. 0.006 0.006 ↑ 1.0 1 1

Index Scan using conciliacao_pagamento_at_idx_cpa_data_to_status on conciliacao_pagamento_at cpa_1 (cost=0.57..2.80 rows=1 width=20) (actual time=0.005..0.006 rows=1 loops=1)

  • Index Cond: ((cpa_data = tr.to_data_venda) AND (to_id = tr.to_id) AND (cpa_status = 10))
54.          

SubPlan (forNested Loop Left Join)

55. 0.015 0.015 ↓ 0.0 0 1

Index Scan using idx_tocancelado_venda_refo_co on transacoes_operadora tocanc (cost=0.42..2.65 rows=1 width=0) (actual time=0.015..0.015 rows=0 loops=1)

  • Index Cond: ((to_data_venda = tr.to_data_venda) AND (refo_id = tr.refo_id) AND (co_id = tr.co_id))
  • Filter: (to_codigo = tr.to_codigo)
  • Rows Removed by Filter: 1
Planning time : 50.907 ms
Execution time : 79.785 ms