explain analyze select distinct npd.exercicio
, npd.unidadegestora
, npd.numero
, npd.numeronld
, nld.numero as nldorigem
, nld.numeroned
, ned.numero as nedorigem
, ned.numeronpf
, npf.numero as npforigem
, npf.grupo_fin as grupofin
, orm.cod_tp_orcamento as tipcre
, npd.classiforcamreduz
, npd.classiforcamcompl
, npd.credor
, npd.nomecredor
, npd.statusmovbancario
, npd.natureza
, nld.numeroned as numemp
, npd.codigoretencao
, npd.dataemissao
, npd.dt_etl
, npd.servicobancario
, npd.bancobeneficiario as banco
, npd.agenciabeneficiario as agencia
, npd.contabeneficiario as conta
, npd.efeito
, substring(ned.classiforcamcompl from 24 for 8) as
natdespesa
, substring(ned.classiforcamcompl from 33 for 2) as
fonterec
, npd.valor as valor
, npd.cpfcnpjcredor
, cast(npd.numeronld as integer) as "numliq"
, npd.numeronpf
, npd.numeronpdordinario
, exerciciorestosapagar
from sefaz_ws.npd_nota_pagamento_despesa npd
left join sefaz_ws.nld_nota_liquidacao_despesa nld
on (npd.exercicio = nld.exercicio
and npd.unidadegestora = nld.unidadegestora
and npd.numeronld = nld.numero)
left join sefaz_ws.ned_nota_empenho_despesa ned
on (nld.exercicio = ned.exercicio
and nld.unidadegestora = ned.unidadegestora
and nld.numeroned = ned.numero)
left join sefaz_ws.npf_nota_programacao_financeira npf
on (ned.exercicio = npf.exercicio
and ned.unidadegestora = npf.unidadegestora
and ned.numeronpf = npf.numero)
left join sefaz_ws.orcamentario_mensal orm
on (npd.exercicio = orm.exercicio and npd.unidadegestora
= orm.unidadegestora and orm.classif_orcam_reduz =
CAST(ned.classiforcamreduz AS integer))
order by npd.exercicio, npd.unidadegestora, npd.numero
#### resultado do explain analyze
"Unique (cost=358689.48..389491.06 rows=352018 width=287)
(actual time=101865.980..107098.130 rows=352018 loops=1)"
" -> Sort (cost=358689.48..359569.53 rows=352018
width=287) (actual time=101865.979..103271.970 rows=2631834
loops=1)"
" Sort Key: npd.exercicio, npd.unidadegestora,
npd.numero, npd.numeronld, nld.numero, nld.numeroned,
ned.numero, ned.numeronpf, npf.numero, npf.grupo_fin,
orm.cod_tp_orcamento, npd.classiforcamreduz,
npd.classiforcamcompl, npd.credor, npd.nomecredor,
npd.statusmovbancario, npd.natureza, npd.codigoretencao,
npd.dataemissao, npd.dt_etl, npd.servicobancario,
npd.bancobeneficiario, npd.agenciabeneficiario,
npd.contabeneficiario, npd.efeito,
("substring"((ned.classiforcamcompl)::text, 24, 8)),
("substring"((ned.classiforcamcompl)::text, 33, 2)),
npd.valor, npd.cpfcnpjcredor, npd.numeronpf,
npd.numeronpdordinario, nld.exerciciorestosapagar"
" Sort Method: external merge Disk: 731704kB"
" -> Hash Left Join (cost=104246.16..279334.81
rows=352018 width=287) (actual time=1322.332..6808.805
rows=2631834 loops=1)"
" Hash Cond: ((npd.exercicio = orm.exercicio) AND
(npd.unidadegestora = orm.unidadegestora) AND
((ned.classiforcamreduz)::integer = orm.classif_orcam_reduz))"
" -> Merge Left Join
(cost=100158.37..188918.69 rows=352018 width=290) (actual
time=1295.093..3653.514 rows=352018 loops=1)"
" Merge Cond: ((npd.unidadegestora =
nld.unidadegestora) AND (npd.exercicio = nld.exercicio) AND
(npd.numeronld = nld.numero))"
" -> Index Scan using
npd_nota_pagamento_despesa_idx_gestora_exercicio_numeronld on
npd_nota_pagamento_despesa npd (cost=0.00..82171.24
rows=352018 width=215) (actual time=0.009..1846.104
rows=352018 loops=1)"
" -> Sort (cost=100158.37..100655.53
rows=198865 width=83) (actual time=1295.075..1355.207
rows=364791 loops=1)"
" Sort Key: nld.unidadegestora,
nld.exercicio, nld.numero"
" Sort Method: quicksort Memory:
32689kB"
" -> Merge Left Join
(cost=54937.60..82656.83 rows=198865 width=83) (actual
time=749.878..980.454 rows=198865 loops=1)"
" Merge Cond:
((nld.unidadegestora = ned.unidadegestora) AND (nld.exercicio
= ned.exercicio) AND (nld.numeroned = ned.numero))"
" -> Index Scan using
nld_nota_liquidacao_despesa_idx_gestora_exercicio_numeroned on
nld_nota_liquidacao_despesa nld (cost=0.00..23405.46
rows=198865 width=21) (actual time=0.008..77.818 rows=198865
loops=1)"
" -> Sort
(cost=54937.55..55372.62 rows=174029 width=70) (actual
time=749.864..771.572 rows=200712 loops=1)"
" Sort Key:
ned.unidadegestora, ned.exercicio, ned.numero"
" Sort Method: quicksort
Memory: 30617kB"
" -> Merge Right
Join (cost=28198.62..39789.22 rows=174029 width=70) (actual
time=363.928..494.100 rows=174029 loops=1)"
" Merge Cond:
((npf.exercicio = ned.exercicio) AND (npf.unidadegestora =
ned.unidadegestora) AND (npf.numero = ned.numeronpf))"
" -> Index Scan
using npf_nota_programacao_financeira_idx on
npf_nota_programacao_financeira npf (cost=0.00..7688.10
rows=100339 width=15) (actual time=0.008..30.949 rows=100339
loops=1)"
" -> Sort
(cost=28198.62..28633.69 rows=174029 width=63) (actual
time=363.906..384.312 rows=174029 loops=1)"
" Sort Key:
ned.exercicio, ned.unidadegestora, ned.numeronpf"
" Sort
Method: quicksort Memory: 30617kB"
" -> Seq
Scan on ned_nota_empenho_despesa ned (cost=0.00..13050.29
rows=174029 width=63) (actual time=0.003..63.878 rows=174029
loops=1)"
" -> Hash (cost=2991.47..2991.47 rows=62647
width=14) (actual time=27.200..27.200 rows=62647 loops=1)"
" Buckets: 8192 Batches: 1 Memory Usage:
2937kB"
" -> Seq Scan on orcamentario_mensal
orm (cost=0.00..2991.47 rows=62647 width=14) (actual
time=0.006..15.844 rows=62647 loops=1)"
"Total runtime: 107298.538 ms"
--
Atenciosamente,
Luiz Henrique
"In
Medium Est Virtus!"
"A Virtude está no meio!"