explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6rah : Optimization for: plan #RWW0

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 2,094.398 2,094.398 ↑ 1.0 1 1

CTE Scan on cliente_saldos (cost=58.19..58.21 rows=1 width=830) (actual time=2,094.398..2,094.398 rows=1 loops=1)

2.          

CTE cliente_saldos

3. 0.006 2,094.391 ↑ 1.0 1 1

HashAggregate (cost=57.65..58.19 rows=1 width=286) (actual time=2,094.391..2,094.391 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 (c.endereco)::text ELSE (((CASE WHEN ((c.endereconumero)::text IS NOT NULL) THEN (((c.endereco)::text || ', '::text) || (c.endereconumero)::text) ELSE (c.endereco)::text END)::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,049.212 2,094.385 ↑ 1.0 1 1

Nested Loop Left Join (cost=40.97..57.61 rows=1 width=286) (actual time=2,094.381..2,094.385 rows=1 loops=1)

5. 0.002 0.020 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.71..16.78 rows=1 width=94) (actual time=0.016..0.020 rows=1 loops=1)

6. 0.003 0.015 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.57..16.61 rows=1 width=95) (actual time=0.014..0.015 rows=1 loops=1)

7. 0.008 0.008 ↑ 1.0 1 1

Index Scan using unq_clientes on clientes c (cost=0.29..8.30 rows=1 width=82) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: (codigo = 2)
8. 0.004 0.004 ↑ 1.0 1 1

Index Scan using municipios_pkey on municipios m (cost=0.28..8.30 rows=1 width=21) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: (codibge = c.codmunicipio)
9. 0.003 0.003 ↑ 1.0 1 1

Index Scan using estados_pkey on estados e (cost=0.14..0.16 rows=1 width=7) (actual time=0.002..0.003 rows=1 loops=1)

  • Index Cond: (codibge = m.codestado)
10. 0.070 1,045.153 ↑ 1.0 1 1

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

11. 1,045.083 1,045.083 ↑ 31.2 32 1

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