Euler, Segue anexo os planos da consulta. Obrigado pela ajuda
Em 31 de agosto de 2016 22:21, Euler Taveira <[email protected]> escreveu: > On 31-08-2016 20:22, Luiz Henrique wrote: > > Estou com problema de lentidão em uma determinada consulta no banco de > > produção (centos linux postgresql 9.1). Tempo de 1 minuto em produção. > > No ambiente de homologação leva cerca de 3s. O banco de produção é > > copiado diariamente para homologação (em homol eu faço : dropdb, > > createdb e pg_restore). Ambientes prod e homol praticamente iguais. > > Pergunta : > > > > o vacuum full analyze pode resolver meu problema ? > > > Sim. É como matar uma mosca com uma 12. > > > quando usar vacuum full analyze ? > > > (quase) nunca. Só em casos extremos de inchaço (não sei se esse é o seu > problema já que não apresentou informações). > > > alguma dica ou pista de como identificar a causa ? > > > Comece por mostrar os planos da consulta na produção e na homologação. > > > -- > Euler Taveira Timbira - http://www.timbira.com.br/ > PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento > _______________________________________________ > pgbr-geral mailing list > [email protected] > https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral -- Atenciosamente, Luiz Henrique “A coruja de Minerva alça seu vôo somente com o início do crepúsculo”. Friedrich Hegel
Pessoal, A consulta é essa: select distinct obt.id, obt.version , numero_obt as numeroObt, obt.data_cadastro as dataCadastroObt, data_tipo as dataTipo, valor_obt as valorObt, tob.nome as tipoOrdemBancariaNome, vps.nome as parceiro, fp.nome_razao_social as fornecedorParceiro, i.id as idInstrumento--, --max(acomp_gestor.data_cadastro) as ultimaAnaliseGestor, max(acomp_col.data_cadastro) as ultimaAnaliseColaborador from s2gpr_mcc.ordem_bancaria obt inner join s2gpr_mcc.instrumento i on i.id = obt.id_instrumento inner join s2gpr_mcc.tipo_ordem_bancaria tob on tob.id = obt.id_tipo_ordem_bancaria inner join s2gpr_mcc.vi_parceiro_situacao vps on vps.id = obt.id_parceiro left join s2gpr_mcc.fornecedor_parceiro fp on fp.id = obt.id_fornecedor_parceiro left join s2gpr_mcc.acompanhamento acomp_gestor on acomp_gestor.id_ordem_bancaria = obt.id and acomp_gestor.id_tipo_acompanhamento_ator = 1 left join s2gpr_mcc.acompanhamento acomp_col on acomp_col.id_ordem_bancaria = obt.id and acomp_col.id_tipo_acompanhamento_ator = 2 where i.numero = '968340' and id_situacao_ordem_bancaria = 5 and acomp_gestor.data_cadastro is null --group by obt.id, numero_obt, obt.data_cadastro, data_tipo, valor_obt, tob.nome, vps.nome, fp.nome_razao_social, i.id order by obt.id limit 5 offset 0 PLANO EM PRODUCAO "Limit (cost=23012.24..23012.27 rows=1 width=150) (actual time=131530.596..131530.614 rows=5 loops=1)" " -> Unique (cost=23012.24..23012.27 rows=1 width=150) (actual time=131530.596..131530.613 rows=5 loops=1)" " -> Sort (cost=23012.24..23012.25 rows=1 width=150) (actual time=131530.594..131530.595 rows=5 loops=1)" " Sort Key: obt.id, obt.version, obt.numero_obt, obt.data_cadastro, obt.data_tipo, obt.valor_obt, tob.nome, "*SELECT* 1".nome, fp.nome_razao_social, i.id" " Sort Method: quicksort Memory: 293kB" " -> Nested Loop Left Join (cost=3682.33..23012.23 rows=1 width=150) (actual time=168.595..131525.268 rows=1009 loops=1)" " Join Filter: (acomp_col.id_ordem_bancaria = obt.id)" " -> Nested Loop Left Join (cost=3682.33..21063.45 rows=1 width=150) (actual time=154.918..120925.273 rows=998 loops=1)" " -> Nested Loop (cost=3682.33..21063.13 rows=1 width=128) (actual time=154.896..120906.475 rows=998 loops=1)" " Join Filter: (obt.id_parceiro = "*SELECT* 1".id)" " -> Nested Loop (cost=1570.57..3430.41 rows=1 width=105) (actual time=23.695..43.001 rows=998 loops=1)" " -> Hash Right Join (cost=1570.57..3430.13 rows=1 width=80) (actual time=23.679..26.522 rows=998 loops=1)" " Hash Cond: (acomp_gestor.id_ordem_bancaria = obt.id)" " Filter: (acomp_gestor.data_cadastro IS NULL)" " -> Seq Scan on acompanhamento acomp_gestor (cost=0.00..1816.54 rows=11463 width=16) (actual time=0.006..10.454 rows=11602 loops=1)" " Filter: (id_tipo_acompanhamento_ator = 1)" " -> Hash (cost=1570.45..1570.45 rows=10 width=80) (actual time=11.260..11.260 rows=2619 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 318kB" " -> Hash Join (cost=8.28..1570.45 rows=10 width=80) (actual time=0.454..10.207 rows=2619 loops=1)" " Hash Cond: (obt.id_instrumento = i.id)" " -> Seq Scan on ordem_bancaria obt (cost=0.00..1488.89 rows=19514 width=84) (actual time=0.021..6.262 rows=19614 loops=1)" " Filter: (id_situacao_ordem_bancaria = 5)" " -> Hash (cost=8.27..8.27 rows=1 width=4) (actual time=0.027..0.027 rows=1 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 1kB" " -> Index Scan using instrumento_numero_key on instrumento i (cost=0.00..8.27 rows=1 width=4) (actual time=0.023..0.024 rows=1 loops=1)" " Index Cond: ((numero)::text = '968340'::text)" " -> Index Scan using tipo_ordem_bancaria_pkey on tipo_ordem_bancaria tob (cost=0.00..0.27 rows=1 width=37) (actual time=0.009..0.012 rows=1 loops=998)" " Index Cond: (id = obt.id_tipo_ordem_bancaria)" " -> Append (cost=2111.76..17546.54 rows=6894 width=39) (actual time=2.306..120.651 rows=3447 loops=998)" " -> Subquery Scan on "*SELECT* 1" (cost=2111.76..11832.11 rows=3447 width=35) (actual time=2.306..92.414 rows=2027 loops=998)" " -> Hash Join (cost=2111.76..11797.64 rows=3447 width=111) (actual time=2.305..92.133 rows=2027 loops=998)" " Hash Cond: (p.id_tipo_classificacao = tc.id)" " -> Hash Join (cost=2110.56..11697.34 rows=3447 width=91) (actual time=2.298..90.849 rows=2027 loops=998)" " Hash Cond: (pf.id = p.id_pessoa)" " -> Seq Scan on pessoa_fisica pf (cost=0.00..7653.73 rows=303773 width=47) (actual time=0.003..27.918 rows=303774 loops=998)" " -> Hash (cost=2067.47..2067.47 rows=3447 width=60) (actual time=4.164..4.164 rows=3447 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 367kB" " -> Seq Scan on parceiro p (cost=0.00..2067.47 rows=3447 width=60) (actual time=0.006..2.956 rows=3447 loops=1)" " -> Hash (cost=1.09..1.09 rows=9 width=36) (actual time=0.011..0.011 rows=9 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 1kB" " -> Seq Scan on tipo_classificacao tc (cost=0.00..1.09 rows=9 width=36) (actual time=0.004..0.006 rows=9 loops=1)" " -> Subquery Scan on "*SELECT* 2" (cost=2114.40..5714.42 rows=3447 width=43) (actual time=0.029..27.866 rows=1420 loops=998)" " -> Hash Join (cost=2114.40..5679.95 rows=3447 width=157) (actual time=0.028..27.648 rows=1420 loops=998)" " Hash Cond: (p.id_tipo_classificacao = tc.id)" " -> Hash Join (cost=2113.20..5571.03 rows=3447 width=137) (actual time=0.016..26.603 rows=1420 loops=998)" " Hash Cond: (pj.id_natureza_juridica = nj.id)" " -> Hash Join (cost=2110.56..5520.99 rows=3447 width=109) (actual time=0.013..26.148 rows=1420 loops=998)" " Hash Cond: (pj.id = p.id_pessoa)" " -> Seq Scan on pessoa_juridica pj (cost=0.00..2920.21 rows=72921 width=65) (actual time=0.003..7.800 rows=72921 loops=998)" " -> Hash (cost=2067.47..2067.47 rows=3447 width=60) (actual time=4.880..4.880 rows=3447 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 367kB" " -> Seq Scan on parceiro p (cost=0.00..2067.47 rows=3447 width=60) (actual time=0.007..3.471 rows=3447 loops=1)" " -> Hash (cost=1.73..1.73 rows=73 width=44) (actual time=0.045..0.045 rows=73 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 6kB" " -> Seq Scan on natureza_juridica nj (cost=0.00..1.73 rows=73 width=44) (actual time=0.004..0.024 rows=73 loops=1)" " -> Hash (cost=1.09..1.09 rows=9 width=36) (actual time=0.010..0.010 rows=9 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 1kB" " -> Seq Scan on tipo_classificacao tc (cost=0.00..1.09 rows=9 width=36) (actual time=0.003..0.004 rows=9 loops=1)" " -> Index Scan using fornecedor_parceiro_pkey on fornecedor_parceiro fp (cost=0.00..0.31 rows=1 width=34) (actual time=0.012..0.013 rows=1 loops=998)" " Index Cond: (id = obt.id_fornecedor_parceiro)" " -> Seq Scan on acompanhamento acomp_col (cost=0.00..1816.54 rows=10580 width=8) (actual time=0.004..9.664 rows=10797 loops=998)" " Filter: (id_tipo_acompanhamento_ator = 2)" "Total runtime: 131530.953 ms" **************************************** EM HOMOL ***************************************** "Limit (cost=16248.11..16248.14 rows=1 width=335) (actual time=885.886..885.897 rows=5 loops=1)" " -> Unique (cost=16248.11..16248.14 rows=1 width=335) (actual time=885.884..885.894 rows=5 loops=1)" " -> Sort (cost=16248.11..16248.12 rows=1 width=335) (actual time=885.883..885.884 rows=5 loops=1)" " Sort Key: obt.id, obt.version, obt.numero_obt, obt.data_cadastro, obt.data_tipo, obt.valor_obt, tob.nome, "*SELECT* 1".nome, fp.nome_razao_social, i.id" " Sort Method: quicksort Memory: 293kB" " -> Nested Loop (cost=2736.02..16248.10 rows=1 width=335) (actual time=689.366..884.991 rows=1010 loops=1)" " Join Filter: (obt.id_parceiro = "*SELECT* 1".id)" " -> Append (cost=224.76..12610.54 rows=6894 width=39) (actual time=5.265..135.792 rows=3447 loops=1)" " -> Subquery Scan on "*SELECT* 1" (cost=224.76..9781.11 rows=3447 width=35) (actual time=5.265..105.439 rows=2027 loops=1)" " -> Hash Join (cost=224.76..9746.64 rows=3447 width=601) (actual time=5.264..105.157 rows=2027 loops=1)" " Hash Cond: (p.id_tipo_classificacao = tc.id)" " -> Hash Join (cost=223.56..9646.34 rows=3447 width=93) (actual time=5.233..103.345 rows=2027 loops=1)" " Hash Cond: (pf.id = p.id_pessoa)" " -> Seq Scan on pessoa_fisica pf (cost=0.00..7489.73 rows=303773 width=47) (actual time=0.008..47.919 rows=303773 loops=1)" " -> Hash (cost=180.47..180.47 rows=3447 width=62) (actual time=2.697..2.697 rows=3447 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 378kB" " -> Seq Scan on parceiro p (cost=0.00..180.47 rows=3447 width=62) (actual time=0.084..1.633 rows=3447 loops=1)" " -> Hash (cost=1.09..1.09 rows=9 width=524) (actual time=0.018..0.018 rows=9 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 1kB" " -> Seq Scan on tipo_classificacao tc (cost=0.00..1.09 rows=9 width=524) (actual time=0.007..0.009 rows=9 loops=1)" " -> Subquery Scan on "*SELECT* 2" (cost=227.40..2829.42 rows=3447 width=43) (actual time=2.363..29.909 rows=1420 loops=1)" " -> Hash Join (cost=227.40..2794.95 rows=3447 width=647) (actual time=2.363..29.632 rows=1420 loops=1)" " Hash Cond: (p.id_tipo_classificacao = tc.id)" " -> Hash Join (cost=226.20..2686.03 rows=3447 width=139) (actual time=2.310..28.139 rows=1420 loops=1)" " Hash Cond: (pj.id_natureza_juridica = nj.id)" " -> Hash Join (cost=223.56..2635.99 rows=3447 width=111) (actual time=2.254..27.527 rows=1420 loops=1)" " Hash Cond: (pj.id = p.id_pessoa)" " -> Seq Scan on pessoa_juridica pj (cost=0.00..1922.21 rows=72921 width=65) (actual time=0.006..11.789 rows=72921 loops=1)" " -> Hash (cost=180.47..180.47 rows=3447 width=62) (actual time=2.236..2.236 rows=3447 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 378kB" " -> Seq Scan on parceiro p (cost=0.00..180.47 rows=3447 width=62) (actual time=0.021..1.206 rows=3447 loops=1)" " -> Hash (cost=1.73..1.73 rows=73 width=44) (actual time=0.046..0.046 rows=73 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 6kB" " -> Seq Scan on natureza_juridica nj (cost=0.00..1.73 rows=73 width=44) (actual time=0.007..0.021 rows=73 loops=1)" " -> Hash (cost=1.09..1.09 rows=9 width=524) (actual time=0.012..0.012 rows=9 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 1kB" " -> Seq Scan on tipo_classificacao tc (cost=0.00..1.09 rows=9 width=524) (actual time=0.003..0.005 rows=9 loops=1)" " -> Materialize (cost=2511.26..3534.16 rows=1 width=312) (actual time=0.008..0.072 rows=1010 loops=3447)" " -> Nested Loop Left Join (cost=2511.26..3534.15 rows=1 width=312) (actual time=27.955..36.956 rows=1010 loops=1)" " -> Nested Loop (cost=2511.26..3533.85 rows=1 width=290) (actual time=27.938..34.757 rows=1010 loops=1)" " -> Hash Right Join (cost=2511.26..3533.57 rows=1 width=80) (actual time=27.891..32.761 rows=1010 loops=1)" " Hash Cond: (acomp_col.id_ordem_bancaria = obt.id)" " -> Seq Scan on acompanhamento acomp_col (cost=0.00..982.62 rows=10580 width=8) (actual time=0.005..4.185 rows=10580 loops=1)" " Filter: (id_tipo_acompanhamento_ator = 2)" " -> Hash (cost=2511.25..2511.25 rows=1 width=80) (actual time=27.118..27.118 rows=999 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 122kB" " -> Hash Right Join (cost=1485.57..2511.25 rows=1 width=80) (actual time=26.458..26.762 rows=999 loops=1)" " Hash Cond: (acomp_gestor.id_ordem_bancaria = obt.id)" " Filter: (acomp_gestor.data_cadastro IS NULL)" " -> Seq Scan on acompanhamento acomp_gestor (cost=0.00..982.62 rows=11470 width=16) (actual time=0.015..9.139 rows=11471 loops=1)" " Filter: (id_tipo_acompanhamento_ator = 1)" " -> Hash (cost=1485.45..1485.45 rows=10 width=80) (actual time=15.357..15.357 rows=2619 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 318kB" " -> Hash Join (cost=8.28..1485.45 rows=10 width=80) (actual time=0.649..14.366 rows=2619 loops=1)" " Hash Cond: (obt.id_instrumento = i.id)" " -> Seq Scan on ordem_bancaria obt (cost=0.00..1403.89 rows=19514 width=84) (actual time=0.031..10.240 rows=19497 loops=1)" " Filter: (id_situacao_ordem_bancaria = 5)" " -> Hash (cost=8.27..8.27 rows=1 width=4) (actual time=0.032..0.032 rows=1 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 1kB" " -> Index Scan using instrumento_numero_key on instrumento i (cost=0.00..8.27 rows=1 width=4) (actual time=0.031..0.031 rows=1 loops=1)" " Index Cond: ((numero)::text = '968340'::text)" " -> Index Scan using tipo_ordem_bancaria_pkey on tipo_ordem_bancaria tob (cost=0.00..0.27 rows=1 width=222) (actual time=0.001..0.001 rows=1 loops=1010)" " Index Cond: (id = obt.id_tipo_ordem_bancaria)" " -> Index Scan using fornecedor_parceiro_pkey on fornecedor_parceiro fp (cost=0.00..0.29 rows=1 width=34) (actual time=0.001..0.002 rows=1 loops=1010)" " Index Cond: (id = obt.id_fornecedor_parceiro)" "Total runtime: 886.386 ms" *************************************************** SELECT ***************************************************
_______________________________________________ pgbr-geral mailing list [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
