Re: Postgres performance comparing GCP and AWS

Hi, Philip

We ran: EXPLAIN (FORMAT JSON) 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';

but it was really fast. I think the results were discarded.

AWS Execution time select without explain: 24.96505s (calculated in python
client)
AWS Execution time select with explain but without analyze: 0.03876s
(calculated in python client)

https://explain.depesz.com/s/5HRO

Thanks in advance

Em qui., 25 de fev. de 2021 às 15:13, Philip Semanchuk <
philip(at)americanefficient(dot)com> escreveu:

>
>
> > 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
> >
> >
>
>

--
*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/>