Acredito que o amigo sugeriu o analyze por ser um procedimento padrão após restore, conforme menciona a documentação:
"After restoring a backup, it is wise to run ANALYZE <http://www.postgresql.org/docs/9.4/static/sql-analyze.html> on each database so the query optimizer has useful statistics" http://www.postgresql.org/docs/9.4/static/backup-dump.html#BACKUP-DUMP-RESTORE De qualquer forma, você pode verificar as estatísticas das tabelas pela view pg_stat_user_tables (ou por sua: variante: pg_stat_all_tables). Abraço! 2016-04-04 17:47 GMT-03:00 Márcio A. Sepp <[email protected]>: > > Bom dia, > > > > > > Atualizei um servidor que estava utilizando a versão 9.0 para a 9.4.7 > > e após atualização esta query passou a ficar extremamente lenta. > > > > SQL: > > select movto_lote.nr_dcto > > from fin_receber_parc parc > > inner join fin_receber receber on (receber.cd_movto = parc.cd_movto_rec > and > > receber.cd_serie_lcto = > > parc.cd_serie_lcto) > > inner join fin_receber_cnt cnt on (cnt.cd_movto_rec= receber.cd_movto and > > > > cnt.cd_serie_lcto=receber.cd_serie_lcto) > > inner join cnt_movto movto on (cnt.cd_movto=movto.cd_movto and > > movto.cd_serie_lcto=cnt.cd_serie_lcto) > > inner join cnt_movto_lote movto_lote on (movto_lote.cd_movto = > > movto.cd_movto_lote and > > movto_lote.cd_serie_lcto = > > movto.cd_serie_lcto) > > inner join cnt_competencia competencia on (competencia.cd_competencia > > = > > movto_lote.cd_competencia) > > inner join uni_emitente emitente on (emitente.cd_emitente = > > receber.cd_devedor) > > inner join fin_especie especie on (especie.cd_especie = > > parc.cd_especie ) where upper(coalesce(movto_lote.ds_historico, '%')) > like upper('%%') and > > upper(coalesce(movto_lote.nr_dcto, '%')) like upper('%%') and > > upper(coalesce(competencia.nm_competencia, '%')) like upper('%%') > and > > upper(coalesce(emitente.nm_emitentecompleto, '%')) like > > upper('%consumidor%') and > > coalesce(parc.vl_valor, 0) between coalesce(0, 0) and > > coalesce(999999.99, 999999999) and > > coalesce(receber.cd_devedor, 0) between coalesce(0, 0) and > > coalesce(999999999, 999999999) and > > movto_lote.dt_movto between ('01/01/2000') and ('01/03/9999') and > > parc.dt_vcto between ('01/01/2000') and ('31/12/9999') and > > parc.st_quitado = false and > > movto_lote.cd_estabelecimento=1 > > order by parc.dt_vcto desc > > > > > > Saída do Explain Analyze: > > "Sort (cost=4779.91..4779.91 rows=1 width=16) (actual > > time=128286.342..128286.342 rows=6 loops=1)" > > " Sort Key: parc.dt_vcto DESC" > > " Sort Method: quicksort Memory: 25kB" > > " -> Nested Loop (cost=1.96..4779.90 rows=1 width=16) (actual > > time=126064.707..128286.279 rows=6 loops=1)" > > " Join Filter: (parc.cd_especie = especie.cd_especie)" > > " Rows Removed by Join Filter: 36" > > " -> Nested Loop (cost=1.96..4778.74 rows=1 width=18) (actual > > time=126064.672..128286.198 rows=6 loops=1)" > > " -> Nested Loop (cost=1.68..4777.55 rows=1 width=22) > (actual > > time=64031.477..128247.981 rows=3189 loops=1)" > > " Join Filter: (movto_lote.cd_competencia = > > competencia.cd_competencia)" > > " Rows Removed by Join Filter: 15945" > > " -> Nested Loop (cost=1.68..4776.37 rows=1 > width=26) > > (actual time=64031.432..128164.285 rows=3189 loops=1)" > > " Join Filter: (receber.cd_serie_lcto = > > movto_lote.cd_serie_lcto)" > > " -> Nested Loop (cost=1.26..4775.86 rows=1 > > width=26) (actual time=0.085..128005.877 rows=6277 loops=1)" > > " Join Filter: (receber.cd_serie_lcto = > > movto.cd_serie_lcto)" > > " -> Nested Loop (cost=0.83..4774.95 > rows=1 > > width=24) (actual time=0.078..127775.182 rows=6277 loops=1)" > > " -> Nested Loop > (cost=0.42..3012.16 > > rows=1 width=30) (actual time=0.067..214.384 rows=6318 loops=1)" > > " -> Seq Scan on > > fin_receber_parc parc (cost=0.00..2745.68 rows=32 width=16) (actual > > time=0.052..91.802 rows=6318 loops=1)" > > " Filter: ((NOT > st_quitado) > > AND (COALESCE(vl_valor, '0'::numeric) >= '0'::numeric) AND > > (COALESCE(vl_valor, '0'::numeric) <= 999999.99) AND (dt_vcto >= > > '2000-01-01'::date) AND (dt_vcto <= '9999-12-31'::date))" > > " Rows Removed by > Filter: > > 81216" > > " -> Index Scan using > > "pk-fin_receber-geral" on fin_receber receber (cost=0.42..8.32 rows=1 > > width=14) (actual time=0.013..0.014 rows=1 loops=6318)" > > " Index Cond: > ((cd_movto = > > parc.cd_movto_rec) AND (cd_serie_lcto = parc.cd_serie_lcto))" > > " Filter: > > ((COALESCE(cd_devedor, 0) >= 0) AND (COALESCE(cd_devedor, 0) <= > 999999999))" > > " -> Index Scan using > > "pk-fin_receber_cnt-cd_movto-cd_serie_lcto" on fin_receber_cnt cnt > > (cost=0.42..1762.78 rows=1 width=18) (actual time=7.918..20.187 rows=1 > > loops=6318)" > > " Index Cond: (cd_serie_lcto = > > receber.cd_serie_lcto)" > > " Filter: (receber.cd_movto = > > cd_movto_rec)" > > " Rows Removed by Filter: > 87707" > > " -> Index Scan using > > "pk-cnt_movto-cd_movto-cd_serie_lcto" on cnt_movto movto > > (cost=0.42..0.90 > > rows=1 width=18) (actual time=0.031..0.031 rows=1 loops=6277)" > > " Index Cond: ((cd_movto = > > cnt.cd_movto) AND (cd_serie_lcto = cnt.cd_serie_lcto))" > > " -> Index Scan using > "pk-cnt_movto_lote-geral" on > > cnt_movto_lote movto_lote (cost=0.42..0.50 rows=1 width=26) (actual > > time=0.022..0.023 rows=1 loops=6277)" > > " Index Cond: ((cd_movto = > > movto.cd_movto_lote) AND (cd_serie_lcto = movto.cd_serie_lcto))" > > " Filter: ((dt_movto >= '2000-01-01 > > 00:00:00-02'::timestamp with time zone) AND (dt_movto <= '9999-03-01 > > 00:00:00-03'::timestamp with time zone) AND (cd_estabelecimento = 1) > > AND (upper((COALESCE(ds_historico, '%'::character va (...)" > > " Rows Removed by Filter: 0" > > " -> Seq Scan on cnt_competencia competencia > > (cost=0.00..1.11 rows=6 width=4) (actual time=0.006..0.012 rows=6 > > loops=3189)" > > " Filter: (upper((COALESCE(nm_competencia, > > '%'::bpchar))::text) ~~ '%%'::text)" > > " -> Index Scan using "pk-uni_emitente-cd_emitente" on > > uni_emitente emitente (cost=0.28..1.18 rows=1 width=4) (actual > > time=0.010..0.010 rows=0 loops=3189)" > > " Index Cond: (cd_emitente = receber.cd_devedor)" > > " Filter: (upper((COALESCE(nm_emitentecompleto, > > '%'::character varying))::text) ~~ '%CONSUMIDOR%'::text)" > > " Rows Removed by Filter: 1" > > " -> Seq Scan on fin_especie especie (cost=0.00..1.07 rows=7 > > width=4) (actual time=0.005..0.006 rows=7 loops=6)" > > "Planning time: 22.367 ms" > > "Execution time: 128286.670 ms" > > > > > > Pelo que estou entendendo o problema está no fin_receber_cnt. Mas não > > to achando o furo. > > Observem que na versão 9.0 estava funcionando de forma satisfatoria. > > > > > Atualize as estatísticas com o comando ANALYZE. > http://www.postgresql.org/docs/9.4/interactive/sql-analyze.html > > > Por favor, me explique como vc chegou a esta conclusão? diz algo aí que as > estatísticas estão desatualizadas? (eu não manjo muito da saída do analyze > e to me batendo nisso a horas). > Mas eu havia feito todas as manutenções antes de enviar a saída do analyze > e inclusive peguei o banco e subi numa máquina minha e continua na mesma > lentidão. > > Talvez precise que eu envie mais alguma informação pra ajudar? > > > _______________________________________________ > pgbr-geral mailing list > [email protected] > https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral >
_______________________________________________ pgbr-geral mailing list [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
