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 < phi...@americanefficient.com> escreveu: > > > > On Feb 24, 2021, at 10:11 AM, Igor Gois <i...@bixtecnologia.com.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 <rjuju...@gmail.com> > escreveu: > > Hi, > > > > On Wed, Feb 24, 2021 at 6:14 AM Maurici Meneghetti > > <maurici.meneghe...@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" = 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/>
