Postgres Pro
Downloads
Home   >   mailing lists

Re: Upgrading from 9.2 to 9.3 causes performance degradation - Mailing list pgsql-general

From Alfonso Afonso
Subject Re: Upgrading from 9.2 to 9.3 causes performance degradation
Date
Msg-id F4F73265-BB93-42BA-A8AD-B0097DF990FD@gmail.com
Whole thread Raw
In response to Re: Upgrading from 9.2 to 9.3 causes performance degradation  (Álvaro Nunes Lemos Melo <al_nunes@atua.com.br>)
List pgsql-general
Tree view
Hi Alvaro

I had a look to the explain analyze you sent and after step 13 you have some differences between both planners.

At 21st step you have a sequence scan on 9.3 instead of a index scan on 19th step (9.2)

Following the process, you can find more differences too (45 & 43, seq scan salario again on subquery)

Those differences in the planner are maybe the reason, and why it happend could be the configuration for join and sort methods,  the statistics stored on 9.2 (more even if that is a real process server, the system has been "learning" during this time) or some other related.

I am not pretty sure what is, but the explain you sent is telling you that both server are not doing the same work, so it may be reasonable to obtain differences among servers.

Good luck with the issue

Regards

Alfonso

El 25/03/2014, a las 02:32, Álvaro Nunes Lemos Melo <al_nunes@atua.com.br> escribió:

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

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:


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

De: "Jeff Janes" <jeff.janes@gmail.com>
Para: "Álvaro Nunes Lemos Melo" <al_nunes@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_nunes@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

<salario.sql>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Alfonso Afonso






pgsql-general by date:

Previous
From: John Smith
Date:
Subject: Re: tsearch2: how to plainto_tsquery() with "|"
Next
From: Carlos Espejo
Date:
Subject: Disk Encryption in Production