Re: Postgres performance comparing GCP and AWS

> On Feb 24, 2021, at 10:11 AM, Igor Gois <igor(at)bixtecnologia(dot)com(dot)br> wrote:
>
> 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.

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

Hope this is useful.

Cheers
Philip

>
>
> 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 | Blog | LinkedIn | Facebook | Instagram
>
>