Same postgres query in two different instances with the same data but with different times

Question

I have the same postgres query running in two different instances restored with the same dump file:

one instance in aws rds => https://explain.depesz.com/s/USMO ('PostgreSQL 11.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit')
one instance in compute engine vm in gcp => https://explain.depesz.com/s/LTUL ('PostgreSQL 11.10 (Debian 11.10-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit')

But the query in 1) (40s) is faster than 2) (400s) (execution time calculated in python)

What I tried:

What are the main reasons for this?

My main hypothesis now is the network traffic. How can I test that?

both have the same postgres version and hardware configuration
I am not sure, but these times are the first run of each query and cache (maybe) is not involved
traceroute 1) = 13.864 ms / traceroute 2) = 32.469 ms

Besides the configuration issue, row estimates are wrong in one of the queries. Did you run ANALYZE? — bobflux, Commented Feb 24, 2021 at 16:03
@bobflux, yes. the query is: EXPLAIN (FORMAT JSON, ANALYZE, BUFFERS) 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'; — Igor Gois, Commented Feb 25, 2021 at 16:05
Postgres execution times are 14.87 ms / 776.976 ms. The huge rest of 40s / 400s is outside of Postgres' jurisdiction. Also, Postgres 11 has reached EOL in 2023. Use a current version for your tests. Either way, a multicolumn index on ("SignalSettingId", "DateTime") should help (but not much for the given filter values). — Erwin Brandstetter, Commented Oct 16 at 23:28

Burak Yurdakul · Accepted Answer · 2021-02-23 20:59:49Z

0

The difference is shared hits.

Shared hit means it read the blocks from ram (shared buffers).

Check for shared_buffers parameter on both servers.

answered Feb 23, 2021 at 20:59

Burak Yurdakul

1017 bronze badges

you were right. The shared_buffers were different. I changed and tested with the same shared_buffers, min_wal_size and max_wal_size but the results were the same. Any idea how to test the network traffic? It turns out that this query returns a big size byte array. Thanks for your time
– Igor Gois
Commented Feb 24, 2021 at 14:56
What was your shared_buffer setting? You should also set effective_cache_size correctly. Run the query twice. Second time should be faster. Also check work_mem parameter too.
– Burak Yurdakul
Commented Feb 24, 2021 at 15:51
the shared_buffer was 128 MB and now is 950 MB. The second time the execution time was 100 ms faster, but the whole execution (run query, download data, show results) in the client was the same. The work_mem is 2.5 MB is aws and is commented in the gcp (#work_mem = 4MB)
– Igor Gois
Commented Feb 25, 2021 at 16:15
If you have access to server run the query there and check for time. If not create a table for the result of your query (create table dummy as select * from ....) and directly query it. The difference between those queries will tell network delay.
– Burak Yurdakul
Commented Feb 26, 2021 at 8:27

Add a comment |

1 Answer 1