Re: Upgrading from 9.2 to 9.3 causes performance degradation

On 03/24/2014 07:45 AM, Álvaro Nunes Lemos Melo wrote:
> Firstly, I'd like to thanks all of you guys for your help. Below, I'll try to provide all the information you asked.
Iknow the query I'm testing could be improved, but I used it because it's not that fast that is hard to measure neither
soslow (another report query took about 4 minutes on 9.2 and now takes 16 minutes on 9.3) that would take the whole day
torun tests. 
> And again, performance on 9.2 improved on the new server (as expected), what surprises me is that 9.3 is slower on
theold one and even slower on the new, as shown in data below. 
>
>> +--------+-------+-------+
>> | Server |  9.2  |  9.3  |
>> +--------+-------+-------+
>> | Old    |   129 |   216 |
>> +--------+-------+-------+
>> | New    |   118 |   275 |
>> +--------+-------+-------+
>
>

>
> ==> Adrian
>> Hardware specification (on both servers)
> Processor:
> - New: Xeon E5-2430 2.20GHz
> - Old: Xeon X3470   2.93GHz
> Memory: 8 GBs
> Hard Drives: SSD
>
>> Minor Versions
> New server:
> PostgreSQL 9.2.6
> PostgreSQL 9.3.3
>
> Old server:
> PostgreSQL 9.2.3
> PostgreSQL 9.3.3
>
>> Explains (ANALYZE, BUFFERS)
> Old server:
> 9.2 - http://explain.depesz.com/s/bXIo
> 9.3 - http://explain.depesz.com/s/Vwt

The actual query text would be useful, unless I am missing how to see
that at explain.depesz.com. The reason I ask is one thing that stands
out is the increase in the Nested Loop Left Join from a count of  2 and
a time of 1.910ms in 9.2 to a count of 4 and a time of  12782.414 ms in
9.3 . There is also a Materialize time of 44236.592 ms in 9.3 that is
not there in 9.2. Those two increases alone account for most of the
difference.


>
> New server:
> I'll run them at night (about 12 hours from now), so that the regular use of the server don't interfere in the
results
>
>
> --
> Álvaro Nunes Melo    Atua Sistemas de Informação
> alvaro@atua.com.br   http://www.atua.com.br
> (54) 9976-0106       (54) 3045-4144
>
>

--
Adrian Klaver
adrian.klaver@aklaver.com