Jeff: I ran a VACUUM FULL ANALYZE and retested. No significative change was notices, the explain is availiable in
9.3 - After VACUUM FULL ANALYZE - http://explain.depesz.com/s/rVoW 9.3 - Original - http://explain.depesz.com/s/Vwt Adrian: I'll try to send the query attached. My doubts are: why 9.3 is slower then 9.2 with the same database/configuration/harware/query? Even worst, why 9.3 got slower results on a better HW, where 9.2 improved on the same scenario. I've just ran the explains on my new server, links below: 9.2 - http://explain.depesz.com/s/AeD 9.3 - http://explain.depesz.com/s/1sp For comparison sake, old server: 9.2 - http://explain.depesz.com/s/bXIo 9.3 - http://explain.depesz.com/s/Vwt I really appreciate your help. Thanks again, -- Álvaro Nunes Melo Atua Sistemas de Informação alv...@atua.com.br http://www.atua.com.br (54) 9976-0106 (54) 3045-4144 ----- Mensagem original ----- De: "Jeff Janes" <jeff.ja...@gmail.com> Para: "Álvaro Nunes Lemos Melo" <al_nu...@atua.com.br> Cc: pgsql-general@postgresql.org Enviadas: Segunda-feira, 24 de março de 2014 15:29:38 Assunto: Re: [GENERAL] Upgrading from 9.2 to 9.3 causes performance degradation On Mon, Mar 24, 2014 at 7:45 AM, Álvaro Nunes Lemos Melo < al_nu...@atua.com.br > wrote: ==> Venkata > After the migration to hardware and to the new version 9.3, any changes have > been done in the postgresql.conf compared to the old settings ? No, as I wrote initially, I'd double checked and the four conf files have the same parameters. > Any maintenance activities (VACUUM or VACUUM FULL and ANALYZE) have been > performed after the migration ? ANALYZE yes. VACUUM or VACUUM FULL no, because I ran the test on recently restores and unchanged databases. How did you do the restore? Please VACUUM and retest. At best it will fix the problem, and if it doesn't it will at least rule out a bunch of possibilities for the source of the problem. Cheers, Jeff
SET statement_timeout=0; EXPLAIN (ANALYZE, BUFFERS) SELECT cd_unidade, cd_centro_custo, cd_pessoa, dt_mes, cd_pessoa_matriz, cd_pessoa_filial, vl_corretor, SUM(vl_supervisor + vl_gerente + vl_agenciador + vl_manifestador + vl_corretor - vl_corretor ) AS vl_comissao FROM ( SELECT acc.cd_unidade, acc.cd_centro_custo, ccp.cd_pessoa, (SUBSTR(ct.dt_emissao::TEXT, 1, 8) || '01') AS dt_mes, ct.cd_pessoa_matriz, ct.cd_pessoa_filial, SUM(ccp.vl_supervisor) AS vl_supervisor, SUM(0) AS vl_gerente, SUM(0) AS vl_agenciador, SUM(0) AS vl_manifestador, SUM(0) AS vl_corretor FROM ctrc ct JOIN ctrc_comissao_pessoa ccp ON ccp.cd_ctrc = ct.cd_ctrc JOIN ctrc_comissao cc ON cc.cd_ctrc = ccp.cd_ctrc JOIN pessoa p ON p.cd_pessoa = ccp.cd_pessoa JOIN salario s ON s.cd_pessoa = ccp.cd_pessoa LEFT OUTER JOIN funcionario f ON f.cd_pessoa = s.cd_pessoa JOIN agencia_centro_custo acc ON acc.cd_agencia = cc.cd_agencia_supervisor WHERE ct.dt_cancelamento IS NULL AND ct.id_operacao IN (3, 1) AND ct.dt_emissao >= (SELECT (p.dt_ultimo_fechamento_salario + INTERVAL '1 month') FROM pessoa p_ WHERE p_.cd_pessoa = p.cd_pessoa)::DATE AND ct.dt_emissao <= '2014-02-28' AND acc.dt_vigencia = (SELECT acc2.dt_vigencia FROM agencia_centro_custo acc2 WHERE acc2.cd_agencia = acc.cd_agencia AND acc2.dt_vigencia <= ct.dt_emissao ORDER BY acc2.dt_vigencia DESC LIMIT 1) AND s.id_pagamento >= 0 AND s.id_pagamento != 1 AND s.dt_vigencia = (SELECT s2.dt_vigencia FROM salario s2 WHERE s2.cd_pessoa = s.cd_pessoa AND s2.dt_vigencia <= '2014-02-28' ORDER BY s2.dt_vigencia DESC LIMIT 1) AND (f.dt_admissao <= '2014-02-28' OR f.dt_admissao IS NULL) AND (f.dt_demissao > '2014-02-28' OR f.dt_demissao IS NULL OR f.dt_demissao >= ct.dt_emissao) AND ccp.vl_supervisor != 0 GROUP BY 1, 2, 3, 4, 5, 6 UNION ALL SELECT acc.cd_unidade, acc.cd_centro_custo, ccp.cd_pessoa, (SUBSTR(ct.dt_emissao::TEXT, 1, 8) || '01') AS dt_mes, ct.cd_pessoa_matriz, ct.cd_pessoa_filial, SUM(0), SUM(ccp.vl_gerente), SUM(0), SUM(0), SUM(0) FROM ctrc ct JOIN ctrc_comissao_pessoa ccp ON ccp.cd_ctrc = ct.cd_ctrc JOIN ctrc_comissao cc ON cc.cd_ctrc = ccp.cd_ctrc JOIN pessoa p ON p.cd_pessoa = ccp.cd_pessoa JOIN salario s ON s.cd_pessoa = ccp.cd_pessoa LEFT OUTER JOIN funcionario f ON f.cd_pessoa = s.cd_pessoa JOIN agencia_centro_custo acc ON acc.cd_agencia = cc.cd_agencia_gerente WHERE ct.dt_cancelamento IS NULL AND ct.id_operacao IN (3, 1) AND ct.dt_emissao >= (SELECT (p.dt_ultimo_fechamento_salario + INTERVAL '1 month') FROM pessoa p_ WHERE p_.cd_pessoa = p.cd_pessoa)::DATE AND ct.dt_emissao <= '2014-02-28' AND acc.dt_vigencia = (SELECT acc2.dt_vigencia FROM agencia_centro_custo acc2 WHERE acc2.cd_agencia = acc.cd_agencia AND acc2.dt_vigencia <= ct.dt_emissao ORDER BY acc2.dt_vigencia DESC LIMIT 1) AND s.id_pagamento >= 0 AND s.id_pagamento != 1 AND s.dt_vigencia = (SELECT s2.dt_vigencia FROM salario s2 WHERE s2.cd_pessoa = s.cd_pessoa AND s2.dt_vigencia <= '2014-02-28' ORDER BY s2.dt_vigencia DESC LIMIT 1) AND (f.dt_admissao <= '2014-02-28' OR f.dt_admissao IS NULL) AND (f.dt_demissao > '2014-02-28' OR f.dt_demissao IS NULL OR f.dt_demissao >= ct.dt_emissao) AND ccp.vl_gerente != 0 GROUP BY 1, 2, 3, 4, 5, 6 UNION ALL SELECT acc.cd_unidade, acc.cd_centro_custo, ccp.cd_pessoa, (SUBSTR(ct.dt_emissao::TEXT, 1, 8) || '01') AS dt_mes, ct.cd_pessoa_matriz, ct.cd_pessoa_filial, SUM(0), SUM(0), SUM(ccp.vl_agenciador), SUM(0), SUM(0) FROM ctrc ct JOIN ctrc_comissao_pessoa ccp ON ccp.cd_ctrc = ct.cd_ctrc JOIN ctrc_comissao cc ON cc.cd_ctrc = ccp.cd_ctrc JOIN pessoa p ON p.cd_pessoa = ccp.cd_pessoa JOIN salario s ON s.cd_pessoa = ccp.cd_pessoa LEFT OUTER JOIN funcionario f ON f.cd_pessoa = s.cd_pessoa JOIN agencia_centro_custo acc ON acc.cd_agencia = cc.cd_agencia_agenciador WHERE ct.dt_cancelamento IS NULL AND ct.id_operacao IN (3, 1) AND ct.dt_emissao >= (SELECT (p.dt_ultimo_fechamento_salario + INTERVAL '1 month') FROM pessoa p_ WHERE p_.cd_pessoa = p.cd_pessoa)::DATE AND ct.dt_emissao <= '2014-02-28' AND acc.dt_vigencia = (SELECT acc2.dt_vigencia FROM agencia_centro_custo acc2 WHERE acc2.cd_agencia = acc.cd_agencia AND acc2.dt_vigencia <= ct.dt_emissao ORDER BY acc2.dt_vigencia DESC LIMIT 1) AND s.id_pagamento >= 0 AND s.id_pagamento != 1 AND s.dt_vigencia = (SELECT s2.dt_vigencia FROM salario s2 WHERE s2.cd_pessoa = s.cd_pessoa AND s2.dt_vigencia <= '2014-02-28' ORDER BY s2.dt_vigencia DESC LIMIT 1) AND (f.dt_admissao <= '2014-02-28' OR f.dt_admissao IS NULL) AND (f.dt_demissao > '2014-02-28' OR f.dt_demissao IS NULL OR f.dt_demissao >= ct.dt_emissao) AND ccp.vl_agenciador != 0 GROUP BY 1, 2, 3, 4, 5, 6 UNION ALL SELECT acc.cd_unidade, acc.cd_centro_custo, ccp.cd_pessoa, (SUBSTR(ct.dt_emissao::TEXT, 1, 8) || '01') AS dt_mes, ct.cd_pessoa_matriz, ct.cd_pessoa_filial, SUM(0), SUM(0), SUM(0), SUM(ccp.vl_manifestador), SUM(0) FROM ctrc ct JOIN ctrc_comissao_pessoa ccp ON ccp.cd_ctrc = ct.cd_ctrc JOIN ctrc_comissao cc ON cc.cd_ctrc = ccp.cd_ctrc JOIN pessoa p ON p.cd_pessoa = ccp.cd_pessoa JOIN salario s ON s.cd_pessoa = ccp.cd_pessoa LEFT OUTER JOIN funcionario f ON f.cd_pessoa = s.cd_pessoa JOIN agencia_centro_custo acc ON acc.cd_agencia = cc.cd_agencia_manifestador WHERE ct.dt_cancelamento IS NULL AND ct.id_operacao IN (3, 1) AND ct.dt_emissao >= (SELECT (p.dt_ultimo_fechamento_salario + INTERVAL '1 month') FROM pessoa p_ WHERE p_.cd_pessoa = p.cd_pessoa)::DATE AND ct.dt_emissao <= '2014-02-28' AND acc.dt_vigencia = (SELECT acc2.dt_vigencia FROM agencia_centro_custo acc2 WHERE acc2.cd_agencia = acc.cd_agencia AND acc2.dt_vigencia <= ct.dt_emissao ORDER BY acc2.dt_vigencia DESC LIMIT 1) AND s.id_pagamento >= 0 AND s.id_pagamento != 1 AND s.dt_vigencia = (SELECT s2.dt_vigencia FROM salario s2 WHERE s2.cd_pessoa = s.cd_pessoa AND s2.dt_vigencia <= '2014-02-28' ORDER BY s2.dt_vigencia DESC LIMIT 1) AND (f.dt_admissao <= '2014-02-28' OR f.dt_admissao IS NULL) AND (f.dt_demissao > '2014-02-28' OR f.dt_demissao IS NULL OR f.dt_demissao >= ct.dt_emissao) AND ccp.vl_manifestador != 0 GROUP BY 1, 2, 3, 4, 5, 6 UNION ALL SELECT acc.cd_unidade, acc.cd_centro_custo, ccor.cd_pessoa_corretor, (SUBSTR(ct.dt_emissao::TEXT, 1, 8) || '01') AS dt_mes, ct.cd_pessoa_matriz, ct.cd_pessoa_filial, SUM(0), SUM(0), SUM(0), SUM(0), SUM(ccor.vl_total_corretor) FROM ctrc ct JOIN ctrc_corretor ccor ON ccor.cd_ctrc = ct.cd_ctrc JOIN agencia_centro_custo acc ON acc.cd_agencia = ct.cd_agencia JOIN pessoa p ON p.cd_pessoa = ccor.cd_pessoa_corretor JOIN corretor c ON c.cd_pessoa = p.cd_pessoa AND c.id_atualiza_rh = 1 LEFT OUTER JOIN carta_frete cf ON cf.cd_ctrc = ct.cd_ctrc LEFT OUTER JOIN ctrc_icms ci ON ci.cd_ctrc = ct.cd_ctrc WHERE ct.dt_cancelamento IS NULL AND ct.id_operacao IN (3, 1) AND ct.dt_emissao >= (SELECT (p.dt_ultimo_fechamento_salario + INTERVAL '1 month') FROM pessoa p_ WHERE p_.cd_pessoa = p.cd_pessoa)::DATE AND ct.dt_emissao <= '2014-02-28' AND acc.dt_vigencia = (SELECT acc2.dt_vigencia FROM agencia_centro_custo acc2 WHERE acc2.cd_agencia = acc.cd_agencia AND acc2.dt_vigencia <= ct.dt_emissao ORDER BY acc2.dt_vigencia DESC LIMIT 1) GROUP BY 1, 2, 3, 4, 5, 6 ) AS tmp_comissao GROUP BY 1, 2, 3, 4, 5, 6 , 7 ORDER BY 1, 2, 3, 4
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
