explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cmF7

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 3,403.238 ↑ 20,000.0 1 1

Limit (cost=130,390.72..130,790.72 rows=20,000 width=890) (actual time=3,402.479..3,403.238 rows=1 loops=1)

2. 0.966 3,403.233 ↑ 40,581.0 1 1

HashAggregate (cost=130,390.72..130,796.53 rows=40,581 width=890) (actual time=3,402.476..3,403.233 rows=1 loops=1)

  • Group Key: findocpar.cd_empresa, findocpar.cd_base, findocpar.seq_processo, findocpar.parcela, findocpar.dt_vencimento, findocpar.dt_inicio_juros, findocpar.dt_inicio_multa, findocpar.vl_parcela, findocpar.vl_saldo, findocpar.qt_moeda, findocpar.qt_saldo_moeda, findocpar.cd_banco_portador, findocpar.nr_agencia_portador, findocpar.nr_conta_portador, findocpar.cd_tipo_cobranca, findocpar.pc_desconto, findocpar.dt_venc_desconto, findocpar.pc_bc_desconto, findocpar.id_situacao_eletronica, findocpar.dt_ult_atu_hist, findocpar.vl_desc_pagto_antecip, findocpar.vl_encargo_dia_atraso, findocpar.id_status_juros, findocpar.obs_juros, findocpar.nr_controle, findocpar.dt_ult_baixa
3. 0.008 3,402.267 ↑ 40,581.0 1 1

Append (cost=18,379.95..127,752.96 rows=40,581 width=890) (actual time=1,486.948..3,402.267 rows=1 loops=1)

4. 51.172 669.941 ↓ 0.0 0 1

Hash Anti Join (cost=18,379.95..51,787.89 rows=14,265 width=638) (actual time=669.940..669.941 rows=0 loops=1)

  • Hash Cond: ((findocpar.cd_empresa = procan.cd_empresa) AND (findocpar.cd_base = procan.cd_base) AND (findocpar.seq_processo = procan.seq_processo))
5. 18.317 517.616 ↓ 3.9 59,700 1

Nested Loop (cost=12,791.71..45,934.67 rows=15,154 width=638) (actual time=386.076..517.616 rows=59,700 loops=1)

6. 0.123 0.123 ↑ 1.0 1 1

Index Scan using pessoa_empresa_financ_pk on pessoa_empresa_financ pesempfin (cost=0.12..8.14 rows=1 width=16) (actual time=0.118..0.123 rows=1 loops=1)

  • Filter: (id_utiliza_juros_cap = 'S'::bpchar)
7. 117.216 499.176 ↓ 3.9 59,700 1

Bitmap Heap Scan on fin_doc_parcela findocpar (cost=12,791.59..45,774.99 rows=15,154 width=638) (actual time=385.945..499.176 rows=59,700 loops=1)

  • Recheck Cond: ((abs(vl_saldo) > '0'::numeric) AND (COALESCE(dt_ult_atu_hist, '1900-01-01'::date) < to_date('2019-09-24'::text, 'yyyy-MM-dd'::text)) AND (dt_vencimento >= pesempfin.dt_ini_rotina_jur) AND (dt_vencimento <= pesempfin.dt_fim_rotina_jur))
  • Filter: ((id_status_juros IS NULL) AND (id_situacao_eletronica = ANY ('{0,9}'::bpchar[])) AND (pesempfin.cd_empresa = cd_empresa))
  • Rows Removed by Filter: 520
  • Heap Blocks: exact=19,926
8. 2.129 381.960 ↓ 0.0 0 1

BitmapAnd (cost=12,791.59..12,791.59 rows=15,156 width=0) (actual time=381.960..381.960 rows=0 loops=1)

9. 33.302 33.302 ↑ 2.3 60,220 1

Bitmap Index Scan on fin_doc_parcela_idx4 (cost=0.00..2,999.81 rows=136,400 width=0) (actual time=33.302..33.302 rows=60,220 loops=1)

  • Index Cond: ((abs(vl_saldo) > '0'::numeric) AND (COALESCE(dt_ult_atu_hist, '1900-01-01'::date) < to_date('2019-09-24'::text, 'yyyy-MM-dd'::text)))
10. 346.529 346.529 ↓ 10.0 2,669,953 1

Bitmap Index Scan on fin_doc_parcela_idx1 (cost=0.00..9,753.64 rows=267,321 width=0) (actual time=346.528..346.529 rows=2,669,953 loops=1)

  • Index Cond: ((dt_vencimento >= pesempfin.dt_ini_rotina_jur) AND (dt_vencimento <= pesempfin.dt_fim_rotina_jur))
11. 56.046 101.153 ↑ 1.0 138,735 1

Hash (cost=3,156.54..3,156.54 rows=138,954 width=24) (actual time=101.152..101.153 rows=138,735 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 9,636kB
12. 45.107 45.107 ↑ 1.0 138,735 1

Seq Scan on processo_canc procan (cost=0.00..3,156.54 rows=138,954 width=24) (actual time=0.064..45.107 rows=138,735 loops=1)

13. 0.816 2,732.318 ↑ 26,316.0 1 1

Hash Anti Join (cost=15,348.99..75,356.35 rows=26,316 width=638) (actual time=817.002..2,732.318 rows=1 loops=1)

  • Hash Cond: ((findocpar_1.cd_empresa = procan_1.cd_empresa) AND (findocpar_1.cd_base = procan_1.cd_base) AND (findocpar_1.seq_processo = procan_1.seq_processo))
14. 0.027 2,637.566 ↑ 27,956.0 1 1

Nested Loop (cost=9,760.75..69,279.26 rows=27,956 width=638) (actual time=722.253..2,637.566 rows=1 loops=1)

15. 0.025 0.025 ↑ 1.0 1 1

Index Scan using pessoa_empresa_financ_pk on pessoa_empresa_financ pesempfin_1 (cost=0.12..8.14 rows=1 width=20) (actual time=0.020..0.025 rows=1 loops=1)

  • Filter: (id_utiliza_juros_cap = 'S'::bpchar)
16. 2,272.470 2,637.514 ↑ 27,956.0 1 1

Bitmap Heap Scan on fin_doc_parcela findocpar_1 (cost=9,760.63..68,991.56 rows=27,956 width=638) (actual time=722.211..2,637.514 rows=1 loops=1)

  • Recheck Cond: ((dt_vencimento >= pesempfin_1.dt_ini_rotina_jur) AND (dt_vencimento <= pesempfin_1.dt_fim_rotina_jur))
  • Filter: ((id_status_juros IS NULL) AND (id_situacao_eletronica = ANY ('{0,9}'::bpchar[])) AND (COALESCE(dt_ult_atu_hist, '1900-01-01'::date) < dt_ult_baixa) AND (pesempfin_1.cd_empresa = cd_empresa) AND (abs(vl_saldo) = '0'::numeric) AND (dt_ult_baixa >= COALESCE(pesempfin_1.dt_ini_hist_dia_cap, to_date('2019-03-01'::text, 'yyyy-MM-dd'::text))))
  • Rows Removed by Filter: 2,406,768
  • Heap Blocks: exact=50,541
17. 365.044 365.044 ↓ 10.0 2,669,953 1

Bitmap Index Scan on fin_doc_parcela_idx1 (cost=0.00..9,753.64 rows=267,321 width=0) (actual time=365.044..365.044 rows=2,669,953 loops=1)

  • Index Cond: ((dt_vencimento >= pesempfin_1.dt_ini_rotina_jur) AND (dt_vencimento <= pesempfin_1.dt_fim_rotina_jur))
18. 52.142 93.936 ↑ 1.0 138,735 1

Hash (cost=3,156.54..3,156.54 rows=138,954 width=24) (actual time=93.936..93.936 rows=138,735 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 9,636kB
19. 41.794 41.794 ↑ 1.0 138,735 1

Seq Scan on processo_canc procan_1 (cost=0.00..3,156.54 rows=138,954 width=24) (actual time=0.030..41.794 rows=138,735 loops=1)

Planning time : 2.088 ms
Execution time : 3,404.475 ms