Re: Postgres performance comparing GCP and AWS

Hi, Julien

Your hypothesis about network transfer makes sense. The query returns a big
size byte array blobs.

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.

[image: image.png]

Thanks in advance

Em qua., 24 de fev. de 2021 às 10:35, Julien Rouhaud <rjuju123(at)gmail(dot)com>
escreveu:

> Hi,
>
> On Wed, Feb 24, 2021 at 6:14 AM Maurici Meneghetti
> <maurici(dot)meneghetti(at)bixtecnologia(dot)com(dot)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" = 103 AND "DateTime" BETWEEN
> '2019-11-28T14:00:12.540200000' AND '2020-07-23T21:12:32.249000000';
> > I’ve 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
>
> 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.
>

--
*Att,*

*Igor Gois | Sócio Consultor*
(48) 99169-9889 | Skype: igor_msg
Site <https://bixtecnologia.com.br/>| Blog
<https://www.bixtecnologia.com.br/blog/> | LinkedIn
<https://www.linkedin.com/company/bixtecnologia/>| Facebook
<https://www.facebook.com/bix.tecnologia>| Instagram
<https://www.instagram.com/bixtecnologia/>