explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mPHf

Settings
# exclusive inclusive rows x rows loops node
1. 0.029 25,543.140 ↑ 1.0 1 1

Limit (cost=404.00..404.01 rows=1 width=62) (actual time=25,543.114..25,543.140 rows=1 loops=1)

  • Output: vw.doc_emitente, vw.categoria, ((SubPlan 1)), (COALESCE(((SubPlan 2)), '0'::numeric))
2. 0.002 25,543.111 ↑ 1.0 1 1

Unique (cost=404.00..404.01 rows=1 width=62) (actual time=25,543.111..25,543.111 rows=1 loops=1)

  • Output: vw.doc_emitente, vw.categoria, ((SubPlan 1)), (COALESCE(((SubPlan 2)), '0'::numeric))
3. 0.045 25,543.109 ↑ 1.0 1 1

Sort (cost=404.00..404.00 rows=1 width=62) (actual time=25,543.108..25,543.109 rows=1 loops=1)

  • Output: vw.doc_emitente, vw.categoria, ((SubPlan 1)), (COALESCE(((SubPlan 2)), '0'::numeric))
  • Sort Key: vw.categoria, ((SubPlan 1)), (COALESCE(((SubPlan 2)), '0'::numeric))
  • Sort Method: quicksort Memory: 25kB
4. 0.083 25,543.064 ↓ 4.0 4 1

Nested Loop (cost=1.11..403.99 rows=1 width=62) (actual time=6,365.233..25,543.064 rows=4 loops=1)

  • Output: vw.doc_emitente, vw.categoria, (SubPlan 1), COALESCE(((SubPlan 2)), '0'::numeric)
5. 41.493 41.493 ↓ 4.0 4 1

Index Scan using idx_rel_finan_nota_fiscal_composto3 on sigest.vw_rel_finan_nota_fiscal vw (cost=0.56..62.39 rows=1 width=24) (actual time=30.746..41.493 rows=4 loops=1)

  • Output: vw.uf_fk, vw.nome_estado, vw.programa_fk, vw.codigo_programa, vw.atividade_fk, vw.codigo_atividade, vw.fonte_fk, vw.codigo_fonte, vw.descricao_conta, vw.descricao, vw.data_gravac
  • Index Cond: (((vw.uf_fk)::text = 'PR'::text) AND (vw.programa_fk = '3'::bigint) AND (vw.atividade_fk = '4'::bigint) AND (vw.fonte_fk = '2'::bigint) AND ((vw.doc_emitente)::text = '264616
  • Filter: ((vw.tipo_operacao <> '9'::bpchar) AND ((date(vw.data_gravacao) = '2019-03-15'::date) OR (date(vw.data_cancelamento) = '2019-03-15'::date)))
  • Rows Removed by Filter: 7395
6. 0.056 25,447.828 ↑ 1.0 1 4

Subquery Scan on temp (cost=0.56..62.65 rows=1 width=46) (actual time=6,349.507..6,361.957 rows=1 loops=4)

  • Output: temp.cnpj_cpf, (SubPlan 2)
7. 160.364 160.364 ↑ 1.0 1 4

Unique (cost=0.56..62.38 rows=1 width=14) (actual time=27.643..40.091 rows=1 loops=4)

  • Output: vw_1.doc_emitente
  • -> Index Only Scan using idx_rel_finan_nota_fiscal_composto3 on sigest.vw_rel_finan_nota_fiscal vw_1 (cost=0.56..62.38 rows=1 width=14) (actual time=27.639..40.079 rows=7 loops=4
  • Output: vw_1.doc_emitente
  • Index Cond: ((vw_1.uf_fk = 'PR'::text) AND (vw_1.programa_fk = '3'::bigint) AND (vw_1.atividade_fk = '4'::bigint) AND (vw_1.fonte_fk = '2'::bigint) AND (vw_1.doc_emitente = '
  • Filter: ((date(vw_1.data_gravacao) = '2019-03-15'::date) OR (date(vw_1.data_cancelamento) = '2019-03-15'::date))
  • Rows Removed by Filter: 7392
  • Heap Fetches: 29596
8.          

SubPlan (forSubquery Scan)

9. 25,287.408 25,287.408 ↑ 1.0 1 4

Result (cost=0.00..0.26 rows=1 width=32) (actual time=6,321.850..6,321.852 rows=1 loops=4)

  • Output: sigest.fc_saldo_movimento_anterior_finan('PR'::character varying, '3'::bigint, '4'::bigint, '2'::bigint, temp.cnpj_cpf, '2019-03-15'::date)
10.          

SubPlan (forNested Loop)

11. 0.064 53.660 ↑ 1.0 1 4

Limit (cost=278.92..278.93 rows=1 width=8) (actual time=13.403..13.415 rows=1 loops=4)

  • Output: tb_finan_movimento_anterior.data_movimento
12. 8.984 53.596 ↑ 1.0 1 4

Sort (cost=278.92..278.93 rows=1 width=8) (actual time=13.398..13.399 rows=1 loops=4)

  • Output: tb_finan_movimento_anterior.data_movimento
  • Sort Key: tb_finan_movimento_anterior.data_movimento DESC
  • Sort Method: top-N heapsort Memory: 25kB
13. 15.412 44.612 ↓ 2,512.0 2,512 4

Result (cost=248.84..278.91 rows=1 width=8) (actual time=3.516..11.153 rows=2,512 loops=4)

  • Output: tb_finan_movimento_anterior.data_movimento
  • One-Time Filter: (vw.tipo_operacao <> '9'::bpchar)
14. 15.900 29.200 ↓ 2,512.0 2,512 4

Bitmap Heap Scan on sigest.tb_finan_movimento_anterior (cost=248.84..278.91 rows=1 width=8) (actual time=3.504..7.300 rows=2,512 loops=4)

  • Output: tb_finan_movimento_anterior.id_conta, tb_finan_movimento_anterior.doc_emitente, tb_finan_movimento_anterior.uf_fk, tb_finan_movimento_anterior.programa_fk, tb
  • Recheck Cond: (((tb_finan_movimento_anterior.doc_emitente)::text = (vw.doc_emitente)::text) AND ((tb_finan_movimento_anterior.uf_fk)::text = 'PR'::text))
  • Filter: ((tb_finan_movimento_anterior.programa_fk = '3'::bigint) AND (tb_finan_movimento_anterior.atividade_fk = '4'::bigint) AND (tb_finan_movimento_anterior.fonte_f
  • Rows Removed by Filter: 4617
  • Heap Blocks: exact=464
15. 0.072 13.300 ↓ 0.0 0 4

BitmapAnd (cost=248.84..248.84 rows=20 width=0) (actual time=3.324..3.325 rows=0 loops=4)

16. 3.296 3.296 ↓ 12.9 7,129 4

Bitmap Index Scan on idx_finan_movimento_anterior_doc_emitente (cost=0.00..9.08 rows=554 width=0) (actual time=0.823..0.824 rows=7,129 loops=4)

  • Index Cond: ((tb_finan_movimento_anterior.doc_emitente)::text = (vw.doc_emitente)::text)
17. 9.932 9.932 ↓ 1.0 21,057 4

Bitmap Index Scan on idx_finan_movimento_anterior_uf_fk (cost=0.00..239.51 rows=20,478 width=0) (actual time=2.482..2.483 rows=21,057 loops=4)

  • Index Cond: ((tb_finan_movimento_anterior.uf_fk)::text = 'PR'::text)
Planning time : 1.127 ms
Execution time : 25,543.528 ms