explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RWW0

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2,185.588 2,185.588 ↑ 44.0 1 1

CTE Scan on cliente_saldos (cost=5,223.45..5,224.55 rows=44 width=830) (actual time=2,185.587..2,185.588 rows=1 loops=1)

2.          

CTE cliente_saldos

3. 0.007 2,185.579 ↑ 44.0 1 1

HashAggregate (cost=5,199.91..5,223.45 rows=44 width=578) (actual time=2,185.579..2,185.579 rows=1 loops=1)

  • Group Key: c.codigo, c.nome, c.apelido, (CASE WHEN (((c.bairro)::text IS NULL) OR ((c.bairro)::text ~ '^[[:space:]]*$'::text)) THEN (((CASE WHEN (((c.endereco_formatado)::text IS NULL) OR ((c.endereco_formatado)::text ~ '^[[:space:]]*$'::text)) THEN '[Não Informado]'::text ELSE (c.endereco_formatado)::text END)::character varying))::text ELSE (((((CASE WHEN (((c.endereco_formatado)::text IS NULL) OR ((c.endereco_formatado)::text ~ '^[[:space:]]*$'::text)) THEN '[Não Informado]'::text ELSE (c.endereco_formatado)::text END)::character varying))::text || ' - '::text) || (c.bairro)::text) END)::character varying, ((((m.nome)::text || ' - '::text) || (e.uf)::text))::character varying(100), obter_saldo_a_prazo_cliente(c.codigo), COALESCE(obter_creditocliente(c.codigo), '0'::numeric), (COALESCE(sum(contasareceber_saldo.valorprincipaldevendo) FILTER (WHERE (contasareceber_saldo.diasatrasodevendo > 0)), '0'::numeric)), (COALESCE(sum(contasareceber_saldo.valormultadevendo) FILTER (WHERE (contasareceber_saldo.diasatrasodevendo > 0)), '0'::numeric)), (COALESCE(sum(contasareceber_saldo.valorjurosdevendo) FILTER (WHERE (contasareceber_saldo.diasatrasodevendo > 0)), '0'::numeric)), (COALESCE(sum(contasareceber_saldo.valorprincipaldevendo) FILTER (WHERE (contasareceber_saldo.diasatrasodevendo = 0)), '0'::numeric)), (COALESCE(sum(contasareceber_saldo.valormultadevendo) FILTER (WHERE (contasareceber_saldo.diasatrasodevendo = 0)), '0'::numeric)), (COALESCE(sum(contasareceber_saldo.valorjurosdevendo) FILTER (WHERE (contasareceber_saldo.diasatrasodevendo = 0)), '0'::numeric)), (((COALESCE(sum(contasareceber_saldo.valorprincipaldevendo) FILTER (WHERE (contasareceber_saldo.diasatrasodevendo > 0)), '0'::numeric)) + (COALESCE(sum(contasareceber_saldo.valormultadevendo) FILTER (WHERE (contasareceber_saldo.diasatrasodevendo > 0)), '0'::numeric))) + (COALESCE(sum(contasareceber_saldo.valorjurosdevendo) FILTER (WHERE (contasareceber_saldo.diasatrasodevendo > 0)), '0'::numeric))), (((COALESCE(sum(contasareceber_saldo.valorprincipaldevendo) FILTER (WHERE (contasareceber_saldo.diasatrasodevendo = 0)), '0'::numeric)) + (COALESCE(sum(contasareceber_saldo.valormultadevendo) FILTER (WHERE (contasareceber_saldo.diasatrasodevendo = 0)), '0'::numeric))) + (COALESCE(sum(contasareceber_saldo.valorjurosdevendo) FILTER (WHERE (contasareceber_saldo.diasatrasodevendo = 0)), '0'::numeric)))
4. 1,054.067 2,185.572 ↑ 44.0 1 1

Nested Loop Left Join (cost=5,004.82..5,198.26 rows=44 width=578) (actual time=2,184.929..2,185.572 rows=1 loops=1)

5. 0.104 63.099 ↑ 44.0 1 1

Nested Loop Left Join (cost=4,964.55..5,133.88 rows=44 width=407) (actual time=62.457..63.099 rows=1 loops=1)

  • Join Filter: (t.codcliente = c.codigo)
6.          

CTE cliente

7. 48.702 48.702 ↑ 1.0 8,708 1

Seq Scan on clientes (cost=0.00..4,766.16 rows=8,708 width=278) (actual time=0.116..48.702 rows=8,708 loops=1)

8. 0.010 62.984 ↑ 44.0 1 1

Hash Left Join (cost=198.11..347.65 rows=44 width=386) (actual time=62.342..62.984 rows=1 loops=1)

  • Hash Cond: (m.codestado = e.codibge)
9. 0.455 62.959 ↑ 44.0 1 1

Hash Right Join (cost=196.48..345.41 rows=44 width=387) (actual time=62.318..62.959 rows=1 loops=1)

  • Hash Cond: (m.codibge = c.codmunicipio)
10. 0.362 0.362 ↑ 1.0 5,571 1

Seq Scan on municipios m (cost=0.00..106.71 rows=5,571 width=21) (actual time=0.003..0.362 rows=5,571 loops=1)

11. 0.016 62.142 ↑ 44.0 1 1

Hash (cost=195.93..195.93 rows=44 width=382) (actual time=62.142..62.142 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 62.126 62.126 ↑ 44.0 1 1

CTE Scan on cliente c (cost=0.00..195.93 rows=44 width=382) (actual time=58.006..62.126 rows=1 loops=1)

  • Filter: (codigo = 2)
  • Rows Removed by Filter: 8707
13. 0.010 0.015 ↑ 1.0 28 1

Hash (cost=1.28..1.28 rows=28 width=7) (actual time=0.015..0.015 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
14. 0.005 0.005 ↑ 1.0 28 1

Seq Scan on estados e (cost=0.00..1.28 rows=28 width=7) (actual time=0.002..0.005 rows=28 loops=1)

15. 0.003 0.011 ↓ 0.0 0 1

Materialize (cost=0.28..8.31 rows=1 width=25) (actual time=0.011..0.011 rows=0 loops=1)

16. 0.008 0.008 ↓ 0.0 0 1

Index Scan using clientes_telefones_codcliente_ddi_ddd_telefone_ramal_idx on clientes_telefones t (cost=0.28..8.30 rows=1 width=25) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (codcliente = 2)
  • Filter: principal
17. 0.005 1,068.406 ↑ 1.0 1 1

Materialize (cost=40.27..40.29 rows=1 width=192) (actual time=1,068.406..1,068.406 rows=1 loops=1)

18. 0.047 1,068.401 ↑ 1.0 1 1

Aggregate (cost=40.27..40.28 rows=1 width=52) (actual time=1,068.401..1,068.401 rows=1 loops=1)

19. 1,068.354 1,068.354 ↑ 31.2 32 1

Function Scan on contasareceber_saldo (cost=0.26..10.26 rows=1,000 width=52) (actual time=1,068.342..1,068.354 rows=32 loops=1)