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.
Thanks in advance
Em qua., 24 de fev. de 2021 às 10:35, Julien Rouhaud <rjuju123@xxxxxxxxx> escreveu:
Hi,
On Wed, Feb 24, 2021 at 6:14 AM Maurici Meneghetti
<maurici.meneghetti@xxxxxxxxxxxxxxxxxxxx> 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.
