explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Cn6Vc

Settings
# exclusive inclusive rows x rows loops node
1. 1.137 720.354 ↑ 1.4 1,253 1

Unique (cost=221,001.80..221,118.02 rows=1,788 width=225) (actual time=718.975..720.354 rows=1,253 loops=1)

2. 7.069 719.217 ↑ 1.4 1,253 1

Sort (cost=221,001.80..221,006.27 rows=1,788 width=225) (actual time=718.974..719.217 rows=1,253 loops=1)

  • Sort Key: registronegativacaocobrancacontareceberitem.aluno, registronegativacaocobrancacontareceberitem.matricula, registronegativacaocobrancacontareceberitem.parcela, registronegativacaocobrancacontareceberitem.codigo, registronegativacaocobrancacontareceberitem.contareceber, registronegativacaocobrancacontareceberitem.unidadeensino, registronegativacaocobrancacontareceberitem.curso, registronegativacaocobrancacontareceberitem.codigocurso, registronegativacaocobrancacontareceberitem.datavencimento, registronegativacaocobrancacontareceberitem.valor, registronegativacaocobrancacontareceberitem.registronegativacaocobrancacontareceber, registronegativacaocobrancacontareceberitem.codigousuario, registronegativacaocobrancacontareceberitem.nomeusuario, registronegativacaocobrancacontareceberitem.dataregistro, registronegativacaocobrancacontareceberitem.situacao, registronegativacaocobrancacontareceberitem.nossonumero, registronegativacaocobrancacontareceberitem.motivo, registronegativacaocobrancacontareceberitem.codigousuarioexclusao, registronegativacaocobrancacontareceberitem.nomeusuarioexclusao, registronegativacaocobrancacontareceberitem.dataexclusao, negociacaocontareceber.codigo, agentenegativacaocobrancacontareceber.nome, ((SubPlan 1))
  • Sort Method: quicksort Memory: 391kB
3. 1.930 712.148 ↑ 1.4 1,253 1

Nested Loop (cost=186,757.68..220,905.21 rows=1,788 width=225) (actual time=477.385..712.148 rows=1,253 loops=1)

4. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on agentenegativacaocobrancacontareceber (cost=0.00..1.04 rows=1 width=14) (actual time=0.004..0.006 rows=1 loops=1)

  • Filter: (codigo = 1)
  • Rows Removed by Filter: 2
5. 41.844 691.417 ↑ 1.4 1,253 1

Hash Right Join (cost=186,757.68..198,459.72 rows=1,788 width=219) (actual time=477.307..691.417 rows=1,253 loops=1)

  • Hash Cond: (contarecebernegociado.contareceber = contareceber.codigo)
  • Filter: (((negociacaocontareceber.codigo IS NOT NULL) AND (negociacaocontareceber.data >= '2018-01-01 00:00:00'::timestamp without time zone) AND (negociacaocontareceber.data <= '2019-01-31 23:59:59.059'::timestamp without time zone)) OR ((negociacaorecebimento.codigo IS NOT NULL) AND (negociacaorecebimento.data >= '2018-01-01 00:00:00-02'::timestamp with time zone) AND (negociacaorecebimento.data <= '2019-01-31 23:59:59.059-02'::timestamp with time zone)) OR ((negociacaocontareceber.codigo IS NOT NULL) AND (SubPlan 2)))
  • Rows Removed by Filter: 147
6. 138.667 209.993 ↑ 1.0 172,990 1

Hash Left Join (cost=2,754.14..9,952.64 rows=173,104 width=16) (actual time=36.948..209.993 rows=172,990 loops=1)

  • Hash Cond: (contarecebernegociado.negociacaocontareceber = negociacaocontareceber.codigo)
7. 34.431 34.431 ↑ 1.0 172,990 1

Seq Scan on contarecebernegociado (cost=0.00..3,520.04 rows=173,104 width=8) (actual time=0.003..34.431 rows=172,990 loops=1)

8. 16.135 36.895 ↑ 1.0 53,823 1

Hash (cost=2,080.73..2,080.73 rows=53,873 width=12) (actual time=36.895..36.895 rows=53,823 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 2523kB
9. 20.760 20.760 ↑ 1.0 53,823 1

Seq Scan on negociacaocontareceber (cost=0.00..2,080.73 rows=53,873 width=12) (actual time=0.003..20.760 rows=53,823 loops=1)

10. 1.334 436.640 ↑ 1.7 1,400 1

Hash (cost=183,974.34..183,974.34 rows=2,336 width=231) (actual time=436.640..436.640 rows=1,400 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 275kB
11. 1.589 435.306 ↑ 1.7 1,400 1

Nested Loop Left Join (cost=176,152.71..183,974.34 rows=2,336 width=231) (actual time=340.487..435.306 rows=1,400 loops=1)

12. 0.582 433.717 ↑ 1.7 1,400 1

Nested Loop Left Join (cost=176,152.28..182,759.30 rows=2,336 width=223) (actual time=340.484..433.717 rows=1,400 loops=1)

13. 1.265 428.935 ↑ 1.7 1,400 1

Hash Join (cost=176,151.85..180,429.06 rows=2,336 width=219) (actual time=340.470..428.935 rows=1,400 loops=1)

  • Hash Cond: (registronegativacaocobrancacontareceberitem.registronegativacaocobrancacontareceber = registronegativacaocobrancacontareceber.codigo)
14. 61.951 424.604 ↑ 1.6 1,455 1

Hash Join (cost=175,998.77..180,234.94 rows=2,358 width=215) (actual time=337.396..424.604 rows=1,455 loops=1)

  • Hash Cond: (registronegativacaocobrancacontareceberitem.contareceber = contareceber.codigo)
15. 25.997 25.997 ↑ 1.0 47,005 1

Seq Scan on registronegativacaocobrancacontareceberitem (cost=0.00..3,681.24 rows=47,231 width=208) (actual time=0.002..25.997 rows=47,005 loops=1)

  • Filter: (dataexclusao IS NULL)
  • Rows Removed by Filter: 36181
16. 59.470 336.656 ↓ 1.5 170,609 1

Hash (cost=174,591.39..174,591.39 rows=112,590 width=7) (actual time=336.656..336.656 rows=170,609 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 6665kB
17. 277.186 277.186 ↓ 1.5 170,609 1

Bitmap Heap Scan on contareceber (cost=4,361.60..174,591.39 rows=112,590 width=7) (actual time=67.254..277.186 rows=170,609 loops=1)

  • Recheck Cond: ((unidadeensino = ANY ('{1001,1,2,3,4,5,6,7,8,9,10,11,13,12,14,15,16,17,18,1000,19,20,21,22,23,24,25,26,27,28,29,30,31,32,1004,33,34,35,36,37,38,39,40,41,42,43,44,1002,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,9,0}'::integer[])) AND ((situacao)::text = 'NE'::text))
  • Heap Blocks: exact=83447
  • -> Bitmap Index Scan on idx_contareceber_unidade_sit_tp_dtv (cost=0.00..4333.45 rows=112590 width=0) (actual time=-214009.632..-214009.632 rows=170965 loops=1)
  • Index Cond: ((unidadeensino = ANY ('{1001,1,2,3,4,5,6,7,8,9,10,11,13,12,14,15,16,17,18,1000,19,20,21,22,23,24,25,26,27,28,29,30,31,32,1004,33,34,35,36,37,38,39,40,41,42,43,44,1002,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,9,0}'::integer[])) AND ((situacao)::text = 'NE'::text))
18. 0.916 3.066 ↑ 1.0 3,680 1

Hash (cost=106.47..106.47 rows=3,729 width=8) (actual time=3.066..3.066 rows=3,680 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 144kB
19. 2.150 2.150 ↑ 1.0 3,680 1

Seq Scan on registronegativacaocobrancacontareceber (cost=0.00..106.47 rows=3,729 width=8) (actual time=0.004..2.150 rows=3,680 loops=1)

  • Filter: ((agente = 1) AND ((tipoagente)::text = 'NEGATIVACAO'::text))
  • Rows Removed by Filter: 35
20. 4.200 4.200 ↓ 0.0 0 1,400

Index Scan using unq_contarecebernegociacaorecebimento_contareceber on contarecebernegociacaorecebimento (cost=0.43..0.99 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1,400)

  • Index Cond: (contareceber = contareceber.codigo)
21. 0.000 0.000 ↓ 0.0 0 1,400

Index Scan using negociacaorecebimento_pkey on negociacaorecebimento (cost=0.43..0.51 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=1,400)

  • Index Cond: (contarecebernegociacaorecebimento.negociacaorecebimento = codigo)
22.          

SubPlan (forHash Right Join)

23. 0.147 2.940 ↓ 0.0 0 147

Limit (cost=1.42..21.51 rows=1 width=0) (actual time=0.020..0.020 rows=0 loops=147)

24. 0.000 2.793 ↓ 0.0 0 147

Nested Loop (cost=1.42..21.51 rows=1 width=0) (actual time=0.019..0.019 rows=0 loops=147)

25. 0.138 2.793 ↓ 0.0 0 147

Nested Loop (cost=0.99..20.99 rows=1 width=4) (actual time=0.019..0.019 rows=0 loops=147)

26. 2.646 2.646 ↓ 0.0 0 147

Index Scan using unique_contareceber_codorigem_tipoorigem_parcela_pessoa_matricu on contareceber cr (cost=0.56..12.53 rows=1 width=4) (actual time=0.018..0.018 rows=0 loops=147)

  • Index Cond: (((codorigem)::text = ((negociacaocontareceber.codigo)::character varying)::text) AND ((tipoorigem)::text = 'NCR'::text))
  • Filter: ((situacao)::text = 'RE'::text)
  • Rows Removed by Filter: 14
27. 0.009 0.009 ↑ 1.0 1 1

Index Scan using unq_contarecebernegociacaorecebimento_contareceber on contarecebernegociacaorecebimento ccnr (cost=0.43..8.45 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (contareceber = cr.codigo)
28. 0.010 0.010 ↓ 0.0 0 1

Index Scan using negociacaorecebimento_pkey on negociacaorecebimento nr (cost=0.43..0.52 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=1)

  • Index Cond: (codigo = ccnr.negociacaorecebimento)
  • Filter: ((data >= '2018-01-01 00:00:00-02'::timestamp with time zone) AND (data <= '2019-01-31 23:59:59.059-02'::timestamp with time zone))
  • Rows Removed by Filter: 1
29.          

SubPlan (forNested Loop)

30. 0.000 18.795 ↑ 1.0 1 1,253

Limit (cost=0.56..12.54 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=1,253)

31. 1.253 18.795 ↑ 1.0 1 1,253

Result (cost=0.56..12.54 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=1,253)

  • One-Time Filter: (negociacaocontareceber.codigo IS NOT NULL)
32. 17.542 17.542 ↑ 1.0 1 1,253

Index Scan using unique_contareceber_codorigem_tipoorigem_parcela_pessoa_matricu on contareceber contanegociada (cost=0.56..12.54 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1,253)

  • Index Cond: (((codorigem)::text = ((negociacaocontareceber.codigo)::character varying)::text) AND ((tipoorigem)::text = 'NCR'::text))
  • Filter: (((situacao)::text = 'AR'::text) AND (datavencimento < ('now'::cstring)::date))
  • Rows Removed by Filter: 4