Re: Upgrading from 9.2 to 9.3 causes performance degradation

Álvaro Nunes Lemos Melo <al_nunes@xxxxxxxxxxx> · Mon, 24 Mar 2014 23:32:23 -0300 (BRT)

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
alvaro@xxxxxxxxxxx   http://www.atua.com.br
(54) 9976-0106       (54) 3045-4144

De: "Jeff Janes" <jeff.janes@xxxxxxxxx>
Para: "Álvaro Nunes Lemos Melo" <al_nunes@xxxxxxxxxxx>
Cc: pgsql-general@xxxxxxxxxxxxxx
Enviadas: Segunda-feira, 24 de março de 2014 15:29:38
Assunto: Re:  Upgrading from 9.2 to 9.3 causes performance degradation

On Mon, Mar 24, 2014 at 7:45 AM, Álvaro Nunes Lemos Melo <al_nunes@xxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general