explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FYmn

Settings
# exclusive inclusive rows x rows loops node
1. 4,691.495 66,637.607 ↓ 3.0 323,475 1

Nested Loop Left Join (cost=4,763.07..2,388,493.16 rows=108,106 width=682) (actual time=9,497.677..66,637.607 rows=323,475 loops=1)

  • Functions: 482
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 91.116 ms, Inlining 77.608 ms, Optimization 10157.015 ms, Emission 6521.052 ms, Total 16846.791 ms
2. 482.072 60,005.262 ↓ 3.0 323,475 1

Nested Loop Left Join (cost=4,761.92..1,459,592.35 rows=108,106 width=527) (actual time=9,497.463..60,005.262 rows=323,475 loops=1)

3. 0.000 9,708.040 ↓ 3.0 323,475 1

Gather (cost=4,760.77..782,175.94 rows=108,106 width=491) (actual time=9,497.394..9,708.040 rows=323,475 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
4. 239.145 13,923.406 ↓ 2.5 161,738 2

Hash Left Join (cost=3,760.77..770,365.34 rows=63,592 width=491) (actual time=9,464.833..13,923.406 rows=161,738 loops=2)

  • Hash Cond: ((tr.to_num_banco = cac.cac_banco_orig) AND (tr.to_num_agencia = cac.cac_agencia_orig) AND (tr.to_num_cc = cac.cac_conta_orig))
5. 119.132 13,682.147 ↓ 2.5 161,738 2

Hash Left Join (cost=3,687.61..769,791.39 rows=63,592 width=476) (actual time=9,462.702..13,682.147 rows=161,738 loops=2)

  • Hash 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. 1,337.000 13,562.078 ↓ 2.5 161,738 2

Nested Loop Left Join (cost=3,651.26..769,089.22 rows=63,592 width=468) (actual time=9,461.756..13,562.078 rows=161,738 loops=2)

7. 507.399 12,225.071 ↓ 2.5 161,736 2

Nested Loop Left Join (cost=3,650.13..482,316.13 rows=63,592 width=444) (actual time=9,461.725..12,225.071 rows=161,736 loops=2)

8. 121.312 11,717.670 ↓ 2.5 161,736 2

Hash Left Join (cost=3,649.56..316,708.95 rows=63,592 width=443) (actual time=9,461.707..11,717.670 rows=161,736 loops=2)

  • Hash Cond: (tr.eobtp_id = eobtp.eobtp_id)
9. 120.783 11,596.127 ↓ 2.5 161,736 2

Hash Left Join (cost=3,636.14..316,526.13 rows=63,592 width=406) (actual time=9,461.465..11,596.127 rows=161,736 loops=2)

  • Hash Cond: (tr.co_id = co.co_id)
10. 124.728 11,475.290 ↓ 2.5 161,736 2

Hash Left Join (cost=3,633.52..316,346.75 rows=63,592 width=397) (actual time=9,461.402..11,475.290 rows=161,736 loops=2)

  • Hash Cond: (cef.ceg_id = ceg.ceg_id)
11. 199.269 11,348.201 ↓ 2.5 161,736 2

Hash Left Join (cost=3,495.19..316,041.28 rows=63,592 width=381) (actual time=9,459.025..11,348.201 rows=161,736 loops=2)

  • Hash Cond: ((tr.co_id = rlt.co_id) AND (tr.refo_id = cl.refo_id) AND (tr.to_num_terminal = rlt.rlt_terminal))
12. 114.213 11,147.980 ↓ 2.5 161,736 2

Hash Left Join (cost=3,463.61..314,340.38 rows=63,592 width=362) (actual time=9,458.037..11,147.980 rows=161,736 loops=2)

  • Hash Cond: (cef.cef_id = cct.cef_id)
13. 122.372 11,033.688 ↓ 2.5 161,736 2

Hash Left Join (cost=3,460.18..314,169.95 rows=63,592 width=355) (actual time=9,457.947..11,033.688 rows=161,736 loops=2)

  • Hash Cond: (refo.cef_id = cef.cef_id)
14. 130.672 10,903.922 ↓ 2.5 161,736 2

Parallel Hash Left Join (cost=2,814.51..313,357.29 rows=63,592 width=318) (actual time=9,450.527..10,903.922 rows=161,736 loops=2)

  • Hash Cond: (tr.refopr_id = refopr.refopr_id)
15. 113.439 1,341.325 ↓ 2.5 161,736 2

Hash Left Join (cost=1,020.89..311,396.73 rows=63,592 width=301) (actual time=18.340..1,341.325 rows=161,736 loops=2)

  • Hash Cond: (tr.refo_id = refo.refo_id)
16. 136.499 1,221.846 ↓ 2.5 161,736 2

Hash Left Join (cost=552.55..310,761.39 rows=63,592 width=297) (actual time=12.253..1,221.846 rows=161,736 loops=2)

  • Hash Cond: (tr.to_id = tm.to_id)
17. 429.481 1,073.199 ↓ 2.5 161,736 2

Nested Loop Left Join (cost=1.57..310,043.48 rows=63,592 width=259) (actual time=0.076..1,073.199 rows=161,736 loops=2)

18. 512.285 643.716 ↓ 2.5 161,736 2

Nested Loop Left Join (cost=1.14..193,857.70 rows=63,592 width=238) (actual time=0.064..643.716 rows=161,736 loops=2)

19. 131.429 131.429 ↓ 2.5 161,736 2

Parallel Index Scan using idx_to_acelerado_transacao_refo_venda_co_eobtp_terminal on transacoes_operadora tr (cost=0.70..58,263.51 rows=63,592 width=222) (actual time=0.042..131.429 rows=161,736 loops=2)

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

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

  • Index Cond: (ida_id = tr.ida_id)
21. 0.002 0.002 ↓ 0.0 0 323,473

Index Scan using to_rede_pkey on to_rede rede (cost=0.43..1.83 rows=1 width=29) (actual time=0.002..0.002 rows=0 loops=323,473)

  • Index Cond: (to_id = tr.to_id)
22. 6.847 12.148 ↓ 1.0 15,837 2

Hash (cost=353.77..353.77 rows=15,777 width=46) (actual time=12.148..12.148 rows=15,837 loops=2)

  • Buckets: 16384 Batches: 1 Memory Usage: 1377kB
23. 5.301 5.301 ↓ 1.0 15,837 2

Seq Scan on to_moedas tm (cost=0.00..353.77 rows=15,777 width=46) (actual time=0.015..5.301 rows=15,837 loops=2)

24. 3.025 6.040 ↑ 1.1 9,408 2

Hash (cost=334.82..334.82 rows=10,682 width=8) (actual time=6.040..6.040 rows=9,408 loops=2)

  • Buckets: 16384 Batches: 1 Memory Usage: 496kB
25. 3.015 3.015 ↑ 1.1 9,408 2

Seq Scan on rel_empresa_filial_operadora refo (cost=0.00..334.82 rows=10,682 width=8) (actual time=0.009..3.015 rows=9,408 loops=2)

26. 1,042.641 9,431.925 ↑ 1.2 26,960 2

Parallel Hash (cost=1,397.72..1,397.72 rows=31,672 width=25) (actual time=9,431.925..9,431.925 rows=26,960 loops=2)

  • Buckets: 65536 Batches: 1 Memory Usage: 3744kB
27. 8,389.284 8,389.284 ↑ 1.2 26,960 2

Parallel Seq Scan on rel_empresa_filial_operadora_pr refopr (cost=0.00..1,397.72 rows=31,672 width=25) (actual time=8,381.225..8,389.284 rows=26,960 loops=2)

28. 3.810 7.394 ↓ 1.0 9,493 2

Hash (cost=530.85..530.85 rows=9,185 width=41) (actual time=7.393..7.394 rows=9,493 loops=2)

  • Buckets: 16384 Batches: 1 Memory Usage: 837kB
29. 3.584 3.584 ↓ 1.0 9,493 2

Seq Scan on cad_empresa_filial cef (cost=0.00..530.85 rows=9,185 width=41) (actual time=0.008..3.584 rows=9,493 loops=2)

30. 0.042 0.079 ↑ 1.0 108 2

Hash (cost=2.08..2.08 rows=108 width=15) (actual time=0.078..0.079 rows=108 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
31. 0.037 0.037 ↑ 1.0 108 2

Seq Scan on cef_categoria cct (cost=0.00..2.08 rows=108 width=15) (actual time=0.008..0.037 rows=108 loops=2)

32. 0.343 0.952 ↑ 1.0 447 2

Hash (cost=23.76..23.76 rows=447 width=35) (actual time=0.952..0.952 rows=447 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 38kB
33. 0.263 0.609 ↑ 1.0 447 2

Hash Join (cost=12.11..23.76 rows=447 width=35) (actual time=0.232..0.609 rows=447 loops=2)

  • Hash Cond: (rlt.cl_id = cl.cl_id)
34. 0.130 0.130 ↑ 1.0 447 2

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

35. 0.118 0.216 ↑ 1.0 316 2

Hash (cost=8.16..8.16 rows=316 width=31) (actual time=0.215..0.216 rows=316 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
36. 0.098 0.098 ↑ 1.0 316 2

Seq Scan on cad_loja cl (cost=0.00..8.16 rows=316 width=31) (actual time=0.007..0.098 rows=316 loops=2)

37. 1.238 2.361 ↓ 1.0 3,474 2

Hash (cost=95.37..95.37 rows=3,437 width=20) (actual time=2.361..2.361 rows=3,474 loops=2)

  • Buckets: 4096 Batches: 1 Memory Usage: 217kB
38. 1.123 1.123 ↓ 1.0 3,474 2

Seq Scan on cad_empresa_grupo ceg (cost=0.00..95.37 rows=3,437 width=20) (actual time=0.007..1.123 rows=3,474 loops=2)

39. 0.028 0.054 ↑ 1.0 72 2

Hash (cost=1.72..1.72 rows=72 width=13) (actual time=0.053..0.054 rows=72 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
40. 0.026 0.026 ↑ 1.0 72 2

Seq Scan on cad_operadora co (cost=0.00..1.72 rows=72 width=13) (actual time=0.007..0.026 rows=72 loops=2)

41. 0.129 0.231 ↓ 1.1 301 2

Hash (cost=9.85..9.85 rows=285 width=41) (actual time=0.231..0.231 rows=301 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
42. 0.102 0.102 ↓ 1.1 301 2

Seq Scan on empresa_operadora_bandeira_tipo_produto eobtp (cost=0.00..9.85 rows=285 width=41) (actual time=0.005..0.102 rows=301 loops=2)

43. 0.002 0.002 ↓ 0.0 0 323,473

Index Scan using to_cartao_estrangeiro_pkey on to_cartao_estrangeiro tce (cost=0.57..2.60 rows=1 width=9) (actual time=0.002..0.002 rows=0 loops=323,473)

  • Index Cond: (to_id = tr.to_id)
44. 0.002 0.007 ↑ 1.0 1 323,473

Nested Loop (cost=1.14..4.50 rows=1 width=48) (actual time=0.006..0.007 rows=1 loops=323,473)

45. 0.003 0.003 ↑ 1.0 1 323,473

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.002..0.003 rows=1 loops=323,473)

  • Index Cond: (to_id = tr.to_id)
  • Filter: (ida_id_to = tr.ida_id)
46. 0.002 0.002 ↑ 1.0 1 322,276

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.002..0.002 rows=1 loops=322,276)

  • Index Cond: (idcia_id = cca.idcia_id)
47. 0.191 0.937 ↑ 1.0 490 2

Hash (cost=30.22..30.22 rows=490 width=28) (actual time=0.937..0.937 rows=490 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
48. 0.285 0.746 ↑ 1.0 490 2

Hash Join (cost=17.02..30.22 rows=490 width=28) (actual time=0.340..0.746 rows=490 loops=2)

  • Hash Cond: (rht.ct_id = ct.ct_id)
49. 0.137 0.137 ↑ 1.0 490 2

Seq Scan on rel_horario_turno rht (cost=0.00..11.90 rows=490 width=24) (actual time=0.006..0.137 rows=490 loops=2)

50. 0.176 0.324 ↑ 1.0 490 2

Hash (cost=10.90..10.90 rows=490 width=20) (actual time=0.323..0.324 rows=490 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
51. 0.148 0.148 ↑ 1.0 490 2

Seq Scan on cad_turno ct (cost=0.00..10.90 rows=490 width=20) (actual time=0.007..0.148 rows=490 loops=2)

52. 1.560 2.114 ↑ 1.0 1,681 2

Hash (cost=43.15..43.15 rows=1,715 width=30) (actual time=2.114..2.114 rows=1,681 loops=2)

  • Buckets: 2048 Batches: 1 Memory Usage: 124kB
53. 0.554 0.554 ↑ 1.0 1,681 2

Seq Scan on cad_ajuste_conta cac (cost=0.00..43.15 rows=1,715 width=30) (actual time=0.008..0.554 rows=1,681 loops=2)

54. 646.950 49,815.150 ↑ 1.0 1 323,475

GroupAggregate (cost=1.15..6.25 rows=1 width=60) (actual time=0.154..0.154 rows=1 loops=323,475)

  • Group Key: po.refo_id, po.po_data_venda, po.co_id, cpa.to_id, cpa.cpa_data
55. 13,238.937 49,168.200 ↑ 1.0 1 323,475

Nested Loop (cost=1.15..6.22 rows=1 width=35) (actual time=0.152..0.152 rows=1 loops=323,475)

  • Join Filter: (po.po_id = cpa.po_id)
  • Rows Removed by Join Filter: 193
56. 970.425 970.425 ↑ 1.0 1 323,475

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.003..0.003 rows=1 loops=323,475)

  • Index Cond: ((cpa_data = tr.to_data_venda) AND (to_id = tr.to_id) AND (cpa_status = 10))
57. 34,958.838 34,958.838 ↓ 127.5 255 246,189

Index Scan using idx_pocancelado_venda_refo_co on pagamentos_operadora po (cost=0.57..3.40 rows=2 width=31) (actual time=0.007..0.142 rows=255 loops=246,189)

  • 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))
  • Rows Removed by Filter: 4
58. 0.000 1,293.900 ↓ 0.0 0 323,475

GroupAggregate (cost=1.15..5.63 rows=1 width=28) (actual time=0.004..0.004 rows=0 loops=323,475)

  • Group Key: po_1.refo_id, po_1.po_data_venda, po_1.co_id, cpa_1.to_id, cpa_1.cpa_data
59. 302.241 1,293.900 ↓ 0.0 0 323,475

Nested Loop (cost=1.15..5.61 rows=1 width=28) (actual time=0.004..0.004 rows=0 loops=323,475)

  • Join Filter: (po_1.po_id = cpa_1.po_id)
  • Rows Removed by Join Filter: 0
60. 970.425 970.425 ↓ 0.0 0 323,475

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.003..0.003 rows=0 loops=323,475)

  • 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))
61. 21.234 21.234 ↑ 1.0 1 7,078

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.002..0.003 rows=1 loops=7,078)

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

SubPlan (forNested Loop Left Join)

63. 646.950 646.950 ↓ 0.0 0 323,475

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

  • 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: 0
Planning time : 28.405 ms
Execution time : 66,780.213 ms