MIME-Version: 1.0
From: =?UTF-8?Q?Fl=C3=A1vio_Henrique?= <yoshimit@gmail.com>
Date: Tue, 27 Dec 2016 21:50:05 -0200
Message-ID: 
 <CAOGex3nXTRPZTD-KeoSwD=bj62hQrMK+6h30u09srV71sePqUA@mail.gmail.com>
Subject: Slow query after 9.3 to 9.6 migration
To: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=94eb2c19031c5fd12a0544ac85a6
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--94eb2c19031c5fd12a0544ac85a6
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hi there, fellow experts!

I need an advice with query that became slower after 9.3 to 9.6 migration.

First of all, I'm from the dev team.

Before migration, we (programmers) made some modifications on query bring
it's average time from 8s to 2-3s.

As this query is the most executed on our system (it builds the user panel
to work), every bit that we can squeeze from it will be nice.

Now, after server migration to 9.6 we're experiencing bad times with this
query again.

Unfortunately, I don't have the old query plain (9.3 version) to show you,
but in the actual version (9.6) I can see some buffers written that tells
me that something is wrong.

Our server has 250GB of memory available, but the database team says that
they can't do nothing to make this query better. I'm not sure, as some
buffers are written on disk.

Any tip/help will be much appreciated (even from the query side).

Thank you!

The query plan: https://explain.depesz.com/s/5KMn

Note: I tried to add index on kilo_victor table already, but Postgresql
still thinks that is better to do a seq scan.


Fl=C3=A1vio Henrique

--94eb2c19031c5fd12a0544ac85a6
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi there, fellow experts!<div><br></div><div>I need an adv=
ice with query that became slower after 9.3 to 9.6 migration.</div><div><br=
></div><div>First of all, I&#39;m from the dev team.</div><div><br></div><d=
iv>Before migration, we (programmers) made some modifications on query brin=
g it&#39;s average time from 8s to 2-3s.</div><div><br></div><div>As this q=
uery is the most executed on our system (it builds the user panel to work),=
 every bit that we can squeeze from it will be nice.</div><div><br></div><d=
iv>Now, after server migration to 9.6 we&#39;re experiencing bad times with=
 this query again.</div><div><br></div><div>Unfortunately, I don&#39;t have=
 the old query plain (9.3 version) to show you, but in the actual version (=
9.6) I can see some buffers written that tells me that something is wrong.<=
/div><div><br></div><div>Our server has 250GB of memory available, but the =
database team says that they can&#39;t do nothing to make this query better=
. I&#39;m not sure, as some buffers are written on disk.</div><div><br></di=
v><div>Any tip/help will be much appreciated (even from the query side).</d=
iv><div><br></div><div>Thank you!</div><div><br></div><div>The query plan:=
=C2=A0<a href=3D"https://explain.depesz.com/s/5KMn">https://explain.depesz.=
com/s/5KMn</a></div><div><br></div><div>Note: I tried to add index on kilo_=
victor table already, but Postgresql still thinks that is better to do a se=
q scan.</div><div><br></div><div><br clear=3D"all"><div><div class=3D"gmail=
_signature"><div>Fl=C3=A1vio Henrique</div></div></div>
</div></div>

--94eb2c19031c5fd12a0544ac85a6--