Re: R: Postgres 9.6.1 big slowdown by upgrading 8.4.22

On 01/07/2017 06:44 AM, Job wrote:
> Hi guys,
>
> First of all excuse me but i really do not explain the problem, sorry...
>
>>> Are you being serious? You're complaining about a "big slowdown" for a query that goes from 1.5ms to 4ms?
>>> What is the actual problem you're trying to solve? Because I don't see one in the above.
>
> Single query if fast both in 8.4.22 and 9.6.1, no problem.
>
> But the problem is not here!
>
> The big problem is the benchmark before put the system under production.
> We launch about 100/200 queries per second and we monitor with "top" the two machines.
> They are VM with 4 vCPU and 10Gb of RAM, with CentOS 7.2 64bit.
>
> This is what it happens:
>
> Postgres 8.4.22
> Medium average load 1.5/2.0
> Further queries respond very quickly
>
> Postgres 9.6.1
> Medium average load 18.0/20.0 !!
> Further queries are really very slow
> There is a bottle neck
>
> By removing *only* this condition in the query function:
> "exists ( select 1 from gruorari where gruorari.idgrucate=grucategorie.id and ( (('{'||gg_sett||'}')::int[] && array[EXTRACT(DOW FROM NOW())::int])='t' and now()::time between gruorari.dalle::time and gruorari.alle::time) )"

Without the rest of the query the above is somewhat difficult to
understand relative to your performance issue. The entire function would
be nice to see if that is possible?

Also is it possible to run the query that is in the function outside the
function as a stand alone query using explain analyze?

>
> The Postgres 9.6.1 machine average workload return at about 2.0/3.0!
>
> The problem is not the single query, but the massive queries!
>
> Thank you again and excuse me for my bad explanation!
>
> /F
>
>

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