explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JOTP

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 3,729.506 ↓ 0.0 0 1

Limit (cost=129,722.42..130,122.42 rows=20,000 width=890) (actual time=3,729.506..3,729.506 rows=0 loops=1)

2. 0.930 3,729.504 ↓ 0.0 0 1

HashAggregate (cost=129,722.42..130,128.23 rows=40,581 width=890) (actual time=3,729.503..3,729.504 rows=0 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.006 3,728.574 ↓ 0.0 0 1

Append (cost=18,379.95..127,084.65 rows=40,581 width=890) (actual time=3,728.574..3,728.574 rows=0 loops=1)

4. 49.732 945.765 ↓ 0.0 0 1

Hash Anti Join (cost=18,379.95..51,787.89 rows=14,265 width=638) (actual time=945.763..945.765 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. 17.550 776.549 ↓ 3.9 59,700 1

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

6. 0.024 0.024 ↑ 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.016..0.024 rows=1 loops=1)

  • Filter: (id_utiliza_juros_cap = 'S'::bpchar)
7. 111.589 758.975 ↓ 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=651.375..758.975 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.572 647.386 ↓ 0.0 0 1

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

9. 46.152 46.152 ↑ 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=46.152..46.152 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. 598.662 598.662 ↓ 10.0 2,669,951 1

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

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

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

  • Buckets: 262,144 Batches: 1 Memory Usage: 9,636kB
12. 57.237 57.237 ↑ 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.030..57.237 rows=138,735 loops=1)

13. 0.002 2,782.803 ↓ 0.0 0 1

Hash Anti Join (cost=15,348.99..74,688.04 rows=26,316 width=638) (actual time=2,782.803..2,782.803 rows=0 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,782.801 ↓ 0.0 0 1

Nested Loop (cost=9,760.75..68,610.96 rows=27,956 width=638) (actual time=2,782.800..2,782.801 rows=0 loops=1)

15. 0.036 0.036 ↑ 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.032..0.036 rows=1 loops=1)

  • Filter: (id_utiliza_juros_cap = 'S'::bpchar)
16. 2,304.721 2,782.738 ↓ 0.0 0 1

Bitmap Heap Scan on fin_doc_parcela findocpar_1 (cost=9,760.63..68,323.26 rows=27,956 width=638) (actual time=2,782.738..2,782.738 rows=0 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 (vl_saldo = '0'::numeric) AND (pesempfin_1.cd_empresa = cd_empresa) 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,769
  • Heap Blocks: exact=50,541
17. 478.017 478.017 ↓ 10.0 2,669,951 1

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

  • Index Cond: ((dt_vencimento >= pesempfin_1.dt_ini_rotina_jur) AND (dt_vencimento <= pesempfin_1.dt_fim_rotina_jur))
18. 0.000 0.000 ↓ 0.0 0

Hash (cost=3,156.54..3,156.54 rows=138,954 width=24) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Seq Scan on processo_canc procan_1 (cost=0.00..3,156.54 rows=138,954 width=24) (never executed)

Planning time : 1.867 ms
Execution time : 3,730.614 ms