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(DOWFROM NOW())::int])='t' and now()::time between gruorari.dalle::time and gruorari.alle::time) )"
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