MIME-Version: 1.0
In-Reply-To: 
 <CAFgnfSf271DtZ8MRbrRNPMKYZjahEzF=q7vMt0+byxfL+NOreA@mail.gmail.com>
References: 
 <CAFgnfSdS1twkog+pF_2v3o3e9RdSn2EuLzoF_z+95J3yvY=HnA@mail.gmail.com>
 <CAFgnfSf8jb__cmP_2ibet_-EuS=5YogyEhbme7qvtzoLn+XURA@mail.gmail.com>
 <CAFgnfSfPEUMRGa-sps_42EKRT+2c4EDsj87k16N7envzc8wfZw@mail.gmail.com>
 <CAFgnfScuLribfQUDS2L7ygr_m9prtorjTN5DzLZoe-cdJro9WQ@mail.gmail.com>
 <CAFgnfSckWF=n89rfj5NxxyTrt6OFqNHPidjfwvUYMEZATv-89A@mail.gmail.com>
 <CAFgnfSe6sh+cfmX=6UfnmkGqJhirHTFOgFvZPKYO2=keeUz8RQ@mail.gmail.com>
 <CAFgnfSeeumftnG=jAhS1hCah3KUvWQNF4_9RySgZ_WfOvKsOYQ@mail.gmail.com>
 <CAFgnfSe-ki5nSzt2MeeFPefi_kM3ffRJbh_xOQiJvdn-mWFYEg@mail.gmail.com>
 <CAFgnfSf-bxBBBa0wW2nMsufQZ+g1BBy7rnO81EWvcF8BJev2tg@mail.gmail.com>
 <CAFgnfSc7kYKvhu4oKxD-VQF4WjDE2hc+f3owuHnMKBYUxdAMCw@mail.gmail.com>
 <CAFgnfSdzy2Q8AM9iE-qRwOO3U-kqGjDHu3aQPjBZni0906Hmsw@mail.gmail.com>
 <CAFgnfSci8qbUmP62zXh-JnmDqd1LkBHpjB-UK+r-635v5FDqBQ@mail.gmail.com>
 <CAFgnfSf-_6xE-ObbdUF8Ysk+LigpKkrn5Gom1knoFdF8X2iKCw@mail.gmail.com>
 <CAFgnfSfsk6ZXA3CnQBL5hzpk_b77CxsvvEO2PasExGybnCD9XQ@mail.gmail.com>
 <CAFgnfSeSLAL5M7q5LzZ3r93R8pQ0oBy0yi8_7a8JjQabfGGn9w@mail.gmail.com>
 <CAFgnfScCosFudCvPTqxVGLZKudmcZZ=p6M+0-MhktAYmtujBcQ@mail.gmail.com>
 <CAFgnfSea1wtkDzM6-a=HNkk50DPaWGPDoEND++PLeXWq=2aO-w@mail.gmail.com>
 <CAFgnfSfoBzVL2N=S=X1qBL9777qdaW+Z_2qUd4VgiyitTVzZog@mail.gmail.com>
 <CAFgnfSem6f4exu5zOd9wge6mfD2a+xkpex1OPVp7_1goWPFn_Q@mail.gmail.com>
 <CAFgnfSccFUfXH02j-hiEACiY8w0Qzw7guD6FVAot+VaJ5MsWhQ@mail.gmail.com>
 <CAFgnfSf271DtZ8MRbrRNPMKYZjahEzF=q7vMt0+byxfL+NOreA@mail.gmail.com>
From: p kirti <kirtip92@gmail.com>
Date: Fri, 10 Nov 2017 16:28:41 +0530
Message-ID: 
 <CAFgnfScjp=-joWVAyXE+v+zDRZ98SvcovUinmKLx2h2kGq6ePg@mail.gmail.com>
Subject: DB slowness after upgrade from Postgres 9.1 to 9.4
To: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary="94eb2c0832dccdfee8055d9ecde1"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--94eb2c0832dccdfee8055d9ecde1
Content-Type: text/plain; charset="UTF-8"

Hi all,

We have recently upgraded our project with a huge DB from Postgres v9.1 to
v9.4. The whole system performance has degraded alarmingly after the
upgrade. Simple operations that were taking only a few seconds in Postgres
9.1 are now taking minutes of time.

The problem is not specific to one query orany particular kind of query.
Its been generic and overall system has become very slow.

We tried running 'VACUUM ANALYZE' on the DB and that seemed to be helpful
too. But the improvement after this is nowhere close to the performance we
had in 9.1.

We tried changing some of the performance parameters in the
postgres.confirm as follows (our Postgres server has an 8GB RAM) -

shared_buffers = 200MB
maintenance_work_mem = 1000MB
default_statistics_target = 1000
effective_cache_size = 4000MB
And these made absolutely no difference to the query execution time.

The strangest part of the problem is when I EXPLAIN ANALYZE the same query
multiple times in the same Postgres server, it gives me different execution
times every time ranging from 45 ms to 181 ms.

We are absolutely clueless on how to proceed. Any help would be greatly
appreciated.
Thanks in advance.

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

<div dir=3D"auto">Hi all,<div dir=3D"auto"><br></div><div dir=3D"auto">We h=
ave recently upgraded our project with a huge DB from Postgres v9.1 to v9.4=
. The whole system performance has degraded alarmingly after the upgrade. S=
imple operations that were taking only a few seconds in Postgres 9.1 are no=
w taking minutes of time.</div><div dir=3D"auto"><br></div><div dir=3D"auto=
">The problem is not specific to one query orany particular kind of query. =
Its been generic and overall system has become very slow.</div><div dir=3D"=
auto"><br></div><div dir=3D"auto">We tried running &#39;VACUUM ANALYZE&#39;=
 on the DB and that seemed to be helpful too. But the improvement after thi=
s is nowhere close to the performance we had in 9.1.</div><div dir=3D"auto"=
><br></div><div dir=3D"auto">We tried changing some of the performance para=
meters in the postgres.confirm as follows (our Postgres server has an 8GB R=
AM) -</div><div dir=3D"auto"><br></div><div dir=3D"auto">shared_buffers =3D=
 200MB</div><div dir=3D"auto">maintenance_work_mem =3D 1000MB</div><div dir=
=3D"auto">default_statistics_target =3D 1000</div><div dir=3D"auto">effecti=
ve_cache_size =3D 4000MB</div><div dir=3D"auto">And these made absolutely n=
o difference to the query execution time.</div><div dir=3D"auto"><br></div>=
<div dir=3D"auto">The strangest part of the problem is when I EXPLAIN ANALY=
ZE the same query multiple times in the same Postgres server, it gives me d=
ifferent execution times every time ranging from 45 ms to 181 ms.</div><div=
 dir=3D"auto"><br></div><div dir=3D"auto">We are absolutely clueless on how=
 to proceed. Any help would be greatly appreciated.</div><div dir=3D"auto">=
Thanks in advance.</div></div>

--94eb2c0832dccdfee8055d9ecde1--