explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2YVo

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 12,386.398 ↑ 1.0 1 1

Sort (cost=5,194.44..5,194.45 rows=1 width=131) (actual time=12,386.398..12,386.398 rows=1 loops=1)

  • Sort Key: banc.cod_banco, con.agencia, con.conta
  • Sort Method: quicksort Memory: 25kB
2. 0.510 12,386.393 ↑ 1.0 1 1

GroupAggregate (cost=5,194.40..5,194.43 rows=1 width=131) (actual time=12,386.393..12,386.393 rows=1 loops=1)

  • Group Key: con.id, emp.id, banc.id
  • Filter: (count(1) > 0)
3. 3.583 12,385.883 ↓ 3,115.0 3,115 1

Sort (cost=5,194.40..5,194.40 rows=1 width=79) (actual time=12,385.795..12,385.883 rows=3,115 loops=1)

  • Sort Key: con.id, emp.id, banc.id
  • Sort Method: quicksort Memory: 535kB
4. 4.893 12,382.300 ↓ 3,115.0 3,115 1

Nested Loop (cost=2,310.63..5,194.39 rows=1 width=79) (actual time=106.461..12,382.300 rows=3,115 loops=1)

5. 4.641 12,371.177 ↓ 3,115.0 3,115 1

Nested Loop Left Join (cost=2,310.35..5,186.07 rows=1 width=83) (actual time=106.455..12,371.177 rows=3,115 loops=1)

6. 2.348 12,360.306 ↓ 3,115.0 3,115 1

Nested Loop Left Join (cost=2,310.07..5,177.77 rows=1 width=52) (actual time=106.441..12,360.306 rows=3,115 loops=1)

7. 4.115 12,351.728 ↓ 3,115.0 3,115 1

Nested Loop Left Join (cost=2,309.93..5,177.60 rows=1 width=48) (actual time=106.435..12,351.728 rows=3,115 loops=1)

  • Filter: ((NOT ext.pendente) OR ((ext.pendente IS NULL) AND con.ativo) OR (ext.pendente AND (ext.conta_id = 0)))
8. 3.981 12,325.808 ↓ 3,115.0 3,115 1

Nested Loop Left Join (cost=2,309.64..5,169.28 rows=1 width=25) (actual time=95.896..12,325.808 rows=3,115 loops=1)

9. 11.646 12,271.987 ↓ 3,115.0 3,115 1

Nested Loop Left Join (cost=2,309.22..5,168.82 rows=1 width=57) (actual time=95.879..12,271.987 rows=3,115 loops=1)

10. 9.046 9.046 ↓ 3,115.0 3,115 1

Index Scan using extrato_bancario_0180_idx_historico on extrato_bancario_0180 ext (cost=0.56..8.72 rows=1 width=117) (actual time=0.031..9.046 rows=3,115 loops=1)

  • Index Cond: ((data_credito >= '2020-07-15'::date) AND (data_credito <= '2020-07-15'::date) AND (rede_id = 6))
  • Filter: (((credito > '0'::numeric) OR (debito > '0'::numeric)) AND (loja_id = ANY ('{9416,3824,2786,2785,3158,2789,2788,2787,2790,2791,2792,2794,2795,2796,2797,2799,2798,2800,2801,2802,2803,2805,2804,2807,2806,2808,2809,2810,2813,2811,2812,2815,2814,2816,2818,2819,2821,2822,2824,2825,2827,12578,2829,2830,2831,2832,2833,2834,2835,2836,2837,2838,2839,2840,2841,2842,2843,2845,2846,2847,2848,2850,2851,2852,2854,2855,2856,2857,2859,2860,2862,2866,2864,2863,2865,2869,2868,2870,12345,2871,2876,2877,2872,2873,2874,2875,2878,2879,2884,2885,4039,2881,2882,2883,9642,9643,2880,11886,11887,11889,11918,14405,2853}'::integer[])))
11. 2,040.325 12,251.295 ↑ 1.0 1 3,115

Bitmap Heap Scan on conciliacao_bancaria_v2_0180 cbv2 (cost=2,308.66..5,160.10 rows=1 width=154) (actual time=3.608..3.933 rows=1 loops=3,115)

  • Recheck Cond: ((ext.data_credito = data_credito) AND (rede_id = 6))
  • Filter: ((ext.rede_id = rede_id) AND (ext.nsu = nsu) AND (ext.autorizacao = autorizacao) AND (ext.conta_id = conta_id) AND (ext.historico_id = historico_id) AND (ext.loja_extensao_id = loja_extensao_id) AND (ext.bandeira_id = bandeira_id) AND (ext.produto_id = produto_id) AND ((ext.chave_extrato_bancario)::text = chave_extrato_bancario))
  • Rows Removed by Filter: 3,114
  • Heap Blocks: exact=373,800
12. 43.610 10,210.970 ↓ 0.0 0 3,115

BitmapAnd (cost=2,308.66..2,308.66 rows=785 width=0) (actual time=3.278..3.278 rows=0 loops=3,115)

13. 763.175 763.175 ↑ 1.2 9,577 3,115

Bitmap Index Scan on conciliacao_bancaria_v2_0180_data_credito_idx (cost=0.00..220.60 rows=11,756 width=0) (actual time=0.245..0.245 rows=9,577 loops=3,115)

  • Index Cond: (ext.data_credito = data_credito)
14. 9,404.185 9,404.185 ↑ 1.1 105,688 3,115

Bitmap Index Scan on conciliacao_bancaria_v2_0180_rede_idx (cost=0.00..2,059.96 rows=111,405 width=0) (actual time=3.019..3.019 rows=105,688 loops=3,115)

  • Index Cond: (rede_id = 6)
15. 49.840 49.840 ↑ 1.0 1 3,115

Index Only Scan using conciliacao_bancaria_v2_lancamento_new_0180_chave_idx on conciliacao_bancaria_v2_lancamento_new_0180 cbvln (cost=0.41..0.45 rows=1 width=32) (actual time=0.015..0.016 rows=1 loops=3,115)

  • Index Cond: (chave_conciliacao_bancaria_v2 = cbv2.chave)
  • Heap Fetches: 3,115
16. 21.805 21.805 ↑ 1.0 1 3,115

Index Scan using pk_conta on conta con (cost=0.29..8.31 rows=1 width=29) (actual time=0.007..0.007 rows=1 loops=3,115)

  • Index Cond: (id = ext.conta_id)
17. 6.230 6.230 ↑ 1.0 1 3,115

Index Scan using pk_banco on banco banc (cost=0.14..0.16 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=3,115)

  • Index Cond: (id = con.banco_id)
18. 6.230 6.230 ↑ 1.0 1 3,115

Index Scan using pk_est_empresa on empresa emp (cost=0.28..8.30 rows=1 width=35) (actual time=0.002..0.002 rows=1 loops=3,115)

  • Index Cond: (id = ext.empresa_id)
19. 6.230 6.230 ↑ 1.0 1 3,115

Index Scan using pk_dim_historico on historico hist (cost=0.28..8.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=3,115)

  • Index Cond: (id = ext.historico_id)
  • Filter: (tipo = ANY ('{0,1,2,3,4,5,6,7}'::integer[]))