explain.depesz.com

PostgreSQL's explain analyze made readable

Result: q8FZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 164,101.629 ↑ 1.0 1 1

Sort (cost=17,680.17..17,680.18 rows=1 width=131) (actual time=164,101.628..164,101.629 rows=1 loops=1)

  • Sort Key: banc.cod_banco, con.agencia, con.conta
  • Sort Method: quicksort Memory: 25kB
  • (actual time=50.759..50.759 rows=921,581 loops=3,115)
2. 0.993 164,101.618 ↑ 1.0 1 1

GroupAggregate (cost=17,680.13..17,680.16 rows=1 width=131) (actual time=164,101.618..164,101.618 rows=1 loops=1)

  • Group Key: con.id, emp.id, banc.id
  • Filter: (count(1) > 0)
3. 12.444 164,100.625 ↓ 3,117.0 3,117 1

Sort (cost=17,680.13..17,680.13 rows=1 width=79) (actual time=164,100.352..164,100.625 rows=3,117 loops=1)

  • Sort Key: con.id, emp.id, banc.id
  • Sort Method: quicksort Memory: 535kB
4. 11.631 164,088.181 ↓ 3,117.0 3,117 1

Nested Loop (cost=14,064.97..17,680.12 rows=1 width=79) (actual time=48.855..164,088.181 rows=3,117 loops=1)

5. 12.351 164,045.380 ↓ 3,117.0 3,117 1

Nested Loop Left Join (cost=14,064.69..17,671.80 rows=1 width=83) (actual time=48.840..164,045.380 rows=3,117 loops=1)

6. 10.964 164,004.976 ↓ 3,117.0 3,117 1

Nested Loop Left Join (cost=14,064.41..17,663.50 rows=1 width=52) (actual time=48.828..164,004.976 rows=3,117 loops=1)

7. 15.792 163,975.310 ↓ 3,117.0 3,117 1

Nested Loop Left Join (cost=14,064.27..17,663.34 rows=1 width=48) (actual time=48.818..163,975.310 rows=3,117 loops=1)

  • Filter: ((NOT ext.pendente) OR ((ext.pendente IS NULL) AND con.ativo) OR (ext.pendente AND (ext.conta_id = 0)))
8. 22.751 163,912.763 ↓ 3,117.0 3,117 1

Nested Loop Left Join (cost=14,063.98..17,655.02 rows=1 width=25) (actual time=48.798..163,912.763 rows=3,117 loops=1)

9. 47.110 163,812.087 ↓ 3,117.0 3,117 1

Nested Loop Left Join (cost=14,063.56..17,654.53 rows=1 width=57) (actual time=48.767..163,812.087 rows=3,117 loops=1)

10. 21.887 21.887 ↓ 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.062..21.887 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. 3,987.200 163,743.090 ↑ 1.0 1 3,115

Bitmap Heap Scan on conciliacao_bancaria_v2_0180 cbv2 (cost=14,063.00..17,645.80 rows=1 width=153) (actual time=51.948..52.566 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,116
  • Heap Blocks: exact=295,925
12. 158,251.345 159,755.890 ↓ 0.0 0 3,115

BitmapAnd (cost=14,063.00..14,063.00 rows=930 width=0) (actual time=51.286..51.286 rows=0 loops=3,115)

13. 1,504.545 1,504.545 ↓ 1.0 9,554 3,115

Bitmap Index Scan on conciliacao_bancaria_v2_0180_data_credito_idx (cost=0.00..326.18 rows=9,300 width=0) (actual time=0.483..0.483 rows=9,554 loops=3,115)

  • Index Cond: (ext.data_credito = data_credito)
14. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on conciliacao_bancaria_v2_0180_rede_idx (cost=0.00..13,638.89 rows=390,728 width=0) (actual rows= loops=)

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

Index Only Scan using conciliacao_bancaria_v2_lancamento_new_0180_chave_idx on conciliacao_bancaria_v2_lancamento_new_0180 cbvln (cost=0.41..0.48 rows=1 width=32) (actual time=0.024..0.025 rows=1 loops=3,117)

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

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

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

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

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

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

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

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

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