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. I know the query I'm testing could be improved, but I used it because it's not that fast that is hard to measure neither so slow (another report query took about 4 minutes on 9.2 and now takes 16 minutes on 9.3) that would take the whole day to run tests.
> And again, performance on 9.2 improved on the new server (as expected), what surprises me is that 9.3 is slower on the old 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(at)atua(dot)com(dot)br http://www.atua.com.br
> (54) 9976-0106 (54) 3045-4144
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com