Re: [GENERAL] Postgres 9.6.1 big slowdown by upgrading 8.4.22

Hello guys,

 

a very strange thing: after upgrading from 8.4.22 to 9.6.1 i noticed, under heavy beanchmarks, a really slowdown of Postgresql 9.6.1, with the machine really "without breath".

By replacing Postgresql 8.4.22 evberything returns working fine.

 

With three days of investigation, i come at a function with lots of joing and conditions.

 

Only by removing this condition:

"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) )"

 

The benchmark with Postgresql 9.6.1 version are now very fast.

 

The table metnioned by the query has got indexes:

 

 id        | numeric(1000,1)   | not null default function_get_next_sequence('gr
uorari_id_seq'::text)
 idgrucate | numeric(1000,1)   |
 dalle     | character varying |
 alle      | character varying |
 gg_sett   | character varying |
 azione    | character varying |
Indexes:
    "keygruorari" PRIMARY KEY, btree (id)
    "alle_idx" btree (alle)
    "dalle_idx" btree (dalle)
    "gg_sett_idx" btree (gg_sett)
    "idgrucate_idx" btree (idgrucate)

What is strange, is that with 8.4.22 version there is no problem, but there is something that does not deal with 9.6.1 version.

 

Could you please help me?

THANK YOU!
/F

You might want to include the query plans for each server