explain.depesz.com

PostgreSQL's explain analyze made readable

Result: StaL

Settings
# exclusive inclusive rows x rows loops node
1. 19.299 3,010.091 ↑ 33.8 3,802 1

Unique (cost=1,263,607.48..1,269,070.81 rows=128,549 width=552) (actual time=2,987.040..3,010.091 rows=3,802 loops=1)

2. 137.981 2,990.792 ↑ 6.5 19,789 1

Sort (cost=1,263,607.48..1,263,928.85 rows=128,549 width=552) (actual time=2,987.036..2,990.792 rows=19,789 loops=1)

  • Sort Key: bt.num_a, bt.competencia, bb.id_contrato, r.id_contrato, ((SubPlan 1)), ((SubPlan 2)), b.num_contrato, a.num_contrato, aa.id, aa.id_categoria, aa.status, aa.id_lote_linha, bb.id, bb.dat_ativacao, bb.dat_desativacao, bb.imei
  • Sort Method: quicksort Memory: 6025kB
3. 288.803 2,852.811 ↑ 6.5 19,789 1

Hash Join (cost=184,612.17..1,221,062.33 rows=128,549 width=552) (actual time=490.401..2,852.811 rows=19,789 loops=1)

  • Hash Cond: ((bt.num_a)::text = (aa.num_linha_telefone)::text)
  • Join Filter: (r.id_contrato <> bb.id_contrato)
  • Rows Removed by Join Filter: 276331
4. 491.547 1,739.750 ↓ 3.4 630,986 1

Hash Join (cost=14,339.16..71,139.04 rows=188,325 width=39) (actual time=199.666..1,739.750 rows=630,986 loops=1)

  • Hash Cond: (bt.id_arquivo_entrada = ag.id_arquivo_entrada)
5. 178.369 1,049.956 ↑ 1.0 1,443,078 1

Append (cost=0.00..49,504.22 rows=1,443,309 width=23) (actual time=0.032..1,049.956 rows=1,443,078 loops=1)

6. 340.609 340.609 ↑ 1.0 556,046 1

Seq Scan on tb_totalizador_cdr_20190708 bt (cost=0.00..19,115.58 rows=556,046 width=23) (actual time=0.031..340.609 rows=556,046 loops=1)

  • Filter: (competencia >= 201906)
7. 280.288 280.288 ↑ 1.0 519,415 1

Seq Scan on tb_totalizador_cdr_20190910 bt_1 (cost=0.00..17,787.69 rows=519,415 width=23) (actual time=0.013..280.288 rows=519,415 loops=1)

  • Filter: (competencia >= 201906)
8. 250.663 250.663 ↑ 1.0 367,617 1

Seq Scan on tb_totalizador_cdr_20191112 bt_2 (cost=0.00..12,522.21 rows=367,617 width=23) (actual time=0.015..250.663 rows=367,617 loops=1)

  • Filter: (competencia >= 201906)
9. 0.010 0.010 ↓ 0.0 0 1

Seq Scan on tb_totalizador_cdr_20200102 bt_3 (cost=0.00..11.25 rows=33 width=62) (actual time=0.010..0.010 rows=0 loops=1)

  • Filter: (competencia >= 201906)
10. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on tb_totalizador_cdr_20200304 bt_4 (cost=0.00..11.25 rows=33 width=62) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (competencia >= 201906)
11. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on tb_totalizador_cdr_20200506 bt_5 (cost=0.00..11.25 rows=33 width=62) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (competencia >= 201906)
12. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on tb_totalizador_cdr_20200708 bt_6 (cost=0.00..11.25 rows=33 width=62) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (competencia >= 201906)
13. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on tb_totalizador_cdr_20200910 bt_7 (cost=0.00..11.25 rows=33 width=62) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (competencia >= 201906)
14. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on tb_totalizador_cdr_20201112 bt_8 (cost=0.00..11.25 rows=33 width=62) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (competencia >= 201906)
15. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on tb_totalizador_cdr_20210102 bt_9 (cost=0.00..11.25 rows=33 width=62) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (competencia >= 201906)
16. 84.104 198.247 ↓ 1.4 154,026 1

Hash (cost=12,966.63..12,966.63 rows=109,803 width=28) (actual time=198.247..198.247 rows=154,026 loops=1)

  • Buckets: 262144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 9711kB
17. 43.145 114.143 ↓ 1.4 154,026 1

Nested Loop (cost=7.41..12,966.63 rows=109,803 width=28) (actual time=0.254..114.143 rows=154,026 loops=1)

18. 0.609 1.002 ↓ 1.0 614 1

Hash Join (cost=6.98..33.50 rows=611 width=32) (actual time=0.207..1.002 rows=614 loops=1)

  • Hash Cond: (ga.id_remessa = r.id)
19. 0.221 0.221 ↓ 1.0 614 1

Seq Scan on tb_grupo_arquivo ga (cost=0.00..18.11 rows=611 width=12) (actual time=0.011..0.221 rows=614 loops=1)

20. 0.028 0.172 ↑ 1.0 38 1

Hash (cost=6.51..6.51 rows=38 width=28) (actual time=0.172..0.172 rows=38 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
21. 0.071 0.144 ↑ 1.0 38 1

Hash Join (cost=1.85..6.51 rows=38 width=28) (actual time=0.077..0.144 rows=38 loops=1)

  • Hash Cond: (a.id = r.id_contrato)
22. 0.027 0.027 ↑ 1.0 85 1

Seq Scan on tb_contrato a (cost=0.00..3.85 rows=85 width=20) (actual time=0.007..0.027 rows=85 loops=1)

23. 0.023 0.046 ↑ 1.0 38 1

Hash (cost=1.38..1.38 rows=38 width=12) (actual time=0.046..0.046 rows=38 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
24. 0.023 0.023 ↑ 1.0 38 1

Seq Scan on tb_remessa r (cost=0.00..1.38 rows=38 width=12) (actual time=0.007..0.023 rows=38 loops=1)

25. 69.996 69.996 ↓ 6.4 251 614

Index Scan using arqgp_idgp_idx on ta_arquivo_grupo ag (cost=0.42..20.78 rows=39 width=12) (actual time=0.011..0.114 rows=251 loops=614)

  • Index Cond: ((id_grupo_arquivo = ga.id) AND (versao = ga.versao))
26. 19.602 289.955 ↓ 1.4 19,285 1

Hash (cost=170,103.99..170,103.99 rows=13,521 width=77) (actual time=289.955..289.955 rows=19,285 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 2377kB
27. 13.891 270.353 ↓ 1.4 19,285 1

Hash Join (cost=5.20..170,103.99 rows=13,521 width=77) (actual time=0.124..270.353 rows=19,285 loops=1)

  • Hash Cond: (bb.id_contrato = b.id)
28. 20.098 256.402 ↓ 1.4 19,285 1

Nested Loop (cost=0.29..169,929.79 rows=13,521 width=61) (actual time=0.051..256.402 rows=19,285 loops=1)

29. 4.860 4.860 ↑ 1.0 19,287 1

Seq Scan on tb_linha_telefone aa (cost=0.00..338.87 rows=19,287 width=25) (actual time=0.011..4.860 rows=19,287 loops=1)

30. 96.234 231.444 ↑ 1.0 1 19,287

Index Scan using lintelhist_idlintel_idx on tb_linha_telefone_hist bb (cost=0.29..8.78 rows=1 width=40) (actual time=0.009..0.012 rows=1 loops=19,287)

  • Index Cond: (id_linha_telefone = aa.id)
  • Filter: (SubPlan 3)
  • Rows Removed by Filter: 0
31.          

SubPlan (for Index Scan)

32. 54.084 135.210 ↑ 1.0 1 27,042

Aggregate (cost=8.31..8.32 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=27,042)

33. 81.126 81.126 ↓ 2.0 2 27,042

Index Scan using lintelhist_idlintel_idx on tb_linha_telefone_hist xx (cost=0.29..8.30 rows=1 width=4) (actual time=0.003..0.003 rows=2 loops=27,042)

  • Index Cond: (id_linha_telefone = aa.id)
34. 0.023 0.060 ↑ 1.0 85 1

Hash (cost=3.85..3.85 rows=85 width=20) (actual time=0.060..0.060 rows=85 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
35. 0.037 0.037 ↑ 1.0 85 1

Seq Scan on tb_contrato b (cost=0.00..3.85 rows=85 width=20) (actual time=0.007..0.037 rows=85 loops=1)

36.          

SubPlan (for Hash Join)

37. 118.734 415.569 ↑ 1.0 1 19,789

Nested Loop (cost=0.00..6.05 rows=1 width=20) (actual time=0.018..0.021 rows=1 loops=19,789)

  • Join Filter: (e.id = c.id_entidade)
  • Rows Removed by Join Filter: 27
38. 237.468 237.468 ↑ 1.0 1 19,789

Seq Scan on tb_contrato c (cost=0.00..4.06 rows=1 width=4) (actual time=0.010..0.012 rows=1 loops=19,789)

  • Filter: (id = bb.id_contrato)
  • Rows Removed by Filter: 84
39. 59.367 59.367 ↑ 1.6 28 19,789

Seq Scan on tb_entidade e (cost=0.00..1.44 rows=44 width=24) (actual time=0.001..0.003 rows=28 loops=19,789)

40. 118.734 118.734 ↑ 1.0 1 19,789

Seq Scan on tb_entidade e_1 (cost=0.00..1.55 rows=1 width=20) (actual time=0.005..0.006 rows=1 loops=19,789)

  • Filter: (r.id_entidade = id)
  • Rows Removed by Filter: 43
Planning time : 16.219 ms
Execution time : 3,010.818 ms