explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Spk5

Settings
# exclusive inclusive rows x rows loops node
1. 17.922 2,760.302 ↑ 33.5 3,802 1

Unique (cost=1,825,285.11..1,830,691.96 rows=127,220 width=552) (actual time=2,737.285..2,760.302 rows=3,802 loops=1)

2. 128.243 2,742.380 ↑ 6.4 19,789 1

Sort (cost=1,825,285.11..1,825,603.16 rows=127,220 width=552) (actual time=2,737.281..2,742.380 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. 265.600 2,614.137 ↑ 6.4 19,789 1

Hash Join (cost=211,786.58..1,783,187.78 rows=127,220 width=552) (actual time=608.818..2,614.137 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. 660.346 1,558.824 ↓ 3.4 630,986 1

Hash Join (cost=41,513.58..643,392.59 rows=186,377 width=39) (actual time=367.458..1,558.824 rows=630,986 loops=1)

  • Hash Cond: (bt.id_arquivo_entrada = ag.id_arquivo_entrada)
5. 553.698 693.556 ↓ 1.0 1,440,208 1

Bitmap Heap Scan on tb_totalizador_cdr bt (cost=27,174.41..621,833.21 rows=1,428,384 width=23) (actual time=149.507..693.556 rows=1,440,208 loops=1)

  • Recheck Cond: (competencia >= 201906)
  • Heap Blocks: exact=35340
6. 139.858 139.858 ↓ 1.0 1,440,208 1

Bitmap Index Scan on totcdr_comp_idx (cost=0.00..26,817.32 rows=1,428,384 width=0) (actual time=139.858..139.858 rows=1,440,208 loops=1)

  • Index Cond: (competencia >= 201906)
7. 80.744 204.922 ↓ 1.4 154,022 1

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

  • Buckets: 262144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 9711kB
8. 42.390 124.178 ↓ 1.4 154,022 1

Nested Loop (cost=7.41..12,966.63 rows=109,803 width=28) (actual time=0.364..124.178 rows=154,022 loops=1)

9. 0.641 1.136 ↑ 1.0 611 1

Hash Join (cost=6.98..33.50 rows=611 width=32) (actual time=0.306..1.136 rows=611 loops=1)

  • Hash Cond: (ga.id_remessa = r.id)
10. 0.234 0.234 ↑ 1.0 611 1

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

11. 0.025 0.261 ↑ 1.0 38 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
12. 0.137 0.236 ↑ 1.0 38 1

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

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

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

14. 0.037 0.070 ↑ 1.0 38 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
15. 0.033 0.033 ↑ 1.0 38 1

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

16. 80.652 80.652 ↓ 6.5 252 611

Index Scan using arqgp_idgp_idx on ta_arquivo_grupo ag (cost=0.42..20.78 rows=39 width=12) (actual time=0.013..0.132 rows=252 loops=611)

  • Index Cond: ((id_grupo_arquivo = ga.id) AND (versao = ga.versao))
17. 15.975 235.621 ↓ 1.4 19,285 1

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

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 2377kB
18. 11.242 219.646 ↓ 1.4 19,285 1

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

  • Hash Cond: (bb.id_contrato = b.id)
19. 10.731 208.342 ↓ 1.4 19,285 1

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

20. 4.741 4.741 ↑ 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.015..4.741 rows=19,287 loops=1)

21. 84.702 192.870 ↑ 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.007..0.010 rows=1 loops=19,287)

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

SubPlan (for Index Scan)

23. 27.042 108.168 ↑ 1.0 1 27,042

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

24. 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.002..0.003 rows=2 loops=27,042)

  • Index Cond: (id_linha_telefone = aa.id)
25. 0.027 0.062 ↑ 1.0 85 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
26. 0.035 0.035 ↑ 1.0 85 1

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

27.          

SubPlan (for Hash Join)

28. 98.945 435.358 ↑ 1.0 1 19,789

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

  • Join Filter: (e.id = c.id_entidade)
  • Rows Removed by Join Filter: 27
29. 257.257 257.257 ↑ 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.013 rows=1 loops=19,789)

  • Filter: (id = bb.id_contrato)
  • Rows Removed by Filter: 84
30. 79.156 79.156 ↑ 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.004 rows=28 loops=19,789)

31. 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.027 ms
Execution time : 2,762.193 ms