Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.60.0.2.21\))
Subject: Re: Postgres performance comparing GCP and AWS
From: Philip Semanchuk <philip@americanefficient.com>
In-Reply-To: 
 <CAH1yB+XWBG9DVRFg825TxvNr6stTWJxZsAZ05EZ8GG3oMk47dQ@mail.gmail.com>
Date: Thu, 25 Feb 2021 13:13:33 -0500
Cc: Julien Rouhaud <rjuju123@gmail.com>,
 Maurici Meneghetti <maurici.meneghetti@bixtecnologia.com.br>,
 pgsql-performance@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <4BD5DB5D-972B-4683-8E2B-88EEDBDFDC7D@americanefficient.com>
References: 
 <CAMzrzcaeYtkJN+tQWpKhpdaAeD-EjxJ3Pfcpfy-QsOKnrdy17w@mail.gmail.com>
 <CAOBaU_ZNF+uE8OED3GbLYEuJULJRAYcR1fkW=dFjB57HVr6P1g@mail.gmail.com>
 <CAH1yB+XWBG9DVRFg825TxvNr6stTWJxZsAZ05EZ8GG3oMk47dQ@mail.gmail.com>
To: Igor Gois <igor@bixtecnologia.com.br>
Precedence: bulk


> On Feb 24, 2021, at 10:11 AM, Igor Gois <igor@bixtecnologia.com.br> =
wrote:
>=20
> Hi, Julien
>=20
> Your hypothesis about network transfer makes sense. The query returns =
a big size byte array blobs.
>=20
> Is there a way to test the network speed against the instances? I have =
access to the network speed in gcp (5 Mb/s), but don't have access in =
aws rds.

Perhaps what you should run is EXPLAIN ANALYZE SELECT...? My =
understanding is that EXPLAIN ANALYZE executes the query but discards =
the results. That doesn=E2=80=99t tell you the network speed of  your =
AWS instance, but it does isolate the query execution speed (which is =
what I think you=E2=80=99re trying to measure) from the network speed.

Hope this is useful.

Cheers
Philip

>=20
>=20
> Em qua., 24 de fev. de 2021 =C3=A0s 10:35, Julien Rouhaud =
<rjuju123@gmail.com> escreveu:
> Hi,
>=20
> On Wed, Feb 24, 2021 at 6:14 AM Maurici Meneghetti
> <maurici.meneghetti@bixtecnologia.com.br> wrote:
> >
> > I have 2 postgres instances created from the same dump (backup), one =
on a GCP VM and the other on AWS RDS. The first instance takes 18 =
minutes and the second one takes less than 20s to run this simples =
query:
> > SELECT "Id", "DateTime", "SignalRegisterId", "Raw" FROM =
"SignalRecordsBlobs" WHERE "SignalSettingId" =3D 103 AND "DateTime" =
BETWEEN '2019-11-28T14:00:12.540200000' AND =
'2020-07-23T21:12:32.249000000';
> > I=E2=80=99ve run this query a few times to make sure both should be =
reading data from cache.
> > I expect my postgres on GPC to be at least similar to the one =
managed by AWS RDS so that I can work on improvements parallelly and =
compare.
> >
> > DETAILS:
> > [...]
> > Planning time: 456.315 ms
> > Execution time: 776.976 ms
> >
> > Query explain for Postgres on AWS RDS:
> > [...]
> > Planning time: 0.407 ms
> > Execution time: 14.87 ms
>=20
> Those queries were executed in respectively ~1s and ~15ms (one thing
> to note is that the slower one had less data in cache, which may or
> may note account for the difference).  Does those plans reflect the
> reality of your slow executions?  If yes it's likely due to quite slow
> network transfer.  Otherwise we would need an explain plan from the
> slow execution, for which auto_explain can help you.  See
> https://www.postgresql.org/docs/11/auto-explain.html for more details.
>=20
>=20
> --=20
> Att,
>=20
> Igor Gois | S=C3=B3cio Consultor
> (48) 99169-9889 | Skype: igor_msg
> Site | Blog | LinkedIn | Facebook | Instagram
>=20
>  =20