Postgres 9.6.1 big slowdown by upgrading 8.4.22

From: Job <Job(at)colliniconsulting(dot)it>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Postgres 9.6.1 big slowdown by upgrading 8.4.22
Date: 2017-01-05 17:18:53
Message-ID: 88EF58F000EC4B4684700C2AA3A73D7A08054EACC23D@W2008DC01.ColliniConsulting.lan
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rob Sargent 2017-01-05 17:28:57 Re: Postgres 9.6.1 big slowdown by upgrading 8.4.22
Previous Message Rob Sargent 2017-01-05 16:31:24 Re: COPY: row is too big